Physical Standby database Fully Switchover

-PRIMARY PFILE

orcl.__db_cache_size=306184192
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=125829120
orcl.__streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0\admin\orcl\adump'
*.background_dump_dest='D:\oracle\product\10.2.0\admin\orcl\bdump'
*.user_dump_dest='D:\oracle\product\10.2.0\admin\orcl\udump'
*.core_dump_dest='D:\oracle\product\10.2.0\admin\orcl\cdump'
*.compatible='10.2.0.3.0'
*.control_files='D:\oracle\product\10.2.0\oradata\orcl\control01.ctl','D:\oracle\product\10.2.0\oradata\orcl\control02.ctl','D:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
*.db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=148897792
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
#sessions=115
*.sga_target=447741952
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'


#PARAMETER FOR STANDBY CONFIGURATION

db_name='ORCL'
db_unique_name='ORCL'
instance_name='ORCL'
service_names='ORCL'
dg_broker_start='TRUE'
log_archive_config='DG_CONFIG=(ORCL,STAN)'
log_archive_dest_1='LOCATION=D:\Archive\ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'
log_archive_dest_2='SERVICE=STAN LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=STAN'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
FAL_CLIENT='ORCL'
FAL_SERVER='STAN'
DB_FILE_NAME_CONVERT='C:\STAND\','D:\oracle\product\10.2.0\oradata\orcl\'
LOG_FILE_NAME_CONVERT='C:\STAND\','D:\oracle\product\10.2.0\oradata\orcl'
standby_file_management='AUTO'
standby_archive_dest='D:\standby_archivelog\'
log_archive_format='ARC_%t_%s_%r.dbf'
log_archive_max_processes=30
log_archive_trace=100
fast_start_mttr_target=300

-STANDBY PFILE

stan.__db_cache_size=306184192
stan.__java_pool_size=4194304
stan.__large_pool_size=4194304
stan.__shared_pool_size=125829120
stan.__streams_pool_size=0
*.audit_file_dest='C:\STAND\adump'
*.background_dump_dest='C:\STAND\bdump'
*.core_dump_dest='C:\STAND\cdump'
*.user_dump_dest='C:\STAND\udump'
*.compatible='10.2.0.3.0'
*.control_files='C:\STAND\control01.ctl'
*.db_recovery_file_dest='C:\STAND\flash_recovery_area\'
*.db_recovery_file_dest_size=2147483648
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=148897792
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
#sessions=115
*.sga_target=447741952
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'


#PARAMETER FOR STANDBY CONFIGURATION

db_name='orcl'
db_unique_name='STAN'
instance_name='STAN'
service_names='STAN'
dg_broker_start='TRUE'
log_archive_config='DG_CONFIG=(orcl,STAN)'
log_archive_dest_1='LOCATION=C:\STANARCHIVE\ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STAN'
log_archive_dest_2='SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=orcl'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
FAL_CLIENT='STAN'
FAL_SERVER='orcl'
DB_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\orcl','C:\STAND\'
LOG_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\orcl','C:\STAND\'
standby_file_management='AUTO'
standby_archive_dest='D:\standby_archivelog\'
log_archive_format='ARC_%t_%s_%r.dbf'
log_archive_max_processes=30
log_archive_trace=100
fast_start_mttr_target=300



##Primary##

1. Enable forced logging on your primary database:


SET LINE 32000 PAGESIZE 32000

ALTER DATABASE FORCE LOGGING;

select * from v$pwfile_users;

col size format a15 heading "LOG SIZE"
SELECT ((BYTES)/1024/1024)||' MB' AS "SIZE" FROM V$LOG;

col member format a100 heading "REDO DESTINATION"
select group#, member from v$logfile ORDER BY 1;

=========================================================================
SWITCHOVER CASE
NOTE:-# both database (primary & standby )create different name of redolog files & its must becuase use current controlfile on the standby & if we are use same name
of redolog file so then switch the logfile hang problem and throw the error on ALERT.log ORA:-01577(so use different name of standby file)
same size but different name.
=========================================================================

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 100M;

select * from v$standby_log;

shu immediate
create spfile from pfile='C:\pfile\tt\INITORCL.ora';

###Copy the data files and temp files over.
host
D:
cd ORCLD1011
COPY *.dbf C:\STAND\
COPY *.log C:\STAND\
color f1
EXIT


STARTUP MOUNT
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'C:\STAND\control01.ctl';
ALTER DATABASE OPEN;

host
oradim -new -sid STAN -startmode manual -intpwd oracle
exit
exit


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

##On the Standby Database Site:
. Configure listeners for the primary and standby databases.

1) On Primary system: use Oracle Net Manager to configure a listener for ORCL and STAN. Then restart the listener.

lsnrctl reload
lsnrctl stop
lsnrctl start
lsnrctl status

Set ORACLE_SID=STAN
sqlplus sys/oracle@stansrv as sysdba
create spfile from pfile='C:\pfile\tt\INITSTAN.ora';
startup mount

# Note:- when both primary & standby database log file name same then delete standy & check the following

# Both database tiger the command

col member format a100 heading "REDO DESTINATION"
select group#, member from v$logfile ORDER BY 1;


# When same name so delete standby log & recreate & check which you one delete logfile is active or current then check status

#select * from v$log;

ALTER SYSTEM CHECKPOINT;


ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 100M;


# Both database tiger the command

col member format a100 heading "REDO DESTINATION"
select group#, member from v$logfile ORDER BY 1;

=========================================================================
set line 32000 pagesize 32000
SELECT NAME,OPEN_MODE,SWITCHOVER_STATUS,PROTECTION_LEVEL,PROTECTION_MODE ,DATABASE_ROLE
FROM V$DATABASE;

SELECT SEQUENCE#,NEXT_TIME,FIRST_TIME,APPLIED,NAME FROM V$ARCHIVED_LOG ORDER BY 1;
=========================================================================

LOG APPLY
alter database recover managed standby database disconnect from session;

Now go to primary database prompt, switch 3-4 times

SQL> alter system switch logfile;

SQL> Archive log list

Verifying the Standby Database
On standby database query the V$ARCHIVED_LOG view to verify that redo log received.

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

Now connect scott/tiger on primary database

create table or insert row in any table.

Now connect as sys on primary database and execute following SQL statement

SQL> alter system switch logfile;

On standby database execute following SQL statements

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL> recover managed standby database cancel;

SQL> alter database open read only;

And check whether the changes applied on the standby database or not.
SQL> SELECT SEQUENCE#, APPLIED, NAME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;




Maximum Protection This mode offers the highest level of data protection. Data is synchronously transmitted to the standby database from the primary database and transactions are not committed on the primary database unless the redo data is available on at least one standby database configured in this mode. If the last standby database configured in this mode becomes unavailable, processing stops on the primary database. This mode ensures no-data-loss.

Maximum Availability This mode is similar to the maximum protection mode, including zero data loss. However, if a standby database becomes unavailable (for example, because of network connectivity problems), processing continues on the primary database. When the fault is corrected, the standby database is automatically resynchronized with the primary database.

Maximum Performance—This mode offers slightly less data protection on the primary database, but higher performance than maximum availability mode. In this mode, as the primary database processes transactions, redo data is asynchronously shipped to the standby database. The commit operation of the primary database does not wait for the standby database to acknowledge receipt of redo data before completing write operations on the primary database. If any standby destination becomes unavailable, processing continues on the primary database and there is little effect on primary database performance.

=========================================================================
-Maximum Protection.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

ALTER DATABASE OPEN;
=========================================================================



Quick Switchover Steps

-PRIMARY

1. Initiate the switchover on the primary database :

Your database should be open in READ WRITE mode

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

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ WRITE PRIMARY SESSIONS ACTIVE

SQL> alter database switchover to STAN verify;

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

SQL> shu immediate

SQL> startup

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

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ ONLY PHYSICAL STANDBY TO PRIMARY


-STANDBY

2. After step 1 finishes, Switch the original physical standby database to primary role; 

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

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY SWITCHOVER PENDING

Initiate Log apply if any log from Primary DB remain unapplied.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL> Select open_mode,database_role ,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY TO PRIMARY

All log applied so stop the log apply.

SQL> Recover managed standby database cancel;

Now both database SWICHOVER_STATUS will show you TO PRIMARY means two standby database.

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

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY TO PRIMARY

Now switch standby database to PRIMARY role.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

SQL> shu immediate

SQL> startup

Now your standby database(STAN) becomes primary database and previous primary database(ORCL) becomes standby

STAN database location

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

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ WRITE PRIMARY SESSIONS ACTIVE

ORCL database location

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

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ ONLY PHYSICAL STANDBY TO PRIMARY

At STAN location
SQL> Alter system switch logfile;
SQL> Alter system switch logfile;
SQL> Archive log list

At ORCL location
SQL> Archive log list

Both location same Current log sequence 63

SELECT SEQUENCE#, APPLIED, NAME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP NAME
--------- --- --------------------------------------------
60 NO D:\ORCL\STANDBY_LOG\ARC00060_0773345094.001
61 NO D:\ORCL\STANDBY_LOG\ARC00061_0773345094.001
62 NO D:\ORCL\STANDBY_LOG\ARC00062_0773345094.001

ORCL>select open_mode,database_role ,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ ONLY PHYSICAL STANDBY RECOVERY NEEDED

Initiate Log apply 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

ORCL>select open_mode,database_role , switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY NOT ALLOWED

SELECT SEQUENCE#, APPLIED, NAME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP NAME
--------- --- --------------------------------------------
60 YES D:\ORCL\STANDBY_LOG\ARC00060_0773345094.001
61 YES D:\ORCL\STANDBY_LOG\ARC00061_0773345094.001
62 YES D:\ORCL\STANDBY_LOG\ARC00062_0773345094.001

If you switch log at this moment from STAN location(primary db), it will be applied automatically at ORCL location. Log 63,64,65 will show you APPLIED YES.

To stop the log apply.

SQL> recover managed standby database cancel;

ORCL> select open_mode,database_role ,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY NOT ALLOWED

SQL> Alter database open;

ORCL>select open_mode,database_role ,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ ONLY PHYSICAL STANDBY NOT ALLOWED

In READ ONLY mode if there is log switch on primary, new logs received but not applied.

SQL> SELECT SEQUENCE#, APPLIED, NAME FROM V$ARCHIVED_LOG WHERE SEQUENCE# > 60 ORDER BY SEQUENCE#;

SEQUENCE# APP NAME
---------- --- --------------------------------------------
71 NO D:\ORCL\STANDBY_LOG\ARC00071_0773345094.001
72 NO D:\ORCL\STANDBY_LOG\ARC00072_0773345094.001

If log file is not shipped to standby location then execute this command on both database. To register database with the listener.

SQL> alter system register;