Duplicate rman from local backup

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>

Um comentário em “Duplicate rman from local backup”

yify em 12 de janeiro de 2021 às 08:07 disse:

You made a number of nice points there. I did a search on the topic and found a good number of people will go along with with your blog. Tera Radcliffe Abba

Comentar ↓

Soudba

I am a database Administrator!