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
