- 03 Oct, 2023
- 0 Comments
- 5 Mins Read
Database Cloning Using Cold Backup
Cold database backup means while taking backup or doing cloning, we need to shutdown the source database.
This method is usually used for test database when database is in no archive log mode.
Please Note – Both the source and target db server should be on same platform and the target db version will be that of the source db.
So make sure oracle binary is already installed on target db server.Here we will clone a database PRIM to a new server with name TRGDB
1.Take backup of controlfile as trace:[SOURCE DB]
alter database backup controlfile to trace as '/u01/app/oracle/ctrl_bkokp.sql';
At SOURCE Database
2. Note down the location of datafiles
SQL> select file_name from dba_data_files; FILE_NAME --------------------------------------------------------------- /u01/app/oracle/product/oradata/PRIM/system01.dbf /u01/app/oracle/product/oradata/PRIM/sysaux01.dbf /u01/app/oracle/product/oradata/PRIM/undotbs01.dbf /u01/app/oracle/product/oradata/PRIM/users01.dbf /u01/app/oracle/product/oradata/PRIM/CTLDATA_01.dbf /u01/app/oracle/product/oradata/PRIM/CTLIDX_01.dbf /u01/app/oracle/product/oradata/PRIM/catalog01.dbf /u01/app/oracle/product/oradata/PRIM/catalog_idx01.dbf /u01/app/oracle/product/oradata/PRIM/GGATE_01.dbf SQL> select file_name from dba_temp_files; FILE_NAME -------------------------------------------------------------- /u01/app/oracle/product/oradata/PRIM/temp01.dbf /u01/app/oracle/product/oradata/PRIM/catalog_temp01.dbf
At SOURCE Database
3. Shutdown the database.
shut immediate
4. Copy the datafiles and tempfiles to the target db server
scp /u01/app/oracle/product/oradata/PRIM/*dbf [email protected]:/u01/app/oracle/product/oradata/TRGDB/
@TARGET Database
5. Prepare the init file for target database.
We can copy the pfile from source db and the change the required parameters like DB_NAME and control_file,audit_file_dest,diag location.cat initTRGDB.ora *.audit_file_dest='/u01/app/oracle/admin/TRGDB/adump' *.audit_trail='D *.compatible='12.1.0.2.0' *.control_files='/u01/app/oracle/product/oradata/TRGDB/control01.ctl','/u01/app/oracle/product/oradata/TRGDB/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='TRGDB' *.diagnostic_dest='/u01/app/oracle/'*.event='' *.open_cursors=300 *.pga_aggregate_target=524288000 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sec_case_sensitive_logon=FALSE *.sessions=1536 *.sga_max_size=7373586432 *.sga_target=7373586432 *.undo_tablespace='UNDOTBS1'
@TARGET DB
6. Start the database in nomount stage.
export ORACLE_SID=TRGDB startup nomount pfile=initTRGDB.ora
@TARGET DB
7. Re-recreate the controlfile.
This is an important steps in cloning process. In step 1 , We had taken backup of the controlfile as trace, We will use that sql file to re-create the controlfile. SNIPPET FROM controlfile sql script: Set #2. RESETLOGS case The following commands will create a new control file and use it to open the database. Data used by Recovery Manager will be lost. The contents of online logs will be lost and all backups will be invalidated. Use this only if online logs are damaged. After mounting the created controlfile, the following SQL statement will place the database in the appropriate protection mode:ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE.
STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "TCOMDB01" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 2336 LOGFILE GROUP 1 '/u01/app/oracle/product/oradata/PRIM/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/product/oradata/PRIM/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/product/oradata/PRIM/redo03.log' SIZE 50M BLOCKSIZE 512 STANDBY LOGFILE DATAFILE '/u01/app/oracle/product/oradata/PRIM/system01.dbf', '/u01/app/oracle/product/oradata/PRIM/CTLDATA_01.dbf', '/u01/app/oracle/product/oradata/PRIM/sysaux01.dbf', '/u01/app/oracle/product/oradata/PRIM/undotbs01.dbf', '/u01/app/oracle/product/oradata/PRIM/CTLIDX_01.dbf', '/u01/app/oracle/product/oradata/PRIM/users01.dbf', '/u01/app/oracle/product/oradata/PRIM/catalog01.dbf', '/u01/app/oracle/product/oradata/PRIM/catalog_idx01.dbf', '/u01/app/oracle/product/oradata/PRIM/GGATE_01.dbf' CHARACTER SET AL32UTF8 ;
Lets modify this sql by replacing the datafile location of source with that of target and the new db name as TRGDB.
After changing it will look as below:
CREATE CONTROLFILE SET DATABASE "TRGDB" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 2336 LOGFILE GROUP 1 '/u01/app/oracle/product/oradata/TRGDB/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/product/oradata/TRGDB/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/product/oradata/TRGDB/redo03.log' SIZE 50M BLOCKSIZE 512 STANDBY LOGFILE DATAFILE '/u01/app/oracle/product/oradata/TRGDB/system01.dbf', '/u01/app/oracle/product/oradata/TRGDB/CTLDATA_01.dbf', '/u01/app/oracle/product/oradata/TRGDB/sysaux01.dbf', '/u01/app/oracle/product/oradata/TRGDB/undotbs01.dbf', '/u01/app/oracle/product/oradata/TRGDB/CTLIDX_01.dbf', '/u01/app/oracle/product/oradata/TRGDB/users01.dbf', '/u01/app/oracle/product/oradata/TRGDB/catalog01.dbf', '/u01/app/oracle/product/oradata/TRGDB/catalog_idx01.dbf', '/u01/app/oracle/product/oradata/TRGDB/GGATE_01.dbf' CHARACTER SET AL32UTF8 ;
SET DATABASE:
SQL>CREATE CONTROLFILE SET DATABASE "PRIM" RESETLOGS FORCE LOGGING ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 2336 LOGFILE GROUP 1 '/u01/app/oracle/product/oradata/TRGDB/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/product/oradata/TRGDB/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/product/oradata/TRGDB/redo03.log' SIZE 50M BLOCKSIZE 512 STANDBY LOGFILE DATAFILE '/u01/app/oracle/product/oradata/TRGDB/system01.dbf', '/u01/app/oracle/product/oradata/TRGDB/CTLDATA_01.dbf', '/u01/app/oracle/product/oradata/TRGDB/sysaux01.dbf', '/u01/app/oracle/product/oradata/TRGDB/undotbs01.dbf', '/u01/app/oracle/product/oradata/TRGDB/CTLIDX_01.dbf', '/u01/app/oracle/product/oradata/TRGDB/users01.dbf', '/u01/app/oracle/product/oradata/TRGDB/catalog01.dbf', '/u01/app/oracle/product/oradata/TRGDB/catalog_idx01.dbf', '/u01/app/oracle/product/oradata/TRGDB/GGATE_01.dbf' CHARACTER SET AL32UTF8 ; / controlfile created;
8. Open the database in resetlog mode:
ALTER DATABASE OPEN RESETLOGS;
9. Add the temp files:
You can get this tempfile script in the end controlfile script . Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/oradata/PRIM/temp01.dbf' SIZE 11529M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; ALTER TABLESPACE CATALOG_TEMP ADD TEMPFILE '/u01/app/oracle/product/oradata/PRIM/catalog_temp01.dbf' SIZE 524288000 REUSE AUTOEXTEND OFF; End of tempfile additions.
Replace the tempfile location as per target db and execute in target database
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/oradata/TRGDB/temp01.dbf' SIZE 11529M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; ALTER TABLESPACE CATALOG_TEMP ADD TEMPFILE '/u01/app/oracle/product/oradata/TRGDB/catalog_temp01.dbf' SIZE 524288000 REUSE AUTOEXTEND OFF;