FLASHBACK RAC DATABASE WITHOUT STANDBY DATABASE


RAC Database -> Flashback to Different Restore Points

Precheck
=============
SQL>  select name,time from v$restore_point;
NAME                                               TIME
---------------------------------------------------------------------------
BEFORE_UPGRADE                21-FEB-18 04.37.05.000000000 PM

Flashback is off with guarantee flashback database

SQL> select log_mode,flashback_on from v$database;

LOG_MODE                             FLASHBACK_ON
------------------------------------ ------------------------------------------------------
ARCHIVELOG                           RESTORE POINT ONLY

I am going to create a restore point before changing further  

Create a restore point RP02
========================
SQL> create restore point RP02 guarantee flashback database;
Restore point created.

Doing some change to the database [Create a table test03 – After RP02 and before RP03]

SQL> create table test03 (col1 number) tablespace users;
Table created.

SQL> insert into test03 values ('03');
1 row created.

SQL> commit;
Commit complete.

Create another restore point RP03
==============================
SQL> create restore point RP03 guarantee flashback database;
Restore point created.

[Create a table test04 – After RP03 ]

SQL>  create table test04 (col1 number) tablespace users;
Table created.

SQL>  insert into test03 values ('04');
1 row created.

SQL> commit;  
Commit complete.


Stop both instance s and start only one instance in mount mode

oracle@racdb01:DMON11:/software/12cUPGRADE/DMON1
$ srvctl status database -d DMON1_01
Instance DMON11 is running on node racdb01
Instance DMON12 is running on node racdb02
oracle@racdb01:DMON11:/software/12cUPGRADE/DMON1
$ srvctl stop database -d DMON1_01
oracle@racdb01:DMON11:/software/12cUPGRADE/DMON1
$ srvctl start instance -i DMON11 -d DMON1_01 -o mount
oracle@racdb01:DMON11:/software/12cUPGRADE/DMON1

Connect using sqlplus and flashback database to restore point RP03
$ sq

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 21 18:59:41 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME            STATUS
------------------------------------
DMON11          MOUNTED

Do flashback
SQL> flashback database to restore point RP03;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> host
aliases:+ASM1 DMON11



Start database using srvctl

oracle@racdb01:DMON11:/software/12cUPGRADE/DMON1
$ srvctl stop database -d DMON1_01
oracle@racdb01:DMON11:/software/12cUPGRADE/DMON1
$ srvctl start database -d DMON1_01
oracle@racdb01:DMON11:/software/12cUPGRADE/DMON1

Connect using sqlplus and flashback database to restore point RP02

$ sq

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 21 19:03:20 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from test03;

  COUNT(*)
----------
         1
SQL> select count(*) from test04;
select count(*) from test04
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

So correct – there were test03 table exist but not test04.

NOW Going to flashback to restore point RP02 before table test02 creation

Stop both instance s and start only one instance in mount mode

$ srvctl stop database -d DMON1_01
oracle@racdb01:DMON11:/software/12cUPGRADE/DMON1
$  srvctl start instance -i DMON11 -d DMON1_01 -o mount
oracle@racdb01:DMON11:/software/12cUPGRADE/DMON1
$ sq
SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 21 19:06:01 2018

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME                                    STATUS
------------------------------------------------ ------------------------------------
DMON11                                           MOUNTED

SQL> flashback database to restore point RP02;

Flashback complete.
SQL>  alter database open resetlogs;

Database altered.
SQL> select count(*) from test03;
select count(*) from test03
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

So restore to different restore point is fine.

$ srvctl stop database -d DMON1_01
oracle@racdb01:DMON11:/software/12cUPGRADE/DMON1
$ srvctl start database -d DMON1_01
oracle@racdb01:DMON11:/software/12cUPGRADE/DMON1
$ srvctl status database -d DMON1_01
Instance DMON11 is running on node racdb01
Instance DMON12 is running on node racdb02

SQL>  select name,time from v$restore_point;

NAME                                                                    TIME
---------------------------------------------------------------------------
BEFORE_UPGRADE                                    21-FEB-18 04.37.05.000000000 PM
RP02                                                          21-FEB-18 06.53.32.000000000 PM
RP03                                                          21-FEB-18 06.55.37.000000000 PM


Dropping Restore points
SQL> drop restore point RP03 ;
Restore point dropped.

SQL> drop restore point RP02;
Restore point dropped.


Comments

Post a Comment

Popular posts from this blog

Oracle DataGuard – How to resize Redo Log and Standby Redo Log

Oracle DataGuard – How to resize Redo Log and Standby Redo Log set linesize 200 col dest_name for a30 select DEST_NAME,STATUS,PROTECTION_MODE,ARCHIVED_SEQ#,APPLIED_SEQ# from v$archive_dest_status; On Standby server - make file management parameter to manual if not: show parameter standby; alter system set standby_file_management=manual; ================== +     On Primary             + ==================  SQL> select group#,thread#,status from v$log; col member for a60 set linesize 200 select member,group#,status from v$logfile; SQL> select group#,thread#,status from v$log;     GROUP#    THREAD# STATUS ---------- ---------- ----------------          1          1 ACTIVE          2       ...
Read more

Exadata - Smart Scan Testing

Image
Exadata - SMART SCAN testing -------------------------------------------- Samrt scan works with cell offloading which relate to offloaded the sql processing from the database layer to the cell (storage) layer. Smart scan is the access mechanism to offload data from storage layer. For example passing only required data blocks after applying predicate filters at the cell layer instead of sending every possible blocks from cell server to database server.                           Figure 1: Traditional Database Storage I/O and SQL Processing Model   Process Steps: ------------------- 1.    The client issues a SELECT statement . 2.    The database kernel maps this request to the database files and extents which contain the table data. 3.    The database kernel issues the I/Os to read all the table bloc...
Read more

Exadata Flash Cache - Management

Total size of Flash CellCLI>  list flashcache attributes effectiveCacheSize          5.82122802734375T Flashcache Mode:  [root@Cellc01-adm ~]# su - celladmin cellcli CellCLI>  list cell attributes flashCacheMode,status          WriteThrough    online > list celldisk where disktype=flashdisk > list physicaldisk FLASH_1_1 detail List PhysicalDisk for FlashCache: CellCLI> list physicaldisk attributes name, disktype, makemodel, physicalrpm, physicalport, status      8:0           HardDisk      "HGST    H7240AS60SUN4.0T"                              normal      8:1  ...
Read more