Standard edition Data Guard enabling
Dataguard feature avaliable only in EE, But we can enable it in SE like below,
Primary
=======
Host: 192.168.56.101 (Already Database 11.2.0.4 running Standard edition)
DB: msc
Standby
=======
Host: 192.168.56.102 (11.2.0.4 standard software installed, no db)
DB: msc (we should create as standby)
Steps:
=====
1. Check archive log enabled in primary, otherwise enable it.
SQL> select name from v$database;
NAME
---------
MSC
SQL> !date
Fri Mar 30 11:10:54 IST 2018
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archive/
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
2. create pfile from spfile (Primary)
SQL> create pfile='/tmp/initdr.ora' from spfile;
File created.
SQL> ! ls -ltr /tmp/initdr.ora
-rw-r--r--. 1 oracle oinstall 973 Mar 30 11:11 /tmp/initdr.ora
3. create standby controlfile (Primary)
SQL> alter database create standby controlfile as '/tmp/standby.ctl';
Database altered.
SQL> ! ls -ltr /tmp/standby.ctl
-rw-r-----. 1 oracle oinstall 9748480 Mar 30 11:15 /tmp/standby.ctl
4. backup primary database plus archivelog (Primary)
[oracle@smartdb ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 30 11:17:06 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MSC (DBID=2695929158)
RMAN> backup database plus archivelog;
5. create the directory structure same as primary, it depends (Standby)
mkdir -p /u01/app/oracle/oradata/msc
mkdir -p /u01/app/oracle/fast_recovery_area/msc
mkdir -p /u01/app/oracle/admin/msc/adump
mkdir -p /u01/app/oracle/fast_recovery_area/MSC
6. copy pfile, controlfile backup, passwd file to standby (primary)
[oracle@smartdb tmp]$ scp -pr initdr.ora oracle@smartdr:/tmp
initdr.ora 100% 973 32.5KB/s 00:00
[oracle@smartdb tmp]$ scp -pr standby.ctl oracle@smartdr:/u01/app/oracle/oradata/msc/control01.ctl
standby.ctl 100% 9520KB 21.7MB/s 00:00
[oracle@smartdb tmp]$ scp -pr standby.ctl oracle@smartdr:/u01/app/oracle/fast_recovery_area/MSC/control02.ctl
standby.ctl 100% 9520KB 29.3MB/s 00:00
[oracle@smartdb tmp]$ scp -pr standby.ctl oracle@smartdr:/u01/app/oracle/fast_recovery_area/msc/control02.ctl
standby.ctl 100% 9520KB 25.3MB/s 00:00
[oracle@smartdb MSC]$ scp -pr $ORACLE_HOME/dbs/orapwmsc oracle@smartdr:$ORACLE_HOME/dbs
7. copy backup to standby (primary)
[oracle@smartdb MSC]$ scp -pr backupset oracle@smartdr:/u01/app/oracle/fast_recovery_area/MSC/
o1_mf_annnn_TAG20180330T111725_fcvmvfz4_.bkp 100% 478KB 10.7MB/s 00:00
o1_mf_nnndf_TAG20180330T111727_fcvmvj0g_.bkp 100% 1014MB 21.8MB/s 00:46
o1_mf_ncsnf_TAG20180330T111727_fcvmwxvz_.bkp 100% 9600KB 18.4MB/s 00:00
o1_mf_annnn_TAG20180330T111815_fcvmwzm7_.bkp 100% 3584 2.9MB/s 00:00
8. Crosscheck the files are copied to standby (standby)
8. using pfile start standby with nomount
[oracle@smartdr tmp]$ export ORACLE_SID=msc
[oracle@smartdr tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 30 11:36:13 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/tmp/initdr.ora
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size 2253544 bytes
Variable Size 956304664 bytes
Database Buffers 587202560 bytes
Redo Buffers 7544832 bytes
9. mount standby (standby)
SQL> alter database mount standby database;
Database altered.
10.restore database (standby)
[oracle@smartdr tmp]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 30 11:38:02 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MSC (DBID=2695929158, not open)
RMAN> restore database;
Restore finished.
11.recover standby (standby)
Copy archives from primary to standby archive location and do below cmd,
SQL> recover standby database
ORA-00279: change 930348 generated at 03/30/2018 11:06:36 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_3_972126346.dbf
ORA-00280: change 930348 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 930415 generated at 03/30/2018 11:17:24 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_4_972126346.dbf
ORA-00280: change 930415 for thread 1 is in sequence #4
ORA-00278: log file '/u01/archive/1_3_972126346.dbf' no longer needed for this
recovery
ORA-00279: change 930445 generated at 03/30/2018 11:18:15 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_5_972126346.dbf
ORA-00280: change 930445 for thread 1 is in sequence #5
ORA-00278: log file '/u01/archive/1_4_972126346.dbf' no longer needed for this
recovery
ORA-00308: cannot open archived log '/u01/archive/1_5_972126346.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Recovery completed.
Testing the db sync via scripts:
======================
rsync script: (in Primary)
------------
*/5 * * * * rsync -av --ignore-existing /u01/archive/* oracle@smartdr.mga.com:/u01/archive/
recover.sql: (standby)
------------
spool /tmp/log.recovery replace
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
SELECT MAX(RECID) "Log id now" FROM V$LOG_HISTORY;
recover standby database
auto
spool off
exit
recover.sh: (standby)
----------
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1; export ORACLE_HOME
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
ORACLE_SID=msc; export ORACLE_SID
sqlplus / as sysdba @/u01/recover.sql
Scenario:
========
****In Primary,******
[oracle@smartdb archive]$ ls -ltr
total 484
-rw-r-----. 1 oracle oinstall 487936 Mar 30 11:17 1_3_972126346.dbf
-rw-r-----. 1 oracle oinstall 2048 Mar 30 11:18 1_4_972126346.dbf
[oracle@smartdb archive]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 30 11:52:24 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archive/
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
); 2 3 4 5 6 7 8
Table created.
SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 ); 2
1 row created.
SQL> SQL> 2
1 row created.
SQL> SQL> 2
1 row created.
SQL> SQL> 2
1 row created.
SQL> SQL> 2
1 row created.
SQL> SQL> 2
1 row created.
SQL>
SQL> select * from customers;
ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- ------------------------- ----------
1 Ramesh 32 Ahmedabad 2000
2 Khilan 25 Delhi 1500
3 kaushik 23 Kota 2000
4 Chaitali 25 Mumbai 6500
5 Hardik 27 Bhopal 8500
6 Komal 22 MP 4500
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archive/
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
You have new mail in /var/spool/mail/oracle
[oracle@smartdb archive]$ ls -ltr
total 832
-rw-r-----. 1 oracle oinstall 487936 Mar 30 11:17 1_3_972126346.dbf
-rw-r-----. 1 oracle oinstall 2048 Mar 30 11:18 1_4_972126346.dbf
-rw-r-----. 1 oracle oinstall 352256 Mar 30 11:58 1_5_972126346.dbf
-rw-r-----. 1 oracle oinstall 1024 Mar 30 11:58 1_6_972126346.dbf
[oracle@smartdb archive]$
Then run rsync script in primary. (automate it in cronjob)
rsync * --ignore-existing /u01/archive/ oracle@smartdr:/u01/archive/
[oracle@smartdb archive]$ rsync * --ignore-existing /u01/archive/ oracle@smartdr:/u01/archive/
skipping directory .
You have new mail in /var/spool/mail/oracle
[oracle@smartdb archive]$
****In Secondary****
Scripts placed under directory /u01
[oracle@smartdr u01]$ ls -ltr
total 864
drwxr-xr-x. 2 oracle oinstall 6 Mar 28 21:50 two
drwxr-xr-x. 4 oracle oinstall 38 Mar 28 21:58 app
-rw-r-----. 1 oracle oinstall 872960 Mar 29 14:46 archive1_12_972041417.dbf
-rwxrwxrwx. 1 oracle oinstall 421 Mar 29 18:01 recover.sh
-rwxrwxrwx. 1 oracle oinstall 186 Mar 29 18:01 recover.sql
drwxr-x---. 2 oracle oinstall 102 Mar 30 11:59 archive
[oracle@smartdr u01]$
Run Recover.sh, it will kick recover.sql and recover standby.
[oracle@smartdr u01]$ sh recover.sh
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 30 12:02:26 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
TO_CHAR(SYSDATE,'YY
-------------------
2018-03-30 12:02:26
Log id now
----------
4
ORA-00279: change 930445 generated at 03/30/2018 11:18:15 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_5_972126346.dbf
ORA-00280: change 930445 for thread 1 is in sequence #5
ORA-00279: change 931586 generated at 03/30/2018 11:58:08 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_6_972126346.dbf
ORA-00280: change 931586 for thread 1 is in sequence #6
ORA-00278: log file '/u01/archive/1_5_972126346.dbf' no longer needed for this
recovery
ORA-00279: change 931589 generated at 03/30/2018 11:58:11 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_972126346.dbf
ORA-00280: change 931589 for thread 1 is in sequence #7
ORA-00278: log file '/u01/archive/1_6_972126346.dbf' no longer needed for this
recovery
ORA-00308: cannot open archived log '/u01/archive/1_7_972126346.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@smartdr u01]$
Test the created table in standby,
[oracle@smartdr u01]$ export ORACLE_SID=msc
[oracle@smartdr u01]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 30 12:07:50 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> alter database open read only;
Database altered.
SQL> select open_mode,name from v$database;
OPEN_MODE NAME
-------------------- ---------
READ ONLY MSC
SQL> select * from customers;
ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- ------------------------- ----------
1 Ramesh 32 Ahmedabad 2000
2 Khilan 25 Delhi 1500
3 kaushik 23 Kota 2000
4 Chaitali 25 Mumbai 6500
5 Hardik 27 Bhopal 8500
6 Komal 22 MP 4500
6 rows selected.
Automate the scripts now.......ENJOY...........
