FLASHBACK DATABASE

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON
--------- ------------------
hello     YES

SQL>  select name,time,guarantee_flashback_database from v$restore_point;

-- List Flashback Log Details.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
COLUMN "Log NO" FOR 9,999
COLUMN "Thread No" FOR 99
COLUMN "Seq No" FOR 99
COLUMN name FOR A50
COLUMN "Size(GB)" FOR 999,999
COLUMN "First Chg No" FOR 999,999,999,999,999,999
 
ALTER SESSION
     SET nls_date_format='DD MON YYYY hh24:mi:ss'
/
 
SELECT
     log# as "Log No", 
     thread# as "Thread No",
     sequence# as "Seq No",
     name,
     bytes/1024/1024/1024 as "Size(GB)",
     first_change# as "First Chg No",
     first_time
FROM  
   v$flashback_database_logfile
/

FLASHBACK DATABASE to a Restore Point

Ref:

http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta023.htm

SQL> CREATE RESTORE POINT before_update GUARANTEE FLASHBACK DATABASE;

The bulk update fails, leaving the database with extensive corrupted data. You start an RMAN session, connect to the target database and recovery catalog, and list the guaranteed restore points:

RMAN> LIST RESTORE POINT ALL;

SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
412742                     GUARANTEED 15-FEB-07 BEFORE_UPDATE

You mount the database, flash back the database to the restore point (sample output included), and then open the database with the RESETLOGS option:

RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE';
Starting flashback at 15-FEB-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=104 device type=DISK

starting media recovery
archived log for thread 1 with sequence 34 is already on disk as file /disk2/oracle/oradata/prod/arch/archive1_34_614598462.dbf
media recovery complete, elapsed time: 00:00:01
Finished flashback at 15-FEB-07

RMAN> ALTER DATABASE OPEN RESETLOGS;
Note.305648.1 What is a Flash Recovery Area and how to configure it ?
Note.369759.1 FLASH RECOVERY AREA and FLASHBACK database
Note.833663.1 Flash Recovery Area - FAQ
Note.560133.1 Benefits Of Using Flash Recovery Area
Note.315098.1 How is the space pressure managed in the Flash Recovery Area - An Example.
Note.305812.1 Flash Recovery area - Space management Warning & Alerts
Note.829755.1 Space issue in Flash Recovery Area( FRA )
Note.305810.1 Configuring file creation in Flash recovery area and order of Precedence
Note.305651.1 How to change Flash Recovery Area to a new location ?
Note.762934.1 Flash Recovery Area Sizing

The articles listed below help you in understanding the concepts,configuration,usage and known issues of the flashback database feature:

Note.249319.1 Configure flashback database
Note.565535.1 Flashback Database Best Practices & Performance
Note.834824.1 Recovering Dropped User using Flashback Database
Note.330535.1 Restore Points in Oracle10g Release2
Note.369759.1 FLASH RECOVERY AREA and FLASHBACK database
Note.761126.1 CALCULATE THE VOLUME OF FLASHBACK LOG GENERATION
Note.369755.1 Flashback Logs-Space management
Note.427222.1 FLASHBACK LOGS NOT PURGED WHEN ARCHIVER DOESN'T HAVE DISK SPACE
Note.454768.1 Can you use flashback database if a period of noarchivelog exists
Note.566647.1 Guaranteed Restore Point with Flashback Database disabled generates too many flashback logs
Note.833584.1 How To Disable Flashback Database Without A Bounce
Note.828410.1 High "Flashback buf free by RVWR" waits
Note.386262.1 Cloning database using EM Fails If flashback is ON
Note.342764.1 Flashback 'ON' fails with ORA-38706 and ORA-38714
Note.829254.1 Database Crashed With ORA-19815 ORA-19809 ORA-16038
Note.847090.1 Flashback Database Failed With ORA-19554 and ORA-27001
Note.554445.1 ORA-26040 FLASHBACK DATABASE WITH NOLOGGING OBJECTS/ACTIVITIES RESULTS IN CORRUPTION
Note.444389.1 Querying V$Flashback_Database_Log Or Flashing Back Database to Before Resetlogs Triggers ORA-07445 [krfbDoConvertPlugin]
Note.982104.1 Workaround for Flashback Database fails with ORA-38753 ORA-01110

Create the Database from RMAN backup of the TEST database until time

Restore database DEMO from Tape full backup of the TEST database files

• Copy the FULL  BACKUP files from Tape to disk /u02/backupsets/TEST_RESTORE
• Drop the database DEMO from HOST if it already present
• Start the database in no mount
• Identify the SCN or date from TEST list backup
• Recover the database until  SCN  using Duplicate command

Identify the Archive log sequence numbers generated at end of the backup
And find the LOW  SCN 42612597056  and restore until LOW SCN
Example (Red to consider)
   List of Archived Logs in backup set 9078
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    6802    42612597056 Jun 01 2012 20:17:10 42612597205 Jun 01 2012 20:17:32
  1    6803    42612597205 Jun 01 2012 20:17:32 42623802321 Jun 02 2012 00:16:00
  1    6804    42623802321 Jun 02 2012 00:16:00 42634272350 Jun 02 2012 06:05:24
  1    6805    42634272350 Jun 02 2012 06:05:24 42645392744 Jun 02 2012 18:00:0

Or you can put any SCN number below 42612597056 numbers ( see  the output)

  List of Archived Logs in backup set 9071
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    6801    42607563494 Jun 01 2012 20:00:20 42612597056 Jun 01 2012 20:17:10
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
9072    3.42M      DISK        00:00:02     Jun 01 2012 20:17:19
        BP Key: 10128   Status: AVAILABLE  Compressed: NO  Tag: TAG20120601T201716
        Piece Name: +FRA/TEST/backupset/2012_06_01/annnf0_tag20120601t201716_0.4826.784844239
 
List of Archived Logs in backup set 9072
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  2    5541    42607563498 Jun 01 2012 20:00:22 42612597059 Jun 01 2012 20:17:11
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
9074    94.50K     DISK        00:00:07     Jun 01 2012 20:17:42
        BP Key: 10130   Status: AVAILABLE  Compressed: NO  Tag: TAG20120601T201734
        Piece Name: +FRA/TEST/backupset/2012_06_01/annnf0_tag20120601t201734_0.4992.784844263
  List of Archived Logs in backup set 9074
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  2    5542    42612597059 Jun 01 2012 20:17:11 42612597207 Jun 01 2012 20:17:33
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
9075    16.00K     DISK        00:00:07     Jun 01 2012 20:17:42
        BP Key: 10131   Status: AVAILABLE  Compressed: NO  Tag: TAG20120601T201734
        Piece Name: +FRA/TEST/backupset/2012_06_01/annnf0_tag20120601t201734_0.3649.784844263
  List of Archived Logs in backup set 9075
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    6802    42612597056 Jun 01 2012 20:17:10 42612597205 Jun 01 2012 20:17:32

Restore works if put Archived Logs  Thrd seq start from 5541 to 5542 
and  6802

   List of Archived Logs in backup set 9078
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    6802    42612597056 Jun 01 2012 20:17:10 42612597205 Jun 01 2012 20:17:32
  1    6803    42612597205 Jun 01 2012 20:17:32 42623802321 Jun 02 2012 00:16:00
  1    6804    42623802321 Jun 02 2012 00:16:00 42634272350 Jun 02 2012 06:05:24
  1    6805    42634272350 Jun 02 2012 06:05:24 42645392744 Jun 02 2012 18:00:0
Or   we can run the query below and identify the SCN number

 alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
col next_change# format 999999999999999;
col first_change# format 999999999999999;
  select sequence#, to_date(first_time,'DD-MON-YYYY HH24:MI:SS') first_time,
  first_change#,    to_date(next_time,'DD-MON-YYYY HH24:MI:SS') next_time,
 next_change# from v$archived_log  where completion_time between to_date('JUN-01-2012','MON-DD-YYYY') and Sysdate

Duplicate script
--------------------
#!/usr/bin/ksh
export ORAENV_ASK='NO'
export ORACLE_SID=TEST1
/usr/local/bin/oraenv
export NLS_LANG=American_america.us7ascii;
export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS";
$ORACLE_HOME/bin/rman target / nocatalog log=/tmp/duplicate_tape_DEMO.log <<EOF
connect auxiliary sys/password@DEMO;
run
{
allocate auxiliary channel aux1 device type disk;
set until SCN 42612597059;
duplicate target database to "DEMO" pfile='/u01/app/xxxxx/product/10.2.0/db_1/dbs/initDEMO.ora' NOFILENAMECHECK;
}
EOF
mailx -s "TEST_Tape to DEMO duplication result" "email id" < /tmp/duplicate_tape_DEMO.log

Restore the database from tape backup of the Database

Restore the database from tape backup of the Database

Suppose the entire server is lost,
we have the  tape copy of the backup of the database , which includes RMAN backup pieces and control file backup
Assuming that were the case, we would have to rebuild the entire server, reinstall the OS and the Oracle Software, then restore the backup of the database from tape. . If the Archives and online redo are lost, the recovered database will not include all transactions,
but it will be current up to the last backup.
Steps Required
1.copy the tape backup of the database to disk
2.create the init.ora file
3.create the password file
 4.start the instance in nomount
5.restore the spfile from autobackup spfile backup piece
6.shutdown immediate
7.startup nomount
 8.RESTORE CONTROLFILE FROM AUTOBACKUP;
9.alter database mount;
10.catalog all the backup piece into RMAN
11.restore database
12.recover database;
13.alter database open resetlogs;
14. check the status of listener and if any connection issues then workout and fix the problem

Detailed Description of Steps
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost Stasdb_database]$ . oraenv
ORACLE_SID = [statsdb] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@localhost Stasdb_database]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 13 15:49:57 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target /
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area     893562880 bytes
Fixed Size                     2218512 bytes
Variable Size                243271152 bytes
Database Buffers             641728512 bytes
Redo Buffers                   6344704 bytes
RMAN> RESTORE SPFILE FROM '/u02/app/Stasdb_database/c-782188580-20120613-02';
Starting restore at 13-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u02/app/Stasdb_database/c-782188580-20120613-02
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 13-JUN-12
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area     893562880 bytes
Fixed Size                     2218512 bytes
Variable Size                243271152 bytes
Database Buffers             641728512 bytes
Redo Buffers                   6344704 bytes
RMAN> RESTORE CONTROLFILE FROM '/u02/app/Stasdb_database/c-782188580-20120613-02';
Starting restore at 13-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/statsdb/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/statsdb/control02.ctl
Finished restore at 13-JUN-12
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> catalog start with '/u01/app/oracle/flash_recovery_area/statsdb';
Starting implicit crosscheck backup at 13-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
Crosschecked 100 objects
Finished implicit crosscheck backup at 13-JUN-12
Starting implicit crosscheck copy at 13-JUN-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13-JUN-12
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/app/oracle/flash_recovery_area/statsdb
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hgndeia5_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hgndeia5_1_1
RMAN> restore database;
Starting restore at 13-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/statsdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/statsdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/statsdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/statsdb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/statsdb/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/statsdb/tripointmonitor01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1 tag=TAG20120613T144216
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:56
Finished restore at 13-JUN-12
RMAN> recover database;
Starting recover at 13-JUN-12
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2345
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1 tag=TAG20120613T144851
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/flash_recovery_area/STATSDB/archivelog/2012_06_13/o1_mf_1_2345_7xl72djp_.arc thread=1 sequence=2345
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/STATSDB/archivelog/2012_06_13/o1_mf_1_2345_7xl72djp_.arc RECID=1851 STAMP=785865820
unable to find archived log
archived log thread=1 sequence=2346
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/13/2012 16:03:42
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2346 and starting SCN of 92188455
RMAN> alter database open resetlogs;
database opened
RMAN>

 

Database Refresh using datapump

Database Refresh :export and import using datapump

Compare columns between schemas before import if any difference then fix it before import

select table_name, column_name
from dba_tab_columns@dabaselink
where owner = 'OWNER'
and table_name not like 'BIN$%'
minus
select table_name, column_name
from dba_tab_columns
where owner = 'OWNER'
and table_name not like 'BIN$%

compare tablespace usage between source and target database tablespace usage

copy and paste the script and add dblink name in the script

ttitle 'Version Check'

select case substr(name,1,2)
         when 'FN' then 'I reckon you want to run the fn version of this script'
         when 'HR' then 'Version checked okay'
         else 'What database are you in? '||name
         end check_db
from v$database;

ttitle 'Data Tablespace Comparison'
set lines 150
set pages 300

select prod.tablespace_name,
       prod.TOTAL-prod.free prod_used,
       local.total local_available,
       case when local.total - (prod.total - prod.free) < 0
           then ((prod.total - prod.free) - local.total)
           else 0
           end MB_Required
       ,
       case
       when local.total - (prod.total - prod.free) < 0 then 'Aaargh'
       else 'Okay'
       end "OOGY"
from
(select  'PROD',
        df.tablespace_name,
        df.MB TOTAL,
        decode(fs.MB,null,0,fs.MB) FREE,
        to_number(to_char((decode(fs.MB,null,0,fs.MB)
                             *100)/df.MB,'999.99')) PCT_FREE
from
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_data_files@dblinkname
group by tablespace_name) df
left join
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_free_space@dblinkname
group by tablespace_name) fs
on df.tablespace_name = fs.tablespace_name
) prod
left join
(select  'LOCAL',
        df.tablespace_name,
        df.MB TOTAL,
        decode(fs.MB,null,0,fs.MB) FREE,
        to_number(to_char((decode(fs.MB,null,0,fs.MB)
                             *100)/df.MB,'999.99')) PCT_FREE
from
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_data_files
group by tablespace_name) df
left join
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_free_space
group by tablespace_name) fs
on df.tablespace_name = fs.tablespace_name
) local
on local.tablespace_name = prod.tablespace_name
order by 4 desc;

title 'temp space comparison'

select prod.tablespace_name, (local.MB - prod.MB) spare_mb,
       case
          when (nvl(local.mb,0) - prod.mb) < 0 then 'Aaargh'
          else 'Whew!'
       end oogy
from
(
select 'PROD' host, tablespace_name, sum(bytes)/1024/1024 MB
from dba_temp_files@dblinkname
group by 'PROD',tablespace_name) prod
left join
(select 'LOCAL' host, tablespace_name, sum(bytes)/1024/1024 MB
from dba_temp_files
group by 'LOCAL',tablespace_name) local
on prod.tablespace_name = local.tablespace_name;

1. Export the dumpfile from source

select * from dba_directories;

SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR';

CREATE DIRECTORY datapump1 AS '/u01/app/XXXX/admin/TRAINING/dpdump';

GRANT READ, WRITE ON DIRECTORY datapump1 to system;

expdp system/password@training FULL=y DIRECTORY=datapump1 DUMPFILE=training1.dmp LOGFILE=training1.log

or

expdp username/password parfile=export.par

 par fle information

directory=data_pump_dir
logfile=FullUAT.log
dumpfile=FullUAT.dmp
SCHEMAS=schemaname
CONTENT=ALL
PARALLEL=16

Kill  Datapump job (Document ID=336014.1)

SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

-- locate Data Pump jobs:

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

SQL> select job_name, state from dba_datapump_jobs;

UNIX\> impdp \’sys/aa as sysdba\’ attach=SYS_IMPORT_SCHEMA_01

IMPORT> STATUS –> This shows the status of your job

IMPORT> KILL_JOB –> This will kill the datapump job and remove the underlying base tables

START_JOB, STOP_JOB, EXIT_CLIENT are few other options while using datapump.

impdp help=y or expdp help=y lists all of them

Due to some locking conditions, if you are not able to kill the job using above method, you can also drop the datapump master table – pls make sure you are dropping the right table as this is irreversible.

UNIX\> sqlplus ‘/as sysdba’
SQL> drop table SYS_IMPORT_SCHEMA_01;
SQL> exit

Before import Disable triggers

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
spool triggerdisable.sql
select 'alter trigger '||OWNER||'.'||TRIGGER_NAME||' disable;' from dba_triggers where TABLE_OWNER='schemaname';
spool off

Import

impdp system/password parfile=import.par

where import.par is

directory=data_pump_dir
dumpfile=file.dmp
logfile=filetst.log
schemas=schemaname
parallel=16
table_exists_action=truncate

After  Import comapre soure and target schema objects

select object_name, object_type from dba_objects@dblinkname where owner='xxxxx'and (object_name, object_type) not in (select object_name, object_type from dba_objects where owner='xxxxx')

select index_name from dba_indexes@dblink where owner ='xxxxx' minus select index_name from dba_indexes where owner = 'xxxx'

select TABLE_NAME from dba_tables@dblink where owner ='xxxxx' minus select TABLE_NAME from dba_tables where owner = 'xxxxx'

enable triggers

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
spool triggerenable.sql
select 'alter trigger '||OWNER||'.'||TRIGGER_NAME||' enable;' from dba_triggers where TABLE_OWNER='schema name';
spool off