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

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;

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

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

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

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;

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

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

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

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> 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> 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

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>

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Sunday 3 June 2018

Oracle Flashback for standby database

Oracle Flashback for standby database ...

Oracle DBA

anuj blog Archive