Active dataguard Archives

Creating Active Data Guard Physical Standby Environment

AIM:Without shutting down primary, we need to create physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command (No need to take backup of primary database)

Active Data Guard is a new option for Oracle Database 11g Enterprise Edition.

Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production/primary database.

0. Enviroment
1. Enable Forced Logging on Primary
2. Create Password File on Primary
3. Configure a Standby Redo Log on Primary
4. Verify Archive Mode Enabled on Primary
5. Set Primary Database Initialization Parameters
6. Transfer Spfile and Password file to Standby node
7. Set Standby Database Initialization Parameters
8. Crete required directories on Standby
9. Add below entry in ORATAB on Standby
10. Create spfile from pfile on Standby
11. Configure TNS Entries on Primary
12. Configure TNS Entries on Standby
13. Verify connection ‘AS SYSDBA’ from Primary
14. Create Standby Database
15. Enable MRP on Standby
16. Verify the Physical Standby Database Is Performing Properly

0. Enviroment

Source:

Platform          : Linuxx86_64
Server Name       : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version        : Oracle 11.2.0.3, File system: Normal
DB Name           : APAC, DB_UNIQUE_NAME: APAC
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0/db_1
		
Target:
		
Platform          : Linuxx86_64
Server Name       : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version        : Oracle 11.2.0.3, , File system: Normal
DB Name           : APAC, DB_UNIQUE_NAME: EMEA
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0/db_1

1. Enable Forced Logging on Primary

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
APAC      READ WRITE

SQL> select force_logging from v$database;

FOR
---
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES <----

SQL>

2. Create Password File on Primary

[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@rac1 dbs]$ orapwd file=orapwapac password=sys force=y
[oracle@rac1 dbs]$ ls -ltr orapwapac
-rw-r----- 1 oracle oinstall 1536 Jul 25 18:21 orapwapac
[oracle@rac1 dbs]$

3. Configure a Standby Redo Log on Primary

SQL> set lines 180
SQL> col MEMBER for a60
SQL> select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

   THREAD#     GROUP# MEMBER                                                            BYTES
---------- ---------- ------------------------------------------------------------ ----------
         1          3 /u01/app/oracle/oradata/apac/redo03.log                        52428800
         1          2 /u01/app/oracle/oradata/apac/redo02.log                        52428800
         1          1 /u01/app/oracle/oradata/apac/redo01.log                        52428800

SQL>

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/apac/redo04.log') SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/apac/redo05.log') SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/apac/redo06.log') SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/apac/redo07.log') SIZE 50M;

Database altered.

SQL>

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /u01/app/oracle/oradata/apac/redo03.log                      NO
         2         ONLINE  /u01/app/oracle/oradata/apac/redo02.log                      NO
         1         ONLINE  /u01/app/oracle/oradata/apac/redo01.log                      NO
         4         STANDBY /u01/app/oracle/oradata/apac/redo04.log                      NO
         5         STANDBY /u01/app/oracle/oradata/apac/redo05.log                      NO
         6         STANDBY /u01/app/oracle/oradata/apac/redo06.log                      NO
         7         STANDBY /u01/app/oracle/oradata/apac/redo07.log  <--                    NO

7 rows selected.

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                            BYTES
---------- ------------------------------------------------------------ ----------
         4 /u01/app/oracle/oradata/apac/redo04.log                        52428800
         5 /u01/app/oracle/oradata/apac/redo05.log                        52428800
         6 /u01/app/oracle/oradata/apac/redo06.log                        52428800
         7 /u01/app/oracle/oradata/apac/redo07.log   <-----                     52428800

SQL>

4. Verify Archive Mode Enabled on Primary

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/arch/apac
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
SQL>

5. Set Primary Database Initialization Parameters

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfileapac.ora
SQL> create pfile='/home/oracle/initapac.ora' from spfile;

File created.

SQL> alter system set db_unique_name='apac' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(apac,emea)' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch/apac VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=apac' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=emea LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=emea' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET fal_client=apac scope=both;

System altered.

SQL> ALTER SYSTEM SET fal_server=emea scope=both;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac','/u02/emea','/u02/apac' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL>

SQL> create pfile='/home/oracle/initapac.ora' from spfile;

File created.

SQL>

[oracle@rac1 ~]$ cat initapac.ora 
apac.__db_cache_size=339738624
apac.__java_pool_size=4194304
apac.__large_pool_size=4194304
apac.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
apac.__pga_aggregate_target=335544320
apac.__sga_target=503316480
apac.__shared_io_pool_size=0
apac.__shared_pool_size=146800640
apac.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/apac/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/apac/control01.ctl','/u01/app/oracle/oradata/apac/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac','/u02/emea','/u02/apac'
*.db_name='apac'
*.db_unique_name='apac'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=apacXDB)'
*.fal_client='APAC'
*.fal_server='EMEA'
*.log_archive_config='DG_CONFIG=(apac,emea)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/apac VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=apac'
*.log_archive_dest_2='SERVICE=emea LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=emea'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@rac1 ~]$

6. Transfer SPFILE AND PASSWORD FILE TO STANDBY SIDE

Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.
The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The best practice for this is to copy the passwordfile as suggested.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

[oracle@rac1 ~]$ scp initapac.ora oracle@rac2:/home/oracle/initemea.ora
initapac.ora                                                 100% 1487     1.5KB/s   00:00
[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/orapwemea
[oracle@rac1 dbs]$ scp orapwapac oracle@rac2:/u01/app/oracle/product/11.2.0/db_1/dbs/
orapwapac                                                    100% 1536     1.5KB/s   00:00
[oracle@rac1 dbs]$

7. Set Standby Database Initialization Parameters

Edit shown highlighted to set it up for the standby role

[oracle@rac2 ~]$ cat initemea.ora
emea.__db_cache_size=339738624
emea.__java_pool_size=4194304
emea.__large_pool_size=4194304
emea.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
emea.__pga_aggregate_target=335544320
emea.__sga_target=503316480
emea.__shared_io_pool_size=0
emea.__shared_pool_size=146800640
emea.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/emea/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/emea/control01.ctl','/u01/app/oracle/oradata/emea/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/apac','/u01/app/oracle/oradata/emea','/u02/apac','/u02/emea'
*.db_name='apac'
*.db_unique_name='emea'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=emeaXDB)'
*.fal_client='emea'
*.fal_server='apac'
*.log_archive_config='DG_CONFIG=(apac,emea)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/emea VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=emea'
*.log_archive_dest_2='SERVICE=apac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=apac'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/u01/app/oracle/oradata/apac','/u01/app/oracle/oradata/emea'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@rac2 ~]$

8. Crete required directories on Standby

[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/emea/adump
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/oradata/emea
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/arch/emea
[oracle@rac2 ~]$ mkdir -p /u02/emea

9. Add below entry in ORATAB on Standby

emea:/u01/app/oracle/product/11.2.0/db_1:N

10. Create spfile from pfile on Standby

Once the new parameter file is ready we create from it the spfile:

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [emca] ? emea
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$
[oracle@rac2 ~]$
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:34:58 2015

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/initemea.ora';
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             490737024 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
SQL> create spfile from pfile='/home/oracle/initemea.ora';

File created.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             490737024 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfileemea.ora
SQL>

11. Configure TNS Entries on Primary

[oracle@rac1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@rac1 admin]$ 

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

APAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apac)
    )
  )

EMEA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = emea)(UR = A)
    )
  )
[oracle@rac1 admin]$ 

[oracle@rac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:22:10

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                25-JUL-2015 19:20:00
Uptime                    0 days 0 hr. 2 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "apac" has 1 instance(s).
  Instance "apac", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 ~]$ 

[oracle@rac1 ~]$ tnsping apac

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:38:14

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = apac)))
OK (0 msec)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ tnsping emea

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:38:17

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = emea)(UR = A)))
OK (0 msec)
[oracle@rac1 ~]$

12. Configure TNS Entries on Standby

[oracle@rac2 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@rac2 admin]$ 

[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

APAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apac)
    )
  )

EMEA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = emea)(UR = A)
    )
  )
[oracle@rac2 admin]$ 

[oracle@rac2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:24:10

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.102)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                25-JUL-2015 19:22:24
Uptime                    0 days 0 hr. 2 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.102)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "emea" has 1 instance(s).
  Instance "emea", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 ~]$ 


[oracle@rac2 ~]$ tnsping apac

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:37:17

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = apac)))
OK (0 msec)
[oracle@rac2 ~]$
[oracle@rac2 ~]$ tnsping emea

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:37:25

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = emea)(UR = A)))
OK (10 msec)
[oracle@rac2 ~]$

13. Verify connection ‘AS SYSDBA’ from Primary

[oracle@rac1 ~]$ sqlplus sys/sys@apac as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:45:37 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ sqlplus sys/sys@emea as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:45:57 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rac1 ~]$

14. Create Standby Database

[oracle@rac1 ~]$ rman target sys/sys@apac auxiliary sys/sys@emea

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jul 25 19:46:37 2015

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

connected to target database: APAC (DBID=2914488844)
connected to auxiliary database: APAC (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 25-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwapac' auxiliary format
 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwemea'   ;
}
executing Memory Script

Starting backup at 25-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK
Finished backup at 25-JUL-15

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/emea/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/oradata/emea/control02.ctl' from
 '/u01/app/oracle/oradata/emea/control01.ctl';
}
executing Memory Script

Starting backup at 25-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_apac.f tag=TAG20150725T195200 RECID=1 STAMP=886017123
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 25-JUL-15

Starting restore at 25-JUL-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 25-JUL-15

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/emea/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/emea/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/emea/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/emea/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/emea/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/app/oracle/oradata/emea/system01.dbf"   datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/emea/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/emea/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/emea/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/emea/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 25-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/apac/system01.dbf
output file name=/u01/app/oracle/oradata/emea/system01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/apac/sysaux01.dbf
output file name=/u01/app/oracle/oradata/emea/sysaux01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/apac/undotbs01.dbf
output file name=/u01/app/oracle/oradata/emea/undotbs01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/apac/users01.dbf
output file name=/u01/app/oracle/oradata/emea/users01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 25-JUL-15

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=886017341 file name=/u01/app/oracle/oradata/emea/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=886017341 file name=/u01/app/oracle/oradata/emea/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=886017341 file name=/u01/app/oracle/oradata/emea/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=886017341 file name=/u01/app/oracle/oradata/emea/users01.dbf
Finished Duplicate Db at 25-JUL-15

RMAN>

15. Enable MRP on Standby

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:58:37 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      MOUNTED              PHYSICAL STANDBY

SQL>

SQL> alter database open;

Database altered.

[oracle@rac2 ~]$ ps -ef | grep mrp
oracle    1523   701  0 20:00 pts/1    00:00:00 grep mrp <----
[oracle@rac2 ~]$


SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>

SQL> !ps -ef | grep mrp
oracle    1533     1  0 20:01 ?        00:00:00 ora_mrp0_emea <----
oracle    1539  1524  0 20:01 pts/1    00:00:00 /bin/bash -c ps -ef | grep mrp
oracle    1541  1539  0 20:01 pts/1    00:00:00 grep mrp

SQL>

16. Verify the Physical Standby Database Is Performing Properly

On Primary

SQL> show parameters db_unique_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      apac
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         5 25-JUL-15 25-JUL-15
         6 25-JUL-15 25-JUL-15
         7 25-JUL-15 25-JUL-15
         8 25-JUL-15 25-JUL-15

4 rows selected.
SQL>
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            10 <----

SQL>

On Standby

SQL> set lines 180
SQL> col MEMBER for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /u01/app/oracle/oradata/emea/redo03.log                      NO
         2         ONLINE  /u01/app/oracle/oradata/emea/redo02.log                      NO
         1         ONLINE  /u01/app/oracle/oradata/emea/redo01.log                      NO
         4         STANDBY /u01/app/oracle/oradata/emea/redo04.log                      NO
         5         STANDBY /u01/app/oracle/oradata/emea/redo05.log                      NO
         6         STANDBY /u01/app/oracle/oradata/emea/redo06.log                      NO
         7         STANDBY /u01/app/oracle/oradata/emea/redo07.log                      NO

7 rows selected.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         5 25-JUL-15 25-JUL-15
         6 25-JUL-15 25-JUL-15
         7 25-JUL-15 25-JUL-15
         8 25-JUL-15 25-JUL-15
         9 25-JUL-15 25-JUL-15
        10 25-JUL-15 25-JUL-15

6 rows selected.

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            10 <----

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:
Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE FROM ACTIVE DATABASE (Doc ID 1075908.1)

Bright DBA

ORACLE DBA – Tips and Techniques

Tag Archives: Active dataguard

Active Dataguard

Creating Active Data Guard Physical Standby Environment