Pfile & SPfile Recovery Without rman or any Backup

S-1: login as sysdba user and start the database

SQL> startup

ORACLE instance started.

 

Total System Global Area 347340800 bytes

Fixed Size 1336456 bytes

Variable Size 285215608 bytes

Database Buffers 54525952 bytes

Redo Buffers 6262784 bytes

Database mounted.

Database opened.

S-2: Once start the database verify it name and mode

SQL> Select name,open_mode from V$database;

 

NAME             OPEN_MODE

-------------     ------------

ORA12DB               READ WRITE

S-3: check the background dump test location

SQL>show parameter background_dump_dest;

 

NAME                    TYPE     VALUE   

---------              --------  -----------

background_dump_dest    string   /u01/app/oracle/product/12.2.0.1/db_1/rdbms/log

S-4: shut down the database

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

S-5: Go to the spfile location

SQL> !

[oracle@trichydoyen dbs]$ cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/

[oracle@trichydoyen dbs]$ ls

[oracle@trichy dbs]$ ls

0cvamqlf_1_1  c-2343286063-20200918-00  hc_jgso.dat  lkJGSO_PITR_TEST  orapwtest      spfiletest.ora

0dvamqou_1_1  c-2343286063-20200918-01  hc_test.dat  lkTEST            snapcf_test.f

(Note: Here we have to remove spfile , if suppose we had a pfile for that database remove that file also )

[oracle@trichydoyen dbs]$ rm spfiletest.ora

[oracle@trichy dbs]$ ls

0cvamqlf_1_1  c-2343286063-20200918-00  hc_jgso.dat  lkJGSO_PITR_TEST  orapwtest

0dvamqou_1_1  c-2343286063-20200918-01  hc_test.dat  lkTEST            snapcf_test.f

[oracle@trichy dbs]$  

 

S-7: now we are going to login as sysdba from dbs location

[oracle@trichy ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 9 02:02:11 2020

 

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

 

Connected to an idle instance.

 

.S-8: Now trying to start the database it through an error like below

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0.1/db_1/dbs/inittest.ora'

SQL>

s-9: now we are going to background dump test location which we found it before background_dump_test parameter location

SQL> !

[oracle@trichydoyen dbs]$ cd /u01/app/oracle/diag/rdbms/test/test/trace

[oracle@trichydoyen trace ]$ cat alert_test.log

(the cat command will show all actions of database)

S-10: since we don't have pfile and spfile so we 'll get back into the alert log file select system parameter with non-default values

Using parameter settings in server-side spfile /u01/app/oracle/product/12.2.0.1/db_1/dbs/spfiletest.ora

System parameters with non-default values:

  processes                = 300

  resource_limit           = TRUE

  nls_language             = "AMERICAN"

  nls_territory            = "AMERICA"

  sga_target               = 1952M

  control_files            = "/u01/app/oracle/oradata/test/control01.ctl"

  control_files            = "/u01/app/oracle/oradata/test/control02.ctl"

  db_block_size            = 8192

  compatible               = "12.2.0"

  undo_tablespace          = "UNDOTBS1"

  remote_login_passwordfile= "EXCLUSIVE"

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=testXDB)"

  audit_file_dest          = "/u01/app/oracle/admin/test/adump"

  audit_trail              = "DB"

  db_name                  = "test"

  open_cursors             = 300

  pga_aggregate_target     = 1941M

  diagnostic_dest          = "/u01/app/oracle"

S-11: From trace location into go dbs location

[oracle@trichy trace]$ cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/

[oracle@trichy dbs]$ ls

0cvamqlf_1_1  c-2343286063-20200918-00  hc_jgso.dat  lkJGSO_PITR_TEST  orapwtest

0dvamqou_1_1  c-2343286063-20200918-01  hc_test.dat  lkTEST            snapcf_test.f

[oracle@trichy dbs]$

S-12: after went dbs location create a new file in the name of 'initCA.ora'

[oracle@trichy dbs]$ vi inittest.ora

(Note copy and paste line from alert log i.e s-10)

S-13: once we done to create a pfile login from dbs location

[oracle@trichy dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 9 02:12:13 2020

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

Database opened.

SQL>

 

S-14: check the name and mode of the database

SQL> Select name,open_mode from V$database;

NAME   OPEN_MODE

--------- --------------------

TEST   READ WRITE

 

S-15: create spfile into pfile

SQL> create spfile from pfile;

 

File created.

S-16: check the parameter by using which file database parameter instance is working

SQL> show parameter spfile

NAME      TYPE  VALUE

------------------------------------ ----------- ------------------------------

spfile      string

we found our database instance is working pfile s-18: shut down the database because we have spfile so start our database using spfile

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL>!

 [oracle@trichydoyen dbs]$ls

 

[oracle@trichy dbs]$ ls

0cvamqlf_1_1  c-2343286063-20200918-00  hc_jgso.dat  inittest.ora      lkTEST     snapcf_test.f

0dvamqou_1_1  c-2343286063-20200918-01  hc_test.dat  lkJGSO_PITR_TEST  orapwtest  spfiletest.ora

[oracle@trichy dbs]$

(Note : here spfile and pfile have been shown above then start our database and use s-17 )

 

 

 

 

 

 

 

 

How to Restore/Recover a Small Table in a Large Database on Oracle 12c

1.) Create a table for that user

2.) Take a backup – a backup of the table has to exist; it can’t just be in archive logs.

3.) Drop the table

4.) Restore the table

5.) Check that the table has been restored

1.) Create a table for that user

SQL> conn / as sysdba

Connected.

SQL> create table test1 as select * from dba_objects;

Table created.

SQL> select count(*) from test1;

COUNT(*)

----------

72737

SQL> commit;

Commit complete.

2.) Take a backup – a backup of the table has to exist; it can’t just be in archive logs.

[oracle@trichy backup]$ rman

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Sep 25 05:15:38 2020

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

RMAN> connect target /

connected to target database: TEST (DBID=2343286063)

RMAN> backup database plus archivelog;

Starting backup at 25-SEP-20

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=57 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=100 RECID=100 STAMP=1049139606

input archived log thread=1 sequence=101 RECID=101 STAMP=1049151012

input archived log thread=1 sequence=102 RECID=102 STAMP=1049181074

input archived log thread=1 sequence=103 RECID=103 STAMP=1049203820

input archived log thread=1 sequence=104 RECID=104 STAMP=1049225418

Starting backup at 25-SEP-20

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=4 RECID=128 STAMP=1052025765

channel ORA_DISK_1: starting piece 1 at 25-SEP-20

channel ORA_DISK_1: finished piece 1 at 25-SEP-20

piece handle=/u01/app/oracle/product/12.2.0.1/db_1/dbs/0rvb98t5_1_1 tag=TAG20200925T052245 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 25-SEP-20

Starting Control File and SPFILE Autobackup at 25-SEP-20

piece handle=/u01/app/oracle/product/12.2.0.1/db_1/dbs/c-2343286063-20200925-00 comment=NONE

Finished Control File and SPFILE Autobackup at 25-SEP-20

3.) Drop the table 

SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE

-------------------

25/09/2020 05:27:40

SQL> drop table test1;

Table dropped.

SQL> select count(*) from test1;

select count(*) from test1

*

ERROR at line 1:

ORA-00942: table or view does not exist

4.) Restore the table

The following script will do a point in time recovery, but you can also restore to an SCN, or to a sequence number also.

 $ rman target /

 RMAN> recover table sys.test1 until time "to_date('25/09/2020 05:27:40','dd/mm/yyyy hh24:mi:ss')" auxiliary destination '/u01/app/oracle/oradata/inba/backup';

Starting recover at 25-SEP-20

using target database control file instead of recovery catalog current log archived allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=22 device type=DISK 

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time 

List of tablespaces expected to have UNDO segments Tablespace SYSTEM Tablespace UNDOTBS1 

Creating automatic instance, with SID='TEST' initialization parameters used for automatic instance: db_name=CDB2

db_unique_name=TEST_pitr_PDB2_CDB2 compatible=12.1.0.2.0 db_block_size=8192 db_files=200 

diagnostic_dest=/u02 _system_trig_enabled=FALSE sga_target=1000M processes=200 

db_create_file_dest=/u02/CDB2/aux log_archive_dest_1='location=/u02/CDB2/aux' 

enable_pluggable_database=true _clone_one_pdb_recovery=true #No auxiliary parameter file used 

starting up automatic instance CDB2 Oracle instance started Total System Global Area 1048576000 

bytes Fixed Size 2932336 bytes Variable Size 276824464 bytes Database Buffers 763363328 bytes 

Redo Buffers 5455872 bytes Automatic instance created contents of Memory Script: { # set requested 

point in time set until time "to_date('09-25-2020 5:27:40','mm/dd/yyyy hh24:mi:ss')"; # restore the 

controlfile restore clone controlfile; # mount the controlfile sql clone 'alter database mount clone 

database'; # archive current online log sql 'alter system archive log current'; } executing Memory Script 

executing command: SET until clause Starting restore at 25-SEP-20 allocated channel: 

ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=12 device type=DISK channel 

ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.1.0.2/dbs/c-600824249-2020-25-09-01 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.1.0.2/dbs/c-600824249-2020-25-09-01 tag=TAG20202509T201310 

channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u02/CDB2/aux/CDB2/controlfile/o1_mf_d40pzfbo_.ctl 

Finished restore at 25-SEP-20

5.) Check that the table has been restored

SQL> select count(*) from test1;

COUNT(*)

----------

72737

PRACTICE :

Active Database Cloning Using RMAN in Oracle 12C

 RMAN(RECOVERY MANAGER):

                             In oracle,RMAN has the ablility to duplicate or clone a database using backup copies or active database using duplicate command to copy all the data in source database.

 

ACTIVE DATABASE DUPLICATION:

                                                                        In this method,RMAN connects the target to the source database and auxiliary to the auxiliary instance.Auxiliary is the

database instance used in the recovery process to perform work of recovery.RMAN copies the live source database over the network on the auxiliary instance.Here

there are no RMAN backup copies are required .

 

ACTIVE DATABASE DUPLICATION STEPS:

 

step1:First copy the source(dev) database pfile to clonedb database

 

make the changes in the pfile for clone db like dbname,db_file_name_convert,log_file_name_convert

 

Step2:create the password file for the clonedb as well as dev database

 

step3: configure listener and service name

 

  eg:SID_DESC =

      (GLOBAL_DBNAME = clone)

      (ORACLE_HOME = D:\oracle\product\11.1.0\db_1)

      (SID_NAME = clone)

    )

 

step4: now add the entries in tnsnames.ora

 

CLONE =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = clone)

    )

  )

 

step5: Now connect with Duplicate Database

 

   eg:

           export ORACLE_SID=clonedb

           sqplus / as sysdba

            startup nomount

 

step6:Now duplicate the target database

 

 

 eg:rman target sys/Pass#123@dev

 

connect auxiliary sys/Pass#123@clonedb

 

duplicate database to 'clonedb' from active database nofilenamecheck;

                                      (or)

duplicate target database to clonedb nofilenamecheck;

 

 

step7:check the duplicate clonedb database

 

sqlplus sys/Pass#123@clonedb nofilenamecheck;

 

PRACTICAL:

How to Add and drop Disks to ASM Disk Group in Oracle

 

I will explain How to Add Disks to ASM Disk Group in Oracle with ALTER DISKGROUP DATA ADD DISK Command in this post.

You can add disks to ASM Disk group as follows. In this Example my Disk group name DATA, you can change it according to your Database.

SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASMDATA08'; Diskgroup altered.

SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASMDATA09'; Diskgroup altered.

You can specify the logical disk name and add disk to ASM Disk group as follows.

ALTER DISKGROUP DATA ADD DISK '/dev/mapper/asmtsk1' NAME DATA_0002 SIZE 102399 M REBALANCE POWER 10;

You can list the All ASM Disks and their state using the v$asm_disk view as follows.

select DISK_NUMBER,name ,PATH, MOUNT_DATE from v$asm_disk;

You can list the All ASM Disks statistics using the v$asm_disk_stat view as follows.

select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat;

You can drop disks from Disk group as follows.

Firstly list the disks and their name.

select DISK_NUMBER,name ,PATH, MOUNT_DATE from v$asm_disk;

Then drop the related disk.

alter diskgroup DATA drop disk DATA_0032;

You can check the drop and add disk operation status using the v$asm_operation view as follows.

select * from v$asm_operation;

You can change Disk group Rebalance power as follows. Thus, You can speed up this task and Disk group will perform rebalance operation in parallel.

ALTER DISKGROUP DATA REBALANCE POWER 8 NOWAIT;

Rename Asm DiskGroup Name On Oracle 12c

 

Step1:Startup asm with created asm pfile.

startup pfile=$ORACLE_HOME/dbs/init+ASM.ora;

Step2:Dismount all disks. 

alter diskgroup all dismount;

Step3:First operation , rename diskgroup name as metadata.

 renamedg phase=one dgname=DATA newdgname=DATA1

asm_diskstring='/dev/oracleasm/disks/DATA_VOL01' config=/tmp/disk.conf verbose=true keep_voting_files=true renamedg phase=two dgname=DATA newdgname=DATA1 config=/tmp/disk.conf verbose=true keep_voting_files=true

renamedg phase=one dgname=RECO newdgname=RECO1 asm_diskstring='/dev/oracleasm/disks/RECO_VOL01' config=/tmp/disk2.conf verbose=true keep_voting_files=true renamedg phase=two dgname=RECO

newdgname=RECO1 config=/tmp/disk2.conf verbose=true keep_voting_files=true

Step4:Move disks in diskgroup

alter diskgroup DATA1 mount restricted; alter diskgroup DATA1 rename disks all; alter diskgroup RECO1 mount restricted; alter diskgroup RECO1 rename disks all;

Step5:Dismount all disks. alter diskgroup all dismount;

Step6:Second operation , rename diskgroup name as metadata. renamedg phase=one dgname=DATA1 newdgname=RECO asm_diskstring='/dev/oracleasm/disks/DATA_VOL01' config=/tmp/disk3.conf verbose=true keep_voting_files=true renamedg phase=two dgname=DATA1 newdgname=RECO config=/tmp/disk3.conf verbose=true keep_voting_files=true

renamedg phase=one dgname=RECO1 newdgname=DATA asm_diskstring='/dev/oracleasm/disks/RECO_VOL01' config=/tmp/disk4.conf verbose=true keep_voting_files=true renamedg phase=two dgname=RECO1 newdgname=DATA config=/tmp/disk4.conf verbose=true keep_voting_files=true

Step7:Move disks in diskgroup .

alter diskgroup DATA mount restricted;

alter diskgroup DATA rename disks all;

alter diskgroup RECO mount restricted;

alter diskgroup RECO rename disks all;

Step8:You can read disk with kfed and kfod . 

kfod asm_diskstring='/dev/oracleasm/disks/*' disks=all kfed read /dev/oracleasm/disks/DATA_VOL01 kfed read /dev/oracleasm/disks/RECO_VOL01

How to Monitor RMAN Backup and Long Operations in Oracle

If you have big database about more than 1 TB, then your backup and export operations are taking long times.

If you start long operations such as RMAN Backup and Datapump Export in Oracle database, probably you want to see progress of these operations.

You can see and monitor RMAN Backup progress with following script.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'

AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK order by 6 desc;

Step1:To check any RMAN Backup progress.

SQL> startup

ORACLE instance started.

Total System Global Area 2046820352 bytes

Fixed Size 8622480 bytes

Variable Size 587206256 bytes

Database Buffers 1442840576 bytes

Redo Buffers 8151040 bytes

Database mounted.

Database opened.

SQL>

SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'

AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK order by 6 desc; 2 3 4 5 6 7

no rows selected

Step2:Now the RMAN Backup progressing and check process details.

RMAN> connect target /

connected to target database: TEST (DBID=2343286063)

RMAN> backup database plus archivelog;

Starting backup at 27-SEP-20

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=66 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=108 RECID=108 STAMP=1049353353

input archived log thread=1 sequence=109 RECID=109 STAMP=1049407361

SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'

AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK order by 6 desc; 2 3 4 5 6 7

SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE

---------- ---------- ---------- ---------- ---------- ----------

66 15044 1 494584 4727734 10.46

You can monitor Datapump Export ,Import and other long operations with following script.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK order by 6 desc;

How to Restore Dropped Tables from Recycle Bin in Oracle

 

                            

Oracle database has the recycle bin which is a data dictionary table and contains information about dropped objects. Until you don’t use purge option, all dropped tables and its objects such as indexes, constraints and etc are not removed and still occupy space.

Actually the recycle bin is any life buoy for the database administrators, because sometime we can restore or flash back the dropped tables from recycle bin, if these objects are still in the recycle bin.

You can check if the recycle bin is on or off as follows.

SQL> show parameter recyclebin; NAME TYPE VALUE ------------------------------------ ----------- recyclebin string on

To disable the recycle bin, you can disable the recycle bin both for session and system level as follows.

ALTER SESSION SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF;

You can enable the recycle bin both for session and system level as follows.

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = ON;

Now, lets go to make an example and recover a dropped table from recycle bin as follows.

SQL> show parameter recyclebin NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on

Following table has 53 rows.

SQL> select count(*) from msdeveci.test; COUNT(*) ---------- 53

Lets drop it.

SQL> drop table msdeveci.test; Table dropped.

Check if it exists.

SQL> select * from msdeveci.test; select * from msdeveci.test * ERROR at line 1: ORA-00942: table or view does not exist

Now lets go to flashback table from recycle bin as follows.

SQL> flashback table msdeveci.test to before drop; Flashback complete.

Check if table exists or not.

SQL> select count(*) from msdeveci.test; COUNT(*) ---------- 53

You can list the objects and droptime from recycle bin as follows.

SQL> select object_name, droptime from dba_recyclebin; OBJECT_NAME DROPTIME ------------------------------ -------------------

BIN$4YkbXtBoAdngQwEAAH8n+g==$0 2020-09-20:10:49:57 BIN$4YkbXtBpAdngQwEAAH8n+g==$0 2020-09-20:10:49:57 BIN$4YkbXtBqAdngQwEAAH8n+g==$0 2020-09-20:10:49:57 BIN$4YkbXtBrAdngQwEAAH8n+g==$0 2020-09-20:10:49:57 BIN$4YkbXtBsAdngQwEAAH8n+g==$0 2020-09-20:10:49:57 BIN$4YkcLLzGAd3gQwEAAH/TdQ==$0 2020-09-20:10:50:11 BIN$4YkcLLzHAd3gQwEAAH/TdQ==$0 2020-09-20:10:50:11 BIN$4YkcttMiAd/gQwEAAH8VjQ==$0 2020-00-20:10:50:20 BIN$4YkcttMjAd/gQwEAAH8VjQ==$0 2020-09-20:10:50:20 BIN$4YkdYIHCAeHgQwEAAH87MA==$0 2020-09-20:10:50:31 BIN$4YkdYIHDAeHgQwEAAH87MA==$0 2020-09-20:10:50:31

RECYCLEBIN

You can purge recycle bin as follows. purge recyclebin will remove all objects from the user’s recycle bin and release all space associated with objects in the recycle bin

SQL> purge recyclebin; Recyclebin purged.

Purge DBA_RECYCLEBIN

Purge dba_recyclebin needs sysdba privilige and it removes all objects from the system-wide recycle bin, and is equivalent to purging the recycle bin of every user

SQL> purge dba_recyclebin;

 DBA Recyclebin purged.

 SQL>Tablespace Purge

purge tablespace option purges all the objects residing in the specified tablespace from the recycle bin.

SQL> purge tablespace Users;

 Tablespace purged. 

You can purge any table as follows.

SQL> PURGE TABLE MSDEVECI.TEST; 

Table purged.

You can purge any dropped table as follows. 

SQL> purge table "BIN$cxP2b6/dEnrgVQBAAAAADQ==$1";

 Table purged.

 How to Flashback Database to Guaranteed Restore Point

Flashback must be turned on in order to be able to use Guaranteed Restore Point. To enable Flashback, we need to use the FRA parameter like following.

SQL> show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/recovery_area db_recovery_file_dest_size big integer 2022043K

Flashback is enabled as follows. This can be done in case in mount mode.

SQL> alter database flashback on;

 Database altered.

You can create a guaranteed restore point as follows.

SQL> create restore point before_open guarantee flashback database;

 Restore point created.

When we want to flash back to any point of database, we will use this restore point.

Opened the database in Read write mode, then perform some database operations for Deployment purpose, test operations or Disaster purpose SQL> create user mehmet identified by salih; User created. SQL> grant dba to mehmet; Grant succeeded.

If Disaster tests or any other tests are completed and you want to flash back database into that point again, then you should perform the following steps.

Open it in Mount mode

SQL> startup mount force; ORACLE instance started. Total System Global Area 534462464 bytes Fixed Size 2230072 bytes Variable Size 163580104 bytes Database Buffers 360710144 bytes Redo Buffers 7942144 bytes Database mounted.

Firstly list the restore points as follows.

select * from v$restore_point;

You can check how long this Flashback operation work, you can see its estimated time as follows.

set pages 100 set line 200 column message format a50 select sid,message from v$session_longops where sofar <> totalwork; set linesize 400 col DB_DATA for 999999999999 col FLASHBACK_SIZE for 999999999999 SELECT TO_CHAR(A.BEGIN_TIME,'DDMMYY HH24:MI') "FLASH BEGIN",TO_CHAR(A.END_TIME,'DDMMYY HH24:MI') "FLASH END",A.DB_DATA, B.FLASHBACK_SIZE, (A.DB_DATA/B.FLASHBACK_SIZE)*100 "% COMPLETE" FROM V$FLASHBACK_DATABASE_STAT A, V$FLASHBACK_DATABASE_LOG B;

Flash back database to related restore point as follows.

SQL> flashback database to restore point before_test_29032020; Flashback complete.

Now open database in open resetlogs mode.

SQL> alter database open resetlogs;

You can drop the restore point as follows.

SQL> DROP RESTORE POINT before_test_29032020;