{"id":1197,"date":"2020-06-09T20:51:30","date_gmt":"2020-06-09T23:51:30","guid":{"rendered":"http:\/\/soudba.com.br\/?p=1197"},"modified":"2020-06-09T20:51:30","modified_gmt":"2020-06-09T23:51:30","slug":"duplicate-rman-from-local-backup","status":"publish","type":"post","link":"https:\/\/www.soudba.com.br\/?p=1197","title":{"rendered":"Duplicate rman from local backup"},"content":{"rendered":"\n<p>We have a lot of posts todo the same &#8211; 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&#8217;s start !<\/p>\n\n\n<h2 class=\"wp-block-heading\">Source database &#8211; bdo<\/h2>\n\n\n<pre class=\"wp-block-code\"><code>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.\nSelect name from v$datafile;\nSelect name from v$controlfile;\nSelect member from v$logfile;<\/code><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Target database &#8211; TESTE<\/h2>\n\n\n<pre class=\"wp-block-code\"><code>Before open the target database check same queries below.\nSelect name from v$datafile;\nSelect name from v$controlfile;\nSelect member from v$logfile;<\/code><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Add database to \/etc\/oratab<\/h2>\n\n\n<p>Add this line to the end of  \/etc\/oratab<\/p>\n\n\n<pre class=\"wp-block-code\"><code>TESTE:\/u01\/app\/oracle\/product\/11.2.0\/db_1:Y<\/code><\/pre>\n\n\n<h2 class=\"wp-block-heading\"><strong>Listener.ora<\/strong><\/h2>\n\n\n<p>Add the listener.ora details below and reload listener &#8211; edit your listener.ora and lsnrctl reload it<\/p>\n\n\n<p>vi $ORACLE_HOME\/network\/admin\/listener.ora<\/p>\n\n\n<pre class=\"wp-block-code\"><code>\nSID_LIST_LISTENER =\n  (SID_LIST =\n    (SID_DESC =\n      (GLOBAL_DBNAME = TESTE)\n      (ORACLE_HOME = \/u01\/app\/oracle\/product\/11.2.0\/db_1)\n      (SID_NAME = TESTE)\n    )\n  )\nLISTENER =\n  (DESCRIPTION_LIST =\n    (DESCRIPTION =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = srvoraclestby)(PORT = 1521))\n    )\n    (DESCRIPTION =\n      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))\n    )\n  )\n<\/code><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Password file creation<\/h2>\n\n\n<p>We need create the password file and use entries=5 sometimes we get error when connecting to auxiliary.<\/p>\n\n\n<pre class=\"wp-block-code\"><code>cd $ORACLE_HOME\/dbs\norapwd file=orapwTESTE password=oracle ignorecase=y entries=5<\/code><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Create new file: initTESTE.ora<\/h2>\n\n\n<pre class=\"wp-block-code\"><code># Minimum Requirement.\nDB_NAME='bdo'<\/code><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Create new file: duplicateTESTE.sql<\/h2>\n\n\n<pre class=\"wp-block-code\"><code>DUPLICATE DATABASE TO TESTE\n  SPFILE\n    parameter_value_convert ('bdo','TESTE')\n    set db_file_name_convert='+DATA\/bdo\/datafile\/','\/backups\/oradata\/TESTE\/','+DATA\/','\/backups\/oradata\/TESTE\/'\n    set log_file_name_convert='+DATA\/bdo\/datafile\/','\/backups\/oradata\/TESTE\/','+DATA\/bdo\/onlinelog\/','\/backups\/oradata\/TESTE\/'\n    set control_files='\/backups\/oradata\/TESTE\/control01.ctl'\n    set db_name='TESTE'\n    set log_archive_dest_1='location=\/stage\/archive'\n    set log_archive_dest=''\n    set DB_CREATE_ONLINE_LOG_DEST_1='\/backups\/oradata\/TESTE\/'\n    set DB_CREATE_FILE_DEST='\/backups\/oradata\/TESTE\/'\n    set audit_trail='none'\n    set remote_login_passwordfile='EXCLUSIVE'\n  BACKUP LOCATION '\/backups\/bkprman'\n  NOFILENAMECHECK;<\/code><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Create new file: duplicateTESTE.sh<\/h2>\n\n\n<pre class=\"wp-block-code\"><code>. oraenv &lt;&lt;&lt; TESTE\nsqlplus \/ as sysdba &lt;&lt;EOF\nshut abort\nstartup nomount pfile='\/stage\/scripts\/initTESTE.ora';\nexit\nEOF\n# rm -Rf $ORACLE_BASE\/admin\/$ORACLE_SID\/adump\n# mkdir -p $ORACLE_BASE\/admin\/$ORACLE_SID\/adump\n# Remove the old datafiles\nrm -Rf \/backups\/oradata\/TESTE\/*\nrman auxiliary sys\/oracle cmdfile=\/stage\/scripts\/duplicaTESTE.sql log=\/stage\/scripts\/duplicaTESTE.log\nsqlplus \/ as sysdba &lt;&lt;EOF\nalter database open resetlogs;\nexit\nEOF<\/code><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Execute the rman restore in background<\/h2>\n\n\n<pre class=\"wp-block-code\"><code> nohup .\/duplicateTESTE.sh &amp;\n<\/code><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Check the results<\/h2>\n\n\n<pre class=\"wp-block-code\"><code>tail -f duplicateTESTE.log\nchannel ORA_AUX_DISK_1: restoring datafile 00024 to \/backups\/oradata\/TESTE\/sankind07.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00025 to \/backups\/oradata\/TESTE\/sanlob08.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00026 to \/backups\/oradata\/TESTE\/sankhya07.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00027 to \/backups\/oradata\/TESTE\/sankhya08.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00028 to \/backups\/oradata\/TESTE\/sankind08.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00029 to \/backups\/oradata\/TESTE\/sanklob08.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00030 to \/backups\/oradata\/TESTE\/sankhya09.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00031 to \/backups\/oradata\/TESTE\/sanklob10.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00032 to \/backups\/oradata\/TESTE\/sankind09.dbf\nchannel ORA_AUX_DISK_1: reading from backup piece \/backups\/bkprman\/bkpfull1042630360_s270188_s1\n<\/code><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Before open the database, check if redo log groups are in the correct location<\/h2>\n\n\n<pre class=\"wp-block-code\"><code>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.\n. oraenv &lt;&lt;&lt; TESTE\nsqlplus \/ as sysdba\nSelect name from v$datafile;\nSelect name from v$controlfile;\nSelect member from v$logfile;\nalter database open resetlogs;\nselect status from v$instance;\nexit<\/code><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Errors and solutions when using duplicate with auxiliary and backup location<\/h2>\n\n\n<p>RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted<\/p>\n\n\n<pre class=\"wp-block-code\"><code>Solution:\nWhen connecting to auxiliary instance, password file is not working. Recreate password file or use this connection with the correct password like below:\nrman auxiliary sys\/oracle<\/code><\/pre>\n\n\n<p>ORA-19504: failed to create file<\/p>\n\n\n<pre class=\"wp-block-code\"><code>Solution:\nUpdate\/revise the parameters on file restoreTESTE.sql:\nparameter_value_convert ('bdo','TESTE')\n    set db_file_name_convert='+DATA\/bdo\/datafile\/','\/backups\/oradata\/TESTE\/','+DATA\/','\/backups\/oradata\/TESTE\/'\n    set log_file_name_convert='+DATA\/bdo\/datafile\/','\/backups\/oradata\/TESTE\/','+DATA\/','\/backups\/oradata\/TESTE\/'\n    set control_files='\/backups\/oradata\/TESTE\/control01.ctl'\n    set log_archive_dest_1='location=\/stage\/archive'\n    set log_archive_dest=''\n    set DB_CREATE_ONLINE_LOG_DEST_1='\/backups\/oradata\/TESTE\/'\n    set DB_CREATE_FILE_DEST='\/backups\/oradata\/TESTE\/'\n    set audit_trail='none'<\/code><\/pre>\n\n\n<p>ORA-09925: Unable to create audit trail file <\/p>\n\n\n<pre class=\"wp-block-code\"><code>Solution:\nupdate the parameter on file restoreTESTE.sql:\nset audit_trail='none'<\/code><\/pre>\n\n\n<p>ORA-01261: Parameter db_create_online_log_dest_1 destination string cannot be translated<\/p>\n\n\n<pre class=\"wp-block-code\"><code>Solution:\nMake sure the directory exists\/update the location to one existent directory on file restoreTESTE.sql:\nDB_CREATE_ONLINE_LOG_DEST_1='\/backups\/oradata\/TESTE\/'<\/code><\/pre>\n\n\n<p>RMAN-06403: could not obtain a fully authorized session<\/p>\n\n\n<pre class=\"wp-block-code\"><code>Solution:\nRecreate the password file:\ncd \/u01\/app\/oracle\/product\/11.2.0\/db_1\/dbs\nrm orapwTESTE\norapwd file=orapwTESTE password=oracle ignorecase=y entries=5<\/code><\/pre>\n\n\n<p>ORA-12514: TNS:listener does not currently know of service requested in  connect descriptor<\/p>\n\n\n<pre class=\"wp-block-code\"><code>Solution:\nUpdate the listener.ora e try to reload the listener :\nSID_LIST_LISTENER =\n  (SID_LIST =\n    (SID_DESC =\n      (GLOBAL_DBNAME = TESTE)\n      (ORACLE_HOME = \/u01\/app\/oracle\/product\/11.2.0\/db_1)\n      (SID_NAME = TESTE)\n    )\n  )\nLISTENER =\n  (DESCRIPTION_LIST =\n    (DESCRIPTION =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = srvoraclestby)(PORT = 1521))\n    )\n    (DESCRIPTION =\n      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))\n    )\n  )\n<\/code><\/pre>\n\n\n<pre class=\"wp-block-code\"><code>Error: Creation of redo logs - not necessary to restore database again - need recreate controlfile.\n GROUP  14 ( '\/backups\/oradata\/TESTE\/bdo\/onlinelog\/group_14.1974.947483695', '\/backups\/oradata\/TESTE\/bdo\/onlinelog\/group_14.2908.947483697' ) SIZE 120 M  REUSE\nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-03002: failure of Duplicate Db command at 06\/10\/2020 04:28:03\nRMAN-05501: aborting duplication of target database\nRMAN-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\nORA-27040: erro ao criar arquivo, n?o foi possivel criar o arquivo\nLinux-x86_64 Error: 2: No such file or directory\nshutdown database, create the controlfile pointing to all new datafiles and open database.\nAdd tempfile to temporary tablespaces.\nmove\/remove controlfile.\nmv \/backups\/oradata\/TESTE\/control01.ctl \/backups\/oradata\/TESTE\/co.old\nmkdir -p \/backups\/oradata\/TESTE\/bdo\/onlinelog\/\nsqlplus \/ as sysdba\nshut abort\nstartup nomount\n CREATE CONTROLFILE reuse SET DATABASE \"TESTE\" RESETLOGS NOARCHIVELOG\n  MAXLOGFILES    192\n  MAXLOGMEMBERS      3\n  MAXDATAFILES     1024\n  MAXINSTANCES    32\n  MAXLOGHISTORY    11680\n LOGFILE\n  GROUP   1 ( '\/backups\/oradata\/TESTE\/bdo\/onlinelog\/group_1.291.947518073', '\/backups\/oradata\/TESTE\/bdo\/onlinelog\/group_1.290.947518075' ) SIZE 120 M  REUSE,\n  GROUP   2 ( '\/backups\/oradata\/TESTE\/bdo\/onlinelog\/group_2.289.947518075', '\/backups\/oradata\/TESTE\/bdo\/onlinelog\/group_2.288.947518075' ) SIZE 120 M  REUSE,\n  GROUP   3 ( '\/backups\/oradata\/TESTE\/bdo\/onlinelog\/group_3.287.947518075', '\/backups\/oradata\/TESTE\/bdo\/onlinelog\/group_3.284.947518075' ) SIZE 120 M  REUSE\nDATAFILE\n'\/backups\/oradata\/TESTE\/system.278.853379527'\n,'\/backups\/oradata\/TESTE\/users.277.853379111'\n,'\/backups\/oradata\/TESTE\/undotbs2.5543.947483683'\n,'\/backups\/oradata\/TESTE\/undotbs1.292.947518065'\n,'\/backups\/oradata\/TESTE\/sysaux.275.853379001'\n,'\/backups\/oradata\/TESTE\/sanklob06.dbf'\n,'\/backups\/oradata\/TESTE\/sanklob05.dbf'\n,'\/backups\/oradata\/TESTE\/sanklob04.dbf'\n,'\/backups\/oradata\/TESTE\/sanklob03.dbf'\n,'\/backups\/oradata\/TESTE\/sanklob02.dbf'\n,'\/backups\/oradata\/TESTE\/sanklob01.dbf'\n,'\/backups\/oradata\/TESTE\/sankind05.dbf'\n,'\/backups\/oradata\/TESTE\/sankind03.dbf'\n,'\/backups\/oradata\/TESTE\/sankind02.dbf'\n,'\/backups\/oradata\/TESTE\/sankind01.dbf'\n,'\/backups\/oradata\/TESTE\/sankhya05.dbf'\n,'\/backups\/oradata\/TESTE\/sankhya04.dbf'\n,'\/backups\/oradata\/TESTE\/sankhya03.dbf'\n,'\/backups\/oradata\/TESTE\/sankhya02.dbf'\n,'\/backups\/oradata\/TESTE\/sankhya01.dbf'\n,'\/backups\/oradata\/TESTE\/sanlob08.dbf'\n,'\/backups\/oradata\/TESTE\/sanklob10.dbf'\n,'\/backups\/oradata\/TESTE\/sanklob08.dbf'\n,'\/backups\/oradata\/TESTE\/sanklob07.dbf'\n,'\/backups\/oradata\/TESTE\/sankind09.dbf'\n,'\/backups\/oradata\/TESTE\/sankind08.dbf'\n,'\/backups\/oradata\/TESTE\/sankind07.dbf'\n,'\/backups\/oradata\/TESTE\/sankind06.dbf'\n,'\/backups\/oradata\/TESTE\/sankhya09.dbf'\n,'\/backups\/oradata\/TESTE\/sankhya08.dbf'\n,'\/backups\/oradata\/TESTE\/sankhya07.dbf'\n,'\/backups\/oradata\/TESTE\/sankhya06.dbf'\nCHARACTER SET WE8ISO8859P1\n;\nSQL&gt; alter database open resetlogs;\nBanco de dados alterado.\nCheck the alert log for errors:\n*********************************************************************\nWARNING: The following temporary tablespaces contain no files.\n         This condition can occur when a backup controlfile has\n         been restored.  It may be necessary to add files to these\n         tablespaces.  That can be done using the SQL statement:\n         ALTER TABLESPACE &lt;tablespace_name&gt; ADD TEMPFILE\n         Alternatively, if these temporary tablespaces are no longer\n         needed, then they can be dropped.\n           Empty temporary tablespace: TEMP\n*********************************************************************\nSQL&gt; alter tablespace temp add tempfile '\/backups\/oradata\/TESTE\/temp02.dbf' size 10G autoextend on;\nTablespace alterado.\nscripts$ sqlplus \/ as sysdba\nSQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 09:54:47 2020\nCopyright (c) 1982, 2011, Oracle.  All rights reserved.\nConectado a:\nOracle Database 11g Release 11.2.0.3.0 - 64bit Production\nDB_UNIQUE_NAME   DATABASE_ROLE    OPEN_MODE            VERSION    COMPATIBLE STATUS\n---------------- ---------------- -------------------- ---------- ---------- --------\nTESTE            PRIMARY          READ WRITE           11.2.0.3.0 11.2.0.0.0 OPEN\nHOST_NAME                          INSTANCE_NAME  STATUS   Startup time       LOGINS   CONNECTED\n---------------------------------- -------------- -------- ------------------ -------- ------------\nsrvoraclestby                      TESTE          OPEN     10-JUN-2020 09:44  ALLOWED  CURRENT\nSQL&gt;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>We have a lot of posts todo the same &#8211; 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 &hellip; <a href=\"https:\/\/www.soudba.com.br\/?p=1197\">Continue lendo <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[365,367,383,384,385,396,397],"class_list":["post-1197","post","type-post","status-publish","format-standard","hentry","category-sem-categoria","tag-duplicate-auxiliary","tag-duplicate-using-backup-location","tag-ora-01261","tag-ora-09925","tag-ora-12514","tag-rman-04014","tag-rman-06403"],"_links":{"self":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts\/1197","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1197"}],"version-history":[{"count":0,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts\/1197\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1197"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1197"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1197"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}