Configure Data guard on same machine or same server

Configure Data guard on same machine or same server

Building the Data Guard

1.  Check the primary database name:

SQL> select name from v$database;

NAME

---------

ORCL

2. Enable Forced logging

--check FORCE LOGGING status

SQL> select force_logging from v$database;

FOR

---

NO

--eable FORCE LOGGING

SQL> alter database force logging;

Database altered.

--Verify

SQL> select force_logging from v$database;

FOR

---

YES

3.            Create a password file

--To create the password file

C:\>orapwd file= C:\app\CYBADMIN\product\11.2.0\dbhome_1\database \PWDORCL.ora password=orcl entries=5

--To verify the new password file

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 27 11:17:41 2015

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

SQL> connect sys/orcl as sysdba

Connected to an idle instance.

4. Redo Log

Step 1 Ensure log file sizes are identical on the primary and standby database

SQL> select group#,thread#,bytes/1024/1024,members from v$log;

    GROUP#    THREAD# BYTES/1024/1024    MEMBERS

---------- ---------- --------------- ----------

         1          1              50          1

         2          1              50          1

         3          1              50          1

Step 2 Determine the appropriate number of standby redo log file groups

The minimal number should be 3+1=4

Step 3 Verify related database parameters and settings

Maxlogfiles

Maxlogmembers

5.  Set Primary Database Initialization Parameters

--Content of INITorcl.ORA

orcl.__db_cache_size=687865856

orcl.__java_pool_size=16777216

orcl.__large_pool_size=16777216

ORCL.__oracle_base='C:\app\CYBADMIN'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=687865856

orcl.__sga_target=1006632960

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=268435456

orcl.__streams_pool_size=0

*.audit_file_dest='C:\app\CYBADMIN\admin\ORCL\adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='D:\app\ORCL\CONTROL01.CTL','D:\app\ORCL_Archive\CONTROL02.CTL'

*.db_block_size=8192

*.db_domain='cybage.com'

*.db_file_name_convert='ORCLSTBY','ORCL'

*.db_name='ORCL'

*.db_recovery_file_dest='D:\app\ORCL_Archive'

*.db_recovery_file_dest_size=4102029312

*.dg_broker_start=TRUE

*.DB_UNIQUE_NAME='ORCL'

*.diagnostic_dest='C:\app\CYBADMIN'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

*.FAL_CLIENT='ORCL'

*.FAL_SERVER='ORCLSTBY'

*.INSTANCE_NAME='ORCL'

*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))'

*.log_archive_config='DG_CONFIG=(ORCL,ORCLSTBY)'

*.log_archive_dest_1='LOCATION=D:\app\ORCL_Archive MANDATORY VALID_FOR=(all_logfiles,all_roles) DB_UNIQUE_NAME=ORCL'

*.log_archive_dest_2='SERVICE=ORCLSTBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTBY'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_archive_format='%t_%s_%r.arc'

*.LOG_ARCHIVE_MAX_PROCESSES=30

*.log_archive_min_succeed_dest=1

*.log_file_name_convert='ORCLSTBY','ORCL'

*.memory_target=1690304512

*.open_cursors=300

*.processes=150

*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

*.STANDBY_FILE_MANAGEMENT='AUTO'

*.undo_tablespace='UNDOTBS1'

--create spfile

SQL> create spfile from pfile = 'C:\app\CYBADMIN\product\11.2.0\dbhome_1\database\INITorcl.ORA';

File created.

6.            Enable the primary DB Archiving

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1686925312 bytes
Fixed Size                  2176368 bytes
Variable Size             989858448 bytes
Database Buffers          687865856 bytes
Redo Buffers                7024640 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

7. Shutdown Database

--Shutdown DB

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

8. Create a Control File for the Standby Database

SQL> startup mount

ORACLE instance started.

Total System Global Area 1686925312 bytes
Fixed Size                  2176368 bytes
Variable Size             989858448 bytes
Database Buffers          687865856 bytes
Redo Buffers                7024640 bytes

Database mounted.

SQL> alter database create standby controlfile as 'D:\app\ORCLSTBY\ORCLSTBY.CTL';

Database altered.

--Copy data files from "D:\app\ORCL" to "D:\app\ORCLSTBY"

9. Create the parameter file for Standby Database

Contents of the ORCLSTBY parameter file(INITorclstby):

orclstby.__db_cache_size=687865856

orclstby.__java_pool_size=16777216

orclstby.__large_pool_size=16777216

ORCLSTBY.__oracle_base='C:\app\CYBADMIN'#ORACLE_BASE set from environment

orclstby.__pga_aggregate_target=687865856

orclstby.__sga_target=1006632960

orclstby.__shared_io_pool_size=0

orclstby.__shared_pool_size=268435456

orclstby.__streams_pool_size=0

*.audit_file_dest='C:\app\CYBADMIN\admin\ORCLSTBY\adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='D:\app\ORCLSTBY\ORCLSTBY.CTL','D:\app\ORCL\ORCLSTBY.CTL'

*.db_block_size=8192

*.db_domain='cybage.com'

*.db_file_name_convert='ORCL','ORCLSTBY'

*.DB_NAME='ORCL'

*.db_recovery_file_dest='D:\app\ORCLSTBY\Arch'

*.db_recovery_file_dest_size=4102029312

*.DB_UNIQUE_NAME='ORCLSTBY'

*.dg_broker_start=TRUE

*.diagnostic_dest='C:\app\CYBADMIN'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLSTBYXDB)'

*.FAL_CLIENT='ORCLSTBY'

*.FAL_SERVER='ORCL'

*.instance_name='ORCLSTBY'

*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCLSTBY)'

*.LOG_ARCHIVE_DEST_1='LOCATION=D:\app\ORCLSTBY\Arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLSTBY'

*.LOG_ARCHIVE_DEST_2='SERVICE=ORCL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'

*.log_archive_max_processes=30

*.log_archive_min_succeed_dest=1

*.log_file_name_convert='ORCL','ORCLSTBY'

*.memory_target=1690304512

*.open_cursors=300

*.processes=150

*.query_rewrite_enabled='FALSE'

*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

*.service_names='ORCLSTBY'

*.STANDBY_FILE_MANAGEMENT='AUTO'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

10. Setup the Environment to Support the Standby Database

Step 1 Create a windows-based service

C:\>oradim -new -sid ORCLSTBY -intpwd orcl -startmode manual

Instance created.

Step 2 create a password file

C:\>set oracle_sid=ORCLSTBY

Copy PWDORCL.ORA and Paste it and rename it as PWDORCLSTBY.ORA in dir C:\app\CYBADMIN\product\11.2.0\dbhome_1\database

Step 3 configure listeners for the primary and standby database

Listener.ORA

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

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

       (GLOBAL_DBNAME = ORCL)

      (ORACLE_HOME = C:\app\CYBADMIN\product\11.2.0\dbhome_1)

       (SID_NAME=ORCL)

      (SERVICE_NAME = ORCLXDB)

    )

     (SID_DESC =

       (GLOBAL_DBNAME = ORCLSTBY)

       (ORACLE_HOME = C:\app\CYBADMIN\product\11.2.0\dbhome_1)

       (SID_NAME=ORCLSTBY)

      (SERVICE_NAME = ORCLSTBYXDB)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

  )

ADR_BASE_ORCL = C:\app\CYBADMIN

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

TNSNAMES.ORA

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

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = ORCL)

        (SERVICE_NAME = orcl)

           (UR = A)

    )

  )

ORCLSTBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

    (CONNECT_DATA =

       (SID = ORCLSTBY)

        (SERVICE_NAME = ORCLSTBY)

           (UR = A)

    )

  )

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

Start listener by firing the following command in cmd

C:\>lsnrctl start Listener

To check services are started or not by using the following command

C:\>lsnrctl stat Listener

C:\>tnsping ORCL

C:\>tnsping ORCLSTBY

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

Edit the hosts file in the folder C:\Windows\System32\drivers\etc as

172.27.232.125 localhost orcl.cybage.com

172.27.232.125 localhost orclstby.cybage.com

172.27.232.125 localhost localhost.localdomain

127.0.0.1 localhost localhost.localdomain

#IP from your manchine ip by firing the command in cmd as ipconfig

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

Step 4 create Oracle Net Service names

Step 5 create a server parameter file for the standby database

C:\>set oracle_sid=ORCLSTBY

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 27 11:18:41 2015
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
SQL> connect sys/orcl as sysdba

Connected to an idle instance.

SQL> create spfile from pfile = 'C:\app\CYBADMIN\product\11.2.0\dbhome_1\database\INITorclstby.ora';

File created.

--Verify the new spfile

SQL> show parameter spfile

NAME             TYPE      VALUE

--------------- ------- --------------------------------------------

spfile          string     C:\app\CYBADMIN\product\11.2.0\dbhome_1\database\SPFILEORCLSTBY.ORA

11. Mount the standby DB

SQL> startup nomount

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1247900 bytes

Variable Size              62915940 bytes

Database Buffers          100663296 bytes

Redo Buffers                2945024 bytes

SQL> alter database mount standby database;

Database altered.

Create standby redo log file groups

SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 4 ('D:\app\ORCLSTBY\REDO01.LOG') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 5 ('D:\app\ORCLSTBY\REDO02.LOG') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 6 ('D:\app\ORCLSTBY\REDO03.LOG') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 7 ('D:\app\ORCLSTBY\REDO04.LOG') SIZE 50M;

12.          Start Redo Apply

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

13.          Verify the status of primary and standby database

--ORCL window

C:\>set oracle_sid=ORCL

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 27 11:19:42 2015

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

SQL> connect sys/orcl as sysdba

Connected.

SQL> alter database open;

Database altered.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME   STATUS

-------------- --------

ORCL            OPEN

--ORCLSTBY window

C:\>set oracle_sid=ORCLSTBY

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 27 11:20:31 2015

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

SQL> connect sys/orcl as sysdba

Connected.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

--------------- ------------

ORCLSTBY         MOUNTED

Both instance status are OK

14.          Verify archived log transport and applying service

--ORCL window

SQL>  select name,sequence# from v$archived_log order by 2;

NAME                                       SEQUENCE#

----------------------------------------- -----------

D:\app\ORCL_Archive\1_80_595473116.ARC        80    

ORCLSTBY                                      80

D:\app\ORCL_Archive\1_81_595473116.ARC        81    
ORCLSTBY                                      81

D:\app\ORCL_Archive\1_82_595473116.ARC        82    
ORCLSTBY                                      82

--ORCLSTBY

SQL>  select name,sequence# from v$archived_log order by 2;

NAME                                                SEQUENCE#

-------------------------------------------------- ----------

D:\app\ORCLSTBY\Arch\1_80_595473116.ARC               80

D:\app\ORCLSTBY\Arch\1_81_595473116.ARC               81

D:\app\ORCLSTBY\Arch\1_82_595473116.ARC               82

--On the primary DB, switch log file by force

--ORCL window

SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:05.21

SQL> /

System altered.

Elapsed: 00:00:01.14

SQL> /

System altered.

Elapsed: 00:00:06.01

--Check the archived log again

--ORCL window

SQL> select name,sequence# from v$archived_log order by 2;

NAME                                     SEQUENCE#

---------------------------------------- ----------

…………

D:\app\ORCL_Archive\1_84_595473116.ARC     84  
ORCLSTBY                                   84

D:\app\ORCL_Archive\1_85_595473116.ARC     85  
ORCLSTBY                                   85

--ORCLSTBY window

SQL> select name,sequence# from v$archived_log order by 2;

NAME                                               SEQUENCE#

-------------------------------------------------- ----------

…………

D:\app\ORCLSTBY\Arch\1_84_595473116.ARC               84

D:\app\ORCLSTBY\Arch\1_85_595473116.ARC               85

--To check whether the new archived logs have been applied

--ORCLSTBY window

SQL> select sequence#,applied from v$archived_log order by sequence#;

SEQUENCE# APP

---------- ---

        80 YES

        81 YES

        82 YES

        83 YES

        84 YES

        85 YES

35 rows selected.

The archived logs are applied successfully on the standby database

Switch-over Testing

1.            Verify it is possible to perform a switchover.

--ORCL window

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

--------------------

SESSIONS ACTIVE

SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES;

NAME                      TYPE         VALUE

------------------------ ----------- ----------

job_queue_processes       integer      10

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION WHERE TYPE = 'USER'

AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);

       SID PROCESS       PROGRAM

---------- ------------  -------------------------------------------

        86 1404:3172     emagent.exe

        89 1234          OMS

        90 1234          OMS

        93 1404:1768     emagent.exe

        95 1234          OMS

        98 1988          ORACLE.EXE (J000)

6 rows selected.

--To stop ‘emagent’ and ‘OMS’, open another window, and in OS run

S:\>set oracle_sid=ORCL

S:\>emctl stop agent

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 27 11:22:21 2015
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

This will stop the Oracle Enterprise Manager 10g Database Control process. Continue [y/n] :y

The OracleDBConsoleORCL service is stopping............

The OracleDBConsoleORCL service was stopped successfully.

--Common Processes That Prevent Switchover

Type of Process                Process Description        Corrective Action

CJQ0      Job Queue Scheduler Process    Change the JOB_QUEUE_PROCESSES dynamic parameter to the value 0. The change will take effect immediately without having to restart the instance.

QMN0   Advanced Queue Time Manager              Change the AQ_TM_PROCESSES dynamic parameter to the value 0. The change will take effect immediately without having to restart the instance.

DBSNMP              Oracle Enterprise Manager Management Agent                Issue the emctl stop agent command from the operating system prompt.

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);

no rows selected

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

--------------------

TO STANDBY

Now it is ready to switch to standby

2.            Initiate the switchover on the primary database.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Database altered.

Elapsed: 00:02:57.73

3.            Shut down and restart the former primary instance.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1686925312 bytes
Fixed Size                  2176368 bytes
Variable Size             989858448 bytes
Database Buffers          687865856 bytes
Redo Buffers                7024640 bytes

Database mounted.

4. Verify the switchover status on the standby database

--ORCLSTBY window

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

--------------------

TO PRIMARY

5.Switch the target physical standby database role to the primary role

--ORCLSTBY window

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

6.Finish the transition of the standby database to the primary role.

--ORCLSTBY window

SQL> alter database open;

Database altered.

Elapsed: 00:00:33.56

7. Verify the status of primary and standby database

--ORCLSTBY now is the primary database

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

ORCLSTBY       OPEN

--ORCL now is the standby database

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

ORCL         MOUNTED

--start archived log applying on ORCL

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

Database altered.

--ORCLSTBY window

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

--ORCL window

SQL> select sequence#,applied from v$archived_log order by 1;

SEQUENCE# APP

--------- ---

…………

       90 YES

       91 YES

       92 YES

successfully switched-over!