Search This Blog

Total Pageviews

Saturday 21 January 2017

Refresh Standby in Standard edition .. ..



 Refresh Standby in standard edition .. ..



on standby database in standard edition 
Refresh Standby Database From The Primary Database Using RMAN Incremental Backup

standby database in standard edition 

Oracle version 

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production


On standby !!!!!!!!!!!!!


SQL> recover managed standby database disconnect;


SQL> 
select INST_ID, min(fhscn), min(FHRBA_SEQ) from x$kcvfh
group by inst_id;

   INST_ID MIN(FHSCN)       MIN(FHRBA_SEQ)
---------- ---------------- --------------
         1 960237                       15

SQL> 

break on INST_ID
set pages 1000 lines 150
COL GROUP#    for a15
col status    for a20
select INST_ID,PROCESS,STATUS,SEQUENCE#,THREAD#,GROUP#,PID from GV$MANAGED_STANDBY
order by INST_ID,STATUS;

   INST_ID PROCESS   STATUS                SEQUENCE#    THREAD# GROUP#                 PID
---------- --------- -------------------- ---------- ---------- --------------- ----------
         1 ARCH      CONNECTED                     0          0 N/A                   4126
           ARCH      CONNECTED                     0          0 N/A                   4128
           ARCH      CONNECTED                     0          0 N/A                   4130
           ARCH      CONNECTED                     0          0 N/A                   4132
           MRP0      WAIT_FOR_GAP                 15          1 N/A                   4431 <<<<<<<<<<---- archive log sequence 



SQL> SQL> select MIN(scn) Min_Scn from (SELECT to_char(CURRENT_SCN) scn FROM V$DATABASE
  2                                union
  3                                select to_char(min(fhscn)) from x$kcvfh
  4                                union
  5                                select to_char(min(f.fhscn)) from x$kcvfh f, v$datafile d
  6                                where f.hxfil =d.file#
  7                                and d.enabled != 'READ ONLY'
  8                                );

MIN_SCN
----------------------------------------
960237   <<<<<<<<<<<<< Take this scn no for rman backup from primary database 




On primary !!!!!!!!!!!!!!!


[oracle@ora INC]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 21 19:59:49 2017

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

connected to target database: ORAEE (DBID=2924703949)

RMAN>  BACKUP INCREMENTAL FROM SCN 960237 DATABASE FORMAT '/home/oracle/INC/ForStandby1_%U' tag 'FORSTANDBY2';  ---<<<<<<<<<<  

Starting backup at 21-JAN-17

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
backup will be obsolete on date 28-JAN-17
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/oraee/oraee/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/oraee/oraee/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/oraee/oraee/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/oraee/oraee/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-JAN-17
channel ORA_DISK_1: finished piece 1 at 21-JAN-17
piece handle=/home/oracle/INC/ForStandby1_0qrqjr9t_1_1 tag=FORSTANDBY2 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:48

using channel ORA_DISK_1
backup will be obsolete on date 28-JAN-17
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 21-JAN-17
channel ORA_DISK_1: finished piece 1 at 21-JAN-17
piece handle=/home/oracle/INC/ForStandby1_0rrqjriu_1_1 tag=FORSTANDBY2 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-17

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/home/oracle/INC/ForStandbyCTRL1.bck' tag 'ControlFile2';

Starting backup at 21-JAN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 21-JAN-17
channel ORA_DISK_1: finished piece 1 at 21-JAN-17
piece handle=/home/oracle/INC/ForStandbyCTRL1.bck tag=CONTROLFILE2 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-17

Starting Control File and SPFILE Autobackup at 21-JAN-17
piece handle=/u01/app/oracle/product/11.2.0/dbhome_3/dbs/c-2924703949-20170121-04 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-17

RMAN>



scp all the backup file from primary database  to standby database  !!!!!!!!!!!!!!!!!!



On standby !!!!!!!!!!!!!

ls -ltr /home/oracle/INC/
total 47160
-rw-r----- 1 oracle oinstall 10452992 Jan 21 11:47 ForStandby_0grqiue3_1_1
-rw-r----- 1 oracle oinstall  9797632 Jan 21 11:47 ForStandby_0hrqiuf7_1_1
-rw-r----- 1 oracle oinstall   441856 Jan 21 12:18 ArchORAEE_0mrqj08o_1_1_933855512
-rw-r----- 1 oracle oinstall  8003584 Jan 21 20:04 ForStandby1_0qrqjr9t_1_1
-rw-r----- 1 oracle oinstall  9797632 Jan 21 20:04 ForStandby1_0rrqjriu_1_1
-rw-r----- 1 oracle oinstall  9797632 Jan 21 20:07 ForStandbyCTRL1.bck


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


Database dismounted.
ORACLE instance shut down.

SQL> startup nomount ;
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2215064 bytes
Variable Size             167773032 bytes
Database Buffers          360710144 bytes
Redo Buffers                3764224 bytes
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

[oracle@ora ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 21 20:09:26 2017

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

connected to target database: ORAEE (not mounted)

RMAN> RESTORE STANDBY CONTROLFILE FROM '/home/oracle/INC/ForStandbyCTRL1.bck' ;

Starting restore at 21-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

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/oraeed/control01.ctl
Finished restore at 21-JAN-17

RMAN> startup mount ;

database is already started
database mounted
released channel: ORA_DISK_1

RMAN> CATALOG START WITH '/home/oracle/INC';

searching for all files that match the pattern /home/oracle/INC

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/INC/ForStandbyCTRL1.bck

Do you really want to catalog the above files (enter YES or NO)? NO

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 21-JAN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/oraeed/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/oraeed/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/oraeed/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/oraeed/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/INC/ForStandby1_0qrqjr9t_1_1
channel ORA_DISK_1: piece handle=/home/oracle/INC/ForStandby1_0qrqjr9t_1_1 tag=FORSTANDBY2
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished recover at 21-JAN-17

RMAN> exit


Recovery Manager complete.
[oracle@ora ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 21 20:11:07 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/ArchiveEED
Oldest online log sequence     20
Next log sequence to archive   22
Current log sequence           22

SQL> select INST_ID, min(fhscn), min(FHRBA_SEQ) from x$kcvfh group by inst_id;  

   INST_ID MIN(FHSCN)       MIN(FHRBA_SEQ)
---------- ---------------- --------------
         1 965195                       22

SQL> recover managed standby database disconnect;
Media recovery complete.


SQL> archive log list ;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/ArchiveEED
Oldest online log sequence     20
Next log sequence to archive   22
Current log sequence           22


break on INST_ID
set pages 1000 lines 150
COL GROUP#    for a15
col status    for a20
select INST_ID,PROCESS,STATUS,SEQUENCE#,THREAD#,GROUP#,PID from GV$MANAGED_STANDBY
order by INST_ID,STATUS;

   INST_ID PROCESS   STATUS                SEQUENCE#    THREAD# GROUP#                 PID
---------- --------- -------------------- ---------- ---------- --------------- ----------
         1 ARCH      CONNECTED                     0          0 N/A                   4516
           ARCH      CONNECTED                     0          0 N/A                   4518
           ARCH      CONNECTED                     0          0 N/A                   4520
           ARCH      CONNECTED                     0          0 N/A                   4522
           MRP0      WAIT_FOR_LOG                 22          1 N/A                   4564  <<<<<<<<< Now archive

Oracle DBA

anuj blog Archive