** Credit Myself&Web
Concept
clone db to new sid
- trace controlfile and edit to new path except database name and add "SET" to database name
alter database backup controlfile to trace as '/some/arbitrary/path' reuse;
- alter database backup controlfile to trace;
- copy all datafile to new path
- copy init file to new name and edit to new path but
db_name and instance_name is original
- make core dir,bdump and .., to new path
- export oracle_sid to new new
- create orapasswd to new name
- start up nomount mode and run create new controlfile
- alter database open resetlogs
# if error in Unable to create logfile file name to set
alter system set db_create_online_log_dest_1='/data1/oradata/newdb2/' scope=both;
# oracle will not the same sid
# difference dbid but same sid is no
# same sid difference instance(rac)
# same sid is start in one instance other to exclusive mode
Step-by-Step
Cloning Procedures
1. Make a full backup of the source database
# stop the listener
$ lsnrctl stop
# login the source database as sysdba
# shutdown the database for a full backup
$ export ORACLE_SID=PROD
$ sqlplus internal
SQL> shutdown immediate
# backup the config files
$ cd /demo/oracle/db/8.1.7/admin
$ tar -czvf PROD_config.tar.gz PROD
# backup the datafiles
$ cd /demo/oracle/db/8.1.7/oradata
$ tar -czvf PROD_data.tar.gz PROD
2. Create the control files script
# startup the source database
$ sqlplus internal
SQL> startup
# create the control files script for the target database
# the file can be found at /demo/oracle/db/8.1.7/admin/PROD/udump
# for example, ora_17832.trc
SQL> alter database backup controlfile to trace resetlogs;
# shutdown the database for cloning
SQL> shutdown immediate
# copy the control file script for later use
$ cd /demo/oracle/db/8.1.7/admin/PROD/udump
$ cp ora_17832.trc /demo/oracle/db/TEST_ctl.sql
3. Copy the source database to the target database
# copy the config files
$ cd /demo/oracle/db/8.1.7/admin
$ cp -r PROD TEST # create the initTEST.ora
$ cd /demo/oracle/db/8.1.7/admin/TEST/pfile
$ mv initPROD.ora initTEST.ora
# copy the datafiles
$ cd /demo/oracle/db/8.1.7/oradata
$ cp -r PROD TEST
# remove the original control files
# we will re-create them later
$ cd /demo/oracle/db/8.1.7/oradata/TEST
$ rm control01.ctl control02.ctl control03.ctl
4. Prepare the initTEST.ora
# edit the initTEST.ora
# 1. change all PROD to TEST
# 2. update the file locations
# 3. set remote_login_passwordfile = none
$ cd /demo/oracle/db/8.1.7/admin/TEST/pfile
$ vi initTEST.ora
# create a link for initTEST.ora at /demo/oracle/db/8.1.7/dbs
# oracle will use this file to start the instance
$ cd /demo/oracle/db/8.1.7/dbs
$ ln -s /demo/oracle/db/8.1.7/admin/TEST/pfile/initTEST.ora initTEST.ora
5. Prepare the control file script
# edit the TEST_ctl.sql
$ cd /demo/oracle/db
$ vi TEST_ctl.ora
# 1. remove comment lines
# 2. change all PROD to TEST
# 3. update the file locations
# 4. change the following line
# CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG
# to
# CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
# 5. remove the following line
# RECOVER DATABASE USING BACKUP CONTROLFILE
# after edit, the TEST_ctl.sql should look like this
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 7260
LOGFILE
GROUP 1 '/demo/oracle/db/8.1.7/oradata/TEST/redo01.log' SIZE 500K,
GROUP 2 '/demo/oracle/db/8.1.7/oradata/TEST/redo02.log' SIZE 500K,
GROUP 3 '/demo/oracle/db/8.1.7/oradata/TEST/redo03.log' SIZE 500K
DATAFILE
'/demo/oracle/db/8.1.7/oradata/TEST/system01.dbf',
'/demo/oracle/db/8.1.7/oradata/TEST/tools01.dbf',
'/demo/oracle/db/8.1.7/oradata/TEST/rbs01.dbf',
'/demo/oracle/db/8.1.7/oradata/TEST/users01.dbf',
'/demo/oracle/db/8.1.7/oradata/TEST/indx01.dbf',
'/demo/oracle/db/8.1.7/oradata/TEST/drsys01.dbf',
CHARACTER SET US7ASCII
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE
'/demo/oracle/db/8.1.7/oradata/TEST/temp01.dbf' REUSE;
6. Re-create the control files
# start the instance for the target database
$ export ORACLE_SID=TEST
$ sqlplus internal
# re-create the control files for the target database
SQL> @/demo/oracle/db/TEST_ctl.sql
# change the global name of the target database
SQL> alter database rename global_name to TEST;
7. Edit the network config files
# edit the listener.ora
# add the following lines in the SID_LIST_LISTENER section
(SID_DESC =
(GLOBAL_DBNAME = TEST)
(ORACLE_HOME = /demo/oracle/db/8.1.7)
(SID_NAME = TEST)
)
# edit the tnsnames.ora
# add the following lines at the buttom
TEST = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.1)(PORT=1521))
(CONNECT_DATA=(SID=TEST))
)
# start the listener
$ lsnrctl start
8. Edit the oratab file
# add a entry for the new database
# dbstart will use this file to bring the database up/down
# during system boot time
$ cd /etc
$ vi oratab
# after edit, the oratab file should look like this
PROD:/demo/oracle/db/8.1.7:N
TEST:/demo/oracle/db/8.1.7:N
0 comments:
Post a Comment