We have a lot of posts todo the same – duplicate. But my post is complete steps to duplicate the database, and we need pay attention if the database will be duplicated on the same server. What we need take care with all attention on it? Redo log files, datafiles and controlfiles never be recreated/created at same source locations. All backups are under the location /backups/bkprman in my lab. Let’s start !
Source database – bdo
Check the locations from source database. Make sure when you are duplicating - any of these files will be replaced. Mistakes are done with redo logfiles members when we are duplicating. Take care to rename all files and check before open it. Take notes of the locations below.
Select name from v$datafile;
Select name from v$controlfile;
Select member from v$logfile;
Target database – TESTE
Before open the target database check same queries below.
Select name from v$datafile;
Select name from v$controlfile;
Select member from v$logfile;
Add database to /etc/oratab
Add this line to the end of /etc/oratab
TESTE:/u01/app/oracle/product/11.2.0/db_1:Y
Listener.ora
Add the listener.ora details below and reload listener – edit your listener.ora and lsnrctl reload it
vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTE)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = TESTE)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srvoraclestby)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
Password file creation
We need create the password file and use entries=5 sometimes we get error when connecting to auxiliary.
cd $ORACLE_HOME/dbs
orapwd file=orapwTESTE password=oracle ignorecase=y entries=5
Create new file: initTESTE.ora
# Minimum Requirement.
DB_NAME='bdo'
Create new file: duplicateTESTE.sql
DUPLICATE DATABASE TO TESTE
SPFILE
parameter_value_convert ('bdo','TESTE')
set db_file_name_convert='+DATA/bdo/datafile/','/backups/oradata/TESTE/','+DATA/','/backups/oradata/TESTE/'
set log_file_name_convert='+DATA/bdo/datafile/','/backups/oradata/TESTE/','+DATA/bdo/onlinelog/','/backups/oradata/TESTE/'
set control_files='/backups/oradata/TESTE/control01.ctl'
set db_name='TESTE'
set log_archive_dest_1='location=/stage/archive'
set log_archive_dest=''
set DB_CREATE_ONLINE_LOG_DEST_1='/backups/oradata/TESTE/'
set DB_CREATE_FILE_DEST='/backups/oradata/TESTE/'
set audit_trail='none'
set remote_login_passwordfile='EXCLUSIVE'
BACKUP LOCATION '/backups/bkprman'
NOFILENAMECHECK;
Create new file: duplicateTESTE.sh
. oraenv <<< TESTE
sqlplus / as sysdba <<EOF
shut abort
startup nomount pfile='/stage/scripts/initTESTE.ora';
exit
EOF
# rm -Rf $ORACLE_BASE/admin/$ORACLE_SID/adump
# mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
# Remove the old datafiles
rm -Rf /backups/oradata/TESTE/*
rman auxiliary sys/oracle cmdfile=/stage/scripts/duplicaTESTE.sql log=/stage/scripts/duplicaTESTE.log
sqlplus / as sysdba <<EOF
alter database open resetlogs;
exit
EOF
Execute the rman restore in background
nohup ./duplicateTESTE.sh &
Check the results
tail -f duplicateTESTE.log
channel ORA_AUX_DISK_1: restoring datafile 00024 to /backups/oradata/TESTE/sankind07.dbf
channel ORA_AUX_DISK_1: restoring datafile 00025 to /backups/oradata/TESTE/sanlob08.dbf
channel ORA_AUX_DISK_1: restoring datafile 00026 to /backups/oradata/TESTE/sankhya07.dbf
channel ORA_AUX_DISK_1: restoring datafile 00027 to /backups/oradata/TESTE/sankhya08.dbf
channel ORA_AUX_DISK_1: restoring datafile 00028 to /backups/oradata/TESTE/sankind08.dbf
channel ORA_AUX_DISK_1: restoring datafile 00029 to /backups/oradata/TESTE/sanklob08.dbf
channel ORA_AUX_DISK_1: restoring datafile 00030 to /backups/oradata/TESTE/sankhya09.dbf
channel ORA_AUX_DISK_1: restoring datafile 00031 to /backups/oradata/TESTE/sanklob10.dbf
channel ORA_AUX_DISK_1: restoring datafile 00032 to /backups/oradata/TESTE/sankind09.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backups/bkprman/bkpfull1042630360_s270188_s1
Before open the database, check if redo log groups are in the correct location
Check if the datafiles have been created in a different location from production database before you open the database with resetlogs option. If all is fine, open database with resetlogs option.
. oraenv <<< TESTE
sqlplus / as sysdba
Select name from v$datafile;
Select name from v$controlfile;
Select member from v$logfile;
alter database open resetlogs;
select status from v$instance;
exit
Errors and solutions when using duplicate with auxiliary and backup location
RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted
Solution:
When connecting to auxiliary instance, password file is not working. Recreate password file or use this connection with the correct password like below:
rman auxiliary sys/oracle
ORA-19504: failed to create file
Solution:
Update/revise the parameters on file restoreTESTE.sql:
parameter_value_convert ('bdo','TESTE')
set db_file_name_convert='+DATA/bdo/datafile/','/backups/oradata/TESTE/','+DATA/','/backups/oradata/TESTE/'
set log_file_name_convert='+DATA/bdo/datafile/','/backups/oradata/TESTE/','+DATA/','/backups/oradata/TESTE/'
set control_files='/backups/oradata/TESTE/control01.ctl'
set log_archive_dest_1='location=/stage/archive'
set log_archive_dest=''
set DB_CREATE_ONLINE_LOG_DEST_1='/backups/oradata/TESTE/'
set DB_CREATE_FILE_DEST='/backups/oradata/TESTE/'
set audit_trail='none'
ORA-09925: Unable to create audit trail file
Solution:
update the parameter on file restoreTESTE.sql:
set audit_trail='none'
ORA-01261: Parameter db_create_online_log_dest_1 destination string cannot be translated
Solution:
Make sure the directory exists/update the location to one existent directory on file restoreTESTE.sql:
DB_CREATE_ONLINE_LOG_DEST_1='/backups/oradata/TESTE/'
RMAN-06403: could not obtain a fully authorized session
Solution:
Recreate the password file:
cd /u01/app/oracle/product/11.2.0/db_1/dbs
rm orapwTESTE
orapwd file=orapwTESTE password=oracle ignorecase=y entries=5
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Solution:
Update the listener.ora e try to reload the listener :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTE)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = TESTE)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srvoraclestby)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
Error: Creation of redo logs - not necessary to restore database again - need recreate controlfile.
GROUP 14 ( '/backups/oradata/TESTE/bdo/onlinelog/group_14.1974.947483695', '/backups/oradata/TESTE/bdo/onlinelog/group_14.2908.947483697' ) SIZE 120 M REUSE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/10/2020 04:28:03
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-00301: erro ao incluir arquivo de log '/backups/oradata/TESTE/bdo/onlinelog/group_9.4980.947483691' - o arquivo n?o pode ser criado
ORA-27040: erro ao criar arquivo, n?o foi possivel criar o arquivo
Linux-x86_64 Error: 2: No such file or directory
shutdown database, create the controlfile pointing to all new datafiles and open database.
Add tempfile to temporary tablespaces.
move/remove controlfile.
mv /backups/oradata/TESTE/control01.ctl /backups/oradata/TESTE/co.old
mkdir -p /backups/oradata/TESTE/bdo/onlinelog/
sqlplus / as sysdba
shut abort
startup nomount
CREATE CONTROLFILE reuse SET DATABASE "TESTE" RESETLOGS NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 11680
LOGFILE
GROUP 1 ( '/backups/oradata/TESTE/bdo/onlinelog/group_1.291.947518073', '/backups/oradata/TESTE/bdo/onlinelog/group_1.290.947518075' ) SIZE 120 M REUSE,
GROUP 2 ( '/backups/oradata/TESTE/bdo/onlinelog/group_2.289.947518075', '/backups/oradata/TESTE/bdo/onlinelog/group_2.288.947518075' ) SIZE 120 M REUSE,
GROUP 3 ( '/backups/oradata/TESTE/bdo/onlinelog/group_3.287.947518075', '/backups/oradata/TESTE/bdo/onlinelog/group_3.284.947518075' ) SIZE 120 M REUSE
DATAFILE
'/backups/oradata/TESTE/system.278.853379527'
,'/backups/oradata/TESTE/users.277.853379111'
,'/backups/oradata/TESTE/undotbs2.5543.947483683'
,'/backups/oradata/TESTE/undotbs1.292.947518065'
,'/backups/oradata/TESTE/sysaux.275.853379001'
,'/backups/oradata/TESTE/sanklob06.dbf'
,'/backups/oradata/TESTE/sanklob05.dbf'
,'/backups/oradata/TESTE/sanklob04.dbf'
,'/backups/oradata/TESTE/sanklob03.dbf'
,'/backups/oradata/TESTE/sanklob02.dbf'
,'/backups/oradata/TESTE/sanklob01.dbf'
,'/backups/oradata/TESTE/sankind05.dbf'
,'/backups/oradata/TESTE/sankind03.dbf'
,'/backups/oradata/TESTE/sankind02.dbf'
,'/backups/oradata/TESTE/sankind01.dbf'
,'/backups/oradata/TESTE/sankhya05.dbf'
,'/backups/oradata/TESTE/sankhya04.dbf'
,'/backups/oradata/TESTE/sankhya03.dbf'
,'/backups/oradata/TESTE/sankhya02.dbf'
,'/backups/oradata/TESTE/sankhya01.dbf'
,'/backups/oradata/TESTE/sanlob08.dbf'
,'/backups/oradata/TESTE/sanklob10.dbf'
,'/backups/oradata/TESTE/sanklob08.dbf'
,'/backups/oradata/TESTE/sanklob07.dbf'
,'/backups/oradata/TESTE/sankind09.dbf'
,'/backups/oradata/TESTE/sankind08.dbf'
,'/backups/oradata/TESTE/sankind07.dbf'
,'/backups/oradata/TESTE/sankind06.dbf'
,'/backups/oradata/TESTE/sankhya09.dbf'
,'/backups/oradata/TESTE/sankhya08.dbf'
,'/backups/oradata/TESTE/sankhya07.dbf'
,'/backups/oradata/TESTE/sankhya06.dbf'
CHARACTER SET WE8ISO8859P1
;
SQL> alter database open resetlogs;
Banco de dados alterado.
Check the alert log for errors:
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
SQL> alter tablespace temp add tempfile '/backups/oradata/TESTE/temp02.dbf' size 10G autoextend on;
Tablespace alterado.
scripts$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 09:54:47 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Conectado a:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE VERSION COMPATIBLE STATUS
---------------- ---------------- -------------------- ---------- ---------- --------
TESTE PRIMARY READ WRITE 11.2.0.3.0 11.2.0.0.0 OPEN
HOST_NAME INSTANCE_NAME STATUS Startup time LOGINS CONNECTED
---------------------------------- -------------- -------- ------------------ -------- ------------
srvoraclestby TESTE OPEN 10-JUN-2020 09:44 ALLOWED CURRENT
SQL>