Creating stand by Database on same host and perform the switchover task

-
Hi all,

I would like to share steps to configure Data-guard on a same host , in this case I am using windows host 


Details inline:
=========
OS: windows 10 64 bit 
DB version: 11.2.0.2 


Primary : sales 
Standby DB : sales_sb


Steps to be followed:
==============
step 1: Lets us create a service for the stand by database
step 2: Create directory structure for standby database
step 3: Configure the listener and tnsnames files 
step 4: Create password files for both primary and standby databases 
step 5: Check min requirements like archive ,force logging on primary database 
step 6: Update primary database parameter file 
step 7: Now take the backup of primary database
step 8: Now connect to rman for restore and recover 


Step 1:
====
C:\app\sl38\product\11.2.0\dbhome_1\bin\oradim -new -sid sales_sb
Instance created.


Step 2: 
====
Create directory Structure 

Step 3:
=====
Copy password file of primary database and rename to stand by as "PWDsales_sb.ora"

Step 4:
=====

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = sales)
      (ORACLE_HOME = C:\app\sl38\product\11.2.0\dbhome_1)
    )
   (SID_DESC =
      (SID_NAME = rmandb)
      (ORACLE_HOME = C:\app\sl38\product\11.2.0\dbhome_1)
    )
   (SID_DESC =
      (SID_NAME = sales_sb)
      (ORACLE_HOME = C:\app\sl38\product\11.2.0\dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )
DIRECT_HANDOFF_TTC_LISTENER=OFF



TNSNAMES.ora:
=========

sales =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sales)
    )
  )

sales_sb =
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = sales_sb)
      )
   )



Testing listener connectivity :
==============================

C:\WINDOWS\system32>tnsping sales
Used parameter files:
C:\app\sl38\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sales)))
OK (60 msec)

C:\WINDOWS\system32>tnsping sales_sb
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sales_sb)))
OK (60 msec)





Step 5 : doing  the prechecks 
===================== 
database should be archive mode 


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

check the force logging 

check the flashback database on 


SQL> select name,open_mode,flashback_on,force_logging from v$database;

NAME      OPEN_MODE            FLASHBACK_ON       FOR
--------- -------------------- ------------------ ---
SALES     READ WRITE           YES                YES


Step 6: Preparing parameter file of primary database

At primary:
==========
DB_name: sales 
DB_UNIQUE_NAME:sales_sb 
alter system set fal_server=sales_sb scope=both;
alter system set fal_client=sales scope=both;
alter system set log_archive_config='DG_CONFIG=(sales,sales_sb)' scope=both;
SQL>select * from v$dataguard_config;
DB_UNIQUE_NAME
------------------------------
sales
sales_sb
alter system set log_archive_dest_2='SERVICE=sales_sb NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sales_sb';
alter system set log_archive_dest_10 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST';
alter system set log_archive_dest_state_2=ENABLE;



step 7: ceate stand by redo logs 


ALTER DATABASE ADD STANDBY LOGFILE ('C:\APP\SL38\ORADATA\SALES\standby_redo01.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE ('C:\APP\SL38\ORADATA\SALES\standby_redo02.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE ('C:\APP\SL38\ORADATA\SALES\standby_redo03.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE ('C:\APP\SL38\ORADATA\SALES\standby_redo04.log') SIZE 100M;


step 8: shutdown and startup the database 


step 9: create pfile from stand by from spfile of primary 
*.audit_file_dest='C:\app\sl38\admin\sales_sb\adump'
*.control_files='C:\app\sl38\oradata\sales_sb\control01.ctl','C:\app\sl38\oradata\sales_sb\contr02.ctl'
*.db_name='sales_sb'
*.db_unique_name='sales_sb'
*.db_recovery_file_dest='C:\app\sl38\flash_recovery_area'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=salesXDB)'
*.fal_client='SALES_SB'
*.fal_server='SALES'
*.local_listener='LISTENER_sales_sb'
*.log_archive_config='DG_CONFIG=(sales_sb,sales)'
*.log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_dest_2='SERVICE=sales NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sales'
*.log_archive_dest_state_2='ENABLE'
*.db_file_name_convert='C:\app\sl38\oradata\sales\','C:\app\sl38\oradata\sales\sales_sb'
*.log_file_name_convert='C:\app\sl38\oradata\sales\','C:\app\sl38\oradata\sales\sales_sb'
*.log_archive_format='ARC%S_%R.%T'
*.standby_file_management='AUTO'

Step 10: Start the stand by using pfile 


SQL> startup pfile=C:\app\sl38\admin\sales_sb\pfile\prim_sales.ora nomount;
ORACLE instance started.

Total System Global Area 3173429248 bytes
Fixed Size                  2179936 bytes
Variable Size            1728056480 bytes
Database Buffers         1426063360 bytes
Redo Buffers               17129472 bytes
SQL>


Step 11: Connect to RMAN as auxiliary and execute the active duplicate 

C:\app\sl38\product\11.2.0\dbhome_1\BIN>rman target sys/oracle123@sales catalog ruser/ruser@rmandb auxiliary sys/oracle123@sales_sb

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 26 12:43:29 2019

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

connected to target database: SALES (DBID=877926465)
connected to recovery catalog database
connected to auxiliary database: SALES_SB (not mounted)

RMAN>


run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database nofilenamecheck 
spfile
parameter_value_convert 'sales','sales_sb'
set db_unique_name='sales_sb'
set db_file_name_convert='C:\app\sl38\oradata\sales\','C:\app\sl38\oradata\sales_sb\'
set log_file_name_convert='C:\app\sl38\oradata\sales\','C:\app\sl38\oradata\sales_sb\'
;
}

Step 11: Now go to primary and switch the logfile 


SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

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     54
Next log sequence to archive   56
Current log sequence           56
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Step 12:  Now start the MRP 


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

SQL> select protection_mode from v$database;

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


Changing the DG modes:
==================
SQL> select protection_mode from v$database;

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

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

    GROUP# BYTES/1024/1024
---------- ---------------
         1             100
         3             100
         2             100

SQL>  select group#,bytes/1024/1024 from v$standby_log;

    GROUP# BYTES/1024/1024
---------- ---------------
         4             100
         5             100
         6             100
         7             100

SQL>  select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING              55
ARCH      CONNECTED             0
ARCH      CLOSING              53
ARCH      CLOSING              54
MRP0      APPLYING_LOG         56
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                 56
RFS       IDLE                  0

9 rows selected.

SQL>

Now stop the MRP process on stand by 


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING              55
ARCH      CONNECTED             0
ARCH      CLOSING              53
ARCH      CLOSING              54
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                 56
RFS       IDLE                  0

8 rows selected.

SQL>



SQL>  show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=sales_sb NOAFFIRM ASYN
                                                 C VALID_FOR=(ONLINE_LOGFILES,P
                                                 RIMARY_ROLE) DB_UNIQUE_NAME=sa
                                                 les_sb
 
SQL> alter system set log_archive_dest_2='service=sales_sb LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=sales_sb';

System altered.
SQL>  show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=sales_sb LGWR AFFIRM S
                                                 YNC valid_for=(online_logfiles
                                                 ,primary_role) db_unique_name=
                                                 sales_sb  

Now shutdown the primary and then mount the database 

SQL> select protection_mode from v$database;

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

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;

Database mounted.
SQL> alter database set standby database to maximize availability;

Database altered.

SQL> alter database set standby database to maximize protection;

Database altered.

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM PROTECTION

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

SQL>



SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;



SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             61



At stand by 
========


SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING              61
ARCH      CONNECTED             0
ARCH      CLOSING              59
ARCH      CLOSING              60
MRP0      WAIT_FOR_LOG         62
RFS       IDLE                 62
RFS       IDLE                  0
RFS       IDLE                  0

8 rows selected.

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



How to switch over from stand by to primary and then vice versa
==============================================

SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> alter database commit to switchover to physical standby;

Database altered.



SQL> startup mount;
ORACLE instance started.

Total System Global Area 3173429248 bytes
Fixed Size                  2179936 bytes
Variable Size            1728056480 bytes
Database Buffers         1426063360 bytes
Redo Buffers               17129472 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
MRP0      WAIT_FOR_LOG          0

SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

SQL>


At stand by :
========
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL>

SQL> alter database commit to switchover to physical standby with session shutdown ;

Database altered.


Total System Global Area 3173429248 bytes
Fixed Size                  2179936 bytes
Variable Size            1728056480 bytes
Database Buffers         1426063360 bytes
Redo Buffers               17129472 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
MRP0      WAIT_FOR_LOG         65

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

SQL>


AT Primary:
==========
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

SQL>






































At stand by :
=========





































Comments

Post a Comment

Popular posts from this blog

Understanding Terraform

-
 Terraform is a tool used  for building, changing, and versioning infrastructure safely and efficiently. A Terraform can manage multiple cloud services using a provider plugin  Terraform work flow? Terraform work flow will be comprised of validate -> plan -> apply and finally destroy. what is the important file of terraform which records the actions of Terraform apply? For every Terraform execution the action will be recorded in a file called Terraform state file .  The terraform init command is used to initialise a working directory containing Terraform configuration files. During init, the configuration is searched for module blocks, and the source code for referenced modules is retrieved from the locations given in  their source arguments. The terraform plan command is used to create an execution plan. It will not modify things in infrastructure. The terraform apply command is used to apply the changes required to reach the desired state of the configur...
Read more

How to make CRS and ASM not to restart after server reboot

-
I have noticed that server rebooting is taking lot of time on a VM machine for every reboot, so I have decided to auto start of the crs and ASM service During this phase I have noticed an attribute called auto_start when I try to execute the below command crsctl stat res -p This AUTO_START attribute is there for several resources like ora.ASM ,ora.crs,ora.DATA_DG it has got 2 options restore(0) , never(2) and always (1) always (1)  Causes the resource to restart when the node restarts regardless of the resource’s state when the node stopped -restore (0)  Does not start the resource at restart time if it was in an offline state, such as STATE=OFFLINE, TARGET=OFFLINE, when the node stopped. The resource is restored to its state when the node went down. The resource is started only if it was online before and not otherwise. -never (2)  Oracle Clusterware never restarts the resource regardless of the resource’s state when the node is stopped. So I have dec...
Read more

How to repair ASM disk header

-
Image
What is ASM disk header corruption? ASM disk header corruption is a loss or damage to the disk header which contains the metadata essential for the operation and availability of an ASM disk group in Oracle. Imagine that you have an ASM disk corruption in any of the ASM diskgroups that have critical business data and the diskgroup has been configured as "EXTERNAL" redundancy, meaning that if one disk is inaccessible the whole diskgroup will be unavailable. Now what? Is your only option to restore it from backup by recreating the diskgroup or are there any other faster ways to recover the data? Here you need to find out whether it’s a complete loss of data in the ASM disk or is it just a header corruption. Hopefully for you, this is just a header corruption. Because, yes, there is a way out!  Below find the steps to restore from the ASM header corruption. Most of the data in the ASM disk header is of interest to that disk only. However, some information in the ASM disk header ...
Read more