By Franck Pachot

.
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.

The primary database

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)

Data Guard configuration

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.

Snapshot standby

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

Log shipping

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.

Transport and Apply lag

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:

  • remove the standby configuration
  • delete all the unwanted archived logs
  • change DBID and DB_NAME to new ones

Remove standby database configuration

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’.

DBNEWID

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.

Flashback logs

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
---------------- --------- ---------- --------- ----

Summary

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.