Primary Database Changes That Require Manual Intervention at a Physical Standby

Most structural changes made to a primary database are automatically propagated through redo data to a physical standby database. lists primary database structural and configuration changes which require manual intervention at a physical standby database.

Table 9-1 Primary Database Changes That Require Manual Intervention at a Physical Standby

Primary Database Change	Action Required on Physical Standby Database
Add a datafile or create a tablespace	No action is required if the STANDBY_FILE_MANAGEMENT database initialization parameter is set to AUTO. If this parameter is set to MANUAL, the new datafile must be copied to the physical standby database.
Drop or delete a tablespace or datafile	Delete datafile from primary and physical standby database after the redo data containing the DROP or DELETE command is applied to the physical standby.
Use transportable tablespaces	Move tablespace between the primary and the physical standby database.
Rename a datafile	Rename the datafile on the physical standby database.
Add or drop a redo log file group	Evaluate the configuration of the redo log and standby redo log on the physical standby database and adjust as necessary.
Perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause	Copy the datafile containing the unlogged changes to the physical standby database.
Grant or revoke administrative privileges or change the password of a user who has administrative privileges	If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to SHARED or EXCLUSIVE, replace the password file on the physical standby database with a fresh copy of the password file from the primary database.
Reset the TDE master encryption key	Replace the database encryption wallet on the physical standby database with a fresh copy of the database encryption wallet from the primary database.
Change initialization parameters	Evaluate whether a corresponding change must be made to the initialization parameters on the physical standby database.

unix CPU and Core information

# Count the number of “physical processor(s)”

grep “physical id” /proc/cpuinfo | sort -u | wc -l

# Count the number of “physical cores per CPU”

grep “cpu cores” /proc/cpuinfo |sort -u |cut -d”:” -f2

# Count the number of “logical cores ” (including multi-threading cores)

grep -c “processor” /proc/cpuinfo

Primary Database Changes That Require Manual Intervention at a Physical Standby

Most structural changes made to a primary database are automatically propagated through redo data to a physical standby database. Table  lists primary database structural and configuration changes which require manual intervention at a physical standby database.

Table 9-1 Primary Database Changes That Require Manual Intervention at a Physical Standby

	Primary Database Change	Action Required on Physical Standby Database
	Add a datafile or create a tablespace	No action is required if the STANDBY_FILE_MANAGEMENT database initialization parameter is set to AUTO. If this parameter is set to MANUAL, the new datafile must be copied to the physical standby database.
	Drop or delete a tablespace or datafile	Delete datafile from primary and physical standby database after the redo data containing the DROP or DELETE command is applied to the physical standby.
	Use transportable tablespaces	Move tablespace between the primary and the physical standby database.
	Rename a datafile	Rename the datafile on the physical standby database.
	Add or drop a redo log file group	Evaluate the configuration of the redo log and standby redo log on the physical standby database and adjust as necessary.
	Perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause	Copy the datafile containing the unlogged changes to the physical standby database.
	Grant or revoke administrative privileges or change the password of a user who has administrative privileges	If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to SHARED or EXCLUSIVE, replace the password file on the physical standby database with a fresh copy of the password file from the primary database.
	Reset the TDE master encryption key	Replace the database encryption wallet on the physical standby database with a fresh copy of the database encryption wallet from the primary database.
	Change initialization parameters	Evaluate whether a corresponding change must be made to the initialization parameters on the physical standby database.

Oracle Table and index size

COLUMN TABLE_NAME FORMAT A32

COLUMN OBJECT_NAME FORMAT A32

COLUMN OWNER FORMAT A10

SELECT

owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg

FROM

(SELECT segment_name table_name, owner, bytes

FROM dba_segments

WHERE segment_type = ‘TABLE’

UNION ALL

SELECT i.table_name, i.owner, s.bytes

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND   s.owner = i.owner

AND   s.segment_type = ‘INDEX’

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND   s.owner = l.owner

AND   s.segment_type = ‘LOBSEGMENT’

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND   s.owner = l.owner

AND   s.segment_type = ‘LOBINDEX’)

WHERE owner in UPPER(‘&owner’)

GROUP BY table_name, owner

HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */

ORDER BY SUM(bytes) desc

;

Dataguard Scripts – Monitoring

Note: This query needs to be run on the Primary database.

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.’),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,’.’)-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
);
DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
——– ———— ———— ———– ———— ——-
CPSPRD PRDU009N1 11213 11213 30-JUN/08:48 0
================================================================================

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

STATUS INSTANCE_NAME DATABASE_ROLE
———— —————- —————-
OPEN prim PRIMARY

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
————–
40
Check the status of the physical standby database and the latest sequence applied on the physcial standby database.

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

STATUS INSTANCE_NAME DATABASE_ROLE
——– ————- ———————
MOUNTED stnd PHYSICAL STANDBY

SQL> select max(sequence#) from v$archived_log where applied=’YES’;

MAX(SEQUENCE#)
————–
40
Check if the Managed Recovery Process (MRP) is active on the physcial standby database.

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

PROCESS STATUS SEQUENCE#
——— ———— ———-
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 41
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 41

9 rows selected.
Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.
Step 4:
Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
—— ————– —————- —————
OPEN stnd PHYSICAL STANDBY READ ONLY
Step 6:
Now start the MRP on the physical standby database.

SQL> alter database recover managed standby database disconnectfrom 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
RFS IDLE 41
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 41

9 rows selected.
Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.

Dataguard Monitoring Scripts

SET PAGESIZE 124

COL DB_NAME FORMAT A8

COL HOSTNAME FORMAT A12

COL LOG_ARCHIVED FORMAT 999999

COL LOG_APPLIED FORMAT 999999

COL LOG_GAP FORMAT 9999

COL APPLIED_TIME FORMAT A12

SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,LOG_ARCHIVED-LOG_APPLIED LOG_GAPFROM(SELECT NAME DB_NAMEFROM V$DATABASE),(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.’),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,’.’)-1))))) HOSTNAMEFROM V$INSTANCE),(SELECT MAX(SEQUENCE#) LOG_ARCHIVEDFROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’),(SELECT MAX(SEQUENCE#) LOG_APPLIEDFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’),(SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIMEFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);

 

 

 

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

 

SQL> select status,instance_name,database_role fromv$instance,v$database;  STATUS       INSTANCE_NAME    DATABASE_ROLE ——————————————– OPEN         prim             PRIMARY   SQL> select max(sequence#) from v$archived_log;   MAX(SEQUENCE#) ————– 40

Check the status of the physical standby database and the latest sequence applied on the physcial standby database.

SQL> select status,instance_name,database_role fromv$database,v$instance;  STATUS   INSTANCE_NAME DATABASE_ROLE —————————————— MOUNTED  stnd          PHYSICAL STANDBY   SQL> select max(sequence#) from v$archived_log where applied=’YES’;   MAX(SEQUENCE#) ————– 40

 

 

Check if the Managed Recovery Process (MRP) is active on the physcial standby database.

SQL> select process,status,sequence# from v$managed_standby;  PROCESS   STATUS        SEQUENCE# ——————————- ARCH      CONNECTED     0 ARCH      CONNECTED     0 ARCH      CONNECTED     0 ARCH      CONNECTED     0 RFS       IDLE          41 RFS       IDLE          0 RFS       IDLE          0 RFS       IDLE          0 MRP0      WAIT_FOR_LOG  41   9rows selected.

Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.

Step 4:

Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.

SQL> alter database recover managed standby database cancel;  Database altered.   SQL> alter database open;   Database altered.   SQL> select status,instance_name,database_role,open_mode fromv$database,v$instance;   STATUS INSTANCE_NAME  DATABASE_ROLE    OPEN_MODE ————————————————— OPEN   stnd           PHYSICAL STANDBY READ ONLY

Step 6:

Now start the MRP on the physical standby database.

SQL> alter database recover managed standby database disconnectfrom 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 RFS       IDLE          41 RFS       IDLE          0 RFS       IDLE          0 RFS       IDLE          0 MRP0      WAIT_FOR_LOG  41   9rows selected.

Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.

 

 

 

Managing dataguard – monitoring scripts

	LOGS             TIME—————- —————— Last applied   :  16-JUL-09:09:24:16 Last received :  16-JUL-09:09:28:36
	select ‘Last applied  : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Timefrom v$archived_log where sequence# = (select max(sequence#) from v$archived_log where applied=’YES’) union select ‘Last received : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);

 

 

 

NAME                   VALUE                  UNIT———————- ———————- ———————————– apply finish time      +00 00:02:07.2         day(2) to second(1) interval apply lag              +00 00:01:59           day(2) to second(0) interval estimated startup time 16                     second standby has been open  N transport lag          +00 00:00:00           day(2) to second(0) interval   Time Computed: 16-JUL-2009 09:33:16

 

 

selectNAME Name, VALUE Value, UNIT Unit from v$dataguard_stats union select null,null,’ ‘ from dual union select null,null,’Time Computed: ‘||MIN(TIME_COMPUTED) from v$dataguard_stats;

Redo onsite ——————– 16-JUL-2009 09:42:44

 

 

select to_char(max(last_time),’DD-MON-YYYY HH24:MI:SS’) “Redo onsite”from v$standby_log

 

RMAN backup job statistics:

col STATUS format a9
col hrs format 999.99
col TIME_TAKEN_DISPLAY format a10
col INPUT_BYTES_PER_SEC_DISPLAY format a10
col OUTPUT_BYTES_PER_SEC_DISPLAY format a10
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,
ELAPSED_SECONDS,
elapsed_seconds/3600 hrs,
TIME_TAKEN_DISPLAY,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY,
OUTPUT_BYTES_PER_SEC_DISPLAY
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;