Search This Blog

Total Pageviews

Sunday, 3 June 2018

Oracle Flashback for standby database

Oracle Flashback for standby database  ... 




Current status ..

[oracle@cloud-ora ~]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration ;

Configuration - vihcdbd

  Protection Mode: MaxPerformance
  Members:
  vihcdbd8 - Primary database
    vihcdbd  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 37 seconds ago)



======================================================
On Standby 

SQL> def
DEFINE _DATE           = "03-JUN-18" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "vihcdbd8" (CHAR)


set linesize 300
SELECT database_role role, name,dbid, db_unique_name, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;

ROLE             NAME            DBID DB_UNIQUE_NAME                 PLATFORM_ID OPEN_MODE            LOG_MODE     FLASHBACK_ON       PROTECTION_MODE      PROTECTION_LEVEL
---------------- --------- ---------- ------------------------------ ----------- -------------------- ------------ ------------------ -------------------- --------------------
PRIMARY          VIHCDBD    617390591 VIHCDBD8                                13 READ WRITE           ARCHIVELOG   NO                 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE


https://docs.oracle.com/cd/B28359_01/server.111/b28294/scenarios.htm#CIHEDFHH

select RESETLOGS_CHANGE#,current_scn,STANDBY_BECAME_PRIMARY_SCN  from v$database;

select RESETLOGS_CHANGE#,current_scn,STANDBY_BECAME_PRIMARY_SCN  from v$database;

RESETLOGS_CHANGE# CURRENT_SCN STANDBY_BECAME_PRIMARY_SCN
----------------- ----------- --------------------------
          3472787    11425577                    4372247



 flashback database to timestamp systimestamp - interval '15' minute;

mkdir -p /u01/app/oracle/oradata/vihcdbd/VIHCDBD8/FlashBack

SQL> !mkdir -p /u01/app/oracle/oradata/vihcdbd/VIHCDBD8/FlashBack

SQL> !ls -ltr /u01/app/oracle/oradata/vihcdbd/VIHCDBD8/
total 12
drwxr-x--- 2 oracle oinstall 4096 Apr 28 14:24 onlinelog
drwxr-x--- 2 oracle oinstall 4096 Apr 29 03:53 datafile
drwxr-xr-x 2 oracle oinstall 4096 Jun  3 03:18 FlashBack



ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/oradata/vihcdbd/VIHCDBD8/FlashBack' scope=both sid='*';


SQL> alter database flashback on;

Database altered.



SQL> select open_mode,log_mode,flashback_on from v$database;

OPEN_MODE            LOG_MODE     FLASHBACK_ON
-------------------- ------------ ------------------
READ WRITE           ARCHIVELOG   YES


 set linesize 300 pagesize 300
 select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                         2.44                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.


on standby 

set linesize 300
SELECT database_role role, name,dbid, db_unique_name, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;

ROLE             NAME            DBID DB_UNIQUE_NAME                 PLATFORM_ID OPEN_MODE            LOG_MODE     FLASHBACK_ON       PROTECTION_MODE      PROTECTION_LEVEL
---------------- --------- ---------- ------------------------------ ----------- -------------------- ------------ ------------------ -------------------- --------------------
PHYSICAL STANDBY VIHCDBD    617390591 VIHCDBD                                 13 MOUNTED              ARCHIVELOG   NO                 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE




SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 4G
recovery_parallelism                 integer     0


mkdir -p /u01/app/oracle/oradata/vihcdbd/VIHCDBD1/FlashBack



ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/oradata/vihcdbd/VIHCDBD1/FlashBack' scope=both sid='*';




SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/oradata/vihcdb
                                                 d/VIHCDBD1/FlashBack
db_recovery_file_dest_size           big integer 4G
recovery_parallelism                 integer     0


alter system set control_files='/u01/app/oracle/oradata/vihcdbd/VIHCDBD1/FlashBack' scope=both sid='*'


SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


 EDIT DATABASE 'vihcdbd' SET STATE='APPLY-OFF'

DGMGRL> EDIT DATABASE 'vihcdbd' SET STATE='APPLY-OFF' ;
Succeeded.


 !ps -ef|grep -i mrp
oracle   21508 21506  0 03:38 pts/0    00:00:00 /bin/bash -c ps -ef|grep -i mrp
oracle   21510 21508  0 03:38 pts/0    00:00:00 grep -i mrp

edit database rastastby set state='APPLY-ON';

DGMGRL> EDIT DATABASE 'vihcdbd' SET STATE='APPLY-ON' ;


[oracle@cloud-ora ~]$ ps -ef|grep -i mrp
oracle   21526     1  0 03:40 ?        00:00:00 ora_mrp0_vihcdbd  <<<<< 
oracle   21536 21433  0 03:40 pts/0    00:00:00 grep -i mrp


for 2 days in minute 

alter system set db_flashback_retention_target = 2880 scope=both sid='*';


 SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG; 



===========================================================================================================

Steps without guaranteed restore point

Create restore point on standby First !!!!!!!!!!!!!!!!!!!!!


SQL> create restore point Anuj_before_upgrade_stdy;
create restore point Anuj_before_upgrade_stdy
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'ANUJ_BEFORE_UPGRADE_STDY'.
ORA-01153: an incompatible media recovery is active


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@cloud-ora ~]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.

DGMGRL> EDIT DATABASE 'vihcdbd' SET STATE='APPLY-OFF' ;
Succeeded.

DGMGRL> exit
[oracle@cloud-ora ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 3 04:10:06 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create restore point Anuj_before_upgrade_stdy;
Restore point created.



[oracle@cloud-ora ~]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> EDIT DATABASE 'vihcdbd' SET STATE='APPLY-ON';
Succeeded.




Check all the restore points from RMAN
RMAN> list restore point all;


RMAN>  list restore point all;

using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
11434592                              03-JUN-18 ANUJ_BEFORE_UPGRADE_PRIM



On standby .. 

set linesize 300
SELECT database_role role, name,dbid, db_unique_name, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;
SQL> SQL>
ROLE             NAME            DBID DB_UNIQUE_NAME                 PLATFORM_ID OPEN_MODE            LOG_MODE     FLASHBACK_ON       PROTECTION_MODE      PROTECTION_LEVEL
---------------- --------- ---------- ------------------------------ ----------- -------------------- ------------ ------------------ -------------------- --------------------
PHYSICAL STANDBY VIHCDBD    617390591 VIHCDBD                                 13 MOUNTED              ARCHIVELOG   YES                MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE


expected error 

SQL> create restore point Anuj_before_upgrade_stdy;
create restore point Anuj_before_upgrade_stdy
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'ANUJ_BEFORE_UPGRADE_STDY'.
ORA-01153: an incompatible media recovery is active



DGMGRL> EDIT DATABASE 'vihcdbd' SET STATE='APPLY-OFF' ;
Succeeded.
DGMGRL> exit
[oracle@cloud-ora ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 3 04:10:06 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create restore point Anuj_before_upgrade_stdy;
Restore point created.


SQL> exit


[oracle@cloud-ora ~]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> EDIT DATABASE 'vihcdbd' SET STATE='APPLY-On';
Succeeded.


Now on primary ..
set linesize 300
SELECT database_role role, name,dbid, db_unique_name, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;SQL> SQL>

ROLE             NAME            DBID DB_UNIQUE_NAME                 PLATFORM_ID OPEN_MODE            LOG_MODE     FLASHBACK_ON       PROTECTION_MODE      PROTECTION_LEVEL
---------------- --------- ---------- ------------------------------ ----------- -------------------- ------------ ------------------ -------------------- --------------------
PRIMARY          VIHCDBD    617390591 VIHCDBD8                                13 READ WRITE           ARCHIVELOG   YES                MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL>
create restore point Anuj_before_upgrade_prim;
SQL>
Restore point created.




[oracle@cloud-ora ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jun 3 04:13:31 2018

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

connected to target database: VIHCDBD (DBID=617390591)

RMAN>  list restore point all;

using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
11434592                              03-JUN-18 ANUJ_BEFORE_UPGRADE_PRIM




SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;  2    3

Table created.

SQL> CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);  2    3    4    5    6    7    8    9

Table created.

SQL> INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES(40,'OPERATIONS','BOSTON');

1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL> commit ;

Commit complete.


On primary ...

create another restore point before upgrade . 

SQL> create restore point Anuj_before_upgrade_prim1;

Restore point created.




set pagesize 200 linesize 300
col scn for 999,999,999,999,999
col incar for 99
col name for a25
col time for a40
col storage_size for 999,999,999,999
col guarantee_flashback_database for a35
select 
      database_incarnation# as incar,
      scn,
      name,
      time,
      storage_size,
      guarantee_flashback_database
from  v$restore_point
order by 4;


INCAR                  SCN NAME                      TIME                                         STORAGE_SIZE GUARANTEE_FLASHBACK_DATABASE
----- -------------------- ------------------------- ---------------------------------------- ---------------- -----------------------------------
    3           11,434,592 ANUJ_BEFORE_UPGRADE_PRIM  03-JUN-18 04.13.05.000000000 AM                         0 NO
    3           11,435,998 ANUJ_BEFORE_UPGRADE_PRIM1 03-JUN-18 04.21.46.000000000 AM                         0 NO


RMAN>  list restore point all;

using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
11434592                              03-JUN-18 ANUJ_BEFORE_UPGRADE_PRIM
11435998                              03-JUN-18 ANUJ_BEFORE_UPGRADE_PRIM1




Updated the salary 
SQL> update emp
  2  set SAL=99999 where ENAME='SCOTT';

1 row updated.

SQL> commit ;

Commit complete.



SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87      99999                    20   <<<<<<< 
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.




SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup mount ;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  2932336 bytes
Variable Size             658506128 bytes
Database Buffers          381681664 bytes
Redo Buffers                5455872 bytes
Database mounted.
SQL> flashback database to restore point ANUJ_BEFORE_UPGRADE_PRIM1 ;

Flashback complete.

SQL>  alter database open resetlogs;

Database altered.

SQL>  select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20  <<<<<<<<<<<<<<<<--- salary reverted  
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.



Now check the standby 


show database vihcdbd statusreport

DGMGRL> show database vihcdbd statusreport ;
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
                   *      ERROR ORA-16700: the standby database has diverged from the primary database
                   *      ERROR ORA-16766: Redo Apply is stopped


Now standby is out of sync 

===============================

Recovey !!!!!!!!!!!!!!


on primary 

SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
         11436000       <<<<< on primary  use this ( scn -2 ) to revert standby i.e.  11435998




On standby .. 

set linesize 300
SELECT database_role role, name,dbid, db_unique_name, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;
SQL> SQL>
ROLE             NAME            DBID DB_UNIQUE_NAME                 PLATFORM_ID OPEN_MODE            LOG_MODE     FLASHBACK_ON       PROTECTION_MODE      PROTECTION_LEVEL
---------------- --------- ---------- ------------------------------ ----------- -------------------- ------------ ------------------ -------------------- --------------------
PHYSICAL STANDBY VIHCDBD    617390591 VIHCDBD                                 13 MOUNTED              ARCHIVELOG   YES                MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE





flashback database to scn 11436000-2 ;

flashback database to scn 11435998 ;


SQL>

flashback database to scn 11435998 ;
Flashback complete.




SQL> !ps -ef|grep -i mrp
oracle   22466 22449  0 04:38 pts/0    00:00:00 /bin/bash -c ps -ef|grep -i mrp
oracle   22468 22466  0 04:38 pts/0    00:00:00 grep -i mrp

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY ;

Database altered.

SQL>  !ps -ef|grep -i mrp
oracle   22474     1  0 04:39 ?        00:00:00 ora_mrp0_vihcdbd
oracle   22481 22449  0 04:39 pts/0    00:00:00 /bin/bash -c ps -ef|grep -i mrp
oracle   22483 22481  0 04:39 pts/0    00:00:00 grep -i mrp




 dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.


Now all good !!!

DGMGRL> show database vihcdbd statusreport ;
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL>

No comments:

Oracle DBA

anuj blog Archive