12c Database : Step by Step standby creation using RMAN duplicate database

Step by Step standby creation using RMAN duplicate database

This post is about to create a standby database in container enabled database (Just want to check any changes required from previous versions steps) All steps are similar like previous versions except we have to create directory structures for PDB databases otherwise RMAN script will fail Please go through the steps to create a standby database in same host. Environment is as follows

	HostName: Geek DBA12c
	Primary DB: DB12c
	Standby DB: STBY12C

As all of you aware you donot required backups or filecopy to create standby from 11g onwards using Duplicate database for standby using active database, which connects to primary creates an onfly image copy backup and send to standby destination.

Further in 12c, this has been enhanced, the image copies can be send to destination host in small chunks (section copies) which I will post later.

For now, here are the steps to create standby database.

Keep the Primary Database in Force Logging mode

	oracle@Geek DBA12c ~]$ sqlplus / as sysdba

	SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 15 13:26:48 2013

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


	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> select force_logging from v$database;

	FORCE_LOGGING
	---------------------------------------
	NO

	SQL> alter database force logging;

	Database altered.

	SQL> exit

On Primary Database, Create Standby redologs

	ALTER DATABASE ADD STANDBY LOGFILE
	'/u01/app/oracle/oradata/DB12c/srl_redo04.log' 
	SIZE 52428800
	
	/
	
	
	ALTER DATABASE ADD STANDBY LOGFILE
	'/u01/app/oracle/oradata/DB12c/srl_redo05.log' 
	SIZE 52428800
	
	/

	
	ALTER DATABASE ADD STANDBY LOGFILE
	'/u01/app/oracle/oradata/DB12c/srl_redo06.log' 
	SIZE 52428800

Set Primary Database Parameters : DB12C

	SQL> SHOW PARAMETER DB_NAME

	NAME                                 TYPE        VALUE
	------------------------------------ ----------- ------------------------------
	db_name                              string      DB12c
	SQL> SHOW PARAMETER DB_UNIQUE_NAME

	NAME                                 TYPE        VALUE
	------------------------------------ ----------- ------------------------------
	db_unique_name                       string      DB12c

	SQL> ALTER SYSTEM SET log_archive_config = 'dg_config=(DB12C,STBY12C)'; 

	System altered.

	SQL> ALTER SYSTEM SET log_archive_dest_2 = 'service=STBY12C async valid_for=(online_logfile,primary_role) db_unique_name=STBY12C';

	System altered.

Update the TNSNAMES.ora with standby tns entry;

	# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.1/db_1/network/admin/tnsnames.ora
	# Generated by Oracle configuration tools.

	DB12C =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12c.localdomain)(PORT = 1521))
	    (CONNECT_DATA =
	      (SERVER = DEDICATED)
	      (SERVICE_NAME = DB12c)
	    )
	  )

	PDB12C =
	  (DESCRIPTION =
	   (ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12c.localdomain)(PORT = 1521))
	  (CONNECT_DATA =
	     (SERVER = DEDICATED)
	     (SERVICE_NAME = pdb12c)
	    )
	  )

	stby12c =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12c.localdomain)(PORT = 1521))
	    (CONNECT_DATA =
	      (SERVER = DEDICATED)
	      (SERVICE_NAME = STBY12C)
	    )
	  )

Update the Listener.ora for Standby Service to be registered with listener.

	LISTENER =
	  (DESCRIPTION_LIST =
	    (DESCRIPTION =
	      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
	      (ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12c.localdomain)(PORT = 1521))
	    )
	  )
	
	SID_LIST_LISTENER =
	  (SID_LIST =
	    (SID_DESC =
	      (GLOBAL_DBNAME = STBY12C)
	      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
	      (SID_NAME = STBY12C)
	    )
	  )
	

Copy the primary password file to Standby site password file

	[oracle@Geek DBA12c dbs]$ cp orapwDB12c orapwSTBY12C

Create a single entry init file for standby

	[oracle@Geek DBA12c dbs]$ echo db_name=STBY12C >> initSTBY12C.ora
	[oracle@Geek DBA12c dbs]$ cat initSTBY12C.ora
	db_name=STBY12C
	[oracle@Geek DBA12c dbs]$

Create a datafiles directory for standby

	[oracle@Geek DBA12c dbs]$ cd $ORACLE_BASE/oradata
	[oracle@Geek DBA12c oradata]$ ls -ltr
	total 4
	drwxr-x---. 5 oracle oinstall 4096 Sep 15 13:29 DB12c

	[oracle@Geek DBA12c oradata]$ mkdir STBY12C
	[oracle@Geek DBA12c oradata]$ ls -ltr
	total 8
	drwxr-x---. 5 oracle oinstall 4096 Sep 15 13:29 DB12c
	drwxr-xr-x. 2 oracle oinstall 4096 Sep 15 13:42 STBY12C
	[oracle@Geek DBA12c oradata]$

	[oracle@Geek DBA12c admin]$ ls -ltr
	total 4
	drwxr-x---. 6 oracle oinstall 4096 Sep  2 08:28 DB12c
	[oracle@Geek DBA12c admin]$ mkdir STBY12C
	[oracle@Geek DBA12c admin]$ mkdir -p STBY12C/adump STBY12c/new STBY12C/pdbseed STBY12C/pdb12c
	[oracle@Geek DBA12c admin]$

Startup the Standby by with parameter file we have created.

        [oracle@Geek DBA12c admin]$ export ORACLE_SID=STBY12C
	[oracle@Geek DBA12c admin]$ sqlplus / as sysdba

	SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 15 13:52:40 2013

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

	Connected to an idle instance.

	SQL> startup nomount pfile=$ORACLE_HOME/dbs/initSTBY12C.ora

	ORACLE instance started.

	Total System Global Area  217157632 bytes
	Fixed Size                  2286656 bytes
	Variable Size             159386560 bytes
	Database Buffers           50331648 bytes
	Redo Buffers                5152768 bytes
	SQL> SQL>

Connect to RMAN to TARGET (Primary) and Auxiliary Instance (Standby)

	[oracle@Geek DBA12c admin]$ rman auxiliary /

	Recovery Manager: Release 12.1.0.1.0 - Production on Sun Sep 15 14:07:27 2013

	Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

	connected to auxiliary database: STBY12C (not mounted)

	RMAN> connect target sys@db12c

	target database Password:
	connected to target database: DB12C (DBID=1279089071)

	RMAN>

# Run the RMAN Script below

	run {
        allocate channel prmy1 type disk;
	allocate channel prmy2 type disk;
	allocate channel prmy3 type disk;
	allocate channel prmy4 type disk;
	allocate auxiliary channel stby type disk;
	duplicate target database for standby from active database
	spfile
		parameter_value_convert 'DB12c','STBY12C'
		set db_unique_name='STBY12C'
		set db_file_name_convert='/DB12c/','/STBY12C/'
		set log_file_name_convert='/DB12c/','/STBY12C/'
		set control_files='/u01/app/oracle/oradata/STBY12C/STBY12C1.ctl'
		set log_archive_max_processes='5'
		set fal_client='STBY12C'
		set fal_server='DB12C'
		set standby_file_management='AUTO'
		set log_archive_config='dg_config=(DB12C,STBY12C)'
		set log_archive_dest_2='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DB12C'
     ;
     }

Standby database create completed,

To keep the length of this post short, I have copied the log to attachment, CLick here –>

Please see the RMAN memory script logfile Login To standby database and keep the standby in real time apply mode

	[oracle@Geek DBA12c stage]$ . oraenv
	ORACLE_SID = [STBY12C] ? STBY12C
	The Oracle base remains unchanged with value /u01/app/oracle
	[oracle@Geek DBA12c stage]$ sqlplus / as sysdba

	SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 15 16:34:00 2013

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


	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 disconnect from session using current logfile;

	Database altered.

	SQL>

To check the standby working or not

	On Primary, provide couple of manual switches
	
	SQL> Alter system switch logfile;
	SQL> Alter system switch logfile;
	SQL> Verify the latest sequence in primary database
	SQL> archive log list
	Database log mode              Archive Mode
	Automatic archival             Enabled
	Archive destination            USE_DB_RECOVERY_FILE_DEST
	Oldest online log sequence     90
	Next log sequence to archive   92
	Current log sequence           92

	On Standby check, the status of MRP process
	
	SQL> select process,status,blocks,sequence# from v$managed_standby;
	
	PROCESS   STATUS           BLOCKS  SEQUENCE#
	--------- ------------ ---------- ----------
	ARCH      CLOSING               2         88
	ARCH      CLOSING               6         90
	ARCH      CONNECTED             0          0
	ARCH      CLOSING              22         87
	ARCH      CLOSING             659         91
	RFS       IDLE                  0          0
	RFS       IDLE                  0          0
	RFS       IDLE                  1         92
	MRP0      APPLYING_LOG     102400         92
	
	9 rows selected.
	
	SQL>

The standby is ready and sync with production database.

Next posts:-

1) 12C Network restore using service

2) 12C, rollforward of standby using service