Oracle Standby database

Shutting Down a Physical Standby Database

Issue the following query to find out if the standby database is performing Redo Apply or real-time apply. 

If the MRP0 or MRP process exists, then the standby database is applying redo.

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

If Redo Apply is running, cancel it as shown in the following example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Shut down the standby database.

SQL> SHUTDOWN IMMEDIATE;
Start and mount the physical standby database:


SQL> STARTUP MOUNT;
Start Redo Apply or real-time apply:

To start Redo Apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT FROM SESSION;
To start real-time apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  USING CURRENT LOGFILE;


ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
select status,instance_name,database_role from v$database,v$instance;
SELECT   name, db_unique_name, log_mode,   protection_mode, database_role FROM v$database;
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS;
SELECT client_process, process, thread#, sequence#, status FROM v$managed_standby  WHERE client_process='LGWR' or process='MRP0' ORDER BY process;

Before getting into the real action, validate the following to avoid any failures during the course of role transition:

Ensure log_archive_dest_2 is configured on PRIMARY and STANDBY databases
Media Recovery Process (MRP) is active on STANDBY and in sync with PRIMARY database

Create STANDBY REDO logs on PRIMARY, if not exists
FAL_CLIENT & FAL_SERVER parameters set on both databases

In Primary site:

FAL_SERVER=STANDBY
FAL_CLIENT=PRIMARY

In Standby site:

FAL_SERVER=PRIMARY
FAL_CLIENT=STANDBY

ALTER SYSTEM SET FAL_SERVER=TSTSB SCOPE=BOTH SID='*';
ALTER SYSTEM SET FAL_CLIENT=TSTSBY SCOPE=BOTH SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TSTDB,TSTSBY)' SCOPE=BOTH SID='*';
alter system set log_archive_config='dg_config=(TSTDB,TSTSBY)' scope=both

FAL_SERVER specifies the FAL (fetch archive log) server for a standby database.The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.
FAL_CLIENT specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER parameter, to refer to the FAL client. 


Verify TEMP tablespaces on STANDBY, add them if required, as TEMPFFILES created after STANDBY creation won’t be propagated to STANDBY site.

select status,instance_name,database_role from v$database,v$instance;

SQL> -- query on standby database
SQL> select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#;

SQL> -- query on physical database
SQL> select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#;

select status , error from v$archive_dest where dest_id=2;
alter database switchover to TSTSBY verify; 
alter system set log_archive_dest_state_2=enable scope=both;



start apply
alter database recover managed standby database disconnect from session;

  alter system switch logfile;
 select dest_id,error,status from v$archive_dest where dest_id=2;
 select max(sequence#),thread# from v$log_history group by thread#;
 select max(sequence#)  from v$archived_log where applied='YES' and dest_id=2


select group#, thread#, bytes/1024/1024 "Size in MB" from v$Log;
select group#, type from v$logfile;




ALTER SYSTEM SET log_archive_dest_2='service="tstsby"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="TSTSBY" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TSTSBY ASYNC NOAFFIRM VALID_FOR=(online_logfile,all_roles) DB_UNIQUE_NAME=TSTSBY' scope=both;

alter system set log_archive_config='dg_config=(TSTDB,TSTSBY)' scope=both

SWITCHOVER VERIFY: Send VERIFY request to switchover target GGSTBY
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected.
ORA-16475 signalled during: ALTER DATABASE SWITCHOVER TO GGSTBY VERIFY…
2017-09-03T08:16:47.465995-04:00

# this fixed the warning by doing this on the PRIMARY -TSTSB

alter system set log_archive_dest_2='service="TSTSBY"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="TSTSBY" net_timeout=30','valid_for=(online_logfile,all_roles)';

# for reference the standby must be defined as a DEST_2 as follows: -standby

alter system set log_archive_dest_2='service="TSTSB"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="TSTSB" net_timeout=30','valid_for=(online_logfile,all_roles)';

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Blocking session on RAC database

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...
Read more

Finding object size in Oracle database

Ref: http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153 Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)  SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name  FROM DBA_SEGMENTS  WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',  'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY')  --AND TABLESPACE_NAME LIKE 'COSTE%'  --AND SEGMENT_NAME LIKE 'P2010201%'  --AND partition_name LIKE 'P20100201%' --AND segment_type = 'TABLE' --AND OWNER = 'TARG...
Read more