Offline Restore or Clone Database on the same server or rename database in Oracle

August 1, 2021
()

Offline Restore or Clone Database on the same server or rename database in Oracle

In a recent article, we have demonstrated How to Rename Database using CONTROLFILE in Oracle, click here to go through it.

In this article, we will demonstrate Offline Restore or clone Database on the same server with a new name or rename database in Oracle. This approach is recommended for small to mid-size databases along with downtime to copy datafiles to the new location, If no RMAN backup schedule i.e database in NO ARCHIVE LOG mode. Let’s begin steps for the same and below are details of source & target components.

COMPONENTSSOURCETARGET
DatabaseLABDB01LABDB02
ServerDBsGuruN2DBsGuruN2
DB Files Directory/oradata/labdb01/oradata/labdb02


1. Create directory: Create directories for AUDIT and physical files as required.

[oracle@DBsGuruN2 ~]$ mkdir -p /oradata/labdb02/
[oracle@DBsGuruN2 ~]$ ls -ld /oradata/labdb02/
drwxr-xr-x. 2 oracle oinstall 6 Jul  9 12:59 /oradata/labdb02/
[oracle@DBsGuruN2 ~]$ mkdir -p /u01/app/oracle/admin/labdb02/adump
[oracle@DBsGuruN2 ~]$ ls -ld /u01/app/oracle/admin/labdb02/adump
drwxr-x---. 2 oracle oinstall 4096 Jul  9 13:01 /u01/app/oracle/admin/labdb02/adump


2. PFILE and CONTROLFILE Backup: Take PFILE backup and generate CONTROLFILE to trace (text format). Here we have two options to generate in text format either to the desire location or to the USER_DUMP_DEST location for CONTROLFILE backup.

LABDB01> CREATE PFILE='/oradata/labdb02/initlabdb02.ora' FROM SPFILE;

File created.
LABDB01> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/oradata/labdb02/labdb02_Controlfile.sql';

Database altered.

OR

LABDB01> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.
[oracle@DBsGuruN2 ~]$ ls -lrt /oradata/labdb02/initlabdb02.ora
-rw-r--r--. 1 oracle oinstall 940 Jul  9 13:00 /oradata/labdb02/initlabdb02.ora
[oracle@DBsGuruN2 ~]$ ls -lrt /oradata/labdb02/labdb02_Controlfile.sql
-rw-r--r--. 1 oracle oinstall 5518 Jul  9 13:03 /oradata/labdb02/labdb02_Controlfile.sql


3. Capture Physical files: Capture details of physical files. Click here to know about How to find all physicals files in Oracle.

LABDB01> @Physical_Files.sql

NAME
------------------------------------------------------------
/oradata/labdb01/control01.ctl
/oradata/labdb01/control02.ctl
/oradata/labdb01/redo01.log
/oradata/labdb01/redo02.log
/oradata/labdb01/redo03.log
/oradata/labdb01/sysaux01.dbf
/oradata/labdb01/system01.dbf
/oradata/labdb01/temp01.dbf
/oradata/labdb01/undotbs01.dbf
/oradata/labdb01/users01.dbf

10 rows selected.


4. Copy datafiles: Copy datafiles & redolog files to the desire location as created in Step 1. Make sure before that clean shutdown database and downtime started from here.

LABDB01> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
LABDB01> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@DBsGuruN2 ~]$ cd /oradata/labdb01
[oracle@DBsGuruN2 ~]$ ls -lrt *.dbf *.log
-rw-r-----. 1 oracle oinstall  33562624 Apr  9 23:42 temp01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Jul  9 12:55 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jul  9 12:55 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jul  9 13:33 redo03.log
-rw-r-----. 1 oracle oinstall 849354752 Jul  9 13:33 system01.dbf
-rw-r-----. 1 oracle oinstall 492838912 Jul  9 13:33 sysaux01.dbf
-rw-r-----. 1 oracle oinstall  68165632 Jul  9 13:33 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jul  9 13:33 users01.dbf
[oracle@DBsGuruN2 labdb01]$ cp *.dbf *.log /oradata/labdb02/


NOTE: Follow the same steps to clone on other servers, instead of command “cp” execute “scp” to transfer all require files to target servers.


5. Prepare PFILE & CONTROLFILE: While copy datafiles are in progress so in between that prepare PFILE & CONTROLFILE for new database LABDB02. Edit the same file which we have taken in Step 2.

5.1. PFILE: Edit PFILE to replace parameter value of DB_NAME to LABDB02, AUDIT LOCATION, and other parameters as required. Below is a sample of PFILE:

*.audit_file_dest='/u01/app/oracle/admin/labdb02/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/oradata/labdb02/control01.ctl','/oradata/labdb02/control02.ctl'
*.db_block_size=8192
*.db_name='labdb02'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=labdb02XDB)'
*.memory_target=1000m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


5.2. CONTROLFILE: To prepare rename database script or clone or offline restore we require to create CONTROLFILE, edit the trace file which is taken in STEP 2 i.e create CONTROLFILE with new database name LABDB02. Key points to remember to edit and below is SAMPLE file.

=> Remove all lines in trace file till up to line STARTUP NOMOUNT i.e remove lines that are comments (line started “–“).
=> Choose wisely options ARCHIVE/NOARCHIVE while creating CONTROLFILE with option RESETLOGS.
=> Replace database name to LABDB02 and keyword REUSE to SET in line CREATE CONTROLFILE command.
=> Replace path of redolog, datafile & tempfile to the new location where we copied files.
=> Make sure file extension should be .sql in case want to execute as a script on SQL prompt.

SET ECHO ON
CREATE CONTROLFILE SET DATABASE "LABDB02" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/labdb02/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/oradata/labdb02/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/oradata/labdb02/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/labdb02/system01.dbf',
  '/oradata/labdb02/sysaux01.dbf',
  '/oradata/labdb02/undotbs01.dbf',
  '/oradata/labdb02/users01.dbf'
CHARACTER SET AL32UTF8
;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/labdb02/temp01.dbf'
SIZE 33554432  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;


6: Validate copied datafiles: Validate files after finish Step 3 and start database LABDB01.

[oracle@DBsGuruN2 labdb01]$ cd ../labdb02
[oracle@DBsGuruN2 labdb02]$ ls -lrt *.dbf *.log
-rw-r-----. 1 oracle oinstall 492838912 Jul  9 13:35 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 849354752 Jul  9 13:35 system01.dbf
-rw-r-----. 1 oracle oinstall  33562624 Jul  9 13:35 temp01.dbf
-rw-r-----. 1 oracle oinstall  68165632 Jul  9 13:36 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jul  9 13:36 users01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Jul  9 13:36 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jul  9 13:36 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jul  9 13:36 redo03.log

LABDB01> startup
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  8628640 bytes
Variable Size             759170656 bytes
Database Buffers          272629760 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
LABDB01> SELECT NAME, OPEN_MODE FROM V$DATABASE;

NAME      OPEN_MODE
--------- --------------------
LABDB01   READ WRITE


7. Add entry in oratab: Using any editor command-line tool to add an entry in the next line for new name DB in /etc/oratab.

[oracle@DBsGuruN2 labdb02]$ vi /etc/oratab

labdb02:/u01/app/oracle/product/12201/db_1:N


8. Start a new database: Start a new database LABDB02, lets follow below:

8.1. Startup in NOMOUNT: Start new database LABDB02 in NOMOUNT stage using PFILE and make sure CONTROLFILEs pointing to correct location.

[oracle@DBsGuruN2 ~]$ . oraenv
ORACLE_SID = [labdb01] ? labdb02
The Oracle base remains unchanged with value /u01/app/oracle

LABDB02$ STARTUP NOMOUNT PFILE='/oradata/labdb02/initlabdb02.ora';
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  8628640 bytes
Variable Size             759170656 bytes
Database Buffers          272629760 bytes
Redo Buffers                8146944 bytes
LABDB02$ SHOW PARAMETER CONTROL_FILES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oradata/labdb02/control01.ctl
                                                 , /oradata/labdb02/control02.c
                                                 tl


8.2. OPEN database: Start the new database in the open stage. Before executing this step make sure all files are routed to the correct location where physical files exist along with the correct new database name in the file labdb02_Controlfile.sql.

LABDB02$ @/oradata/labdb02/labdb02_Controlfile.sql
LABDB02$ SET ECHO ON
LABDB02$ CREATE CONTROLFILE SET DATABASE "LABDB02" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/oradata/labdb02/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/oradata/labdb02/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/oradata/labdb02/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/oradata/labdb02/system01.dbf',
 14    '/oradata/labdb02/sysaux01.dbf',
 15    '/oradata/labdb02/undotbs01.dbf',
 16    '/oradata/labdb02/users01.dbf'
 17  CHARACTER SET AL32UTF8
 18  ;

Control file created.

LABDB02$
LABDB02$ ALTER DATABASE OPEN RESETLOGS;

Database altered.

LABDB02$ ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/labdb02/temp01.dbf'
  2       SIZE 33554432  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.


9. Validate database new name, Control files along with physical files: Now Validate the database name, CONTROLFILES along with all physical files after cloning from LABDB01 to LABDB02. Click here to know more about How to find all physicals files in Oracle.

LABDB02$ SELECT NAME, OPEN_MODE FROM V$DATABASE;

NAME      OPEN_MODE
--------- --------------------
LABDB02   READ WRITE

LABDB02$ @Physical_Files.sql 

NAME
------------------------------------------------------------
/oradata/labdb02/control01.ctl
/oradata/labdb02/control02.ctl
/oradata/labdb02/redo01.log
/oradata/labdb02/redo02.log
/oradata/labdb02/redo03.log
/oradata/labdb02/sysaux01.dbf
/oradata/labdb02/system01.dbf
/oradata/labdb02/temp01.dbf
/oradata/labdb02/undotbs01.dbf
/oradata/labdb02/users01.dbf

10 rows selected.


10. SPFILE & PASSWORD FILE: Create SPFILE using PFILE and restart the database, upon successful restart also create PFILE in the default location and PASSWORD FILE as require.

LABDB02$ CREATE SPFILE FROM PFILE='/oradata/labdb02/initlabdb02.ora';

File created.

LABDB02$ SELECT NAME FROM V$DATABASE;

NAME
------------------------------------------------------------
LABDB02


LABDB02$ SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
LABDB02$ STARTUP
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  8628640 bytes
Variable Size             759170656 bytes
Database Buffers          272629760 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
LABDB02$ SHOW PARAMETER SPFILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12201/
                                                 db_1/dbs/spfilelabdb02.ora

LABDB02$ CREATE PFILE FROM SPFILE;

File created.

LABDB02$ exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@DBsGuruN2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwlabdb02 password=P@ssw0rd#567 entries=10
[oracle@DBsGuruN2 ~]$ ls -lrt $ORACLE_HOME/dbs/orapwlabdb02
-rw-r-----. 1 oracle oinstall 6144 Aug  1 12:25 /u01/app/oracle/product/12201/db_1/dbs/orapwlabdb02


11. Validate database services and update TNS entries: Validate database services in LISTENER and add TNS entry for LABDB02 as require.

[oracle@DBsGuruN2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 01-AUG-2021 12:28:30

------------------------TRIMMED DATA------------------------

Service "labdb02" has 1 instance(s).
  Instance "labdb02", status READY, has 1 handler(s) for this service...
Service "labdb02XDB" has 1 instance(s).
  Instance "labdb02", status READY, has 1 handler(s) for this service...
The command completed successfully


12. Other requirements: Add new DB in OEM, add/enable DBMS Scheduler/cronjobs, application checkout, etc. as require and applicable.


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

 

 

Related articles

, , , ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

DBsGuru

🌐

<strong>Hello and welcome to DBsGuru,</strong>DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!<strong>Share Learn Grow!</strong>

Leave a Reply

Your email address will not be published. Required fields are marked *