Restoring a Database Backup in The Same Server
Here is a procedure that I needed to execute a few times for different customers and I decided to share.
The goal is to restore a backup from a database, in the same server that this database is running, but with a different name. We cannot shut the source database anytime.
In my scenario, I have one production database running, named eocpy. I will create a new one called rsteocpy.
The first step is to have a backup, you can use an old one, or take a new one.
$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jul 5 21:36:10 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: EOCPY (DBID=212252882)
RMAN> run {
show all;
report schema;
allocate channel d1 type disk maxpiecesize 10G;
backup as compressed backupset format '/home/oracle/backup/full_%d_%s_%p_%t.bkp' tag 'DatabaseFull' database;
sql 'alter system archive log current';
backup as compressed backupset format '/home/oracle/backup/arch_%d_%s_%p_%t.bkp' tag 'Archivelog' archivelog all delete input;
backup as compressed backupset format '/home/oracle/backup/ctrl_%d_%s_%p_%t.bkp' tag 'Controlfile' current controlfile;
backup as compressed backupset format '/home/oracle/backup/spfile_%d_%s_%p_%t.bkp' tag 'spfile' spfile;
sql "create pfile=''/home/oracle/backup/pfile.ora'' from spfile";
sql "alter database backup controlfile to ''/home/oracle/backup/control.ctl'' reuse";
sql "alter database backup controlfile to trace as ''/home/oracle/backup/control.trc'' reuse";
release channel d1;
}
RMAN configuration parameters for database with db_unique_name EOCPY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_eocpy.f'; # default
Report of database schema for database with db_unique_name EOCPY
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 900 SYSTEM YES /data/EOCPY/system01.dbf
3 530 SYSAUX NO /data/EOCPY/sysaux01.dbf
4 345 UNDOTBS1 YES /data/EOCPY/undotbs01.dbf
7 5 USERS NO /data/EOCPY/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 32 TEMP 32767 /data/EOCPY/temp01.dbf
released channel: ORA_DISK_1
allocated channel: d1
channel d1: SID=498 device type=DISK
Starting backup at 05-JUL-24
channel d1: starting compressed full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=/data/EOCPY/system01.dbf
input datafile file number=00003 name=/data/EOCPY/sysaux01.dbf
input datafile file number=00004 name=/data/EOCPY/undotbs01.dbf
input datafile file number=00007 name=/data/EOCPY/users01.dbf
channel d1: starting piece 1 at 05-JUL-24
channel d1: finished piece 1 at 05-JUL-24
piece handle=/home/oracle/backup/full_EOCPY_7_1_1173562584.bkp tag=DATABASEFULL comment=NONE
channel d1: backup set complete, elapsed time: 00:00:25
Finished backup at 05-JUL-24
Starting Control File and SPFILE Autobackup at 05-JUL-24
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-212252882-20240705-00 comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUL-24
sql statement: alter system archive log current
Starting backup at 05-JUL-24
current log archived
channel d1: starting compressed archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=24 RECID=3 STAMP=1173562330
input archived log thread=1 sequence=25 RECID=4 STAMP=1173562610
input archived log thread=1 sequence=26 RECID=5 STAMP=1173562610
channel d1: starting piece 1 at 05-JUL-24
channel d1: finished piece 1 at 05-JUL-24
piece handle=/home/oracle/backup/arch_EOCPY_9_1_1173562611.bkp tag=ARCHIVELOG comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
channel d1: deleting archived log(s)
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_24_1165863701.dbf RECID=3 STAMP=1173562330
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_25_1165863701.dbf RECID=4 STAMP=1173562610
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_26_1165863701.dbf RECID=5 STAMP=1173562610
Finished backup at 05-JUL-24
Starting backup at 05-JUL-24
channel d1: starting compressed full datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
channel d1: starting piece 1 at 05-JUL-24
channel d1: finished piece 1 at 05-JUL-24
piece handle=/home/oracle/backup/ctrl_EOCPY_10_1_1173562612.bkp tag=CONTROLFILE comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-JUL-24
Starting backup at 05-JUL-24
channel d1: starting compressed full datafile backup set
channel d1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel d1: starting piece 1 at 05-JUL-24
channel d1: finished piece 1 at 05-JUL-24
piece handle=/home/oracle/backup/spfile_EOCPY_11_1_1173562614.bkp tag=SPFILE comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-JUL-24
Starting Control File and SPFILE Autobackup at 05-JUL-24
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-212252882-20240705-01 comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUL-24
sql statement: create pfile=''/home/oracle/backup/pfile.ora'' from spfile
sql statement: alter database backup controlfile to ''/home/oracle/backup/control.ctl'' reuse
sql statement: alter database backup controlfile to trace as ''/home/oracle/backup/control.trc'' reuse
released channel: d1
RMAN> exit
Recovery Manager complete.
Now we need to add the new database to the oratab file
$ cat /etc/oratab
ACCEPT:/u01/app/oracle/product/12.2.0/dbhome_1:N
STAGING:/u01/app/oracle/product/12.2.0/dbhome_1:N
eocpy:/u01/app/oracle/product/19.0.0/dbhome_1:N
oasiscur:/u01/app/oracle/product/19.0.0/dbhome_1:N
I created this script to make it easy, you just need to change the database name and the ORACLE_HOME path properly.
$ cat >> /etc/oratab <<EOF
rsteocpy:/u01/app/oracle/product/19.0.0/dbhome_1:N
EOF
$ cat /etc/oratab
ACCEPT:/u01/app/oracle/product/12.2.0/dbhome_1:N
STAGING:/u01/app/oracle/product/12.2.0/dbhome_1:N
eocpy:/u01/app/oracle/product/19.0.0/dbhome_1:N
oasiscur:/u01/app/oracle/product/19.0.0/dbhome_1:N
rsteocpy:/u01/app/oracle/product/19.0.0/dbhome_1:N
Then create a parameter file for our new database, it can be very simple, like mine. Remember to change the paths and names for your environment.
Make sure you are using a right configuration for the log_file_name_convert, this is very critical, otherwise it will overwrite your production redo logs.
$ cat > $ORACLE_HOME/dbs/initrsteocpy.ora <<EOF
db_name=eocpy
db_unique_name=rsteocpy
db_create_file_dest=/data
LOG_FILE_NAME_CONVERT = '/data/EOCPY/','/data/RSTEOCPY/'
EOF
Now set the variable environments for the new database.
$ . oraenv <<< rsteocpy
Connect to SQL Plus and create a spfile from your pfile.
$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> exit
Check your backup files, you will need to restore the control file now.
$ ls -lt /home/oracle/backup
total 307860
-rw-r--r--. 1 oracle oinstall 5555 Jul 5 21:36 control.trc
-rw-r-----. 1 oracle oinstall 10600448 Jul 5 21:36 control.ctl
-rw-r--r--. 1 oracle oinstall 953 Jul 5 21:36 pfile.ora
-rw-r-----. 1 oracle oinstall 114688 Jul 5 21:36 spfile_EOCPY_11_1_1173562614.bkp
-rw-r-----. 1 oracle oinstall 1097728 Jul 5 21:36 ctrl_EOCPY_10_1_1173562612.bkp
-rw-r-----. 1 oracle oinstall 2477568 Jul 5 21:36 arch_EOCPY_9_1_1173562611.bkp
-rw-r-----. 1 oracle oinstall 300941312 Jul 5 21:36 full_EOCPY_7_1_1173562584.bkp
[oracle@oracle-patching data]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jul 5 21:40:31 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 452981680 bytes
Fixed Size 8897456 bytes
Variable Size 385875968 bytes
Database Buffers 50331648 bytes
Redo Buffers 7876608 bytes
RMAN> restore controlfile from '/home/oracle/backup/control.ctl';
Starting restore at 05-JUL-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/data/RSTEOCPY/controlfile/o1_mf_m8k4nv8m_.ctl
Finished restore at 05-JUL-24
Now get the control file from the output line above (in red).
Set the new control file using this one.
RMAN> sql "alter system set control_files=''/data/RSTEOCPY/controlfile/o1_mf_m8k4nv8m_.ctl'' scope=spfile";
Restart the database to use this new control file.
RMAN> startup force mount;
Oracle instance started
database mounted
Total System Global Area 452981680 bytes
Fixed Size 8897456 bytes
Variable Size 385875968 bytes
Database Buffers 50331648 bytes
Redo Buffers 7876608 bytes
Now run the restore script, you don't need to change anything in this script.
RMAN> run {
set newname for database to new;
restore database;
}
executing command: SET NEWNAME
Starting restore at 05-JUL-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data/RSTEOCPY/datafile/o1_mf_system_%u_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data/RSTEOCPY/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data/RSTEOCPY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /data/RSTEOCPY/datafile/o1_mf_users_%u_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_EOCPY_7_1_1173562584.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_EOCPY_7_1_1173562584.bkp tag=DATABASEFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 05-JUL-24
Switch the database to copy, to use our new files instead of the original ones.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "/data/RSTEOCPY/datafile/o1_mf_system_m8k4q3h7_.dbf"
datafile 3 switched to datafile copy "/data/RSTEOCPY/datafile/o1_mf_sysaux_m8k4q3j6_.dbf"
datafile 4 switched to datafile copy "/data/RSTEOCPY/datafile/o1_mf_undotbs1_m8k4q3js_.dbf"
datafile 7 switched to datafile copy "/data/RSTEOCPY/datafile/o1_mf_users_m8k4q3k4_.dbf"
Apply the archivelogs.
RMAN> recover database;
Starting recover at 05-JUL-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 30 is already on disk as file /data/EOCPY/redo03.log
archived log for thread 1 with sequence 31 is already on disk as file /data/EOCPY/redo01.log
archived log for thread 1 with sequence 32 is already on disk as file /data/EOCPY/redo02.log
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=25
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=26
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/arch_EOCPY_9_1_1173562611.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/arch_EOCPY_9_1_1173562611.bkp tag=ARCHIVELOG
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_25_1165863701.dbf thread=1 sequence=25
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_26_1165863701.dbf thread=1 sequence=26
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_27_1165863701.dbf thread=1 sequence=27
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_28_1165863701.dbf thread=1 sequence=28
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_29_1165863701.dbf thread=1 sequence=29
archived log file name=/data/EOCPY/redo03.log thread=1 sequence=30
archived log file name=/data/EOCPY/redo01.log thread=1 sequence=31
archived log file name=/data/EOCPY/redo02.log thread=1 sequence=32
media recovery complete, elapsed time: 00:00:03
Finished recover at 05-JUL-24
RMAN> exit
Now we need to change our control file, change the database name to the new one, change the redo log file location and then we can open the database.
Connect to SQL Plus and make a backup of the control file to trace.
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 5 21:43:28 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter database backup controlfile to trace;
Database altered.
SQL> select tracefile
from v$session s,
v$process p
where s.paddr = p.addr
and s.audsid = sys_context('USERENV', 'SESSIONID');
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/rsteocpy/rsteocpy/trace/rsteocpy_ora_3047.trc
SQL> exit
Let's create a script to create our new controlfile.
Change the trace file to yours.
$ awk '/CREATE.* RESETLOGS/,/;/' /u01/app/oracle/diag/rdbms/rsteocpy/rsteocpy/trace/rsteocpy_ora_3047.trc \
| awk '!/GROUP.* SIZE/ {print} /GROUP.* SIZE/ {sub(/\).*/, "", $0); print}' \
> create_rsteocpy_controlfile.sql
Check the file created.
$ cat create_rsteocpy_controlfile.sql
CREATE CONTROLFILE REUSE DATABASE "EOCPY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/data/EOCPY/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/data/EOCPY/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/data/EOCPY/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/data/RSTEOCPY/datafile/o1_mf_system_m8k4q3h7_.dbf',
'/data/RSTEOCPY/datafile/o1_mf_sysaux_m8k4q3j6_.dbf',
'/data/RSTEOCPY/datafile/o1_mf_undotbs1_m8k4q3js_.dbf',
'/data/RSTEOCPY/datafile/o1_mf_users_m8k4q3k4_.dbf'
CHARACTER SET AL32UTF8
;
Open this file created called create_rsteocpy_controlfile.sql and change from REUSE to SET, and the database name from the original to the new one.
Also very important, remember to change the redo log file location - This is critical !
$ vi create_rsteocpy_controlfile.sql
$ cat create_rsteocpy_controlfile.sql
CREATE CONTROLFILE SET DATABASE "RSTEOCPY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/data/RSTEOCPY/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/data/RSTEOCPY/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/data/RSTEOCPY/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/data/RSTEOCPY/datafile/o1_mf_system_m8k4q3h7_.dbf',
'/data/RSTEOCPY/datafile/o1_mf_sysaux_m8k4q3j6_.dbf',
'/data/RSTEOCPY/datafile/o1_mf_undotbs1_m8k4q3js_.dbf',
'/data/RSTEOCPY/datafile/o1_mf_users_m8k4q3k4_.dbf'
CHARACTER SET AL32UTF8
;
Connect to SQL Plus again, reset the control file parameter in your spfile, and then create a new pfile from your spfile, because we will need to change it manually.
After making this copy, shut the database down.
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 5 21:44:44 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter system reset control_files scope=spfile;
System altered.
SQL> create pfile from spfile;
File created.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Open the pfile and change the db_name parameter from the original to the new one.
$ vi $ORACLE_HOME/dbs/initrsteocpy.ora
$ cat $ORACLE_HOME/dbs/initrsteocpy.ora
rsteocpy.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.db_create_file_dest='/data'
*.db_name='rsteocpy'
*.db_unique_name='rsteocpy'
*.LOG_FILE_NAME_CONVERT='/data/EOCPY/','/data/RSTEOCPY/'
Now let's create a new spfile from our changed pfile using the new database name.
Then start the database.
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 5 21:45:42 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 452981680 bytes
Fixed Size 8897456 bytes
Variable Size 385875968 bytes
Database Buffers 50331648 bytes
Redo Buffers 7876608 bytes
We will create the control file from the previous script we made a few steps back.
After creating the new control file, open the database in resetlogs mode.
SQL> @create_rsteocpy_controlfile
Control file created.
SQL> alter database open resetlogs;
Database altered.
Check if everything is as expected and save the evidences.
SQL> set pages 200 lin 200
SQL> col member for a70
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------------------------------------- --- ----------
3 ONLINE /data/RSTEOCPY/redo03.log NO 0
2 ONLINE /data/RSTEOCPY/redo02.log NO 0
1 ONLINE /data/RSTEOCPY/redo01.log NO 0
SQL> select name, open_mode, instance_name from v$database,v$instance;
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
RSTEOCPY READ WRITE rsteocpy
If you check, both databases will be up and running now.
$ ps -ef|grep pmon
oracle 1644 1 0 21:32 ? 00:00:00 ora_pmon_eocpy
oracle 3192 1 0 21:45 ? 00:00:00 ora_pmon_rsteocpy
Comments
Post a Comment