Oracle Certified Master's - Blogs: MANUAL ACTIVE DUPLICATION FAILURE AND FIXUP ISSUE | RMAN-03002: failure of Duplicate Db command

MANUAL ACTIVE DUPLICATION FAILURE AND FIXUP ISSUE -

· SOURCE DATABASE : NCTEST@VM215

· TARGET DATABASE : NPTEST@VM216

· DATABASE VERISON: 12.1.0.2

In my case active duplication failed cause of missing Archivelog on production (source)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 06/15/2020 08:55:19

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 78 and starting SCN of 2054903

RMAN>

Recovery Manager complete.

Duplicate issue during missing archive from prod..

[oracle@vm216 script]$ nohup ./active_NPTEST_dup.sh &

[1] 28521

[oracle@vm216 script]$ ls -ltrah

total 100K

-rw-------. 1 oracle oinstall 128 Jun 10 12:46 nohup.out

-rw-r--r--. 1 oracle oinstall 13K Jun 10 12:51 NPTEST_act_Duplicate_06_10_2020_12-47-59.log

-rw-r--r--. 1 oracle oinstall 5.2K Jun 10 13:03 NPTEST_act_Duplicate_06_10_2020_13-02-19.log

-rw-r--r--. 1 oracle oinstall 9.7K Jun 10 13:09 NPTEST_act_Duplicate_06_10_2020_13-06-44.log

-rw-r--r--. 1 oracle oinstall 410 Jun 10 13:14 open.sql

-rw-r--r--. 1 oracle oinstall 13K Jun 13 14:13 NPTEST_act_Duplicate_06_13_2020_14-11-46.log

-rw-r--r--. 1 oracle oinstall 5.2K Jun 14 22:14 NPTEST_act_Duplicate_06_14_2020_22-13-14.log

-rw-r--r--. 1 oracle oinstall 2.7K Jun 14 22:17 NPTEST_act_Duplicate_06_14_2020_22-17-18.log

-rwxr-xr-x. 1 oracle oinstall 1.1K Jun 14 22:18 active_NPTEST_dup.sh

drwx------. 34 oracle oinstall 4.0K Jun 14 22:18 ..

-rw-r--r--. 1 oracle oinstall 9.7K Jun 14 22:22 NPTEST_act_Duplicate_06_14_2020_22-18-57.log

drwxr-xr-x. 2 oracle oinstall 4.0K Jun 15 08:52 .

-rw-r--r--. 1 oracle oinstall 889 Jun 15 08:52 NPTEST_act_Duplicate_06_15_2020_08-52-21.log

Check the duplication logs ... the reason of failure

[oracle@vm216 script]$ cat NPTEST_act_Duplicate_06_15_2020_08-52-21.log

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jun 15 08:52:21 2020

RMAN>

connected to target database: NCTEST (DBID=2691280463)

RMAN>

connected to auxiliary database: NPTEST (not mounted)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19>

using target database control file instead of recovery catalog

allocated channel: t1

channel t1: SID=35 device type=DISK

allocated channel: t2

channel t2: SID=65 device type=DISK

allocated channel: t3

channel t3: SID=51 device type=DISK

allocated channel: t4

channel t4: SID=33 device type=DISK

allocated channel: t5

channel t5: SID=56 device type=DISK

allocated channel: t6

channel t6: SID=32 device type=DISK

allocated channel: t7

channel t7: SID=54 device type=DISK

allocated channel: t8

channel t8: SID=73 device type=DISK

allocated channel: a1

channel a1: SID=543 device type=DISK

allocated channel: a2

channel a2: SID=805 device type=DISK

allocated channel: a3

channel a3: SID=6 device type=DISK

allocated channel: a4

channel a4: SID=276 device type=DISK

allocated channel: a5

channel a5: SID=544 device type=DISK

allocated channel: a6

channel a6: SID=811 device type=DISK

allocated channel: a7

channel a7: SID=7 device type=DISK

allocated channel: a8

channel a8: SID=277 device type=DISK

Starting Duplicate Db at 15-JUN-20

current log archived

contents of Memory Script:

{

sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 629145600 bytes

Fixed Size 2927528 bytes

Variable Size 520094808 bytes

Database Buffers 100663296 bytes

Redo Buffers 5459968 bytes

allocated channel: a1

channel a1: SID=543 device type=DISK

allocated channel: a2

channel a2: SID=805 device type=DISK

allocated channel: a3

channel a3: SID=6 device type=DISK

allocated channel: a4

channel a4: SID=276 device type=DISK

allocated channel: a5

channel a5: SID=544 device type=DISK

allocated channel: a6

channel a6: SID=811 device type=DISK

allocated channel: a7

channel a7: SID=7 device type=DISK

allocated channel: a8

channel a8: SID=277 device type=DISK

contents of Memory Script:

{

sql clone "alter system set db_name =

''NCTEST'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''NPTEST'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone from service 'NCTEST' primary controlfile;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''NCTEST'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''NPTEST'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 629145600 bytes

Fixed Size 2927528 bytes

Variable Size 520094808 bytes

Database Buffers 100663296 bytes

Redo Buffers 5459968 bytes

allocated channel: a1

channel a1: SID=543 device type=DISK

allocated channel: a2

channel a2: SID=805 device type=DISK

allocated channel: a3

channel a3: SID=6 device type=DISK

allocated channel: a4

channel a4: SID=276 device type=DISK

allocated channel: a5

channel a5: SID=544 device type=DISK

allocated channel: a6

channel a6: SID=811 device type=DISK

allocated channel: a7

channel a7: SID=7 device type=DISK

allocated channel: a8

channel a8: SID=277 device type=DISK

Starting restore at 15-JUN-20

channel a1: starting datafile backup set restore

channel a1: using network backup set from service NCTEST

channel a1: restoring control file

channel a1: restore complete, elapsed time: 00:00:02

output file name=/u01/app/oracle/oradata/NPTEST/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/NPTEST/control02.ctl

Finished restore at 15-JUN-20

database mounted

contents of Memory Script:

{

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 5 to new;

set newname for clone datafile 6 to new;

restore

from service 'NCTEST' clone database

;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

Starting restore at 15-JUN-20

channel a1: starting datafile backup set restore

channel a1: using network backup set from service NCTEST

channel a1: specifying datafile(s) to restore from backup set

channel a1: restoring datafile 00001 to /u01/app/oracle/oradata/NPTEST/datafile/o1_mf_system_ %u_.dbf

channel a2: starting datafile backup set restore

channel a2: using network backup set from service NCTEST

channel a2: specifying datafile(s) to restore from backup set

channel a2: restoring datafile 00003 to /u01/app/oracle/oradata/NPTEST/datafile/o1_mf_sysaux_ %u_.dbf

channel a3: starting datafile backup set restore

channel a3: using network backup set from service NCTEST

channel a3: specifying datafile(s) to restore from backup set

channel a3: restoring datafile 00004 to /u01/app/oracle/oradata/NPTEST/datafile/o1_mf_undotbs 1_%u_.dbf

channel a4: starting datafile backup set restore

channel a4: using network backup set from service NCTEST

channel a4: specifying datafile(s) to restore from backup set

channel a4: restoring datafile 00005 to /u01/app/oracle/oradata/NPTEST/datafile/o1_mf_example _%u_.dbf

channel a5: starting datafile backup set restore

channel a5: using network backup set from service NCTEST

channel a5: specifying datafile(s) to restore from backup set

channel a5: restoring datafile 00006 to /u01/app/oracle/oradata/NPTEST/datafile/o1_mf_users_% u_.dbf

channel a5: restore complete, elapsed time: 00:00:01

channel a3: restore complete, elapsed time: 00:00:16

channel a4: restore complete, elapsed time: 00:01:27

channel a1: restore complete, elapsed time: 00:01:37

channel a2: restore complete, elapsed time: 00:01:37

Finished restore at 15-JUN-20

sql statement: alter system archive log current

current log archived

contents of Memory Script:

{

restore clone force from service 'NCTEST'

archivelog from scn 2054755;

switch clone datafile all;

}

executing Memory Script

Starting restore at 15-JUN-20

channel a1: starting archived log restore to default destination

channel a1: using network backup set from service NCTEST

channel a1: restoring archived log

archived log thread=1 sequence=80

channel a2: starting archived log restore to default destination

channel a2: using network backup set from service NCTEST

channel a2: restoring archived log

archived log thread=1 sequence=81

channel a1: restore complete, elapsed time: 00:00:00

channel a2: restore complete, elapsed time: 00:00:01

Finished restore at 15-JUN-20

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=1043139318 file name=/u01/app/oracle/oradata/NPTEST/datafil e/o1_mf_system_hgftfqh8_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=1043139318 file name=/u01/app/oracle/oradata/NPTEST/datafil e/o1_mf_sysaux_hgftfqtp_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=1043139318 file name=/u01/app/oracle/oradata/NPTEST/datafil e/o1_mf_undotbs1_hgftfs4c_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=9 STAMP=1043139318 file name=/u01/app/oracle/oradata/NPTEST/datafil e/o1_mf_example_hgftfsdh_.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=10 STAMP=1043139318 file name=/u01/app/oracle/oradata/NPTEST/datafi le/o1_mf_users_hgftfrvb_.dbf

contents of Memory Script:

{

set until scn 2055153;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 15-JUN-20

starting media recovery

archived log for thread 1 with sequence 80 is already on disk as file /u01/app/oracle/fast_re covery_area/NPTEST/archivelog/2020_06_15/o1_mf_1_80_hgftjx5r_.arc

archived log for thread 1 with sequence 81 is already on disk as file /u01/app/oracle/fast_re covery_area/NPTEST/archivelog/2020_06_15/o1_mf_1_81_hgftjx88_.arc

unable to find archived log

archived log thread=1 sequence=78

released channel: t1

released channel: t2

released channel: t3

released channel: t4

released channel: t5

released channel: t6

released channel: t7

released channel: t8

Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_system_hgftfqh8_.dbf'

released channel: a1

released channel: a2

released channel: a3

released channel: a4

released channel: a5

released channel: a6

released channel: a7

released channel: a8

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 06/15/2020 08:55:19

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 78 and starting SCN of 2054903

RMAN>

Recovery Manager complete.

[1]+ Exit 1 nohup ./active_NPTEST_dup.sh

Now connect to SYSDBA and get the archive sequence details which need to required recovery on target.

SQL> set lin200 pages 200

SQL> set numwidth 30;

SQL> set lin200 pagesize 50000;

alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';

select status,

checkpoint_change#,

checkpoint_time,

resetlogs_change#,

resetlogs_time,

count(*), fuzzy

from v$datafile_header

group by status,

checkpoint_change#,

checkpoint_time,

resetlogs_change#,

resetlogs_time,

fuzzy;

Session altered.

STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME RESETLOGS_CHANGE# RESETLOGS_TIME COUNT(*) FUZ

------- ------------------ -------------------- ------------------ -------------------- --------- ---

ONLINE 2054921 15-JUN-2020 08:53:35 1594143 11-JUN-2020 12:23:43 1 NO

ONLINE 2054922 15-JUN-2020 08:53:35 1594143 11-JUN-2020 12:23:43 1 YES

ONLINE 2054923 15-JUN-2020 08:53:35 1594143 11-JUN-2020 12:23:43 1 NO

ONLINE 2054929 15-JUN-2020 08:53:36 1594143 11-JUN-2020 12:23:43 1 NO

ONLINE 2054938 15-JUN-2020 08:53:36 1594143 11-JUN-2020 12:23:43 1 NO

SQL> select min(fhrba_Seq), max(fhrba_Seq)

from X$KCVFH;

MIN(FHRBA_SEQ) MAX(FHRBA_SEQ)

------------------------------ ------------------------------

78 79

So as per above output we identified than archive log sequence 78 and 79 has not been applied on target database (destination) because of archive log is missing on source(production). There could be many reason for missing archive log..

· Could be archive log has been deleted after backed

· Deleted archive log or missing archive destination on Source(production)

So to fix the issue we have to restore the missing archive log from production to target (destination) database server.

On Production

So as per old logs we found archive log has been deleted after backup during duplication

From Bakcup logs on production

Starting backup at 15-JUN-20

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=79 RECID=89 STAMP=1043139224

channel ORA_DISK_1: starting piece 1 at 15-JUN-20

channel ORA_DISK_1: finished piece 1 at 15-JUN-20

piece handle=/u01/app/oracle/fast_recovery_area/NCTEST/ backupset/2020_06_15/o1_mf_annnn_TAG20200615T085344_hgftg0p6_.bkp tag=TAG20200615T085344 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/NCTEST/archivelog/2020_06_15/o1_mf_1_79_hgftg09h_.arc RECID=89 STAMP=1043139224

Finished backup at 15-JUN-20

Now restore the archive log from archive backup piece.

RMAN> restore archivelog from logseq 78 until logseq 79 ;

Starting restore at 15-JUN-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=78

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/NCTEST/backupset/2020_06_15/o1_mf_annnn_TAG20200615T085336_hgftfsmx_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/NCTEST/backupset/2020_06_15/o1_mf_annnn_TAG20200615T085336_hgftfsmx_.bkp tag=TAG20200615T085336

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=79

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/NCTEST/backupset/2020_06_15/o1_mf_annnn_TAG20200615T085344_hgftg0p6_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/NCTEST/backupset/2020_06_15/o1_mf_annnn_TAG20200615T085344_hgftg0p6_.bkp tag=TAG20200615T085344

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 15-JUN-20

Copy archive log to target (destination server)

[oracle@vm215 ~]$ cd /u01/app/oracle/fast_recovery_area/NCTEST/archivelog/2020_06_15

[oracle@vm215 2020_06_15]$ ls

o1_mf_1_78_hgftq3tr_.arc o1_mf_1_79_hgftq4ym_.arc o1_mf_1_80_hgftjsog_.arc o1_mf_1_81_hgftjwrs_.arc

[oracle@vm215 2020_06_15]$ scp o1_mf_1_7* oracle@vm216:/tmp/archivelog

oracle@vm216's password:

o1_mf_1_78_hgftq3tr_.arc 100% 2048 2.0KB/s 00:00

o1_mf_1_79_hgftq4ym_.arc 100% 59KB 59.0KB/s 00:00

[oracle@vm215 2020_06_15]$

On Target (destination)

[oracle@vm216 script]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 15 09:04:28 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> recover database using backup controlfile ;

ORA-00279: change 2054903 generated at 06/15/2020 08:53:26 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/NPTEST/archivelog/2020_06_15/o1_mf_1_78_%u_.arc

ORA-00280: change 2054903 for thread 1 is in sequence #78

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/tmp/archivelog/o1_mf_1_78_hgftq3tr_.arc

ORA-00279: change 2054935 generated at 06/15/2020 08:53:36 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/NPTEST/archivelog/2020_06_15/o1_mf_1_79_%u_.arc

ORA-00280: change 2054935 for thread 1 is in sequence #79

ORA-00278: log file '/tmp/archivelog/o1_mf_1_78_hgftq3tr_.arc' no longer needed

for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/tmp/archivelog/o1_mf_1_79_hgftq4ym_.arc

ORA-00279: change 2055085 generated at 06/15/2020 08:53:44 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/NPTEST/archivelog/2020_06_15/o1_mf_1_80_hgftj

x5r_.arc

ORA-00280: change 2055085 for thread 1 is in sequence #80

ORA-00278: log file '/tmp/archivelog/o1_mf_1_79_hgftq4ym_.arc' no longer needed

for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open resetlogs ;

alter database open resetlogs

ERROR at line 1:

ORA-19838: Cannot use this control file to open database

So here it required to recreate the controlfile to open the database with resetlogs.

Now dump the trace controlfile to recreate controlfile script on target

SQL> alter database backup controlfile to trace as '/tmp/control_15Jun2020_1.txt';

Database altered.

[oracle@vm216 script]$ more /tmp/control_15Jun2020_1.txt

-- The following are current System-scope REDO Log Archival related

-- parameters and can be included in the database initialization file.

-- LOG_ARCHIVE_DEST=''

-- LOG_ARCHIVE_DUPLEX_DEST=''

-- LOG_ARCHIVE_FORMAT=NPTEST_%t_%s_%r.arc

-- DB_UNIQUE_NAME="NPTEST"

-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'

-- LOG_ARCHIVE_MAX_PROCESSES=4

-- STANDBY_FILE_MANAGEMENT=MANUAL

-- STANDBY_ARCHIVE_DEST=?/dbs/arch

-- FAL_CLIENT=''

-- FAL_SERVER=''

-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

-- Below are two sets of SQL statements, each of which creates a new

-- control file and uses it to open the database. The first set opens

-- the database with the NORESETLOGS option and should be used only if

-- the current versions of all online logs are available. The second

-- set opens the database with the RESETLOGS option and should be used

-- if online logs are unavailable.

-- The appropriate set of statements can be copied from the trace into

-- a script file, edited as necessary, and executed when there is a

-- need to re-create the control file.

-- Set #1. NORESETLOGS case

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline

-- Use this only if the current versions of all online logs are

-- available.

-- WARNING! The current control file needs to be checked against

-- the datafiles to insure it contains the correct files. The

-- commands printed here may be missing log and/or data files.

-- Another report should be made after the database has been

-- successfully opened.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "NCTEST" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/NCTEST/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/NCTEST/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/NCTEST/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_system_hgftfqh8_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_sysaux_hgftfqtp_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_undotbs1_hgftfs4c_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_example_hgftfsdh_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_users_hgftfrvb_.dbf'

CHARACTER SET AL32UTF8

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/NPTEST/archivelog/2020

_06_15/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/NPTEST/archivelog/2020

_06_15/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/NCTEST/temp01.dbf' REUSE;

-- End of tempfile additions.

-- Set #2. RESETLOGS case

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

-- WARNING! The current control file needs to be checked against

-- the datafiles to insure it contains the correct files. The

-- commands printed here may be missing log and/or data files.

-- Another report should be made after the database has been

-- successfully opened.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "NCTEST" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/NCTEST/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/NCTEST/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/NCTEST/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_system_hgftfqh8_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_sysaux_hgftfqtp_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_undotbs1_hgftfs4c_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_example_hgftfsdh_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_users_hgftfrvb_.dbf'

CHARACTER SET AL32UTF8

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/NPTEST/archivelog/2020

_06_15/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/NPTEST/archivelog/2020

_06_15/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/NCTEST/temp01.dbf' REUSE;

-- End of tempfile additions.

[oracle@vm216 script]$

Script for CONTROLFILE

CREATE CONTROLFILE REUSE SET DATABASE "NCTEST" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/NPTEST/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/NPTEST/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/NPTEST/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_system_hgftfqh8_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_sysaux_hgftfqtp_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_undotbs1_hgftfs4c_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_example_hgftfsdh_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_users_hgftfrvb_.dbf'

CHARACTER SET AL32UTF8

;

Now recreating controlfile on target

Shutdown the database and start in nomount to recreate the controlfile

[oracle@vm216 script]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 15 09:40:26 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shut immediate ;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup nomount

ORACLE instance started.

Total System Global Area 629145600 bytes

Fixed Size 2927528 bytes

Variable Size 520094808 bytes

Database Buffers 100663296 bytes

Redo Buffers 5459968 bytes

Execute the controlfile script to recreate

SQL> CREATE CONTROLFILE REUSE SET DATABASE "NCTEST" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/NPTEST/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/NPTEST/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/NPTEST/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_system_hgftfqh8_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_sysaux_hgftfqtp_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_undotbs1_hgftfs4c_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_example_hgftfsdh_.dbf',

'/u01/app/oracle/oradata/NPTEST/datafile/o1_mf_users_hgftfrvb_.dbf'

CHARACTER SET AL32UTF8

;

Control file created.

Now we are good to go to open database with read write mode.

SQL> alter database open resetlogs ;

Database altered.

SQL> @open

DB_NAME INSTANCE_NAME VERSION HOST_NAME OPEN_MODE STARTUP_TIME CURR_SCN

-------- -------------- ------------ --------------- ----------- ------------------------ ---------

NCTEST NPTEST 12.1.0.2.0 vm216.ora.com READ WRITE 15-JUN-2020 09:43:25 AM 2056142

Cool.. Now db is opened in read write mode..

Now need to change the dbname NCTEST to NPTEST...

[oracle@vm216 script]$ which nid

/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/nid

[oracle@vm216 script]$ nid TARGET=sys/oracle@NPTEST DBNAME=NPTEST

DBNEWID: Release 12.1.0.2.0 - Production on Mon Jun 15 09:58:50 2020

Connected to database NCTEST (DBID=2691280463)

Connected to server version 12.1.0

Control Files in database:

/u01/app/oracle/oradata/NPTEST/control01.ctl

/u01/app/oracle/fast_recovery_area/NPTEST/control02.ctl

Change database ID and database name NCTEST to NPTEST? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 2691280463 to 740075866

Changing database name from NCTEST to NPTEST

Control File /u01/app/oracle/oradata/NPTEST/control01.ctl - modified

Control File /u01/app/oracle/fast_recovery_area/NPTEST/control02.ctl - modified

Datafile /u01/app/oracle/oradata/NPTEST/datafile/o1_mf_system_hgftfqh8_.db - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/NPTEST/datafile/o1_mf_sysaux_hgftfqtp_.db - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/NPTEST/datafile/o1_mf_undotbs1_hgftfs4c_.db - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/NPTEST/datafile/o1_mf_example_hgftfsdh_.db - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/NPTEST/datafile/o1_mf_users_hgftfrvb_.db - dbid changed, wrote new name

Control File /u01/app/oracle/oradata/NPTEST/control01.ctl - dbid changed, wrote new name

Control File /u01/app/oracle/fast_recovery_area/NPTEST/control02.ctl - dbid changed, wrote new name

Instance shut down

Database name changed to NPTEST.

Modify parameter file and generate a new password file before restarting.

Database ID for database NPTEST changed to 740075866.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

[oracle@vm216 script]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 15 09:59:57 2020

Connected to an idle instance.

SQL> startup mount ;

ORACLE instance started.

Total System Global Area 629145600 bytes

Fixed Size 2927528 bytes

Variable Size 520094808 bytes

Database Buffers 100663296 bytes

Redo Buffers 5459968 bytes

ORA-01103: database name 'NPTEST' in control file is not 'NCTEST'

SQL> SHOW PARAMETER DB_NAME

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string NCTEST

SQL>

SQL> ALTER SYSTEM SET DB_NAME=NPTEST SCOPE=SPFILE ;

System altered.

SQL>

SQL> SHUT IMMEDIATE ;

ORA-01507: database not mounted

ORACLE instance shut down.

Recreate the password file if required. In my case i have already been done.

[oracle@vm216 dbs]$ ls -ltrah orapw*

-rw-r-----. 1 oracle oinstall 7.5K Sep 6 2015 orapwcdb1

-rw-r-----. 1 oracle oinstall 7.5K Sep 6 2015 orapwcdb2

-rw-r-----. 1 oracle oinstall 7.5K Oct 12 2017 orapwcdb5

-rw-r-----. 1 oracle oinstall 7.5K Oct 12 2017 orapwarnav

-rw-r-----. 1 oracle oinstall 7.5K Oct 12 2017 orapwPAVAN

-rw-r-----. 1 oracle oinstall 1.5K Sep 19 2018 orapwPROD1

-rw-r-----. 1 oracle oinstall 7.5K Jun 10 12:42 orapwNCTEST

-rw-r-----. 1 oracle oinstall 7.5K Jun 10 12:43 orapwNPTEST

[oracle@vm216 dbs]$ exit

Open database in resetlogs mode.

[oracle@vm216 script]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 15 10:02:17

startuConnected to an idle instance.

SQL> startup mount ;

ORACLE instance started.

Total System Global Area 629145600 bytes

Fixed Size 2927528 bytes

Variable Size 520094808 bytes

Database Buffers 100663296 bytes

Redo Buffers 5459968 bytes

Database mounted.

Also validate the listener registration to correct service..

SQL> !

[oracle@vm216 script]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-JUN-2020 10:03:39

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

Service "NPTEST" has 1 instance(s).

Instance "NPTEST", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

The command completed successfully

Open database in resetlogs after using nid

SQL> alter database open resetlogs ;

Database altered.

SQL> @open

DB_NAME INSTANCE_NAME VERSION HOST_NAME OPEN_MODE STARTUP_TIME CURR_SCN

---------- ------------- ----------- --------------- ----------- ------------------------ --------------

NPTEST NPTEST 12.1.0.2.0 vm216.ora.com READ WRITE 15-JUN-2020 10:03:15 AM 2057550

no rows selected

Check the listener registration

[oracle@vm216 script]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-JUN-2020 10:18:23

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

Service "NPTEST" has 1 instance(s).

Instance "NPTEST", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "NPTESTXDB" has 1 instance(s).

Instance "NPTEST", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: vm216.ora.com, pid: 31240>

(ADDRESS=(PROTOCOL=tcp)(HOST=vm216.ora.com)(PORT=25802))

The command completed successfully

[oracle@vm216 script]$

So we have fix the duplication issue without re-executing the duplication command.

Oracle Certified Master's - Blogs

Home

Monday, June 15, 2020

MANUAL ACTIVE DUPLICATION FAILURE AND FIXUP ISSUE | RMAN-03002: failure of Duplicate Db command | RMAN-06054

No comments:

Post a Comment