oraclednaThis post demonstrates the clone a database using cold backup performed at the OS level (often referred to as cold or offline backups) on same host.
The source and clone databases are sharing same ORACLE HOME.
Environment:
==========
Source Database: PROD (CDB), PDB (PRODPDB1)
Target Database: CLONE (CDB), PDB (CLONEPDB1)
DB Version: 19.27
ORACLE HOME of Source and Clone database: /u01/app/oracle/product/19.27/db
Database Type: Multitenant (CDB with one PDB)
1. Prepare the pfile for the target database (CLONE).
Take the pfile backup from Source database.
SQL> create pfile=’/tmp/initclone.ora’ from spfile;
File created.
Modify the pfile for clone database as below.
[oracle@prod tmp]$ cat initclone.ora
clone.__data_transfer_cache_size=0
clone.__db_cache_size=1711276032
clone.__inmemory_ext_roarea=0
clone.__inmemory_ext_rwarea=0
clone.__java_pool_size=0
clone.__large_pool_size=16777216
clone.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
clone.__pga_aggregate_target=788529152
clone.__sga_target=2365587456
clone.__shared_io_pool_size=117440512
clone.__shared_pool_size=503316480
clone.__streams_pool_size=0
clone.__unified_pga_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/clone/adump’
*.audit_trail=’db’
*.compatible=’19.0.0′
*.control_files=’/u01/app/oracle/oradata/CLONE/control01.ctl’,’/u01/app/oracle/fast_recovery_area/CLONE/control02.ctl’
*.db_block_size=8192
*.db_name=’clone’
*.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’
*.db_recovery_file_dest_size=12732m
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloneXDB)’
*.enable_pluggable_database=true
*.log_archive_format=’%t_%s_%r.arc’
*.nls_language=’AMERICAN’
*.nls_territory=’INDIA’
*.open_cursors=300
*.pga_aggregate_target=748m
*.processes=300
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=2244m
*.undo_tablespace=’UNDOTBS1′
2. Take control file backup of source database (PROD) and modify the backup control file for target database to recreate.
SQL> alter database backup controlfile to trace as ‘/home/oracle/ctrl_clone.sql’;
Database altered.
Modify the trace control file for clone database as below.
[oracle@prod ~]$ cat ctrl_clone.sql
CREATE CONTROLFILE SET DATABASE “CLONE” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/CLONE/redo01.log’ SIZE 200M BLOCKSIZE 512,
GROUP 2 ‘/u01/app/oracle/oradata/CLONE/redo02.log’ SIZE 200M BLOCKSIZE 512,
GROUP 3 ‘/u01/app/oracle/oradata/CLONE/redo03.log’ SIZE 200M BLOCKSIZE 512
DATAFILE
‘/u01/app/oracle/oradata/CLONE/system01.dbf’,
‘/u01/app/oracle/oradata/CLONE/sysaux01.dbf’,
‘/u01/app/oracle/oradata/CLONE/undotbs01.dbf’,
‘/u01/app/oracle/oradata/CLONE/pdbseed/system01.dbf’,
‘/u01/app/oracle/oradata/CLONE/pdbseed/sysaux01.dbf’,
‘/u01/app/oracle/oradata/CLONE/users01.dbf’,
‘/u01/app/oracle/oradata/CLONE/pdbseed/undotbs01.dbf’,
‘/u01/app/oracle/oradata/CLONE/clonepdb1/system01.dbf’,
‘/u01/app/oracle/oradata/CLONE/clonepdb1/sysaux01.dbf’,
‘/u01/app/oracle/oradata/CLONE/clonepdb1/undotbs01.dbf’,
‘/u01/app/oracle/oradata/CLONE/clonepdb1/users01.dbf’
CHARACTER SET AL32UTF8
;
3. Create directories for the target database (CLONE).
[oracle@prod ~]$ mkdir -p /u01/app/oracle/oradata/CLONE
[oracle@prod ~]$ mkdir -p /u01/app/oracle/admin/clone/adump
[oracle@prod ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/CLONE
[oracle@prod ~]$ mkdir -p /u01/app/oracle/oradata/CLONE/pdbseed
[oracle@prod ~]$ mkdir -p /u01/app/oracle/oradata/CLONE/clonepdb1
4. Shutdown the source database and perform a cold backup of the source database (PROD).
Find the datafile, redo logs and controlfile location in source database.
sqlplus / as sysdba
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;
SQL> Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@prod ~]$ cd /u01/app/oracle/oradata/PROD
[oracle@prod PROD]$ cp *.dbf /u01/app/oracle/oradata/CLONE
[oracle@prod ~]$ cd /u01/app/oracle/oradata/PROD/pdbseed
[oracle@prod pdbseed]$ cp *.dbf /u01/app/oracle/oradata/CLONE/pdbseed
[oracle@prod ~]$ cd /u01/app/oracle/oradata/PROD/prodpdb1
[oracle@prod prodpdb1]$ cp *.dbf /u01/app/oracle/oradata/CLONE/clonepdb1
Note: We haven’t copied the redo logs, these will be created during the opening of clone database by “open resetlogs”.
5. Start the target database (CLONE) in nomount state.
[oracle@prod ~]$ export ORACLE_SID=clone
[oracle@prod ~]$ sqlplus / as sysdba
SQL> startup nomount pfile=/u01/app/oracle/product/19.27/db/dbs/initclone.ora
ORACLE instance started.
Total System Global Area 2365584784 bytes
Fixed Size 9180560 bytes
Variable Size 520093696 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7593984 bytes
6. Re-recreate the controlfile for target database (CLONE).
SQL> @ctrl_clone.sql
Control file created.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
———- ———-
CLONE MOUNTED
7. Open the target database with resetlogs.
SQL> alter database open resetlogs;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PRODPDB1 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PRODPDB1 READ WRITE NO
9. Add the temp files in CLONE DB.
We can get this tempfile script in the end controlfile script .
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/app/oracle/oradata/CLONE/temp01.dbf’ SIZE 221249536 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
—
SQL> ALTER SESSION SET CONTAINER = “PDB$SEED”;
Session altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/app/oracle/oradata/CLONE/pdbseed/temp01.dbf’ SIZE 210763776 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
—
SQL> ALTER SESSION SET CONTAINER = “PRODPDB1”;
Session altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/app/oracle/oradata/CLONE/clonepdb1/temp01.dbf’ SIZE 210763776 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
10. Rename the PDB of Clone database to the new name.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PRODPDB1 READ WRITE NO
SQL> ALTER PLUGGABLE DATABASE PRODPDB1 CLOSE;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE PRODPDB1 OPEN RESTRICTED;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PRODPDB1 READ WRITE YES
SQL> ALTER SESSION SET CONTAINER=PRODPDB1;
Session altered.
SQL> ALTER PLUGGABLE DATABASE PRODPDB1 RENAME GLOBAL_NAME TO CLONEPDB1;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE CLONEPDB1 OPEN;
Pluggable database altered.
SQL> show con_name
CON_NAME
——————————
CLONEPDB1
Disclaimer:
Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.
