Although it is usual to have to recover a database in a different host, it is possible that in some occasions it is necessary to recover a new database in the same machine where we have the original database, this is the purpose of this entry where we specify the necessary steps to be able to recover a database in the same host, in this case we are not using ASM as storage. The source database is called "test" and our copy will be called "testcopy".
First of all, we must start from a previously performed backup .
In our case we have launched the following:
RMAN> run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
backup format ‘/u01/backup/%U’ (base de datos);
backup format ‘/u01/backup/%U’ (archivelog all);
backup format ‘/u01/backup/%U’ current controlfile;
}
Before we start, let's create the oratab entry for the new database.
Edit the oratab file and add the new database:
vi /etc/oratab
test:/u01/sw:N
testcopy:/u01/sw:N
The first step is to copy the pfile of the current database, in our case "test" with which we will start our database, it is enough with the minimum necessary parameters. An init file with the minimum parameters would be something similar to what I show here later.
Very important to pay attention to the parameters "db_create_file_dest" where we indicate where our data will be saved, in this scenario is very important, since we are recovering the database in the same host where we have our source database, it is also very important the parameter "db_unique_name", which will differentiate our copy from the original one.
[oracle@localhost dbs]$ cat inittestcopy.ora
*.compatible='19.0.0′
*.db_block_size=8192
*.db_create_file_dest='/data/testcopy/'
*.db_name='test'
*.db_unique_name='testcopy'
*.diagnostic_dest='/u01/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=797m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2388m
*.undo_tablespace='UNDOTBS1′
We create the spfile of our new database through the pfile.
SQL> create spfile from pfile='inittestcopy.ora';
File created.
We check that the spfile has been created correctly:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2516581464 bytes
Fixed Size 8899672 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7876608 bytes
SQL> show parameter pfile
NAME TYPE VALUE
--------------------- ---------------------
spfile string /u01/sw/dbs/spfiletestcopy.ora
Restore the controlfile
We connect to RMAN and retrieve the controlfile.
Note. We can locate the controlfile backup by connecting to the "original" database, or database to copy, in our case test and execute the following command.
RMAN> list backup of controlfile;
Once connected to the testcopy database, through rman, we retrieve the copy of the controlfile that we have located previously.
RMAN> restore controlfile from '/u01/backup/0e267imt_1_1';
Starting restore at 12-SEP-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/data/testcopy/TESTCOPY/controlfile/o1_mf_lj1qob64_.ctl
Finished restore at 12-SEP-23
We set up the database:
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
Before launching the database recovery, let's make sure that the redo log files are in the right path. This can be done with the following query:
set lines 200
col member format a60
select a.thread#,a.group#,b.type,b.member,a.bytes/1048576
from v$log a,v$logfile b
where a.group#=b.group# order by a.group#;
If the redo logs point to the old database we have to change them to the location they will have in our new database. To do this we launch the following command:
SQL> alter database rename file ‘<old file location and name>’ to ‘<new location and name>’;
You can help yourself with a query like the following to do it automatically;
select ‘ALTER DATABASE RENAME FILE »’||member||»’ TO »<localización destino> ||substr(member,INSTR(member,’\’,-1,1),length(member)) ||»’;’ from v$logfile;
We execute the rename of the datafiles and we check it with the previously launched query to make sure that the change of location is correct:
ALTER DATABASE RENAME FILE '/data/test/TEST/onlinelog/o1_mf_3_hz8q3jd1_.log' TO '/data/testcopy/TESTCOPY/redolog/o1_mf_3_hz8q3jd1_.log';
ALTER DATABASE RENAME FILE '/u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_hz8q4lbd_.log' TO '/data/testcopy/TESTCOPY/redolog/o1_mf_3_hz8q4lbd_.log';
ALTER DATABASE RENAME FILE '/data/test/TEST/onlinelog/o1_mf_2_hz8q3jcc_.log' TO '/data/testcopy/TESTCOPY/redolog/o1_mf_2_hz8q3jcc_.log';
ALTER DATABASE RENAME FILE '/u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_2_hz8q4qgp_.log' TO '/data/testcopy/TESTCOPY/redolog/o1_mf_2_hz8q4qgp_.log';
ALTER DATABASE RENAME FILE '/data/test/TEST/onlinelog/o1_mf_1_hz8q3jbo_.log' TO '/data/testcopy/TESTCOPY/redolog/o1_mf_1_hz8q3jbo_.log';
ALTER DATABASE RENAME FILE '/u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_1_hz8q4lfp_.log' TO '/data/testcopy/TESTCOPY/redolog/o1_mf_1_hz8q4lfp_.log';
SQL> set lines 200
col member format a60
select a.thread#,a.group#,b.type,b.member,a.bytes/1048576
from v$log a,v$logfile b
where a.group#=b.group# order by a.group#;SQL> SQL> 2 3
THREAD# GROUP# TYPE MEMBER A.BYTES/1048576
1 1 ONLINE /data/testcopy/TESTCOPY/redolog/o1_mf_1_hz8q4lfp_.log
We restore the database through the RMAN backup.
With the target database (testcopy) mounted, we launch the following command:
run{
set newname for database to ‘/data/tescopy/TESCOPY/%U’;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
The command "set newname for database to", indicates where we want our files of our new DB to be stored, this can be done manually, renaming each datafile or in the way exposed in this document.
If everything is correct, you will have an output similar to this one:
Starting restore at 13-SEP-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=237 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 00004 to /data/tescopy/TESCOPY
.
.
starting media recovery
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2023_09_12/o1_mf_1_23_lj1pkgkj_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2023_09_12/o1_mf_1_23_lj1pkgkj_.arc thread=1 sequence=23
unable to find archived log
archived log thread=1 sequence=24
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/13/2023 13:15:18
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 24 and starting SCN of 313766
This output indicates that the database has been successfully retrieved and that the latest available archive has been applied.
At this point we can open our database.
SQL> alter database open resetlogs;
Database altered.
We check that everything is correct:
SQL> select name from v$datafile;
/data/tescopy/TESCOPY/data_D-TEST_TS-SYSTEM_FNO-1
/data/tescopy/TESCOPY/data_D-TEST_TS-SYSAUX_FNO-3
/data/tescopy/TESCOPY/data_D-TEST_TS-UNDOTBS1_FNO-4
/data/tescopy/TESCOPY/data_D-TEST_TS-USERS_FNO-7 /data/tescopy/TESCOPY/data_D-TEST_TS-USERS_FNO-7
SQL> Select member from v$logfile;
/data/testcopy/TESTCOPY/onlinelog/o1_mf_3_lj37cp5g_.log
/data/testcopy/TESTCOPY/onlinelog/o1_mf_2_lj37cp4r_.log
/data/testcopy/TESTCOPY/onlinelog/o1_mf_1_lj37cp41_.log
In this last point, we would already have the recovered database, in this case it can be interesting to rename the database, since the DBID and the DB_NAME of the original database and the copy will be the same. For this I recommend to do it with the NID tool, better than manually.
