Cloning a DB from a Snapshot of my Physical Standby Database

11 Jun Cloning a DB from a Snapshot of my Physical Standby Database

The other day I had a client that when planning his backup solution he wanted to take snapshots of his DG environment and from that clone to his Dev/QA environments. So below is the result of that, which is probably really nothing you have not seen.

Even though I am using the DG broker, for the purpose of this exercise I will do it via the sqlplus interface, but I did wanted to show you before, the environment in the DGMGRL.

For this, I’m going to use as the standby orclstby and for the duplicate orcltest

[oracle@adg12c ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
26G 22G 3.3G 87% /
/dev/sda1 99M 24M 71M 25% /boot
tmpfs 2.0G 641M 1.4G 33% /dev/shm
/dev/sdb1 12G 159M 12G 2% /u02
[oracle@adg12c ~]$ . oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base has been set to /u01/app/oracle
[oracle@adg12c ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected as SYSDG.
DGMGRL> show configuration

Configuration - DGCONFIG

Protection Mode: MaxPerformance
Databases:
orcl - Primary database
farsync - Far Sync
orclstby - Physical standby database
farsync2 - Far Sync (inactive)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> exit

For this to work and not have any problems, we have to make sure that the Standby DB is consistent , and for this we have to stop the DG environment for the snapshot to happen.I also stopped the DB through this process, even though is not necessary, but it was the client’s request to do so during this process (Probably ease of mind).

[oracle@adg12c ~]$ . oraenv
ORACLE_SID = [orcl] ? orclstby
The Oracle base has been set to /u01/app/oracle

[oracle@adg12c ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 11 17:49:05 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

The snapshot is taken during this process, and for this exercise, it was put in the following directory /u02/app/oracle/oradata/orcltest/, in the same server, but it can be put in another server. You can emulate this with an scp (scp -r /u01/app/oracle/oradata/orclstby/ /u02/app/oracle/oradata/orcltest/)

[oracle@adg12c orclstby]$ cd /u02/app/oracle/oradata/orcltest/
[oracle@adg12c orcltest]$ ls
orclstby
[oracle@adg12c orcltest]$ cd orclstby
[oracle@adg12c orclstby]$ ls
control01.ctl ORCLSTBY redo01.log redo03.log srl02.log srl04.log system01.dbf undotbs01.dbf
orcl_pdb pdbseed redo02.log srl01.log srl03.log sysaux01.dbf temp01.dbf users01.dbf

Also once the snapshot finished, the Standby orclstby is brought up as well

[oracle@adg12c orclstby]$ . oraenv
ORACLE_SID = [farsync2] ? orclstby
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@adg12c orclstby]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 11 18:43:18 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 409194496 bytes
Fixed Size 2288968 bytes
Variable Size 318767800 bytes
Database Buffers 79691776 bytes
Redo Buffers 8445952 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

So now the next thing to do, is create a pfile for the orcltest database changing the location of the files corresponding to the new location

[oracle@adg12c orclstby]$ cat /tmp/initorcltest.ora
*.archive_lag_target=0
*.audit_file_dest='/u02/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/u02/app/oracle/oradata/orcltest/orclstby/control01.ctl','/u02/app/oracle/oradata/orcltest/orclstby/control02.ctl'
*.db_block_size=8192
*.db_domain='oracle.com'
*.db_name='orcltest'
*.db_recovery_file_dest='/u02/app/oracle/recovery_area'
*.db_recovery_file_dest_size=5368709120
*.dg_broker_start=TRUE
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
orcl.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
orcl.log_archive_trace=0
*.memory_target=390m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@adg12c orclstby]$ . oraenv
ORACLE_SID = [orclstby] ? orcltest
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@adg12c orclstby]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 11 18:30:28 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup nomount pfile='/tmp/initorcltest.ora';
ORACLE instance started.

Total System Global Area 409194496 bytes
Fixed Size 2288968 bytes
Variable Size 318767800 bytes
Database Buffers 79691776 bytes
Redo Buffers 8445952 bytes

Now, we just need to create the controlfile with the location of the snapshot datafiles and the new name of the database orlctest

SQL> CREATE CONTROLFILE SET DATABASE "orcltest" RESETLOGS
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/app/oracle/oradata/orcltest/orclstby/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/app/oracle/oradata/orcltest/orclstby/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/app/oracle/oradata/orcltest/orclstby/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u02/app/oracle/oradata/orcltest/orclstby/system01.dbf',
'/u02/app/oracle/oradata/orcltest/orclstby/sysaux01.dbf',
'/u02/app/oracle/oradata/orcltest/orclstby/undotbs01.dbf',
'/u02/app/oracle/oradata/orcltest/orclstby/pdbseed/system01.dbf',
'/u02/app/oracle/oradata/orcltest/orclstby/users01.dbf',
'/u02/app/oracle/oradata/orcltest/orclstby/pdbseed/sysaux01.dbf',
'/u02/app/oracle/oradata/orcltest/orclstby/orcl_pdb/system01.dbf',
'/u02/app/oracle/oradata/orcltest/orclstby/orcl_pdb/sysaux01.dbf',
'/u02/app/oracle/oradata/orcltest/orclstby/orcl_pdb/SAMPLE_SCHEMA_users01.dbf',
'/u02/app/oracle/oradata/orcltest/orclstby/orcl_pdb/example01.dbf'
CHARACTER SET WE8MSWIN1252
;
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
Control file created.

SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/app/oracle/oradata/orcltest/orclstby/temp01.dbf'
SIZE 62914560 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = PDB$SEED;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/app/oracle/oradata/orcltest/orclstby/pdbseed/pdbseed_temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = ORCL_PDB;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/app/oracle/oradata/orcltest/orclstby/orcl_pdb/orcl_pdb_temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = CDB$ROOT;
2
Tablespace altered.

SQL>
Session altered.

SQL> 2
Tablespace altered.

SQL>
Session altered.

SQL> 2

Tablespace altered.

SQL>
Session altered.

And as you can see, the new DB is up and running from a snapshot of the Standby Database

SQL> set lines 200 pages 9999
SQL> SELECT NAME,OPEN_MODE,PROTECTION_MODE,
PROTECTION_LEVEL,
DATABASE_ROLE ROLE,
SWITCHOVER_STATUS
FROM V$DATABASE;

NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- -------------------- ---------------- --------------------
ORCLTEST READ WRITE MAXIMUM PERFORMANCE UNPROTECTED PRIMARY NOT ALLOWED

SQL> select host_name,instance_name from gv$instance;

HOST_NAME INSTANCE_NAME
---------------------------------------------------------------- ----------------
adg12c.oracle.com orcltest

Conclusion
This is not the recommended way to do it, but it doesn’t mean it can’t be done. Also you can have loss of data as the Primary might not be fully synchronised with the standby, so you have to be sure what was the last log applied. Just make sure that this doesn’t have an impact on your DG , as for a minute there, the DG replication will be inactive so you can have a consistent database.