16 Ocak 2019 Çarşamba

12c New Feature=> RESTORE AND RECOVER STANDBY DATABASE FROM NETWORK SERVICE

Oracle Database 12c New Feature: 

RESTORE AND RECOVER STANDBY DATABASE 
FROM NETWORK SERVICE  


It is always possible to loose archivelogs in our Prmiary-Standby Database configurations. When we loose even only one archivelog file, a gap would occur and the automatic recovery of our standby database stops.

We'd have several methods to resolve the gaps:

·                      Restore the archivelogs from backup and apply them manually
·                      Create incremental backup from primary and recover standby.


With the release of 12c, we have a new method to resolve the gaps; 

--->Restore&Recover from Network Service 


Steps:

1. Check the status:

SQL> SELECT distinct PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY order by process;
  

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH      CLOSING               1         82
ARCH      CLOSING               1         89
ARCH      CONNECTED             0          0
MRP0      WAIT_FOR_GAP          1         86
RFS       IDLE                  0          0
RFS       IDLE                  1         90

6 rows selected.

SQL> 


2. Stop the recovery on standby

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> 


3. Recover the database over the network;

RMAN> recover database from service TWELVE;

Starting recover at 25-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=249 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TWELVE
destination for restore of datafile 00001: /u01/app/oracle/oradata/TWELVE/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network bac
kup set from service TWELVE
destination for restore of datafile 00003: /u01/app/oracle/oradata/TWELVE/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TWELVE
destination for restore of datafile 00004: /u01/app/oracle/oradata/TWELVE/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TWELVE
destination for restore of datafile 00005: /u01/app/oracle/oradata/TWELVE/example01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TWELVE
destination for restore of datafile 00006: /u01/app/oracle/oradata/TWELVE/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 87 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_87_910395669.dbf
archived log for thread 1 with sequence 88 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_88_910395669.dbf
archived log for thread 1 with sequence 89 is already on disk as file /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch1_89_910395669.dbf
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-APR-16

RMAN> 


4. Start the recovery Check the status again;

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> SELECT distinct PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY order by process;

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH      CLOSING               1         82
ARCH      CLOSING               1         89
ARCH      CONNECTED             0          0
MRP0      WAIT_FOR_GAP          1         86
RFS       IDLE                  0          0
RFS       IDLE                  1         90

6 rows selected.

SQL> 

***As you see, standby is still waiting 86th archivelog because controlfile doesnt know the new SCN values of the datafiles yet. So, we need to restore the controlfile too.


5. Restore standby controlfile from network service

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shu immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  926941184 bytes
Fixed Size                  2930944 bytes
Variable Size             343934720 bytes
Database Buffers          574619648 bytes
Redo Buffers                5455872 bytes


SQL> 

[oracle@rent ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 25 21:23:20 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TWELVE (not mounted)

RMAN>  restore standby controlfile from service TWELVE;

Starting restore at 25-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service TWELVE
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TWELVE/control01.ctl
output file name=/u01/app/oracle/oradata/TWELVE/control02.ctl
Finished restore at 25-APR-16

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> alter database recover managed standby database using current logfile disconnect from session;

Statement processed



SQL> SELECT distinct PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY order by process;

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
MRP0      APPLYING_LOG          1         90
RFS       IDLE                  0          0
RFS       IDLE                  1         90

SQL>   


Goldengate between Oracle 11gR2 and Oracle 12cR1 include DDL replication

Goldengate Multiple Schema Replication between
Oracle 11gR2 and Oracle 12cR1
include DDL replication



Source Host:
OS:                  Red Hat Enterprise Linux Server release 5.10 (Tikanga)  x86_64
DB Name:       source  à 11gR2  (11.2.0.4.0)  reside on ASM
Host Name:     rac1.localdomain  
                
Target Host:
OS:                  Red Hat Enterprise Linux Server release 6.6 (Santiago) x86_64 
DB Name:        target  à 12cR1   (12.1.0.2.0)  reside on ASM
Host Name:     node1.localdomain

Goldengate Version:  12.1.2.1.0



Main Configuration Steps


  • Install Goldengate Software on both source and target systems
  • Export the schemas which will be replicated and import them to the target database
  • Prepare the source database
  • Prepare the target database
  • Configure extract, pump and replicat process
  • Start extract and replication proccess
  • Control DML and DDL operations




  
1.       Install Goldengate Software on both source and target systems

Source:

[oracle@rac1 ~]$ cd /media/sf_oracle_setups/goldengate/fbo_ggs_Linux_x64_shiphome/Disk1/
[oracle@rac1 Disk1]$ sh runInstaller






































































































 [oracle@rac1 gg]$ pwd


/u01/app/oracle/product/gg
[oracle@rac1 gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (rac1.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          


GGSCI (rac1.localdomain) 2>



Target:

[oracle@node1 goldengate]$ cd /media/sf_12c/goldengate/fbo_ggs_Linux_x64_shiphome/Disk1/
[oracle@node1 Disk1]$ sh runInstaller


















   
  
  









[oracle@node1 gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:21:34
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.



GGSCI (node1.localdomain) 1> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          

GGSCI (node1.localdomain) 2>


  
2.       Export the schemas which will be replicated and import them to the target database


Source:

create or replace directory pump_dir as '/u01/app/oracle/pump';

expdp  userid='"/ as sysdba"' schemas=hr,scott dumpfile=scott_hr.dmp directory='pump_dir'


Target:

create or replace directory pump_dir as '/u01/app/oracle/pump';

create tablespace target_tbs datafile '+DATA' size 500m;

impdp  userid='"/ as sysdba"' dumpfile=scott_hr.dmp directory='pump_dir' remap_tablespace=example:target_tbs;

grant connect, resource, unlimited tablespace to hr, scott;

***impdp can give some errors about granting roles if you dont create the roles first manually.



3.      Prepare the source database

a.       Switch database archivelog

[oracle@rac1 ~]$ srvctl stop database -d source
[oracle@rac1 ~]$ srvctl start database -d source -o mount

SQL> alter database archivelog;
SQL> alter database open;

b.       Add supplemental log and disable recyclebin

SQL> alter database add supplemental log data;
SQL> alter system set recyclebin=off scope=spfile;
SQL> purge dba_recyclebin;

c.        Create goldengate tablespace and user

SQL> create tablespace gg_tbs datafile '+DATA' size 500M reuse autoextend on;
SQL> create user gg_user identified by oracle default tablespace gg_tbs quota unlimited on gg_tbs;
SQL> grant create session, connect, resource to gg_user;
SQL> grant dba to gg_user;
SQL> grant execute on utl_file to gg_user;

d.       Create the objects and roles for ddl replication

[oracle@rac1 gg]$ pwd
/u01/app/oracle/product/gg

SQL> @marker_setup.sql
--enter gg_user for goldengate schema

SQL> @ddl_setup.sql
--enter gg_user for goldengate schema

SQL> @role_setup.sql
--enter gg_user for goldengate schema

SQL> GRANT GGS_GGSUSER_ROLE TO gg_user;

SQL> @ddl_enable.sql

SQL> alter system set enable_goldengate_replication=true;

e.       Add info about Goldengate DDL schema

[oracle@rac1 gg]$ ./ggsci
GGSCI (rac1.localdomain) 1> edit params ./GLOBALS
GGSCHEMA gg_user

GGSCI (rac1.localdomain) 2>


f.        Create trail and discard(only on target) directories

Source:

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/product/gg/dirdat/tr

Target:

[oracle@node1 ~]$ mkdir -p /u01/app/oracle/product/gg/dirdat/tr
[oracle@node1 ~]$ mkdir -p /u01/app/oracle/product/gg/discard

g.       Check manager process and port on host side

GGSCI (rac1.localdomain) 1> edit params mgr
PORT 7809

GGSCI (rac1.localdomain) 2> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED

GGSCI (rac1.localdomain) 3> start manager
Manager started.

GGSCI (rac1.localdomain) 4> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING

h.       Add primary key information to the logfiles for replicated schemas

GGSCI (rac1.localdomain) 1> dblogin userid gg_user;
Password:
Successfully logged into database.

GGSCI (rac1.localdomain as gg_user@source1) 2> add schematrandata hr
2015-01-14 17:24:04  INFO    OGG-01788  SCHEMATRANDATA has been added on schema hr.
2015-01-14 17:24:04  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema hr.

GGSCI (rac1.localdomain as gg_user@source1) 3> add schematrandata scott
2015-01-14 17:24:28  INFO    OGG-01788  SCHEMATRANDATA has been added on schema scott.
2015-01-14 17:24:28  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema scott.

i.         Add ASM entry to the tnsnames.ora for redo logs

ASM1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.150.198)(PORT = 1521))
    (CONNECT_DATA =
      (INSTANCE_NAME = +ASM1)
      (SERVER = dedicated)
      (SERVICE_NAME = +ASM)
    )
  )


4.      Prepare the target database

SQL> create tablespace gg_tbs datafile '+DATA' size 500M reuse autoextend on;

SQL> create user gg_user identified by oracle default tablespace gg_tbs quota unlimited on gg_tbs;

SQL> grant create session, connect, resource to gg_user;

SQL> grant dba to gg_user;

SQL> grant execute on utl_file to gg_user;

SQL> alter system set enable_goldengate_replication=TRUE;


5.      Configure extract, pump and replicat process

Source:

GGSCI (rac1.localdomain) 1> add extract ext1, tranlog, begin now
EXTRACT added.

GGSCI (rac1.localdomain) 2> add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1
EXTTRAIL added.

GGSCI (rac1.localdomain) 3> edit params ext1
extract ext1
userid gg_user password oracle
tranlogoptions asmuser sys@asm1 asmpassword 123456
exttrail /u01/app/oracle/product/gg/dirdat/tr
ddl include mapped
map hr.*;
map scott.*;
table hr.*;
table scott.*;


GGSCI (rac1.localdomain) 5> add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr, begin now
EXTRACT added

GGSCI (rac1.localdomain) 7> add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1
RMTTRAIL added.

GGSCI (rac1.localdomain) 6> edit params pump1
extract pump1
userid gg_user, password oracle
rmthost 192.168.150.241, mgrport 7809
rmttrail /u01/app/oracle/product/gg/dirdat/tr
passthru
table hr.*;
table scott.*;


GGSCI (rac1.localdomain) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     STOPPED     EXT1        00:00:00      00:31:46   
EXTRACT     STOPPED     PUMP1       00:00:00      00:09:15   



Target:

GGSCI (node1.localdomain) 1> edit params mgr
PORT 7809

GGSCI (node1.localdomain) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING


GGSCI (node1.localdomain) 6> edit params ./GLOBALS
checkpointtable gg_user.checkpoint


GGSCI (node1.localdomain) 1> dblogin userid gg_user
Password:
Successfully logged into database.


GGSCI (node1.localdomain as gg_user@target1) 2> add checkpointtable gg_user.checkpoint
Successfully created checkpoint table gg_user.checkpoint.


GSCI (node1.localdomain as gg_user@target1) 3> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin now
REPLICAT added.


GGSCI (node1.localdomain as gg_user@target1) 4> edit params rep1
replicat rep1
assumetargetdefs
SETENV (ORACLE_SID=target1)
SETENV (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)
userid gg_user, password oracle
discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10
DDL
map hr.*, target hr.*;
map scott.*, target scott.*;


GGSCI (node1.localdomain as gg_user@target1) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    STOPPED     REP1        00:00:00      00:11:44
  


6.      Start extract and replication proccess.

Source:
GGSCI (rac1.localdomain) 1> start extract ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (rac1.localdomain) 2> start extract pump1
Sending START request to MANAGER ...
EXTRACT PUMP1 starting

GGSCI (rac1.localdomain) 3> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT1        00:00:00      00:00:00   
EXTRACT     RUNNING     PUMP1       00:00:00      00:00:08

Target:

GGSCI (node1.localdomain) 11> start replicat rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (node1.localdomain) 12> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP1        00:00:00      00:00:08


7.      Control DML and DDL operations

Source:

[oracle@rac1 ~]$ sqlplus hr/hr
SQL> create table emre_test (idx number);
SQL> insert into emre_test values (1);
SQL> insert into emre_test values (2);
SQL> commit;
exit;

[oracle@rac1 ~]$ sqlplus scott/scott
SQL> create table emre_test2 (names varchar2(30));
SQL> insert into emre_test2 values ('emre');
SQL> insert into emre_test2 values ('emir');
SQL> commit;

Target:

[oracle@node1 tr]$ sqlplus hr/hr
SQL> select * from emre_test;

       IDX
----------
        1
        2
[oracle@node1 tr]$ sqlplus scott/scott
SQL> select * from emre_test2;

NAMES
------------------------------
emre
emir