Tuesday, September 9, 2014

Configure Oracle EBS Applications with RAC Snapshot Standby database to test the functionality before actual DR Drill (Switchover)

In my Case, Switchover has been already done, where My Standby is acting as Production and Former Primary is acting as a Standby. This you can do first time for Standby Site after DataGuard Built up, to test the functionality before actual DR Drill (Switchover)

But here, I tested Snapshot Standby Configuration at former Primary (Current Standby) Site, which make us to put standby in Read/Write Mode with the help of Flashback logs and to configure EBS (R12) Applications with it.

Below are the steps,

[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :srvctl config database -d EBSPRD
Database unique name: EBSPRD
Database name:
Oracle home: /u01/app/oracle/product/11.2.0.3/EBSPRD
Oracle user: oracle
Spfile: +DATA_DC/EBSPRD/spfileEBSPRD.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: EBSPRD
Database instances: EBSPRD1,EBSPRD2
Disk Groups: DATA_DC,RECO_DC
Mount point paths:
Services:
Type: RAC
Database is administrator managed

[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :srvctl status database -d EBSPRD
Instance EBSPRD1 is running on node inmumdcdbadm01
Instance EBSPRD2 is not running on node inmumdcdbadm02

[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :sqlplus "/as sysdba"

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
SQL> alter system set db_recovery_file_dest_size=150G sid='*' scope=both;

System altered.

SQL> Alter system set db_recovery_file_dest='+RECO_DC' sid='*' scope=both;

System altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameters db_recovery;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +RECO_DC
db_recovery_file_dest_size           big integer 150G
SQL> select protection_mode, protection_level from v$database;

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

SQL> select flashback_on from v$database;

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

SQL> alter database flashback on;

Database altered.

SQL> set linesize 1000
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from  v$restore_point;

no rows selected

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

NAME      LOG_MODE     OPEN_MODE            CONTROL DATABASE_ROLE
--------- ------------ -------------------- ------- ----------------
EBSPRD    ARCHIVELOG   MOUNTED              STANDBY PHYSICAL STANDBY

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 4 02:39:52 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1.4965E+10 bytes
Fixed Size                  2242944 bytes
Variable Size            3288336000 bytes
Database Buffers         1.1643E+10 bytes
Redo Buffers               31297536 bytes
Database mounted.
Database opened.
SQL>


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

NAME      LOG_MODE     OPEN_MODE            CONTROL DATABASE_ROLE
--------- ------------ -------------------- ------- ----------------
EBSPRD    ARCHIVELOG   READ WRITE           CURRENT SNAPSHOT STANDBY


SQL> set linesize 1000
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from  v$restore_point;

NAME                                                                                                                             GUA
-------------------------------------------------------------------------------------------------------------------------------- ---
SNAPSHOT_STANDBY_REQUIRED_09/04/2014 02:38:04                                                                                    YES


SQL> exit

SQL> select node_name,server_address from fnd_nodes;

NODE_NAME                      SERVER_ADDRESS
------------------------------ ------------------------------
INBLRDRDBADM02
CBOS                           172.18.92.33                   #### (Primary Application Server IP)
AUTHENTICATION                 *
INBLRDRDBADM01

SQL> exec fnd_conc_clone.setup_clean;

PL/SQL procedure successfully completed.

SQL> select node_name,server_address from fnd_nodes;

no rows selected

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

####################### On 1st RAC DB Node #########################

[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :cd /u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/bin/
[inmumdcdbadm01.tajhotels.com -> oracle:/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/bin] :echo $CONTEXT_FILE
/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/EBSPRD1_inmumdcdbadm01.xml
[inmumdcdbadm01.tajhotels.com -> oracle:/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/bin] :vi 

/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/EBSPRD1_inmumdcdbadm01.xml
[inmumdcdbadm01.tajhotels.com -> oracle:/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/bin] :grep -i scan $CONTEXT_FILE
      <remote_listener oa_var="s_instRemoteListener">inmumdc-scan:1521</remote_listener>
         <scanName oa_var="s_scan_name">inmumdc-scan</scanName>
         <scanPort oa_var="s_scan_port">1521</scanPort>
         <scanUpdateFlag oa_var="s_update_scan">TRUE</scanUpdateFlag>
[inmumdcdbadm01.tajhotels.com -> oracle:/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/bin] :./adconfig.sh
Enter the full path to the Context file: /u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/EBSPRD1_inmumdcdbadm01.xml
Enter the APPS user password:
The log file for this session is located at: /u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/log/EBSPRD1_inmumdcdbadm01/09040245/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.
        Using ORACLE_HOME location : /u01/app/oracle/product/11.2.0.3/EBSPRD
        Classpath                   : 

:/u01/app/oracle/product/11.2.0.3/EBSPRD/jdbc/lib/ojdbc5.jar:/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/java/xmlparserv2.jar:/u01/app/oracle/product/11.2.0.3/EBS

PRD/appsutil/java:/u01/app/oracle/product/11.2.0.3/EBSPRD/jlib/netcfg.jar:/u01/app/oracle/product/11.2.0.3/EBSPRD/jlib/ldapjclnt11.jar

        Using Context file          : /u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/EBSPRD1_inmumdcdbadm01.xml

Context Value Management will now update the Context file

        Updating Context file...COMPLETED

        Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db112
Updating rdbms type in Context file to 64 bits
Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.
[inmumdcdbadm01.tajhotels.com -> oracle:/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/bin] :


#####################On 2nd RAC DB Node ##########################

[inmumdcdbadm02.tajhotels.com -> oracle:/home/oracle] :srvctl status database -d EBSPRD
Instance EBSPRD1 is running on node inmumdcdbadm01
Instance EBSPRD2 is not running on node inmumdcdbadm02

[inmumdcdbadm02.tajhotels.com -> oracle:/home/oracle] :. EBSPRD2_inmumdcdbadm02.env

[inmumdcdbadm02.tajhotels.com -> oracle:/home/oracle] :sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 4 02:49:07 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1.4965E+10 bytes
Fixed Size                  2242944 bytes
Variable Size            3288336000 bytes
Database Buffers         1.1643E+10 bytes
Redo Buffers               31297536 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,log_mode,open_mode,controlfile_type,database_role from v$database;

NAME      LOG_MODE     OPEN_MODE            CONTROL DATABASE_ROLE
--------- ------------ -------------------- ------- ----------------
EBSPRD    ARCHIVELOG   READ WRITE           CURRENT SNAPSHOT STANDBY

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

[inmumdcdbadm02.tajhotels.com -> oracle:/home/oracle] :srvctl status database -d EBSPRD
Instance EBSPRD1 is running on node inmumdcdbadm01
Instance EBSPRD2 is running on node inmumdcdbadm02


[inmumdcdbadm02.tajhotels.com -> oracle:/home/oracle] :cd /u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/bin/
[inmumdcdbadm02.tajhotels.com -> oracle:/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/bin] :echo $CONTEXT_FILE
/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/EBSPRD2_inmumdcdbadm02.xml
[inmumdcdbadm02.tajhotels.com -> oracle:/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/bin] :vi 

/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/EBSPRD2_inmumdcdbadm02.xml

[inmumdcdbadm02.tajhotels.com -> oracle:/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/bin] :grep -i scan $CONTEXT_FILE
      <remote_listener oa_var="s_instRemoteListener">inmumdc-scan:1521</remote_listener>
         <scanName oa_var="s_scan_name">inmumdc-scan</scanName>
         <scanPort oa_var="s_scan_port">1521</scanPort>
         <scanUpdateFlag oa_var="s_update_scan">TRUE</scanUpdateFlag>

[inmumdcdbadm02.tajhotels.com -> oracle:/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/bin] :./adconfig.sh
Enter the full path to the Context file: /u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/EBSPRD2_inmumdcdbadm02.xml
Enter the APPS user password:
The log file for this session is located at: /u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/log/EBSPRD2_inmumdcdbadm02/09040248/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.
        Using ORACLE_HOME location : /u01/app/oracle/product/11.2.0.3/EBSPRD
        Classpath                   : 

:/u01/app/oracle/product/11.2.0.3/EBSPRD/jdbc/lib/ojdbc5.jar:/u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/java/xmlparserv2.jar:/u01/app/oracle/product/11.2.0.3/EBS

PRD/appsutil/java:/u01/app/oracle/product/11.2.0.3/EBSPRD/jlib/netcfg.jar:/u01/app/oracle/product/11.2.0.3/EBSPRD/jlib/ldapjclnt11.jar

        Using Context file          : /u01/app/oracle/product/11.2.0.3/EBSPRD/appsutil/EBSPRD2_inmumdcdbadm02.xml

Context Value Management will now update the Context file

        Updating Context file...COMPLETED

        Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db112
Updating rdbms type in Context file to 64 bits
Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.

#################Application Tier ############################

In my case, previously the Application Server was configured, hence Context File was ready. So Here I had to just run Autoconfig with Existing Context File.

Or if the Application Server need to, then we need to run adcfgclone.pl appsTier <CONTEXT_FILE>

[cbos -> appebs:/home/appebs] :cd $AD_TOP/bin
[cbos -> appebs:/d04_r12prodapp/oracle/apps/apps_st/appl/ad/12.0.0/bin] :echo $CONTEXT_FILE
/d04_r12prodapp/oracle_base/inst/apps/EBSPRD_cbos/appl/admin/EBSPRD_cbos.xml
[cbos -> appebs:/d04_r12prodapp/oracle/apps/apps_st/appl/ad/12.0.0/bin] :./adconfig.sh
Enter the full path to the Context file: /d04_r12prodapp/oracle_base/inst/apps/EBSPRD_cbos/appl/admin/EBSPRD_cbos.xml
Enter the APPS user password:
The log file for this session is located at: /d04_r12prodapp/oracle_base/inst/apps/EBSPRD_cbos/admin/log/09040334/adconfig.log

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.
        Using CONFIG_HOME location     : /d04_r12prodapp/oracle_base/inst/apps/EBSPRD_cbos
        Classpath                   : /d04_r12prodapp/oracle/apps/apps_st/comn/java/lib/appsborg2.zip:/d04_r12prodapp/oracle/apps/apps_st/comn/java/classes

        Using Context file          : /d04_r12prodapp/oracle_base/inst/apps/EBSPRD_cbos/appl/admin/EBSPRD_cbos.xml

Context Value Management will now update the Context file

        Updating Context file...COMPLETED

        Attempting upload of Context file and templates to database...COMPLETED

Configuring templates from all of the product tops...
        Configuring AD_TOP........COMPLETED
        Configuring FND_TOP.......COMPLETED
        Configuring ICX_TOP.......COMPLETED
        Configuring MSC_TOP.......COMPLETED
        Configuring IEO_TOP.......COMPLETED
        Configuring BIS_TOP.......COMPLETED
        Configuring AMS_TOP.......COMPLETED
        Configuring CCT_TOP.......COMPLETED
        Configuring WSH_TOP.......COMPLETED
        Configuring CLN_TOP.......COMPLETED
        Configuring OKE_TOP.......COMPLETED
        Configuring OKL_TOP.......COMPLETED
        Configuring OKS_TOP.......COMPLETED
        Configuring CSF_TOP.......COMPLETED
        Configuring IGS_TOP.......COMPLETED
        Configuring IBY_TOP.......COMPLETED
        Configuring JTF_TOP.......COMPLETED
        Configuring MWA_TOP.......COMPLETED
        Configuring CN_TOP........COMPLETED
        Configuring CSI_TOP.......COMPLETED
        Configuring WIP_TOP.......COMPLETED
        Configuring CSE_TOP.......COMPLETED
        Configuring EAM_TOP.......COMPLETED
        Configuring FTE_TOP.......COMPLETED
        Configuring ONT_TOP.......COMPLETED
        Configuring AR_TOP........COMPLETED
        Configuring AHL_TOP.......COMPLETED
        Configuring OZF_TOP.......COMPLETED
        Configuring IES_TOP.......COMPLETED
        Configuring CSD_TOP.......COMPLETED
        Configuring IGC_TOP.......COMPLETED

AutoConfig completed successfully.
[cbos -> appebs:/d04_r12prodapp/oracle/apps/apps_st/appl/ad/12.0.0/bin] :

[cbos -> appebs:/home/appebs] :sqlplus apps

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Sep 4 20:39:07 2014

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

Enter password:

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

SQL>
SQL> select node_name,server_address from fnd_nodes;

NODE_NAME                      SERVER_ADDRESS
------------------------------ ------------------------------
CBOS                           172.17.92.33
AUTHENTICATION                 *
INMUMDCDBADM01
INMUMDCDBADM02



[cbos -> appebs:/home/appebs] :cd $ADMIN_SCRIPTS_HOME

[cbos -> appebs:/d04_r12prodapp/oracle_base/inst/apps/EBSPRD_cbos/admin/scripts] :./adstrtal.sh apps/xxxxx

You are running adstrtal.sh version 120.15.12010000.3

The logfile for this session is located at /d04_r12prodapp/oracle_base/inst/apps/EBSPRD_cbos/logs/appl/admin/log/adstrtal.log
Executing service control script:

...
...
...
...
...

****************************************************


All enabled services for this node are started.

adstrtal.sh: Exiting with status 0

adstrtal.sh: check the logfile /d04_r12prodapp/oracle_base/inst/apps/EBSPRD_cbos/logs/appl/admin/log/adstrtal.log for more information ...


[cbos -> appebs:/d04_r12prodapp/oracle_base/inst/apps/EBSPRD_cbos/admin/scripts] :./adopmnctl.sh status -l

You are running adopmnctl.sh version 120.6.12010000.5

Checking status of OPMN managed processes...

Processes in Instance: EBSPRD_cbos.cbos.tajhotels.com
---------------------------------+--------------------+---------+----------+------------+----------+-----------+------
ias-component                    | process-type       |     pid | status   |        uid |  memused |    uptime | ports
---------------------------------+--------------------+---------+----------+------------+----------+-----------+------
OC4JGroup:default_group          | OC4J:oafm          | 5505114 | Alive    |   61182869 |   158596 |   0:00:21 | rmi:25635,ajp:25135,jms:24635
OC4JGroup:default_group          | OC4J:forms         | 3997890 | Alive    |   61182868 |   147512 |   0:00:31 | rmi:20636,ajp:22136,jms:23636
OC4JGroup:default_group          | OC4J:forms         | 117967~ | Alive    |   61182867 |   158560 |   0:00:31 | rmi:20635,ajp:22135,jms:23635
OC4JGroup:default_group          | OC4J:oacore        | 5571206 | Alive    |   61182866 |   148144 |   0:01:10 | rmi:20137,ajp:21637,jms:23137
OC4JGroup:default_group          | OC4J:oacore        | 157287~ | Alive    |   61182865 |   148256 |   0:01:10 | rmi:20136,ajp:21636,jms:23136
OC4JGroup:default_group          | OC4J:oacore        | 136973~ | Alive    |   61182864 |   160164 |   0:01:10 | rmi:20135,ajp:21635,jms:23135
HTTP_Server                      | HTTP_Server        | 3670524 | Alive    |   61182863 |     1228 |   0:01:03 | https1:4470,http1:8027


adopmnctl.sh: exiting with status 0

adopmnctl.sh: check the logfile /d04_r12prodapp/oracle_base/inst/apps/EBSPRD_cbos/logs/appl/admin/log/adopmnctl.txt for more information ...


Check the Application Login, Connection from Toad, SQL Plus, SQl Developer etc.


After accessing one day Oracle EBS Applications for testing purpose from technical/functional people, my flash recovery usage are

SQL> select NAME,SPACE_LIMIT/1024/1024/1024,SPACE_USED/1024/1024/1024,SPACE_RECLAIMABLE,NUMBER_OF_FILES from v$recovery_file_dest;

NAME                 SPACE_LIMIT/1024/1024/1024 SPACE_USED/1024/1024/1024    SPACE_RECLAIMABLE      NUMBER_OF_FILES
-------------------- -------------------------- ------------------------- -------------------- --------------------
+RECO_DC                                    150               59.42578125                    0                  110


SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE              PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE      NUMBER_OF_FILES
-------------------- -------------------- ------------------------- --------------------
CONTROL FILE                          .02                         0                    1
REDO LOG                                0                         0                    0
ARCHIVED LOG                            0                         0                    0
BACKUP PIECE                            0                         0                    0
IMAGE COPY                              0                         0                    0
FLASHBACK LOG                       39.93                         0                  110
FOREIGN ARCHIVED LOG                    0                         0                    0

7 rows selected.


###############################################################

Now It's time to revert from Snapshot Standby (Read Write) to Physical Standby Database after Testing,

First Stop the Application.

Stop RAC Database.

[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :srvctl stop database -d EBSPRD

[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :
[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 4 22:15:45 2014

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.4965E+10 bytes
Fixed Size                  2242944 bytes
Variable Size            3523217024 bytes
Database Buffers         1.1409E+10 bytes
Redo Buffers               31297536 bytes
Database mounted.
SQL> set linesize 1000
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from  v$restore_point;

NAME                                                                                                                             GUA
-------------------------------------------------------------------------------------------------------------------------------- ---
SNAPSHOT_STANDBY_REQUIRED_09/04/2014 02:38:04                                                                                    YES


SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;  

Database altered.

(The above command will take several minutes to apply flashback logs depending upon size)

============Database Alert Log during applying flashback logs ================

Thu Sep 04 22:17:08 2014
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (EBSPRD1)
Thu Sep 04 22:17:08 2014
krsv_proc_kill: Killing 16 processes (all RFS)
Flashback Restore Start
Thu Sep 04 22:20:04 2014
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point  dropped
Clearing standby activation ID 658089983 (0x2739a7ff)
The primary database controlfile was created using the
'MAXLOGFILES 315' clause.
There is space for up to 303 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl8.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl9.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl10.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl11.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl12.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl13.f' SIZE 536870912;
Shutting down archive processes
Archiving is disabled
Thu Sep 04 22:20:04 2014
ARCH shutting down
Thu Sep 04 22:20:04 2014
ARCH shutting down
Thu Sep 04 22:20:04 2014
ARCH shutting down
Thu Sep 04 22:20:04 2014
Thu Sep 04 22:20:04 2014
Thu Sep 04 22:20:04 2014
ARCH shutting downARCH shutting down

Thu Sep 04 22:20:04 2014
ARCH shutting downARCH shutting down
Thu Sep 04 22:20:04 2014
ARC7: Archival stoppedARCH shutting down


ARC5: Archival stopped
ARC6: Archival stopped
ARC3: Archival stoppedARC4: Archival stopped

ARC0: Archival stopped
ARC1: Archival stopped
ARC2: Archival stopped
Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Thu Sep 04 22:20:11 2014

=======================================================================================================================================


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


ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 4 22:25:17 2014

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.4965E+10 bytes
Fixed Size                  2242944 bytes
Variable Size            3523217024 bytes
Database Buffers         1.1409E+10 bytes
Redo Buffers               31297536 bytes
Database mounted.
SQL> select name,log_mode,open_mode,database_role,controlfile_type from v$database;

NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    CONTROL
--------- ------------ -------------------- ---------------- -------
EBSPRD    ARCHIVELOG   MOUNTED              PHYSICAL STANDBY STANDBY

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from  v$restore_point;

no rows selected

SQL> select flashback_on from v$database;

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

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from v$database;

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

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

Database altered.

SQL> select distinct recovery_mode from v$archive_dest_status;

RECOVERY_MODE
-----------------------

MANAGED REAL TIME APPLY

SQL> select PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS from v$managed_standby order by 3;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0          0
ARCH      CONNECTED             0          0          0          0          0
ARCH      CONNECTED             0          0          0          0          0
ARCH      CONNECTED             0          0          0          0          0
ARCH      CONNECTED             0          0          0          0          0
ARCH      CONNECTED             0          0          0          0          0
RFS       RECEIVING             1      22809     512001       2048          0
RFS       RECEIVING             1      22803     577537       2048          0
RFS       RECEIVING             1      22806     481281       2048          0
RFS       RECEIVING             1      22807     458753       2048          0
RFS       RECEIVING             1      22805     540673       2048          0

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ---------- ----------
RFS       RECEIVING             1      22804     473089       2048          0
RFS       RECEIVING             1      23014     458096       2048          0
RFS       RECEIVING             1      22808     444417       2048          0
RFS       RECEIVING             2      22180     491521       2048          0
RFS       RECEIVING             2      22178     518145       2048          0
RFS       RECEIVING             2      22182     503809       2048          0
RFS       RECEIVING             2      22179     468993       2048          0
RFS       RECEIVING             2      22183     608257       2048          0
RFS       RECEIVING             2      22181     589825       2048          0
MRP0      WAIT_FOR_GAP          2      21957          0          0          0
RFS       RECEIVING             2      22411      14338       2048          0

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ---------- ----------
ARCH      CLOSING               2      22408     454656       1495          0
ARCH      CLOSING               2      22407     196608        206          0
RFS       RECEIVING             2      22184     452609       2048          0

25 rows selected.


Verify that BLOCK# is changing which means recovery to the Physical Standby is in progress.

You can also verify the Archives Gap using below query,

============ On Primary ===============

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

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          23025
         2          22410

============ On Standby ===============

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

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          22432
         2          21956

After Some 12-13 hours,


============ On Primary ===============

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

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          23544
         2          22820


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

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          23543
         2          22820



I've purposely kept 2nd Physical Standby RAC Instance down, as anyway is in recovery mode so it would be happend from one node.

[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :srvctl status database -d EBSPRD
Instance EBSPRD1 is running on node inmumdcdbadm01
Instance EBSPRD2 is not running on node inmumdcdbadm02


- Any Suggestions /  Comments are welcome 
Manish

1 comment:

  1. Hi,
    Your post looks good.

    I understand your domain name is tajhotels.com from which I believe tajhotels is your client.
    I filtered out the following details from your post:

    Your CMOS server IP: 172.18.92.33
    Your Web Server Port: 8027
    Hopefully your DB listener port would be: 1521+27=1548
    Your DB's Scan listener Port: 1521
    Your DB server Name: INMUMDCDBADM01 & 02.
    Your Client use RAC Server with DR.
    and a lot of inner details of your application and DB.

    My humble opinion is, It's not a good practice to expose your client's IT Architecture details to Public on internet. Simple copy & paste would lead a disaster for your client and for your career too.

    Kindly edit your post and look for vulnerabilities before you post any article on public forum like this one.

    Hope you understand my point

    ReplyDelete