A cold backup method is the fastest way of duplicating any oracle database and creating its replica. Here I am going to create a new database with separate DB NAME using a source database.
Also, the source database is a CDB with 1 PDB attached but it won't make that much of difference for non-cdb databases as well. It's just few less actions for non-cdb.
Prerequisites:
- pfile backup of source database
- controlfile backup as trace from source database
- source datafiles copy when database is in mount/shutdown state. Do not copy a source database when it's running.
Please follow below steps to duplicate your database via cold backup method,
1) Create a copy of pfile from your source database.
C:\Users\parvi>sqlplus sys/CdbP$$wd1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 6 21:21:53 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Mon Jan 06 2025 21:21:37 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name, open_mode from V$Database;
NAME OPEN_MODE
--------- --------------------
CDB1 READ WRITE
SQL> create pfile='C:\app\oracle\oradata\copydb\copydb_pfile.ora' from spfile;
File created.
2) Backup controlfile to trace and specify location.
SQL> alter database backup controlfile to trace as 'C:\app\oracle\oradata\copydb\cdb1.trc';
Database altered.
3) Check the created file on physical server. You can open this file in notepad. Once opened, you'll see 2 versions of the CREATE CONTROLFILE statements, 1) with RESETLOGS and 2) with NORESETLOGS. You need to use the one with RESETLOGS option as you are going to create a new database. Also, you need to use SET rather than REUSE since you want to change the DB NAME.
I'll be using the below code to create my new database. You can adapt the similar modifications.
Note: I am going with minimum modifications, just a new folder in ORADATA with new DB Name - COPYDB and have made changes accordingly. If you are copying on a different server/path, ensure to adapt in script.
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "COPYDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\APP\ORACLE\ORADATA\COPYDB\REDO01.LOG' SIZE 200M BLOCKSIZE 512,
GROUP 2 'C:\APP\ORACLE\ORADATA\COPYDB\REDO02.LOG' SIZE 200M BLOCKSIZE 512,
GROUP 3 'C:\APP\ORACLE\ORADATA\COPYDB\REDO03.LOG' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'C:\APP\ORACLE\ORADATA\COPYDB\SYSTEM01.DBF',
'C:\APP\ORACLE\ORADATA\COPYDB\SYSAUX01.DBF',
'C:\APP\ORACLE\ORADATA\COPYDB\UNDOTBS01.DBF',
'C:\APP\ORACLE\ORADATA\COPYDB\PDBSEED\SYSTEM01.DBF',
'C:\APP\ORACLE\ORADATA\COPYDB\PDBSEED\SYSAUX01.DBF',
'C:\APP\ORACLE\ORADATA\COPYDB\USERS01.DBF',
'C:\APP\ORACLE\ORADATA\COPYDB\PDBSEED\UNDOTBS01.DBF',
'C:\APP\ORACLE\ORADATA\COPYDB\PDB1\SYSTEM01.DBF',
'C:\APP\ORACLE\ORADATA\COPYDB\PDB1\SYSAUX01.DBF',
'C:\APP\ORACLE\ORADATA\COPYDB\PDB1\UNDOTBS01.DBF',
'C:\APP\ORACLE\ORADATA\COPYDB\PDB1\USERS01.DBF'
CHARACTER SET AL32UTF8
;
4) Similarly modify your PFILE for your new database. Also update required paths.
Below is the content of my new PFILE for COPYDB.
COPYDB.__data_transfer_cache_size=0
COPYDB.__db_cache_size=1509949440
COPYDB.__inmemory_ext_roarea=0
COPYDB.__inmemory_ext_rwarea=0
COPYDB.__java_pool_size=0
COPYDB.__large_pool_size=67108864
COPYDB.__oracle_base='C:\app\oracle'#ORACLE_BASE set from environment
COPYDB.__pga_aggregate_target=536870912
COPYDB.__sga_target=2147483648
COPYDB.__shared_io_pool_size=100663296
COPYDB.__shared_pool_size=452984832
COPYDB.__streams_pool_size=0
COPYDB.__unified_pga_pool_size=0
*.audit_file_dest='C:\app\oracle\admin\COPYDB\adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='C:\app\oracle\oradata\COPYDB\control01.ctl','C:\app\oracle\oradata\COPYDB\control02.ctl'
*.db_block_size=8192
*.db_name='COPYDB'
*.diagnostic_dest='C:\app\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=COPYDBXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_COPYDB'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=512m
*.processes=640
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2048m
*.undo_tablespace='UNDOTBS1'
5) Once the updated PFILE and CONTROLFILE TRACE is ready, shutdown your SOURCE DATABASE and copy all datafiles + redologs to new location.
SOURCE DB Location:
PS C:\Users\parvi> dir C:\app\oracle\oradata\CDB1
Directory: C:\app\oracle\oradata\CDB1
Mode LastWriteTime Length Name
---- ------------- ------ ----
d----- 04-12-2022 14:29 pdb1
d----- 04-12-2022 13:37 pdbseed
-a---- 06-01-2025 21:42 18726912 CONTROL01.CTL
-a---- 06-01-2025 21:42 18726912 CONTROL02.CTL
-a---- 06-01-2025 21:42 209715712 REDO01.LOG
-a---- 06-01-2025 21:42 209715712 REDO02.LOG
-a---- 06-01-2025 21:42 209715712 REDO03.LOG
-a---- 06-01-2025 21:42 555753472 SYSAUX01.DBF
-a---- 06-01-2025 21:42 933240832 SYSTEM01.DBF
-a---- 04-12-2022 18:58 136323072 TEMP01.DBF
-a---- 06-01-2025 21:42 68165632 UNDOTBS01.DBF
-a---- 06-01-2025 21:42 5251072 USERS01.DBF
TARGET DB Location:
PS C:\Users\parvi> dir C:\app\oracle\oradata\copydb
Directory: C:\app\oracle\oradata\copydb
Mode LastWriteTime Length Name
---- ------------- ------ ----
d----- 06-01-2025 21:51 pdb1
d----- 06-01-2025 21:51 pdbseed
-a---- 06-01-2025 21:24 7528 CDB1.TRC
-a---- 06-01-2025 21:38 1059 COPYDB_PFILE.ORA
-a---- 06-01-2025 21:42 209715712 REDO01.LOG
-a---- 06-01-2025 21:42 209715712 REDO02.LOG
-a---- 06-01-2025 21:42 209715712 REDO03.LOG
-a---- 06-01-2025 21:42 555753472 SYSAUX01.DBF
-a---- 06-01-2025 21:42 933240832 SYSTEM01.DBF
-a---- 04-12-2022 18:58 136323072 TEMP01.DBF
-a---- 06-01-2025 21:42 68165632 UNDOTBS01.DBF
-a---- 06-01-2025 21:42 5251072 USERS01.DBF
Note: I have not copied the CONTROLFILES as they will be created in the coming steps.
6) You need to create a new Oracle Service on Windows Machine to manage this new database, ORADIM utility helps you with that.
Note: Ensure you are doing this with Administrative privileges OR you might face issues.
PS C:\Users\parvi> oradim -NEW -SID COPYDB -SYSPWD C0pydbP$#wd Instance created.
7) Start instance for this new DB in NO MOUNT state with updated PFILE.
C:\Users\parvi>set ORACLE_SID=copydb C:\Users\parvi>sqlplus sys/C0pydbP$#wd as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 6 21:53:38 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='C:\app\oracle\oradata\copydb\COPYDB_PFILE.ORA'; ORACLE instance started. Total System Global Area 2147479688 bytes Fixed Size 9269384 bytes Variable Size 520093696 bytes Database Buffers 1610612736 bytes Redo Buffers 7503872 bytes SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string COPYDB
8) Now create controlfile for this new database using the update code we prepared in step 3).
SQL> CREATE CONTROLFILE SET DATABASE "COPYDB" RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 1024 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 'C:\APP\ORACLE\ORADATA\COPYDB\REDO01.LOG' SIZE 200M BLOCKSIZE 512, 9 GROUP 2 'C:\APP\ORACLE\ORADATA\COPYDB\REDO02.LOG' SIZE 200M BLOCKSIZE 512, 10 GROUP 3 'C:\APP\ORACLE\ORADATA\COPYDB\REDO03.LOG' SIZE 200M BLOCKSIZE 512 11 -- STANDBY LOGFILE 12 DATAFILE 13 'C:\APP\ORACLE\ORADATA\COPYDB\SYSTEM01.DBF', 14 'C:\APP\ORACLE\ORADATA\COPYDB\SYSAUX01.DBF', 15 'C:\APP\ORACLE\ORADATA\COPYDB\UNDOTBS01.DBF', 16 'C:\APP\ORACLE\ORADATA\COPYDB\PDBSEED\SYSTEM01.DBF', 17 'C:\APP\ORACLE\ORADATA\COPYDB\PDBSEED\SYSAUX01.DBF', 18 'C:\APP\ORACLE\ORADATA\COPYDB\USERS01.DBF', 19 'C:\APP\ORACLE\ORADATA\COPYDB\PDBSEED\UNDOTBS01.DBF', 20 'C:\APP\ORACLE\ORADATA\COPYDB\PDB1\SYSTEM01.DBF', 21 'C:\APP\ORACLE\ORADATA\COPYDB\PDB1\SYSAUX01.DBF', 22 'C:\APP\ORACLE\ORADATA\COPYDB\PDB1\UNDOTBS01.DBF', 23 'C:\APP\ORACLE\ORADATA\COPYDB\PDB1\USERS01.DBF' 24 CHARACTER SET AL32UTF8 25 ; Control file created. SQL>
9) Open the database using RESETLOGS option.
SQL> alter database open resetlogs;
Database altered.
10) Finish the rest of the scripts from CONTROLFILE TRACE FILE with needed modifications.
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
Pluggable database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\APP\ORACLE\ORADATA\COPYDB\TEMP01.DBF' SIZE 136314880 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
SQL> ALTER SESSION SET CONTAINER = "PDB$SEED";
Session altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\APP\ORACLE\ORADATA\COPYDB\PDBSEED\TEMP012022-12-04_13-37-09-398-PM.DBF' SIZE 37748736 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
SQL> ALTER SESSION SET CONTAINER = "PDB1";
Session altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\APP\ORACLE\ORADATA\COPYDB\PDB1\TEMP01.DBF' REUSE;
Tablespace altered.
SQL> ALTER SESSION SET CONTAINER = "CDB$ROOT";
Session altered.
11) Final step is to create SPFILE from PFILE and restart the database.
SQL> create spfile from pfile='C:\app\oracle\oradata\copydb\COPYDB_PFILE.ORA';
File created.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 2147479688 bytes
Fixed Size 9269384 bytes
Variable Size 520093696 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7503872 bytes
Database mounted.
Database opened.
SQL>
SQL> select name, open_mode from V$database;
NAME OPEN_MODE
-------------------------------------------------- --------------------
COPYDB READ WRITE
SQL>
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL>
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> select file#, name from V$datafile;
FILE# NAME
---------- --------------------------------------------------
########## C:\APP\ORACLE\ORADATA\COPYDB\SYSTEM01.DBF
########## C:\APP\ORACLE\ORADATA\COPYDB\SYSAUX01.DBF
########## C:\APP\ORACLE\ORADATA\COPYDB\UNDOTBS01.DBF
########## C:\APP\ORACLE\ORADATA\COPYDB\PDBSEED\SYSTEM01.DBF
########## C:\APP\ORACLE\ORADATA\COPYDB\PDBSEED\SYSAUX01.DBF
########## C:\APP\ORACLE\ORADATA\COPYDB\USERS01.DBF
########## C:\APP\ORACLE\ORADATA\COPYDB\PDBSEED\UNDOTBS01.DBF
########## C:\APP\ORACLE\ORADATA\COPYDB\PDB1\SYSTEM01.DBF
########## C:\APP\ORACLE\ORADATA\COPYDB\PDB1\SYSAUX01.DBF
########## C:\APP\ORACLE\ORADATA\COPYDB\PDB1\UNDOTBS01.DBF
########## C:\APP\ORACLE\ORADATA\COPYDB\PDB1\USERS01.DBF
11 rows selected.
I hope this helps !!
No comments:
Post a Comment