Search This Blog

Total Pageviews

Saturday, 2 June 2018

Oracle create guaranteed restore point 11g/12c ...

Oracle create guaranteed restore point 11g/12c  ... 



Check DiskGroup Name ...

select listagg (name, ' | ') within group (order by name) "     DiskGroupName"  from v$asm_diskgroup;

     DiskGroupName
--------------------------------------------------------------------------------
DATA | ALOG



select name,database_role,open_mode,flashback_on,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON       LOG_MODE
--------- ---------------- -------------------- ------------------ ------------
  RAC     PRIMARY          READ WRITE           NO                 ARCHIVELOG


show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
remote_recovery_file_dest            string


ALTER SYSTEM set db_recovery_file_dest_size=40G scope=both sid='*' ; 
ALTER SYSTEM SET db_recovery_file_dest='+LOGS' sid='*';

ALTER SYSTEM set db_recovery_file_dest_size=40G scope=both sid='*' ; 
System altered.


SQL> ALTER SYSTEM SET db_recovery_file_dest='+LOGS' sid='*';
System altered.

CREATE RESTORE POINT test_anuj GUARANTEE FLASHBACK DATABASE;


CREATE RESTORE POINT test_anuj GUARANTEE FLASHBACK DATABASE;SQL>
Restore point created.



SQL> 
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                          .98                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.



SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY    <<<<<<<<<<if FLASHBACK_ON - NO 



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          263,845,411 TEST_ANUJ                 01-JUN-18 02.07.57.000000000 PM               209,715,200 YES

alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
col name for a50
select * from v$flashback_database_logfile;

NAME                                                     LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME       TYPE          CON_ID
-------------------------------------------------- ---------- ---------- ---------- ---------- ------------- ---------------- --------- ----------
+LOGS/IRAC/FLASHBACK/log_1.678.977666877                   1          2          1  209715200     263845409 01-06-2018 14:07 NORMAL             0
+LOGS/IRAC/FLASHBACK/log_2.677.977666883                   2          2          1  209715200             0                  RESERVED           0



SQL> create table test_anuj as select * from scott.emp ;

Table created.


CREATE RESTORE POINT test_anuj1 GUARANTEE FLASHBACK DATABASE;

CREATE RESTORE POINT test_anuj1 GUARANTEE FLASHBACK DATABASE;SQL>
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          263,845,411 TEST_ANUJ                 01-JUN-18 02.07.57.000000000 PM                         0 YES
    3          263,846,069 TEST_ANUJ1                01-JUN-18 02.16.28.000000000 PM               209,715,200 YES


PROMPT How Far Back Can We Flashback To (Time)?
PROMPT


select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time" from v$flashback_database_log;


PROMPT

PROMPT How Far Back Can We Flashback To (SCN)?

PROMPT



col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;

PROMPT
PROMPT Flashback Area Usage
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;






PROMPT

col round(space_limit/1048576) heading "space allocated (mb)" format 999999
col round(space_used/1048576)  heading "space used (mb)"      format 99999
col name                       heading "flashback location"   format a50
select name, round(space_limit/1048576),round(space_used/1048576) from v$recovery_file_dest;



create table test_anuj1 as select * from scott.emp ;

SQL> create table test_anuj1 as select * from scott.emp ;
Table created.




SQL>startup mount;
SQL> flashback database to restore point TEST_ANUJ1;

Flashback complete.


SQL> alter database open resetlogs;

Database altered.



SQL> select * from test_anuj1;    <<<< this table gone 
select * from test_anuj1
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from test_anuj;

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



Via Rman ...

RUN {
        STARTUP MOUNT;
        FLASHBACK DATABASE TO RESTORE POINT 'TEST_ANUJ';
        ALTER DATABASE OPEN RESETLOGS;
        SHUTDOWN IMMEDIATE;
}



RMAN>

RUN {
        STARTUP MOUNT;
        FLASHBACK DATABASE TO RESTORE POINT 'TEST_ANUJ';
        ALTER DATABASE OPEN RESETLOGS;
        SHUTDOWN IMMEDIATE;
}


RMAN>
RMAN> 

Oracle instance started
database mounted

Total System Global Area    4294967296 bytes

Fixed Size                     8628936 bytes
Variable Size               1577059640 bytes
Database Buffers            2701131776 bytes
Redo Buffers                   8146944 bytes

Starting flashback at 01-JUN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=372 instance=ibrac2 device type=DISK


starting media recovery

archived log for thread 2 with sequence 440 is already on disk as file +DATA/IBRAC/ARCHIVELOG/2018_06_01/thread_2_seq_440.688.977670083
media recovery complete, elapsed time: 00:00:01
Finished flashback at 01-JUN-18

Statement processed

database closed
database dismounted
Oracle instance shut down



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          263,845,411 TEST_ANUJ                 01-JUN-18 02.07.57.000000000 PM                         0 YES
    3          263,846,069 TEST_ANUJ1                01-JUN-18 02.16.28.000000000 PM               209,715,200 YES




SQL> drop restore point TEST_ANUJ1;

Restore point dropped.



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          263,845,411 TEST_ANUJ                 01-JUN-18 02.07.57.000000000 PM               209,715,200 YES



col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999
col round(space_used/1048576) heading "Space Used (MB)" format 99999
col name Heading "Flashback Location" format a50

select name, round(space_limit/1048576),round(space_used/1048576) from v$RECOVERY_FILE_DEST;

Flashback Location                                 Space Allocated (MB) Space Used (MB)
-------------------------------------------------- -------------------- ---------------
+LOGS                                                             40960             402



PROMPT How Far Back Can We Flashback To (Time)?
PROMPT

select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time" from v$flashback_database_log;

SQL> How Far Back Can We Flashback To (Time)?

Oldest Flashback Time
-----------------------------
01-jun-2018 14:08:03

col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;

       OLDEST_FLASHBACK_SCN
---------------------------
                  263845411

1 comment:

Anuj Singh said...




select current_scn, flashback_on from v$database;




Prompt ===================================
prompt Show the flash recovery area usage
Prompt ===================================
select * from v$flash_recovery_area_usage;




alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
set linesize 300 pagesize 200 trimspool on
col name format a35
col time format a35
col guaranteed format a10
col "oldest flback SCN" format 9999999999999
col SCN format 9999999999999

prompt =================================================
prompt * estimated flashback log size
prompt * retention target
prompt * current accumulated size of all flashback logs
prompt =================================================
select estimated_flashback_size/1024/1024 "Estimated Flbacklog Size mb",
retention_target/60 "Hours of flback logs",
flashback_size/1024/1024 "Current Flbacklog Size mb"
from v$flashback_database_log
/



prompt ===============================================
Prompt How far back can the database be flashed back?
prompt ===============================================
select oldest_flashback_scn "oldest flback SCN",
oldest_flashback_time "oldest flback time"
from v$flashback_database_log
/



prompt =================================================
prompt show the restore points created the last 2 weeks
prompt =================================================
SELECT NAME,
SCN,
TIME,
DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE "guaranteed",
STORAGE_SIZE
FROM V$RESTORE_POINT
where time >= SYSDATE-14;


Prompt ======================================
prompt I/O information for flashback logging
Prompt ======================================
select begin_time "Begin time",
end_time "End time",
round(flashback_data/1024/1024) "MB of flbk data written",
round(redo_data/1024/1024) "MB of redo data"
from v$flashback_database_stat
order by begin_time asc;


Oracle DBA

anuj blog Archive