Daily Archives: April 17, 2013

Oracle Activating the Standby Database – Switchover Method

Activating the Standby Database – Switchover Method

The main advantage of a graceful switchover is that it avoids the resetlogs operation. By avoiding the resetlogs operation, the source database can resume its role as the standby database almost immediately with no data loss. Another feature to a graceful switchover is that it does not invalidate previous backups.

1. Prerequisites :-

There is no loss of any archive logs that haven’t been (yet) applied to the standby database.

2. Set job_queue_processes value to 0 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 10

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 scope=both;

3. In PRIMARY database check the database role. (ABCD02 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE
——— —————- ——- ——————– ———– ———- ——————–
AIRMAN PRIMARY NONE SESSIONS ACTIVE 4106602309 READ WRITE MAXIMUM PERFORMANCE

4. In STANDBY database check the database role. (ABCD03 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE
——— —————- ——- ——————– ———– ———- ——————–
AIRMAN PHYSICAL STANDBY NONE SESSIONS ACTIVE 4106602309 MOUNTED MAXIMUM PERFORMANCE

5. Shutdown the PRIMARY database. (ABCD02 server)

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

6. Open the PRIMARY database in RESTRICTED mode. (ABCD02 server)

SQL> startup restrict

ORACLE instance started.

Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

7. Archive the current log on the PRIMARY database. (ABCD02 server)

SQL> alter system archive log current;

System altered.

8. Make sure the primary database and standby database are in sync. On both the primary and standby instances, issue the following. (ABCD02 & ABCD03)

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

THREAD# MAX(SEQUENCE#)
———- ————–
1 1934

Now, compare the results and make sure the Thread and Sequence # are the same. If the standby instance is ahead by 1 or none, you are in sync.
9. Initiate the switchover on the PRIMARY database. (ABCD02)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
10. Once the step above has completed, log on to the STANDBY database and issues the following command. (ABCD03)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
11. Immediately return to the FORMER PRIMARY database and issue a shutdown and mount the NEW STANDBY database. (ABCD02 server)
SQL> shutdown immediate
SQL> startup mount;
12. On the NEW PRIMARY/OLD STANDBY, you can now open the database as the PRIMARY database. (ABCD03 server)
SQL> alter database open;
Database opened.

13. Verify the new STANDBY’S status. (ABCD02 server)
SQL> select name, database_role from v$database;
NAME DATABASE_ROLE
——— —————-
PHYSICAL STANDBY
14. Set job_queue_processes value to 10 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 0

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;
15. Put the NEW STANDBY/FORMER PRIMARY database into managed recovery mode. (ABCD02)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
16. Test the communications for archive by performing a log switch. (ABCD03 server)
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
Now check whether these logs are applying in the NEW STANDBY and also check the listener status.
17. On the NEW PRIMARY database instance, take a backup if possible. (ABCD03 server)
Switch back from SZB location (ABCD03) to KJ location orginal primary sever (ABCD02)
Switch back to Orginal Primary Database – Switchover Method
1. Prerequisites :-

There is no loss of any archive logs that haven’t been (yet) applied to the standby database.

2. Set job_queue_processes value to 0 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 10

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 scope=both;

3. In New PRIMARY database check the database role. (ABCD03 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

4. In New STANDBY database check the database role. (ABCD02 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

5. Shutdown the New PRIMARY database. (ABCD03 server)

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

6. Open the New PRIMARY database in RESTRICTED mode. (ABCD03 server)

SQL> startup restrict

ORACLE instance started.

Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

7. Archive the current log on the New PRIMARY database. (ABCD03 server)

SQL> alter system archive log current;

System altered.

8. Make sure the primary database and standby database are in sync. On both the primary and standby instances, issue the following. (ABCD02 & ABCD03)

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

THREAD# MAX(SEQUENCE#)
———- ————–
1 1934

Now, compare the results and make sure the Thread and Sequence # are the same. If the standby instance is ahead by 1 or none, you are in sync.
9. Initiate the switchover on the New PRIMARY database. (ABCD03)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
10. Once the step above has completed, log on to the New STANDBY database/Orginal Primary and issues the following command. (ABCD02)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
11. Immediately return to the FORMER PRIMARY database/Orginal STANDBY and issue a shutdown and mount the Orginal STANDBY database. (ABCD03 server)
SQL> shutdown immediate
SQL> startup mount;
12. On the Orginal PRIMARY/OLD STANDBY, you can now open the database as the PRIMARY database. (ABCD02 server)
SQL> alter database open;
Database opened.

13. Verify the orginal STANDBY’S status. (ABCD03 server)
SQL> select name, database_role from v$database;
NAME DATABASE_ROLE
——— —————-
PHYSICAL STANDBY
14. Set job_queue_processes value to 10 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 0

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;
15. Put the Orginal STANDBY/FORMER PRIMARY database into managed recovery mode. (ABCD03)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
16. Test the communications for archive by performing a log switch. (ABCD02 server)
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
Now check whether these logs are applying in the Orginal STANDBY and also check the listener status.
17. On the Orginal PRIMARY database instance, take a backup if possible. (ABCD02 server)

Oracle Database Health Check Script

Leave a reply

SPOOL OFF
CLEAR SCREEN
SPOOL /tmp/quickaudit.lst

–SELECT SYSDATE FROM DUAL;
–SHOW USER

PROMPT
PROMPT ———————————————————————–|
PROMPT

SET TERMOUT ON
SET VERIFY OFF
SET FEEDBACK ON

PROMPT
PROMPT Checking database name and archive mode, dbid
PROMPT

column NAME format A9
column LOG_MODE format A12

SELECT NAME,CREATED, LOG_MODE, DBID FROM V$DATABASE;

PROMPT
PROMPT ———————————————————————–|
PROMPT

PROMPT
PROMPT Checking Time since last RMAN backup
PROMPT

select (sysdate-min(t))*24 from
(
select max(b.CHECKPOINT_TIME) t
from v$backup_datafile b, v$tablespace ts, v$datafile f
where INCLUDED_IN_DATABASE_BACKUP=’YES’
and f.file#=b.file#
and f.ts#=ts.ts#
group by f.file#
);

PROMPT
PROMPT ———————————————————————–|
PROMPT

PROMPT
PROMPT Checking Tablespace name and status
PROMPT

column TABLESPACE_NAME format a30
column STATUS format a10
set pagesize 400

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking free space in tablespaces
PROMPT

column tablespace_name format a30

SELECT tablespace_name ,sum(bytes)/1024/1024 “MB Free” FROM dba_free_space WHERE
tablespace_name ‘TEMP’ GROUP BY tablespace_name;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking freespace by tablespace
PROMPT

column dummy noprint
column pct_used format 999.9 heading “%|Used”
column name format a16 heading “Tablespace Name”
column bytes format 9,999,999,999,999 heading “Total Bytes”
column used format 99,999,999,999 heading “Used”
column free format 999,999,999,999 heading “Free”
break on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report

set linesize 132
set termout off
select a.tablespace_name name,
b.tablespace_name dummy,
sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id ) bytes,
sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id ) –
sum(a.bytes)/count( distinct b.file_id ) used,
sum(a.bytes)/count( distinct b.file_id ) free,
100 * ( (sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id )) –
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking Size and usage in GB of Flash Recovery Area
PROMPT

SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking free space In Flash Recovery Area
PROMPT

column FILE_TYPE format a20

select * from v$flash_recovery_area_usage;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking last sequence in v$archived_log
PROMPT

clear screen
set linesize 100

column STANDBY format a20
column applied format a10

–select max(sequence#), applied from v$archived_log where applied = ‘YES’ group by applied;

SELECT name as STANDBY, SEQUENCE#, applied, completion_time from v$archived_log WHERE DEST_ID = 2 AND NEXT_TIME > SYSDATE -1;

prompt
prompt—————-Last log on Primary————————————–|
prompt

select max(sequence#) from v$archived_log where NEXT_TIME > sysdate -1;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking switchover status
PROMPT

select switchover_status from v$database;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking for 20 Largest items in Database
PROMPT

column SEGMENT_NAME format A50

SELECT * FROM (
SELECT
OWNER, SEGMENT_NAME, BYTES/1024/1024 SIZE_MB
FROM
DBA_SEGMENTS
ORDER BY
BYTES/1024/1024 DESC ) WHERE ROWNUM <= 20;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking for Failed jobs
PROMPT

SELECT
OWNER,
LOG_DATE,
JOB_NAME,
STATUS –,
— REQ_START_DATE,
— ACTUAL_START_DATE,
— RUN_DURATION
FROM
DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE
STATUS ‘SUCCEEDED’
AND
LOG_DATE > SYSDATE -7;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking for invalid objects
PROMPT

column owner format A15
column object_name format A30 heading ‘Object’
column object_id format 999999 heading “Id#”
column object_type format A15
column status format A8

select owner, object_name, object_id, object_type, status
from dba_objects where status != ‘VALID’ and object_type != ‘SYNONYM’;

PROMPT
PROMPT ————————————————————————|

PROMPT
PROMPT How large is the database
PROMPT

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking the recycle Bin
PROMPT

SELECT
OWNER, SUM(SPACE) AS TOTAL_BLOCKS
FROM
DBA_RECYCLEBIN GROUP BY OWNER
ORDER BY OWNER;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking database versions
PROMPT

column BANNER format A64
select * from v$version;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking control file(s)
PROMPT

column STATUS format a7
column NAME format a68
column IS_RECOVERY_DEST_FILE format a3
set linesize 132

SELECT * FROM V$CONTROLFILE;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking redo logs and group(s)
PROMPT

column member format a90

SELECT group#, member FROM v$logfile;

SPOOL OFF

exit

Shanoj

Learn.Share.Grow

Daily Archives: April 17, 2013

Oracle Activating the Standby Database – Switchover Method

Oracle Database Health Check Script