vshersh
Full Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
How to Create a Database in the Unix Environment
PURPOSE
This entry includes the following four methods for creating a database
within the UNIX environment and Oracle7:
Method I - Creating the Database Manually
Method II - Using the CRDBXXX.SQL Script
Method III - Using ORAINST
Method IV - Using the UNIX "CP" Command
SCOPE & APPLICATION
To help ensure a better understanding of the corresponding created
database files, it is recommended that the following documentation
is also referenced:
Administrator's Guide, Creating A Database: Chapter 2
SQL Language Reference Manual, Create Database Command
Method I - Creating the Database Manually
Setting the Unix Enviroment
When creating a database, Oracle looks at the environment to see which
database to create. Therefore, it is essential that before creating a
database your environment is set with the database name. If you are
creating a second database, be sure to use a new database name.
The following environment variables need to be set:
ORACLE_SID - set to the database name you wish to create
ORACLE_HOME - set to full pathname of the Oracle system home directory
PATH - needs to include $ORACLE_HOME/bin
To set your Unix environment use the following commands depending on the Unix
shell you are using:
sh - ORACLE_SID XXX;export ORACLE_SID
csh - setenv ORACLE_SID XXX
Check to be sure it was changed:
echo $ORACLE_SID
<NEW_NAME>
NOTE: Not changing the ORACLE_SID environment and running the create
database may wipe out your existing database and all of its data.
Creating the Database
This method involves typing the create database statement within SQL*DBA
or Server Manager. Using this method allows for more flexibility such as
specifying the MAXDATAFILES parameter or specifying multiple SYSTEM tablespace
database files. However by doing this manually there is also a greater
possibility of syntax errors. In addition there is no logfile automatically
created to record the options which have been specified.
Steps for Method I:
1. Set the UNIX environment (SEE SETTING UNIX ENVIRONMENT SECTION ABOVE).
2. Create new init<sid>.ora and config<sid>.ora files for your new
database by copying the default ones provided by Oracle:
% cp $ORACLE_HOME/dbs/init<sid>.ora $ORACLE_HOME/dbs/initNEW_NAME.ora
% cp $ORACLE_HOME/dbs/config<sid>.ora
$ORACLE_HOME/dbs/configNEW_NAME.ora
3. Change db_name parameter in the new initNEW_NAME.ora from DEFAULT to
the new database name and specify the control file's name and location
(this is often found in the config<sid>.ora file).
NOTE: If the database name is not specified when using the create
database statement, the name specified for the initialization
parameter "DB_NAME" in the init<sid>.ora file is used.
4. Startup SQL*DBA or Server Manager in line mode:
sqldba lmode=y
or
svrmgrl
Note: We will use Server Manager in our examples.
If you are using SQL*DBA (versions prior to V7.3.x), the
commands are the same.
5. Connect to the instance, and startup in a 'NOMOUNT' state:
SVRMGR> connect internal
Connected
SVRMGR> startup nomount
ORACLE instance started
6. Refer to the SQL Language Reference Guide for the 'CREATE DATABASE'
statement syntax - page 4-148.
Here is a sample create database statement:
SVRMGR> create database NEW_NAME
2> logfile group 1 '$ORACLE_HOME/dbs/log1NEW_NAME.dbf' size 500K,
3> group 2 '$ORACLE_HOME/dbs/log2NEW_NAME.dbf' size 500K
4> datafile '$ORACLE_HOME/dbs/dbsNEW_NAME.dbf' size 20M
5> maxdatafiles 50;
7. Once completed run catalog.sql located in the oracle_home/rdbms/admin
directory. This script must be run under the 'SYS' user or connected
'internal'.
NOTE: If such products as replication, parallel server or procedural
option are installed, the following additional scripts must be run:
SVRMGR>@oracle_home/rdbms/admin/catalog.sql
In addition, if procedural option is installed:
SVRMGR>@oracle_home/rdbms/admin/catproc.sql
In addition, if replication is installed:
SVRMGR>@oracle_home/rdbms/admin/catrep.sql
In addition, if parallel server in installed:
SVRMGR>@oracle_home/rdbms/admin/catparr.sql
8. After the database has been created, the SYSTEM tablespace and SYSTEM
rollback segment will exist. However, a second rollback segment in the
SYSTEM tablespace must be created and activated before any other
tablespaces can be created in the database (Refer to SQL Language
Reference Manual for full syntax).
Creating the rollback segment:
SYNTAX: CREATE ROLLBACK SEGMENT system2
TABLESPACE SYSTEM
STORAGE (...);
Activating the rollback segment:
SYNTAX: ALTER ROLLBACK SEGMENT system2 ONLINE;
You will then need to create a new tablespace. This tablespace should only
contain rollback segments. For example, to create a tablespace of size 20M
with the name RBS:
SVRMGR> create tablespace RBS datafile '<datafile name>' size <20M>;
You will then need to create additional rollback segments and bring
them online. Once at least one other rollback segment is online, you
should then drop the second rollback segment 'system2' created above.
SVRMGR> create rollback segment <name1> tablespace RBS size <n>;
SVRMGR> create rollback segment <name2> tablespace RBS size <n>;
SVRMGR> alter rollback segment <name1> online;
SVRMGR> alter rollback segment <name2> online;
SVRMGR> alter rollback segment system2 offline;
SVRMGR> drop rollback segment system2 ;
If the rollback segments were created as private rollback segments,
you will need to edit the init<SID>.ora parameter file and add the line:
rollback_segments = (<list of rollback segments>)
so that the next time the database is started they are brought online
automatically.
9. Run the pupbld.sql script
% cd $ORACLE_HOMEsqlplus/admin
% svrmgrl
SVRMGR> connect system/<password>
SVRMGR> @pupbld
10. Modify the /etc/oratab file by adding the new database name. This
is used by dbstart to startup all databases with a 'Y' entry in this
file. (See page 4-17 of Oracle for Unix technical Reference Guide).
[Top]
Method II - Using the CRDBXXX.SQL Script
Setting the Unix Environment
When creating a database, Oracle looks at the environment to see which
database to create. Therefore, it is essential that before creating a
database your environment is set with the database name. If you are
creating a second database, be sure to use a new database name.
The following environment variables need to be set:
ORACLE_SID - set to the database name you wish to create
ORACLE_HOME - set to full pathname of the Oracle system home directory
PATH - needs to include $ORACLE_HOME/bin
To set your Unix environment use the following commands depending on the Unix
shell you are using:
sh - ORACLE_SID XXX;export ORACLE_SID
csh - setenv ORACLE_SID XXX
Check to be sure it was changed:
echo $ORACLE_SID
<NEW_NAME>
NOTE: Not changing the ORACLE_SID environment and running the create
database may wipe out your existing database and all of its data.
Creating the Database
This method assumes that you have created *at least one database through the
install* and therefore have a 'crdbXXX.sql' script (XXX being the created
database name). With this option you copy this sql script and make the
necessary modifications. This option allows you to make whatever changes
are desired, such as the MAXDATAFILES parameter or specifying multiple
SYSTEM tablespace database files.
Steps for Method II:
1. Set the UNIX environment (SEE SETTING UNIX ENVIRONMENT SECTION ABOVE).
2. Create new init<sid>.ora and config<sid>.ora files for your new database by
copying the default ones provided by Oracle:
% cp $ORACLE_HOME/dbs/init<sid>.ora $ORACLE_HOME/dbs/initNEW_NAME.ora
% cp $ORACLE_HOME/dbs/config<sid>.ora $ORACLE_HOME/dbs/configNEW_NAME.ora
3. Change db_name parameter in the new initNEW_NAME.ora from DEFAULT to
the new database name and specify the control file's name and location
(this is often found in the config<sid>.ora file).
NOTE: If the database name is not specified when using the create
database statement, the name specified for the initialization
parameter "DB_NAME" in the init<sid>.ora file is used.
4. Create a new crdbXXX.sql for your new database by copying the existing one
in the ORACLE_HOME/dbs directory. NOTE: If you have never created a
database through the install, you CANNOT use this method.
5. Modify the crdb<dbname>.sql and change the system datafile,
logfiles, database name, and pfile="path/initNEW_NAME.ora"
6. Run the modified create script:
$ sqldba lmode=y
SQLDBA> @crdbXXX
or
$ svrmgrl
SVRMGR> @crdbXXX
Note: We will use Server Manager in our examples.
If you are using SQL*DBA (versions prior to V7.3.x),
the commands are the same.
OPTIONAL!!! If you wish only to create the system tablespace, skip to step 9.
Only go through steps 7 and 8 if you wish to also create the standard
tablespaces the install creates.
7. Create a new crdb2<dbname>.sql by coping an existing one.
Make the necessary changes to is, as above, such as datafile names, sizes,
etc. However, this applies to the standard tablespaces which are created
by the install, including RBS, USERS, TEMP, TOOLS.
8. Run the modified script, skip to step 10:
SVRMGR> @crdb2XXX
9. Run catalog.sql found in the ORACLE_HOME/rdbms/admin directory.
This script should be run while connected internal after the
database is successfully created.
NOTE: this step is only necessary if you skipped steps 7 and 8.
SVRMGR>@oracle_home/rdbms/admin/catalog.sql
10. If such products as replication, parallel server or procedural
option are installed, the following additional scripts must be run:
If procedural option is installed:
SVRMGR>@oracle_home/rdbms/admin/catproc.sql
If replication is installed:
SVRMGR>@oracle_home/rdbms/admin/catrep.sql
If parallel server in installed:
SVRMGR>@oracle_home/rdbms/admin/catparr.sql
11. Run the pupbld.sql script
% cd $ORACLE_HOMEsqlplus/admin
% svrmgrl
SVRMGR> connect system/<password>
SVRMGR> @pupbld
[Top]
Method III - Using ORAINST
Setting the Unix Environment
When creating a database, Oracle looks at the environment to see which
database to create. Therefore, it is essential that before creating a
database your environment is set with the database name. If you are
creating a second database, be sure to use a new database name.
The following environment variables need to be set:
ORACLE_SID - set to the database name you wish to create
ORACLE_HOME - set to full pathname of the Oracle system home directory
PATH - needs to include $ORACLE_HOME/bin
To set your Unix environment use the following commands depending on the Unix
shell you are using:
sh - ORACLE_SID XXX;export ORACLE_SID
csh - setenv ORACLE_SID XXX
Check to be sure it was changed:
echo $ORACLE_SID
<NEW_NAME>
NOTE: Not changing the ORACLE_SID environment and running the create
database may wipe out your existing database and all of its data.
Creating the Database
Note: This method *cannot* be used for V7.3.2.1 installs,
but *can* be used for versions 7.3.2.2 and up.
With this method a database can be created using the orainst.
Since this process is menu driven it is easy to use, in addition
it will run necessary scripts for any product selected. However,
this method does not have all database options available, such as
specifying a higher MAXDATAFILES value. In addition, if this method is
chosen, you must create all the standard non-system tablespaces.
Here are the steps for Method III:
1. Make a copy of your existing config.ora file in the $ORACLE_HOME/dbs
directory. THIS FILE WILL BE OVERWRITTEN WITH THIS METHOD.
% cp $ORACLE_HOME/dbs/config.ora $ORACLE_HOME/dbs/configORIG_DB.ora
2. Run orainst from the $ORACLE_HOME/install directory.
(Note: This may also be in the $ORACLE_HOME/orainst directory.)
3. When running orainst a logfile will be generated. Specify a name
for this logfile so that this file can be easily found and accessed.
4. From the Install Actions choose:
'Create New Database Objects'
5. Enter the new SID for this database.
6. From the Select Available Products choose:
'Oracle7 Server (RDBMS)'
In addition select all other appropriate products that you will
wish to use with the new database. (Note: This option will
not reinstall the product software.)
Once you select all the products, tab to <install> and accept.
7. Continue the installation by answering the appropriate questions.
8. Orainst will let you know what step it is processing. Once it is done,
in addition to a database, you will also have a crdb<DBNAME>.sql discussed
in Method II above. Be sure to check the log specified in step 2
to be sure no errors occurred.
9. Clear up the config.ora confusion:
a. Copy config.ora to new database name
% cp $ORACLE_HOME/dbs/config.ora $ORACLE_HOME/dbs/configNEW_DB.ora
b. Edit initORIGINAL_DB.ora change the ifile entry from config.ora to
configORIG_DB.ora
c. Edit initNEW_DB.ora change the ifile entry from config.ora to
configNEW_DB.ora
[Top]
Method IV - Using the UNIX "CP" Command
Setting the Unix Environment
When creating a database, Oracle looks at the environment to see which
database to create. Therefore, it is essential that before creating a
database your environment is set with the database name. If you are
creating a second database, be sure to use a new database name.
The following environment variables need to be set:
ORACLE_SID - set to the database name you wish to create
ORACLE_HOME - set to full pathname of the Oracle system home directory
PATH - needs to include $ORACLE_HOME/bin
To set your Unix environment use the following commands depending on the Unix
shell you are using:
sh - ORACLE_SID XXX;export ORACLE_SID
csh - setenv ORACLE_SID XXX
Check to be sure it was changed:
echo $ORACLE_SID
<NEW_NAME>
NOTE: Not changing the ORACLE_SID environment and running the create
database may wipe out your existing database and all of its data.
Creating the Database
The following procedure describes how to create a second database and instance
from an existing database using the UNIX 'cp' command and Oracle's 'create
control file statement'. The second instance can also be created by restoring
datafiles from a cold backup.
Steps for Method IV:
1. Set the UNIX environment variable to the current database, DatabaseA.
(SEE SETTING UNIX ENVIRONMENT SECTION ABOVE).
2. Backup the control file to trace From DatabaseA. The trace file
(ora_xxx.trc) is located in the directory defined by USER_DUMP_DEST.
SQLDBA>alter database backup controlfile to trace resetlogs;
3. Modify trace file script by doing the following:
a. Remove the header information.
b. Modify the LOGFILE to point to the new names of the redo logfiles.
c. Modify the DATAFILE to point to the new names of the data files.
d. Modify the create controlfile statement to:
CREATE CONTROLFILE SET DATABASE DatabaseB RESETLOGS NOARCHIVELOG
or if the database is in archive log mode:
CREATE CONTROLFILE SET DATABASE DatabaseB RESETLOGS ARCHIVELOG
e. Remove everything from script after the end of the
create controlfile statement.
4. Shutdown DatabaseA. Make sure that this is a NORMAL or IMMEDIATE
shutdown. DatabaseA MUST NOT be shutdown using SHUTDOWN ABORT.
5. Copy DatabaseA database files and redo logfiles (dbf,log) to the DatabaseB
directories. The files may alternatively be restored from a cold backup.
6. Copy DatabaseA parameter files to DatabaseB parameter files by
doing the following:
a. Copy initDatabaseA.ora to initDatabaseB.ora.
b. Copy configDatabaseA.ora to configDatabaseB.ora.
c. Modify initDatabaseB.ora to point to the config.ora of DatabaseB.
d. Modify the following parameters in initDatabaseB.ora:
IFILE = configDatabaseB.ora
CONTROL_FILE = new control files names
DB_NAME = new database name
Comment out the parameter REMOTE_LOGIN_PASSWORDFILE if it is set
to EXCLUSIVE.
Be sure to copy the files using the same Unix user as the current owner.
If the files have the wrong permissions, when attempting to create
the controlfile you are likely to encounter ORA-1503, ORA-1161,
ORA-1110.
7. Set the following environment variables:
ORACLE_SID = DatabaseB
ORACLE_HOME = Full pathname of home directory of DatabaseB
8. Run the trace file script from 3) via SVRMGRL as a privileged user
(connect internal in Oracle 8.1 or earlier).
After the CREATE CONTROLFILE statement has run, you will need to
take one of the following actions:
8a. If this is a copy from a cold backup, issue the command:
ALTER DATABASE OPEN RESETLOGS;
8b. If the files are from a backup, and you want to roll forward, perform
standard media recovery. When the recovery is successfully complete,
issue the command:
ALTER DATABASE OPEN RESETLOGS;
8c. If the files are from an aborted instance, you will need to perform
a recovery. The recovery will need to roll forward through the online
redo logs copied over with the database. The controlfile will not
indicate which online redo log is current. A list of online logs
which will indicate which redo log is current can be obtained using
the command:
SELECT member FROM v$logfile;
Issue the following command:
RECOVER DATABASE USING BACKUP CONTROLFILE
At the recovery prompt apply the online logs in sequence by typing the
unquoted full path and file name of the online redo log to apply. After
applying the current redo log, you will receive the message 'Media
Recovery Complete'. Once the media recovery is complete issue the
command:
ALTER DATABASE OPEN RESETLOGS;
The database is now up and open under the new sid. DatabaseA may be
restarted.
alter database rename global_name to DatabaseB.WORLD;
9. Modify other utilities:
SQLNET (i.e. listener.ora, tnsnames.ora)
AUTOMATIC STARTUP (i.e. oratab)
[Top]
Search Words:
db, 2nd, instance, second, dbase, manual, copy, create, database, methods,
creation
@ create database create copy creation instance crdb create database cp
@ create database create copy creation instance crdb create database cp
@ create database create copy creation instance crdb create database cp
@ create database create copy creation instance crdb create database cp
@ create database create copy creation instance crdb create database cp
@ create database create copy creation instance crdb create database cp
@ create database create database create database create database create database
@ create database create database create database create database create database
@ create database create database create database create database create database
@ create database create database create database create database create database .
|
