Converting from Physical Standby to Logical Standby

 Converting from Physical Standby to Logical Standby.
Creating a Logical Standby Database by Converting from Physical to Logical Standby
You can now easily convert a physical standby database to a logical one. Here are the steps:

1. On the standby database, stop the managed recovery process:
SQL> alter database recover managed standby database cancel;
Database altered.

Or

DGMGRL> edit database stdy set state='APPLY-OFF';

2. The standby database will need to get the data dictionary information from somewhere. The dictionary information should be put in the redo stream that comes from the primary. So, on the primary database, issue the following to build the LogMiner tables for dictionary:
SQL> begin
dbms_logstdby.build;
end;
/

PL/SQL procedure successfully completed.

Alert log.

Logminer Bld: Build started
ALTER SYSTEM SWITCH ALL LOGFILE start (orcl)
2012-03-05 13:49:50.416000 +05:30
ALTER SYSTEM SWITCH ALL LOGFILE complete (orcl)
Thread 1 advanced to log sequence 41 (LGWR switch)
  Current log# 2 seq# 41 mem# 0: +DATA/orcl/onlinelog/group_2.264.719142951
  Current log# 2 seq# 41 mem# 1: +DATA/orcl/onlinelog/group_2.265.719142957
Mon Mar 05 13:49:50 2012
Logminer Bld: Lockdown Complete.  DB_TXN_SCN is 0 1013551 LockdownSCN is 1013568
2012-03-05 13:49:55.952000 +05:30
Archived Log entry 44 added for thread 1 sequence 40 ID 0x4a53118e dest 1:
2012-03-05 13:49:59.315000 +05:30
ARC3: Standby redo logfile selected for thread 1 sequence 40 for destination LOG_ARCHIVE_DEST_2
2012-03-05 13:50:38.821000 +05:30
ALTER SYSTEM ARCHIVE LOG
2012-03-05 13:50:40.303000 +05:30
Thread 1 advanced to log sequence 42 (LGWR switch)
  Current log# 3 seq# 42 mem# 0: +DATA/orcl/onlinelog/group_3.266.719142961
  Current log# 3 seq# 42 mem# 1: +DATA/orcl/onlinelog/group_3.267.719142965
2012-03-05 13:50:45.058000 +05:30
Archived Log entry 46 added for thread 1 sequence 41 ID 0x4a53118e dest 1:
Mon Mar 05 13:50:45 2012
Logminer Bld: Done

SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database ;

SUPPLEME SUP SUP
-------- --- ---
IMPLICIT YES YES

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES' and STANDBY_DEST='NO';

NAME
---------------------------------------------------------------------------------
+FRA/stdy/archivelog/2012_03_05/thread_1_seq_41.277.777131459

3. Now, issue the command in standby side to convert it to logical:

SQL> alter database recover to logical standby lstdy;
Database altered.

If you didn't execute Step 2, the above command will wait since the dictionary information is not found. Don't worry; just execute the Step 2 at this point. If you have enabled RTA, the information will immediately appear on the standby database.
4. Issue a few log switches on primary to make sure the archived logs are created and sent over to the standby:

SQL> alter system switch logfile;
System altered.

5. On the standby side, you can see that the alter database command has completed, after some time. Now the standby is a logical one.

Alert log
alter database recover to logical standby lstdy
Media Recovery Start: Managed Standby Recovery (stdy)
2012-03-05 13:53:35.624000 +05:30
 started logmerger process
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
2012-03-05 13:53:36.677000 +05:30
Media Recovery Log +FRA/stdy/archivelog/2012_03_05/thread_1_seq_40.276.777131407
2012-03-05 13:53:53.679000 +05:30
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
2012-03-05 13:54:02.997000 +05:30
Media Recovery Log +FRA/stdy/archivelog/2012_03_05/thread_1_seq_41.277.777131459
2012-03-05 13:54:21.718000 +05:30
Media Recovery Log +FRA/stdy/archivelog/2012_03_05/thread_1_seq_42.278.777131463
Incomplete Recovery applied until change 1014941 time 03/05/2012 13:50:46
Media Recovery Complete (stdy)
krsv_proc_kill: Killing 4 processes (all RFS)
2012-03-05 13:54:24.439000 +05:30
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 1014941
Resetting resetlogs activation ID 1246957966 (0x4a53118e)
Online log +DATA/stdy/onlinelog/group_1.262.776967645: Thread 1 Group 1 was previously cleared
Online log +FRA/stdy/onlinelog/group_1.256.776967651: Thread 1 Group 1 was previously cleared
Online log +DATA/stdy/onlinelog/group_2.263.776967657: Thread 1 Group 2 was previously cleared
Online log +FRA/stdy/onlinelog/group_2.257.776967663: Thread 1 Group 2 was previously cleared
Online log +DATA/stdy/onlinelog/group_3.264.776967673: Thread 1 Group 3 was previously cleared
Online log +FRA/stdy/onlinelog/group_3.258.776967677: Thread 1 Group 3 was previously cleared
2012-03-05 13:54:26.926000 +05:30
Standby became primary SCN: 1014939
Setting recovery target incarnation to 4
RECOVER TO LOGICAL STANDBY: Complete - Database shutdown required after NID finishes
*** DBNEWID utility started ***
DBID will be changed from 1246923666 to new DBID of 2386946899 for database ORCL
DBNAME will be changed from ORCL to new DBNAME of LSTDY
Starting datafile conversion
Datafile conversion complete
Database name changed to LSTDY.
Modify parameter file and generate a new password file before restarting.
Database ID for database LSTDY changed to 2386946899.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open with RESETLOGS option.
Succesfully changed database name and ID.
*** DBNEWID utility finished succesfully ***
2012-03-05 13:54:28.351000 +05:30
Completed: alter database recover to logical standby lstdy
2012-03-05 13:54:34.755000 +05:30
destination database instance is 'started' not 'mounted'

6. Bounce the database:

SQL> shutdown

SQL> startup mount

SQL> alter database open resetlogs;

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> select name from v$database;

NAME
---------
LSTDY

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
LOGICAL STANDBY

SQL> select guard_status from v$database;
GUARD_S
-------
ALL

7. Now that this is a logical standby database, you should start the SQL Apply process.
SQL> alter database start logical standby apply immediate;

The logical standby database is now fully operational! Once you convert the physical standby to a logical one, you can't convert it back to a physical one unless you use the special clause ("keep identity"), described in the section below.

Output from Alert logs

Verify That the Logical Standby Database Is Performing Properly

a. Verify that the archived redo log files were registered on logical standby:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN,DICT_END FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC
---------- --------- --------- --- ---
        40 03-MAR-12 05-MAR-12 NO  NO
        41 05-MAR-12 05-MAR-12 YES YES
        42 05-MAR-12 05-MAR-12 NO  NO

SQL>

b. Begin sending redo data to the standby database:
Connect to the primary database and issue the following command to begin sending redo data to the standby database

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

c. Query the DBA_LOGSTDBY_LOG view to verify that the archived redo log files were registered. Connect to the logical standby database and re-query the DBA_LOGSTDBY_LOG view as shown in step a. This enables you to verify that the new archived redo log files were
registered.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN,DICT_END FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC
---------- --------- --------- --- ---
        40 03-MAR-12 05-MAR-12 NO  NO
        41 05-MAR-12 05-MAR-12 YES YES
        42 05-MAR-12 05-MAR-12 NO  NO
        43 05-MAR-12 05-MAR-12 NO  NO
        44 05-MAR-12 05-MAR-12 NO  NO

SQL>

d. Verify that redo data is being applied correctly:
On the logical standby database, query the V$LOGSTDBY_STATS view to verify that redo data is being applied correctly:

SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME = 'coordinator state';

NAME                           VALUE
------------------------------ ----------------------------------------------------------------
coordinator state              INITIALIZING

SQL>

e. View the V$LOGSTDBY view to see current Query the V$LOGSTDBY view on the logical standby database to see a current snapshot of  SQL Apply activity. A text message describing the current activity of each process that is involved in reading and applying changes is displayed.

SQL> SELECT TYPE, HIGH_SCN, STATUS
FROM V$LOGSTDBY;

TYPE                             HIGH_SCN STATUS
------------------------------ ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COORDINATOR                       1014941 ORA-16116: no work available
ANALYZER                          1013570 ORA-16116: no work available
APPLIER                                   ORA-16116: no work available
APPLIER                                   ORA-16116: no work available
APPLIER                                   ORA-16116: no work available
APPLIER                                   ORA-16116: no work available
APPLIER                                   ORA-16116: no work available
READER                            1014928 ORA-16127: stalled waiting for additional transactions to be applied
BUILDER                           1014166 ORA-16117: processing
PREPARER                          1014168 ORA-16117: processing

10 rows selected.

SQL> SELECT TYPE, HIGH_SCN, STATUS  FROM V$LOGSTDBY;

TYPE                             HIGH_SCN STATUS
------------------------------ ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COORDINATOR                               ORA-16117: processing
READER                             624900 ORA-16117: processing
BUILDER                                   ORA-16117: processing
PREPARER                                  ORA-16117: processing

f. Check the overall progress of SQL Apply:
Query the V$LOGSTDBY_PROGRESS view on the logical standby database to check the  overall progress of SQL Apply:
SQL> SELECT APPLIED_SCN, LATEST_SCN FROM V$LOGSTDBY_PROGRESS;

SELECT APPLIED_SCN, LATEST_SCN
  2  FROM V$LOGSTDBY_PROGRESS;

APPLIED_SCN LATEST_SCN
----------- ----------
    1015278    1015278

Add theLogical Standby to the Broker Configuration

DGMGRL> remove database stdy;
DGMGRL> SHOW CONFIGURATION;
DGMGRL> ADD DATABASE STDY AS CONNECT IDENTIFIER IS STDY MAINTAINED AS logical;
DGMGRL> SHOW CONFIGURATION;
DGMGRL> enable database stdy;
DGMGRL> SHOW CONFIGURATION;

Creating a Data Guard Broker Configuration

Creating a Data Guard Broker Configuration.
Enable Flashback Database on the Primary and Standby databases.

On Primary
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/orcl/spfileorcl.ora

SQL> show parameter undo_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                  string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>

On Standby
SQL> show parameter spfile

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

SQL> show parameter undo_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;

Implementation of Data Guard Broker

SQL> show parameter DG_BROKER_START

NAME TYPE VALUE
------------------------------------ ----------- -------
dg_broker_start boolean FALSE

Enable Data Guard Broker Start on the Primary and Standby databases

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
System altered

Alert log shows

DMON started with pid=36, OS id=26022
2012-03-04 12:06:42.362000 +05:30
Starting Data Guard Broker (DMON)
2012-03-04 12:06:50.601000 +05:30
INSV started with pid=38, OS id=26039

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr1stdy.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr2stdy.dat
dg_broker_start                      boolean     TRUE

Setup the Local_Listener parameter on both the Primary and Standby databases
SQL> show parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------
local_listener string

If not Set
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST= 11gr2.rhel5.com)(PORT=1521))' scope=both;

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST= 11gr2sb.rhel5.com)(PORT=1521))' scope=both;

Setup the tnsnames & listener to enable communication with both the Primary and Standby databases

To enable the Data Guard broker to restart instances during the course of broker
operations, a service with a specific name must be statically registered with the local
listener of each instance. The value of the GLOBAL_DBNAME attribute must be set to a
concatenation of db_unique_name_DGMGRL.db_domain

Prior to Oracle11g Release 2, you had to use the static entry format of  “GLOBAL_DBNAME attribute of db_unique_name_DGMGRL.db_domain”, or in our case, “orcl_DGMGRL” and “stdy_DGMGRL”. The Broker in Oracle11g Release 2 has a new property called StaticConnectIdentifier that you can set to tell the Broker to use your own static listener entry.
On Primary

$ cd /u01/app/oracle/product/11.2.0/grid/network/admin/
$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_DGMGRL.rhel5.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

$ . oraenv
+ASM

$ lsnrctl reload

On Primary

$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
$ vi tnsnames.ora

STDY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stdy_DGMGRL)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_DGMGRL.rhel5.com)
    )
  )

On Standby
$ cd /u01/app/oracle/product/11.2.0/grid/network/admin/

$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = stdy_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = stdy)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

$ lsnrctl reload

On Standby
$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
$ vi tnsnames.ora

STDY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stdy_DGMGRL)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_DGMGRL.rhel5.com)
    )
  )

Setup the tnsnames to enable communication with both the Primary, Standby & Observer databases
$ tnsping orcl

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl_DGMGRL.rhel5.com)))
OK (10 msec)
$ tnsping stdy

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stdy_DGMGRL)))
OK (10 msec)

On Primary:

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr1orcl.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr2orcl.dat

On Standby:

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr1stdy.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr2stdy.dat

Next create from within the DGMGRL the configuration
On Primary

$ dgmgrl

DGMGRL>

DGMGRL> CONNECT sys/oracle
Connected.

DGMGRL> CREATE CONFIGURATION ORCL_STDY AS PRIMARY DATABASE IS ORCL CONNECT IDENTIFIER IS ORCL;

Configuration "orcl" created with primary database "orcl"
DGMGRL> ADD DATABASE STDY AS CONNECT IDENTIFIER IS STDY MAINTAINED AS PHYSICAL;

Database "stdy" added

DGMGRL> edit database orcl set property StaticConnectIdentifier='orcl';

DGMGRL> edit database stdy set property StaticConnectIdentifier='stdy';

DGMGRL> SHOW CONFIGURATION;
Configuration
Name: orcl
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database
stdy - Physical standby database
Current status for "orcl":
DISABLED

DGMGRL> SHOW DATABASE VERBOSE STDY;

Database - stdy

  Role:            PHYSICAL STANDBY
  Intended State:  OFFLINE
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Real Time Query: OFF
  Instance(s):
    stdy

  Properties:
    DGConnectIdentifier             = 'stdy'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '5'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = '11gr2sb.rhel5.com'
    SidName                         = 'stdy'
    StaticConnectIdentifier         = 'stdy'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
DISABLED

DGMGRL> SHOW DATABASE VERBOSE ORCL;

Database - orcl

  Role:            PRIMARY
  Intended State:  OFFLINE
  Instance(s):
    orcl

  Properties:
    DGConnectIdentifier             = 'orcl'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = '11gr2.rhel5.com'
    SidName                         = 'orcl'
    StaticConnectIdentifier         = 'orcl'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
DISABLED

DGMGRL> enable configuration;
Enabled.
DGMGRL>

DGMGRL> enable database STDY;
Enabled.

DGMGRL> show configuration;
Configuration
  Name:                orcl
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    orcl - Primary database
    stdy - Physical standby database

Fast-Start Failover: DISABLED
Current status for "orcl":
SUCCESS

Subhan Oracle DBA Blog's: Physical Standby Database by using SQL and RMAN

Subhan Oracle DBA Blog's: Physical Standby Database by using SQL and RMAN: Creating a Physical Standby Database by Using SQL and RMAN Commands. On Both Nodes check. # cat /etc/hosts 127.0.0.1       localhost.locald...

Physical Standby Database by using SQL and RMAN

Creating a Physical Standby Database by Using SQL and RMAN Commands.

On Both Nodes check.
# cat /etc/hosts
127.0.0.1       localhost.localdomain   localhost
192.168.1.100   11gr2.rhel5.com         11gr2
192.168.1.200   11gr2sb.rhel5.com       11gr2sb

Ping each nodes.
Create Directories
On standby

mkdir /u01/app/oracle/admin/stdy
mkdir -p /u01/app/oracle/admin/stdy/adump
mkdir -p /u01/app/oracle/admin/stdy/bdump
mkdir -p /u01/app/oracle/admin/stdy/cdump
mkdir -p /u01/app/oracle/admin/stdy/udump
mkdir -p /u01/app/oracle/admin/stdy/dpdump
mkdir -p /u01/app/oracle/admin/stdy/pfile
mkdir -p /u01/app/oracle/oradata/stdy
chown -R oracle:oinstall /u01/app/oracle/admin/stdy/
chown -R oracle:oinstall /u01/app/oracle/oradata/stdy

$ . oraenv
+ASM
$ asmcmd

ASMCMD> ls
DATA/
FRA/

ASMCMD> cd data
ASMCMD> ls
ASM/

ASMCMD> pwd
+data

ASMCMD> mkdir stdy

On Standby

$ crsctl start resource -all

On Primary
See the DB & ASM & Listener are up

$ crs_stat -t

Name           Type           Target    State     Host      
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    11gr2     
ora.FRA.dg     ora....up.type ONLINE    ONLINE    11gr2     
ora....ER.lsnr ora....er.type ONLINE    ONLINE    11gr2     
ora.asm        ora.asm.type   ONLINE    ONLINE    11gr2     
ora.cssd       ora.cssd.type  ONLINE    ONLINE    11gr2     
ora.diskmon    ora....on.type ONLINE    ONLINE    11gr2     
ora.orcl.db    ora....se.type ONLINE    ONLINE    11gr2

On Primary

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/orcl/spfileorcl.ora

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 1216M
memory_target                        big integer 1216M
shared_memory_address                integer     0

SQL> archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence           25

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> select force_logging from v$database;

FOR
---
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

Now create standby redo files

SQL> SELECT group#, member, status FROM V$LOGfile;

SQL> ALTER DATABASE ADD STANDBY LOGFILE  size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE  size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE  size 50M;

SQL> SELECT GROUP#, BYTES/1024, STATUS FROM V$STANDBY_LOG;

    GROUP# BYTES/1024 STATUS
---------- ---------- ----------
         4      51200 UNASSIGNED
         5      51200 UNASSIGNED
         6      51200 UNASSIGNED

Setting Enviornment & Listener & Tnsnames.ora files
On Standby
Edit .bash_profile
Change the ORACLE_SID=stdy

$ env |grep ORA

ORACLE_SID=stdy
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

Configure listener.ora & tnsnames.ora on both servers to hold entries for both databases

On Primary

$ . oraenv
orcl

$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
$ vi tnsnames.ora

STDY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = stdy)(UR = A)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.rhel5.com)
    )
  )

$ tnsping stdy

$ . oraenv
+ASM

$ cd /u01/app/oracle/product/11.2.0/grid/network/admin/
$ vi listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

Notice the value (UR=A) in the CONNECT_DATA of the tns entry
This will allow the primary database to connect remotely to a database in nomount or mount mode. It’s a feature introduced in oracle 10g.
On the Standby

Edit listener.ora or use netmgr by setting (+ASM environment) & Add static service (stdy) to the listener

$ cd /u01/app/oracle/product/11.2.0/grid/network/admin

$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = stdy.rhel5.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = stdy)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

$ lsnrctl status

Bounce the listener

$ lsnrctl reload

$ lsnrctl status

Add service (orcl) on the standby

export ORACLE_SID=stdy
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

$ env|grep ORA

$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin
$ vi tnsnames.ora

STDY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = stdy)(UR = A)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.rhel5.com)
    )
  )

Tnsping from both servers
$ . oraenv
orcl

On Standby

$ tnsping orcl

On Primary

$ tnsping stdy
Copy the password file from Primary to Standby, sys password must be identical

On Primary

$ cd /u01/app/oracle/product/11.2.0/db_1/dbs

$ scp orapworcl oracle@11gr2sb:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstdy

Configuring primary database initialization parameter’s

SQL> show parameter db_unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      orcl

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      orcl

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stdy)';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STDY VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES) DB_UNIQUE_NAME=STDY';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER=STDY;

System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT=ORCL;

System altered.

Check All the Parameters

SELECT DEST_ID,VALID_TYPE,VALID_ROLE,VALID_NOW FROM V$ARCHIVE_DEST;

   DEST_ID VALID_TYPE      VALID_ROLE   VALID_NOW
---------- --------------- ------------ ----------------
         1 ALL_LOGFILES    ALL_ROLES    YES
         2 ONLINE_LOGFILE  PRIMARY_ROLE YES

SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME
------------------------------
orcl
stdy

SQL> archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=STDY VALID_FOR=(PRIMAR
                                                 Y_ROLE,ONLINE_LOGFILES) DB_UNI
                                                 QUE_NAME=STDY

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(orcl,stdy)

SQL> show parameter fal_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      ORCL
fal_server                           string      STDY

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL>

On Standby

In Oracle Home/dbs directory, create a file initstdy.ora containing add below lines. This will serve as the initialization file for the standby instance.
export ORACLE_SID=stdy
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

cd /u01/app/oracle/product/11.2.0/db_1/dbs

$ vi initstdy.ora

db_name=orcl

Start the instance stdy in NOMOUNT state

sqlplus / as sysdba

SQL> startup nomount

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

SQL> show parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------
local_listener string

On Primary

SQL> alter system set local_listener=
  '(ADDRESS=(PROTOCOL=TCP)(HOST= 11gr2.rhel5.com)(PORT=1521))' scope=memory;

On Standby

SQL> alter system set local_listener=
  '(ADDRESS=(PROTOCOL=TCP)(HOST= 11gr2sb.rhel5.com)(PORT=1521))' scope=memory;

Active DataGuard

Now that all initial preparation is completed, it's time to call the all-powerful RMAN script that creates the standby database. On orcl, start RMAN and run the following script. You will find it easier to save this in a file and run the script from the RMAN prompt. In case of RMAN connection error restart auxiliary instance and listener.

On Primary

$ rman

RMAN> connect target sys/oracle@orcl

connected to target database: ORCL (DBID=1223873454)

RMAN> connect auxiliary sys/oracle@stdy

connected to auxiliary database: ORCL (not mounted)

run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate channel prmy5 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database nofilenamecheck
spfile
parameter_value_convert 'orcl','stdy'
set 'db_unique_name'='stdy'
set control_files='+DATA/stdy/control.ctl'
set db_create_file_dest='+DATA'
set db_recovery_file_dest='+FRA'
set DB_RECOVERY_FILE_DEST_SIZE='10G'
set log_archive_max_processes='5'
set fal_client='stdy'
set fal_server='orcl'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(orcl,stdy)'
set log_archive_dest_2='service=orcl LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcl';
}

This script creates the standby database, places the appropriate parameters in the spfile for the standby instance, creates the diagnostic destination for the standby database, and restarts the standby.

NOFILENAMECHECK:

Prevents RMAN from checking whether datafiles of the source database share the same names as the standby database files that are in use.
The NOFILENAMECHECK option is required when the standby and primary datafiles and online redo logs have identical filenames. Thus, if you want the duplicate database filenames to be the same as the source database filenames, and if the databases are in different hosts, then you must specify NOFILENAMECHECK

PARAMETER_VALUE_CONVERT:

Replaces the first string with the second string in all matching initialization parameter values. Note that DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT are exceptions to this rule and are not affected.

You can use PARAMETER_VALUE_CONVERT to set a collection of initialization parameter values and avoid explicitly setting them all. For example, if the source database uses disk group +ALPHA while the standby database will use +BETA, then you could modify all parameters that refer to these disk groups by specifying SPFILE PARAMETER_VALUE_CONVERT (‘+ALHPA’,'+BETA’).


Add entry into Standby

$ vi /etc/oratab

+ASM:/u01/app/oracle/product/11.2.0/grid:N
stdy:/u01/app/oracle/product/11.2.0/db_1:N

Data Guard & Oracle Restart in 11gR2

$ srvctl add database -d stdy -o /u01/app/oracle/product/11.2.0/db_1 -r PHYSICAL_STANDBY -s MOUNT

$ srvctl config database -d stdy
Database unique name: stdy
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: MOUNT
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups:
Services:

Test the DataGuard Configuration

On the Primary database, connect as HR and add a row to the HR.REGIONS table

SQL> connect hr/hr@orcl
Connected.

SQL> insert into HR.REGIONS values (30,'OpenWorld');
1 row created.

SQL> commit;
Commit complete.

ON Standby

SQL> select * from hr.regions;

Test2
On Primary

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     28
Next log sequence to archive   30
Current log sequence           30

On Standby

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           30

On Primary

SQL> alter system archive log current;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     29
Next log sequence to archive   31
Current log sequence           31

On Standby

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     30
Next log sequence to archive   0
Current log sequence           31

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

SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
         1 03-MAR-12 NO
        29 03-MAR-12 YES
        30 03-MAR-12 YES
        31 03-MAR-12 YES
        32 03-MAR-12 YES
        33 03-MAR-12 YES
        34 03-MAR-12 YES

New Columns in v$database

On Primary
SQL> select controlfile_type from v$database;

CONTROL
-------
CURRENT

On Standby
SQL> select controlfile_type from v$database;

CONTROL
-------
STANDBY

SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> select archivelog_compression from v$database;

ARCHIVEL
--------
DISABLED

SQL> select db_unique_name, PRIMARY_DB_UNIQUE_NAME from v$database;

DB_UNIQUE_NAME                 PRIMARY_DB_UNIQUE_NAME
------------------------------ ------------------------------
stdy                           orcl

SQL> select dataguard_broker from v$database;

DATAGUAR
--------
DISABLED

SQL> select CONTROLFILE_CONVERTED from v$database;

CON
---
NO

SQL> select remote_archive from v$database;

REMOTE_A
--------
ENABLED

Enable Recovery on the Standby

SQL> select recovery_mode from v$archive_dest_status;

SQL> alter database recover managed standby database using current logfile disconnect;

SQL> select recovery_mode from v$archive_dest_status;

Cancel Recovery

 SQL> alter database recover managed standby database cancel;