This was first published on https://blog.dbi-services.com/convert-a-snapshot-standby-to-a-new-database (2015-06-12)
Republishing here for new followers. The content is related to the the versions available at the publication date
A snapshot standby database is a nice solution when you want a temporary copy of the primary where you can do whatever you want (test a bug fix for example) and then convert it back to physical standby. But you don’t want to stay in that state definitely because you will accumulate archived logs from the changes done to the primary, and flashback logs for the changes made to the snapshot standby.
Here I’ll show how to finish it when you want a permanent new database. You can use that when you want to do a duplicate, but don’t know in advance which point-in-time should be used. Then you just configure a physical standby, convert it to snapshot standby, maybe flashback it the the required point. And once you have found the right point, then you finish it to have a new database name and DBID.
Here is the primary on host VM111, db name is DEMO11 and db unique name is DEMO11. I’m checking the DBID:
[oracle@VM111 ~]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Thu May 28 22:36:07 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: DEMO11 (DBID=684456715)
The standby is on VM112, db name is DEMO11 and db unique name is DEMO12:
[oracle@VM112 ~]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show database demo12 Database - demo12 Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Average Apply Rate: 52.00 KByte/s Real Time Query: ON Instance(s): DEMO12 Database Status: SUCCESS
I’ve a standby that is in sync.
I want to convert that standby to a new database. For the moment, I’ll only convert to a snapshot standby, so that I can flashback if I need to apply more archived logs later and start again from another point.
DGMGRL> convert database demo12 to snapshot standby; Converting database "demo12" to a Snapshot Standby database, please wait... Database "demo12" converted successfully
A restore point has been automatically created in order to be able to flashback to the point where log apply can continue:
RMAN> list restore point all; using target database control file instead of recovery catalog SCN RSP Time Type Time Name ---------------- --------- ---------- --------- ---- 2156577 GUARANTEED 28-MAY-15 SNAPSHOT_STANDBY_REQUIRED_05/28/2015 22:36:46
The snapshot standby is a database that is open read-write. The only difference with a new database (a duplicate for example) is that the DBID is still the same as the primary. But the advantage is that it stills receives the archived log and can be converted back to a standby database. Look at the primary and switch a few logfiles:
[oracle@VM111 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu May 28 22:39:36 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. 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> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 47 Next log sequence to archive 49 Current log sequence 49 SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered.
and check at the standby site:
[oracle@VM112 ~]$ NLS_DATE_FORMAT='dd-mon-yy hh24:mi:ss' rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Thu May 28 22:41:35 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: DEMO11 (DBID=684456715) RMAN> list archivelog all; ... 44 1 49 A 28-may-15 22:39:32 Name: /u02/DEMO/fast_recovery_area/DEMO12/archivelog/2015_05_28/o1_mf_1_49_bpgz9pxt_.arc 45 1 50 A 28-may-15 22:40:22 Name: /u02/DEMO/fast_recovery_area/DEMO12/archivelog/2015_05_28/o1_mf_1_50_bpgzbbjs_.arc 46 1 51 A 28-may-15 22:40:42 Name: /u02/DEMO/fast_recovery_area/DEMO12/archivelog/2015_05_28/o1_mf_1_51_bpgzbg5x_.arc
all the archived logs are there. You can see that DBID is the same as the primary.
Let’s check the lag:
[oracle@VM111 ~]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show database demo12 Database - demo12 Role: SNAPSHOT STANDBY Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 5 minutes 4 seconds (computed 1 second ago) Instance(s): DEMO12 Database Status: SUCCESS
I have an apply lag (the apply stopped when I converted the physical standby to a snapshot standby) but there is no transport lag.
At that point, I have my new database. I can do whatever I want, with the possibility to convert back to a standby, apply more archived logs, re-convert to a snapshot standby, etc. Then when I’m happy with it, I’ll finalize it:
This is done from Data Guard Broker and it will stop log shipping to the standby.
DGMGRL> remove database demo12; Removed database "demo12" from the configuration
At that point we can remove the archived logs on the standby site (rman delete archivelog all). We can also check the parameters that have been set by standby configuration(file name convert, fal_server, archive_lag_target) and set the dg_broker_start if you don’t want it anymore. The log_archive_config and log_archive_dest should have been reset properly by the ‘remove database from configuration’.
I’m setting the DB_NAME to DEMO12 (instead of DEMO11) and I want a new DBID. This is done with ‘nid’ when in mount:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 473956352 bytes Fixed Size 2925744 bytes Variable Size 268438352 bytes Database Buffers 197132288 bytes Redo Buffers 5459968 bytes Database mounted. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@VM112 dbs]$ nid target=sys/oracle dbname=DEMO12 DBNEWID: Release 12.1.0.2.0 - Production on Thu May 28 22:52:04 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to database DEMO11 (DBID=684456715) Connected to server version 12.1.0 Control Files in database: /u01/DEMO/oradata/DEMO12/control01.ctl /u01/DEMO/fast_recovery_area/DEMO12/control02.ctl Change database ID and database name DEMO11 to DEMO12? (Y/[N]) => Y Proceeding with operation Changing database ID from 684456715 to 833681684 Changing database name from DEMO11 to DEMO12 Control File /u01/DEMO/oradata/DEMO12/control01.ctl - modified Control File /u01/DEMO/fast_recovery_area/DEMO12/control02.ctl - modified Datafile /u01/DEMO/oradata/DEMO12/system01.db - dbid changed, wrote new name Datafile /u01/DEMO/oradata/DEMO12/sysaux01.db - dbid changed, wrote new name Datafile /u01/DEMO/oradata/DEMO12/undotbs01.db - dbid changed, wrote new name Datafile /u01/DEMO/oradata/DEMO12/example01.db - dbid changed, wrote new name Datafile /u01/DEMO/oradata/DEMO12/users01.db - dbid changed, wrote new name Datafile /u01/DEMO/oradata/DEMO12/temp01.db - dbid changed, wrote new name Control File /u01/DEMO/oradata/DEMO12/control01.ctl - dbid changed, wrote new name Control File /u01/DEMO/fast_recovery_area/DEMO12/control02.ctl - dbid changed, wrote new name Instance shut down Database name changed to DEMO12. Modify parameter file and generate a new password file before restarting. Database ID for database DEMO12 changed to 833681684. 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.
I have to change the DB_NAME in the spfile and I also reset the parameters that I do not want as this database is not in a Data Guard configuration anymore:
SQL> alter system set db_name='DEMO12' scope=spfile dg_broker_start=false scope=spfile; System altered. SQL> alter system reset fal_server; System altered.
then I restart and open resetlogs:
SQL> startup force ORACLE instance started. Total System Global Area 473956352 bytes Fixed Size 2925744 bytes Variable Size 268438352 bytes Database Buffers 197132288 bytes Redo Buffers 5459968 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open resetlogs; Database altered.
I had to remove the archived logs, bit let’s check the flashback logs:
SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES ----------------------- ------------------ ------------------------- --------------- CONTROL FILE 0 0 0 REDO LOG 0 0 0 ARCHIVED LOG 0 0 0 BACKUP PIECE 0 0 0 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 AUXILIARY DATAFILE COPY 0 0 0
the cleanup has been done because the restore point has been removed:
RMAN> list restore point all; using target database control file instead of recovery catalog SCN RSP Time Type Time Name ---------------- --------- ---------- --------- ----
The operation is something simple. The goal of this post is to show in which order to do it so that there is a minimum cleanup to do. Snapshot standby is a very useful feature available in Enterprise Edition since 11g.
I got this issues
NID-00600: Internal Error – [30] [3113] [0] [0]
Change of database name and ID failed during validation – database is intact. DBNEWID – Completed with validation errors.
Alert log:
Errors in file /diagnostic/diag/rdbms/houstby/prod1/trace/prod1_rvwr_96533.trc: ORA-38734: Flashback log is inconsistent; belongs to another database. ORA-38701: Flashback database log 1 seq 1 thread 1: “/u01/app/oracle/fast_recovery_area/datastore/STBY/PRD/flashback/o1_mf_d47ph976_.flb” Sun Dec 04 22:38:32 2016