Clone Database Using Cold Backup



Cloning database using coldbackup

Note : Check for the following


  •               We need to take the cold backup of the database if it is not available.
  •               If the cold backup of the source database is available we can restore the target database with the available one

Take the below as example
Source Database Name: ORCL
Clone Database Name: TEST
Source Database physical files path=/u01/ORCL/oradata
Cloned Database physical files path=/u02/TEST/oradata
Steps to be followed:

     1.    Startup the source database (if not open)
$ export ORACLE_SID=ORCL
$ sqlplus / as sysdba
SQL> startup

     2.    Find out the path and names of datafiles, control files, and redo log files.
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;


     3.    Take the control file backup.
SQL> alter database backup controlfile to trace;
Or use the below command to send the controlfile to a desired location
SQL> alter database backup controlfile to trace as '/home/oracle/cr_<new sid>.sql'

If the source database ‘ORCL’ is using a spfile create a pfile using the spfile as below.

SQL> create pfile=’/u02/ORCL/initORCL.ora’ from spfile;

     4.    If database is using pfile, use OS command to copy the pfile to a backup location.
Shutdown the ‘ORCL’ database
SQL> shutdown

     5.    Copy all data files, and redo log files of ‘ORCL’ database to the respective locations of the target database.

     6.    Transfer the controlfile script to the target database
$ mkdir /u02/TEST/oradata
$ cp /u01/ORCL/oradata/* /u02/TEST/oradata/
OR USE SCP command if you are transferring to another machine.
 scp  <file> <username>@<IP address or hostname>:<Destination>

     7.    Create appropriate directory structure in clone database for dumps and specify them in the parameter file.
$ mkdir -p /u02/TEST/{bdump,udump}

     8.    Edit the clone database parameter file and make necessary changes to the clone database
$ cd /u02/TEST/
$ vi initTEST.ora
db_name=TEST
control_files=/u02/TEST/oradata/cntrl01.ctl
background_dump_dest=/u02/TEST/bdump
user_dump_dest=/u02/TEST/udump


     9.    Startup the clone database in NOMOUNT stage.
$ export ORACLE_SID=RISCLON
SQL> startup nomount pfile=’/u02/RISCLON/initRISCLON.ora’

    10.   Create the control file trace for the clone database using the trace control file and specify the appropriate paths for redolog and datafiles.
CREATE CONTROLFILE SET DATABASE “TEST” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u02/TEST/oradata/redo01.log’ SIZE 5M,
GROUP 2 ‘/u02/TEST/oradata/redo02.log’ SIZE 5M,
DATAFILE
‘/u02/TEST/oradata/system01.dbf’,
‘/u02/TEST/oradata/undotbs01.dbf’,
‘/u02/TEST/oradata/sysaux01.dbf’,
‘/u02/TEST/oradata/users01.dbf’,
‘/u02/TEST/oradata/example01.dbf’
CHARACTER SET AL32UTF8

    11.  Create the control file by running from the trace path
SQL> @u01/TEST/source/udump/cntrl.sql

    12.  Once the control file’s successfully created, open the database with resetlogs option.
SQL> alter database open resetlogs;

My Practise Sessions



Cloning a Database.


SQL> select name from v$datafile;


NAME
--------------------------------------------------------------------------------
/oradisk/app/oracle/oradata/PROD/system01.dbf
/oradisk/app/oracle/oradata/PROD/undotbs01.dbf
/oradisk/app/oracle/oradata/PROD/sysaux01.dbf
/oradisk/app/oracle/oradata/PROD/users01.dbf


SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/oradisk/app/oracle/oradata/PROD/redo03.log
/oradisk/app/oracle/oradata/PROD/redo02.log
/oradisk/app/oracle/oradata/PROD/redo01.log


SQL> select name from v$controlfile;


NAME
--------------------------------------------------------------------------------
/oradisk/app/oracle/oradata/PROD/control01.ctl
/oradisk/app/oracle/oradata/PROD/control02.ctl
/oradisk/app/oracle/oradata/PROD/control03.ctl




Created pfile from spfile;


SQL> create pfile from spfile;


File created.


cp initPROD.ora initTEST.ora  


esc :%s/PROD/TEST/g changes the database name in the file globally




SQL> alter database backup controlfile to trace as  '/tmp/controlfile.sql'
  2  ;


Database altered.


altered the controlfile


STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradisk/app/oracle/oradata/TEST/redo01.log'  SIZE 50M,
  GROUP 2 '/oradisk/app/oracle/oradata/TEST/redo02.log'  SIZE 50M,
  GROUP 3 '/oradisk/app/oracle/oradata/TEST/redo03.log'  SIZE 50M
-- STANDBY LOGFILE


DATAFILE
  '/oradisk/app/oracle/oradata/TEST/system01.dbf',
  '/oradisk/app/oracle/oradata/TEST/undotbs01.dbf',
  '/oradisk/app/oracle/oradata/TEST/sysaux01.dbf',
  '/oradisk/app/oracle/oradata/TEST/users01.dbf'
CHARACTER SET WE8ISO8859P1
;


ALTER TABLESPACE TEMP ADD TEMPFILE '/oradisk/app/oracle/oradata/TEST/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--




[oracle@krishnan TEST]$ ls -ltr
total 923448
-rw-r-----  1 oracle oinstall  52429312 Aug  4 18:40 redo01.log
-rw-r-----  1 oracle oinstall  52429312 Aug  4 18:40 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Aug  4 18:40 redo03.log
-rw-r-----  1 oracle oinstall 251666432 Aug  4 18:41 sysaux01.dbf
-rw-r-----  1 oracle oinstall 503324672 Aug  4 18:41 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Aug  4 18:41 temp01.dbf
-rw-r-----  1 oracle oinstall   5251072 Aug  4 18:41 users01.dbf
-rw-r-----  1 oracle oinstall  26222592 Aug  4 18:41 undotbs01.dbf


copied the file to the new TEST database and controlfile is not copied as we are generating the controlfile


SQL> startup nomount
pfile=/oradisk/app/oracle/product/10.2.0/db/dbs/initTEST.or a;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              71304784 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
SQL> host

SQL> @/tmp/controlfile1.sql
ORA-01081: cannot start already-running ORACLE - shut it down first

Control file created.

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradisk/app/oracle/oradata/TEST/temp01.dbf'
*
ERROR at line 1:
ORA-01109: database not open


SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$database;
     






Subscribe to: Posts (Atom)