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;

How to check core CPU's

How to check core CPU's

Command to check CPU info on Linux
cat /proc/cpuinfo|grep processor|wc -l

OR
getconf _NPROCESSORS_ONLN

Command to check CPU info on Solaris
psrinfo -v|grep "Status of processor"|wc -l

Command to check CPU info on AIX
lsdev -C|grep Process|wc -l

Command to check CPU info on HP/UX
ioscan -C processor | grep processor | wc -l

RSYNC Usage Examples

RSYNC Usage Examples

An Example:  this will replicate the directory mqmrsynctest located on the local machine rac1 to the remote machine rac2

oracle@rac1 backup]$ rsync -avzh ./MQMRSYNCTEST oracle@rac2:/home/oracle/backup
oracle@rac2's password:
sending incremental file list
MQMRSYNCTEST/
MQMRSYNCTEST/mqmtest
MQMRSYNCTEST/test1
MQMRSYNCTEST/test10
MQMRSYNCTEST/test2
MQMRSYNCTEST/test3
MQMRSYNCTEST/test4
MQMRSYNCTEST/test5
MQMRSYNCTEST/test6
MQMRSYNCTEST/test7
MQMRSYNCTEST/test8
MQMRSYNCTEST/test9
MQMRSYNCTEST/test01/
MQMRSYNCTEST/test02/
MQMRSYNCTEST/test03/
MQMRSYNCTEST/test04/
MQMRSYNCTEST/test05/
MQMRSYNCTEST/test06/
MQMRSYNCTEST/test07/
MQMRSYNCTEST/test08/
MQMRSYNCTEST/test09/

sent 1.21K bytes  received 267 bytes  423.14 bytes/sec
total size is 1.20K  speedup is 0.81

[oracle@rac2 MQMRSYNCTEST]$ ls -l
total 40
-rw-r--r-- 1 oracle oinstall 1200 May  2 15:16 mqmtest
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test01
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test02
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test03
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test04
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test05
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test06
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test07
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test08
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:18 test09
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test1
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test10
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test2
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test3
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test4
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test5
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test6
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test7
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test8
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test9
[oracle@rac2 MQMRSYNCTEST]$


RSYNC FAQ's

1. Is Rsync replication encrypted?  -----   
Yes it can be, for example you could use rsync in conjunction with ssh : rsync -avzhe ssh oracle@rac1:/home/oracle//MQMRSYNCTEST /home/oracle/

2. In case of disconnection, will Rsync resend the package without any loss of data 
You would need to initiate another rsync operation to continue syncing the folders

3. Can Rsync be used with separate Ethernet on a private network? 
any TCP based network connection will work

4. Will Rsync affect the Server performance ?
Execution of any additional command will impact a machines performed,  whether it impacts performance to the point others notice it would be dependent on the to many variables to give you a definitive answer.  What volume of information is being read on the node and written to the receiving node, what are the specs of the machines involved, how fast is the network, how fast is the storage involved etc etc...

Note the rsync commands here have been implemented through a linux environment, they may differ across the unix variants.