Let us understand the states of the Database by running a command shown below.
DGMGRL> show database verbose 'mobpro_collab1';
Database - mobpro_collab1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
mobpro
***********************************************************************
DGMGRL> show database verbose 'mobpro_collab2';
Database - mobpro_collab2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
mobpro
***********************************************************************
Looking at the output marked in red, gives us a idea that the primary state indicates the the transport of the redo log is switched on and on the standby the redo log apply is on.
There are different states associated with the database depending on the type of database.
PRIMARY : TRANSPORT-ON, TRANSPORT-OFF
PHYSICAL STANDBY : APPLY-ON, APPLY-OFF
SNAPSHOT STANDBY: APPLY-OFF
LOGICAL STANDBY : APPLY-ON, APPLY-OFF
We will run a small test to understand what happens when we change the state of a database. Let us observe by changing the state of the primary database.
On the standby
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
On the primary switch a logfile
SQL> alter system switch logfile;
System altered.
Confirm if this is applied on the standby
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
194 YES
As you see Sequence 194 is applied on the standby. Lets run the same test again by changing the state of the transport on the primary.
DGMGRL> EDIT DATABASE 'mobpro_collab1' SET STATE='TRANSPORT-OFF';
Succeeded.
Transport is switched off, Switch a log on the primary.
SQL> alter system switch logfile;
System altered.
Check if this log is applied on the Standby.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
194 YES
Nope, not applied since the transport is switched off, internally the broker has changed the value of the parameter log_archive_dest_state_2, this can be confirmed by looking at the alert log of the primary.
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;
Sun Oct 25 15:47:19 2009
Remember in my earlier posting I had mentioned that spfile is mandatory, since the broker dynamically changes the parameter values and stores the information in its configuartion file.
Verify if the transport begins
DGMGRL> EDIT DATABASE 'mobpro_collab1' SET STATE='TRANSPORT-ON';
Succeeded.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
194 YES
195 YES
196 YES
197 YES
198 IN-MEMORY
Now I change the state of the standby database as shown below.
DGMGRL> EDIT DATABASE 'mobpro_collab2' SET STATE='APPLY-OFF';
Succeeded.
Checking the information
DGMGRL> show database verbose 'mobpro_collab2';
Database - mobpro_collab2
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 1 minute 14 seconds
Real Time Query: OFF
Instance(s):
mobpro
Switch a logfile on the primary
SQL> alter system switch logfile;
System altered.
Check if the log has been applied,
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
196 YES
197 YES
198 YES
199 NO
Log is shipped but not applied, the broker has actually cancelled the recovery, it is indicated by the entry in the alert log of the standby as show below.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Sun Oct 25 16:05:45 2009
MRP0: Background Media Recovery cancelled with status 16037
So far we had looked at the state of the DB Objects, let us now visit the properties of the Database objects. Not a rocket science to know whats a property,
DGMGRL> show database verbose 'mobpro_collab1';
Database - mobpro_collab1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
mobpro
Properties:
DGConnectIdentifier = 'mobpro_collab1_dg'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'collab1'
SidName = 'mobpro'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collab1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MOBPRO_COLLAB1_DGMGRL)(INSTANCE_NAME=mobpro)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/admin/mobpro/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
All the above marked in red describes the property of the primary database. Some of the properties is editable and some are readonly, hence Oracle document has named it monitorable and configurable. In short if you look at the above output the readonly properties has the value '(monitor)'.
To get more info, run the command as shown below.
DGMGRL> show database 'mobpro_collab1' 'TopWaitEvents';
TOP SYSTEM WAIT EVENTS
Event Wait Time
rdbms ipc message 36099998
SQL*Net message from client 3630353
DIAG idle wait 3627291
jobq slave wait 3219358
pmon timer 1813734
Here is the listing to see the value of the configurable property and how to change it.
DGMGRL> show database 'mobpro_collab1' 'NetTimeout';
NetTimeout = '30'
DGMGRL> EDIT DATABASE 'mobpro_collab1' SET PROPERTY 'NetTimeout' = 60;
Property "NetTimeout" updated
DGMGRL> show database 'mobpro_collab1' 'NetTimeout';
NetTimeout = '60'
A very important note, if we disable the primary to stop the transport of redo it effects the all the standby databases, if we choose to stop the redo transport for a particular standby then we need to change the configurable property of a particular standby as shown below.
DGMGRL> EDIT DATABASE 'mobpro_collab2' SET PROPERTY 'LogShipping'='OFF';
Property "LogShipping" updated
One more important property worth mentioning is DelayMins, the value to be supplied is in minutes which means that the standby will be behind the primary in terms of the apply services, useful if there is a user error on the primary like dropping a table, can be recovered from the standby.
Database - mobpro_collab1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
mobpro
***********************************************************************
DGMGRL> show database verbose 'mobpro_collab2';
Database - mobpro_collab2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
mobpro
***********************************************************************
Looking at the output marked in red, gives us a idea that the primary state indicates the the transport of the redo log is switched on and on the standby the redo log apply is on.
There are different states associated with the database depending on the type of database.
PRIMARY : TRANSPORT-ON, TRANSPORT-OFF
PHYSICAL STANDBY : APPLY-ON, APPLY-OFF
SNAPSHOT STANDBY: APPLY-OFF
LOGICAL STANDBY : APPLY-ON, APPLY-OFF
We will run a small test to understand what happens when we change the state of a database. Let us observe by changing the state of the primary database.
On the standby
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
On the primary switch a logfile
SQL> alter system switch logfile;
System altered.
Confirm if this is applied on the standby
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
194 YES
As you see Sequence 194 is applied on the standby. Lets run the same test again by changing the state of the transport on the primary.
DGMGRL> EDIT DATABASE 'mobpro_collab1' SET STATE='TRANSPORT-OFF';
Succeeded.
Transport is switched off, Switch a log on the primary.
SQL> alter system switch logfile;
System altered.
Check if this log is applied on the Standby.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
194 YES
Nope, not applied since the transport is switched off, internally the broker has changed the value of the parameter log_archive_dest_state_2, this can be confirmed by looking at the alert log of the primary.
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;
Sun Oct 25 15:47:19 2009
Remember in my earlier posting I had mentioned that spfile is mandatory, since the broker dynamically changes the parameter values and stores the information in its configuartion file.
Verify if the transport begins
DGMGRL> EDIT DATABASE 'mobpro_collab1' SET STATE='TRANSPORT-ON';
Succeeded.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
194 YES
195 YES
196 YES
197 YES
198 IN-MEMORY
Now I change the state of the standby database as shown below.
DGMGRL> EDIT DATABASE 'mobpro_collab2' SET STATE='APPLY-OFF';
Succeeded.
Checking the information
DGMGRL> show database verbose 'mobpro_collab2';
Database - mobpro_collab2
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 1 minute 14 seconds
Real Time Query: OFF
Instance(s):
mobpro
Switch a logfile on the primary
SQL> alter system switch logfile;
System altered.
Check if the log has been applied,
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
196 YES
197 YES
198 YES
199 NO
Log is shipped but not applied, the broker has actually cancelled the recovery, it is indicated by the entry in the alert log of the standby as show below.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Sun Oct 25 16:05:45 2009
MRP0: Background Media Recovery cancelled with status 16037
So far we had looked at the state of the DB Objects, let us now visit the properties of the Database objects. Not a rocket science to know whats a property,
DGMGRL> show database verbose 'mobpro_collab1';
Database - mobpro_collab1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
mobpro
Properties:
DGConnectIdentifier = 'mobpro_collab1_dg'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'collab1'
SidName = 'mobpro'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collab1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MOBPRO_COLLAB1_DGMGRL)(INSTANCE_NAME=mobpro)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/admin/mobpro/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
All the above marked in red describes the property of the primary database. Some of the properties is editable and some are readonly, hence Oracle document has named it monitorable and configurable. In short if you look at the above output the readonly properties has the value '(monitor)'.
To get more info, run the command as shown below.
DGMGRL> show database 'mobpro_collab1' 'TopWaitEvents';
TOP SYSTEM WAIT EVENTS
Event Wait Time
rdbms ipc message 36099998
SQL*Net message from client 3630353
DIAG idle wait 3627291
jobq slave wait 3219358
pmon timer 1813734
Here is the listing to see the value of the configurable property and how to change it.
DGMGRL> show database 'mobpro_collab1' 'NetTimeout';
NetTimeout = '30'
DGMGRL> EDIT DATABASE 'mobpro_collab1' SET PROPERTY 'NetTimeout' = 60;
Property "NetTimeout" updated
DGMGRL> show database 'mobpro_collab1' 'NetTimeout';
NetTimeout = '60'
A very important note, if we disable the primary to stop the transport of redo it effects the all the standby databases, if we choose to stop the redo transport for a particular standby then we need to change the configurable property of a particular standby as shown below.
DGMGRL> EDIT DATABASE 'mobpro_collab2' SET PROPERTY 'LogShipping'='OFF';
Property "LogShipping" updated
One more important property worth mentioning is DelayMins, the value to be supplied is in minutes which means that the standby will be behind the primary in terms of the apply services, useful if there is a user error on the primary like dropping a table, can be recovered from the standby.
No comments:
Post a Comment