How to start and stop dataguard sync using dgmgrl

How to start and stop dataguard sync using dgmgrl

To check the configuration:

DGMGRL> show configuration
Configuration - dell
Protection Mode: MaxPerformance
Databases:
dell_live - Primary database
dell_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

To check the primary database status:

DGMGRL> show database dell_live
Database - dell_live
Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
DELL
Database Status:
SUCCESS

To check the standby database status:

DGMGRL> show database dell_stby
Database - dell_stby
Role:            PHYSICAL STANDBY
Intended State:  APPLY-ON
Transport Lag:   0 seconds
Apply Lag:       0 seconds
Real Time Query: OFF
Instance(s):
DELL
Database Status:
SUCCESS

To stop the database sync:

DGMGRL> edit database dell_stby set state=apply-off;
Succeeded.
DGMGRL> show database dell_stby
Database - dell_stby
Role:            PHYSICAL STANDBY
Intended State:  APPLY-OFF
Transport Lag:   0 seconds
Apply Lag:       0 seconds
Real Time Query: OFF
Instance(s):
DELL
Database Status:
SUCCESS

To start the database sync:

DGMGRL> edit database dell_stby set state=apply-on;
Succeeded.

To check the primary database status:

DGMGRL> show database dell_live
Database - dell_live
Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
DELL
Database Status:
SUCCESS

To check the standby database status:

DGMGRL> show database dell_stby;
Database - dell_stby
Role:            PHYSICAL STANDBY
Intended State:  READ-ONLY
Transport Lag:   0 seconds
Apply Lag:       3 minutes 10 seconds
Real Time Query: OFF
Instance(s):
DELL
Database Status:
SUCCESS

Oracle dataguard real time apply and non-real time apply

Oracle dataguard real time apply and non-real time apply

Real-time apply, which allows Data Guard to recover redo data from the current standby redo log file as it is being filled up by the RFS process.(Standby redo logs must for it)

How to enable real time apply?

Physical Standby:

SQL> alter database recover managed standby database cancel;

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

For Logical Standby:

SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE

How to check real-time apply is enabled or not?

SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
1 LOG_ARCHIVE_DEST_2             VALID     LOCAL          YES  MANAGED REAL TIME APPLY

(or)

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
DELL      READ ONLY WITH APPLY PHYSICAL STANDBY DELL



Non Real-time Apply (Managed Recovery), which allows Data Guard to recover redo data from the archived redo log file as it is being filled up from the standby redo log files.


How to enable Non-real time apply?

Physical Standby:

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

If you face below error:
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Stop the mrp process first:

SQL> alter database recover managed standby database cancel;

Logical Standby:

SQL>ALTER DATABASE START LOGICAL STANDBY APPLY;

How to check real-time apply is enabled or not?

SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
1 LOG_ARCHIVE_DEST_2             VALID     LOCAL          YES  MANAGED

(or)

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
DELL      READ ONLY            PHYSICAL STANDBY DELL



Quick Testing

On Primary:

SQL> set time on
00:03:15 SQL> create table test (name varchar2(30));
Table created.

00:03:24 SQL> insert into test values ('Mohd Qader');
1 row created.
00:05:22 SQL>  COMMIT;
Commit complete.

00:05:23 SQL> select * from test;

NAME
------------------------------
Mohd Qader


On Standby:

SQL> set time on
00:05:30 SQL> select * from test;

NAME
------------------------------
Mohd Qader

00:05:21 SQL>

Oracle Physical Standby Database shutdown and startup

Oracle Physical Standby Database shutdown and startup

Shutdown Physical Standby Database and MRP:

Please execute below on physical standby database to stop the Managed Recovery Process(MRP) and shutdown physical standby database:

# connect / as sysdba
STANDBY> alter database recover managed standby database cancel;
STANDBY> recover standby database until cancel;
auto
STANDBY> shutdown immediate
STANDBY> exit

Startup Physical Standby Database and MRP:

Please execute below commands on physical standby database to start physical standby database and the Managed Recovery Process(MRP) :

# connect / as sysdba
STANDBY> startup nomount
STANDBY> alter database mount standby database;
STANDBY> alter database recover managed standby database disconnect from session;
STANDBY> exit

How to check the database growth

How to check the database growth

SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
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;

How to check FRA location and it's utilization in Oracle

How to check FRA location and it's utilization in Oracle

set linesize 500
col NAME for a50
select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)",
round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)",
round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" ,
(select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2)
from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
from V$RECOVERY_FILE_DEST;

How to check installed components and options in Oracle

How to check installed components and options in Oracle

To check which components are installed in a database:
SELECT * FROM DBA_REGISTRY;

To check which options are enabled:
SELECT * FROM V$OPTION;

To check which features are used:
SELECT * FROM DBA_FEATURE_USAGE_STATISTICS;