Flashback Data Archive (Oracle Total Recall) 11g.

Starting from 11g Oracle has added another tool to its bundle of flashback technologies called Flashback
Data Archive(Oracle Total Recall).

With this feature previous state of a table can be viewed. But Unlike Flashback query
and or Flashback transaction query this feature does not depend on the undo_retention
of the database.


Flashback Data Archive requires  one or more tablespaces where it can store historical data
for one or several tables.

whenever the data in the table is modified its original unmodified value is written to the undo
tablespace. From there the Flashback Data Archiver Process(FBDA) will collect it and write
the data to flashback data archive.

#Create a Tablespace for the Flashback Data Archive


SQL> create tablespace fda datafile  '/u01/fda01.dbf' size 500M
     autoextend on next 100M
     extent management local
     segment space management auto;



SQL> select * from dba_sys_privs where privilege like '%FLASH%';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          FLASHBACK ARCHIVE ADMINISTER             NO
SYS                            FLASHBACK ANY TABLE                      NO
DBA                            FLASHBACK ANY TABLE                      YES
SYS                            FLASHBACK ARCHIVE ADMINISTER             NO
DBA                            FLASHBACK ARCHIVE ADMINISTER             YES
MDSYS                          FLASHBACK ANY TABLE                      NO

6 rows selected.


SQL> grant flashback archive administer to scott;

Grant succeeded.


SQL> create flashback archive flash_data
     tablespace fda
     quota 4g
     retention 5 year;

Flashback archive created.

#Quota is optional.It means that FDA can only use upto 4Gb space in the tablespace.

SQL> drop flashback archive flash_data;

Flashback archive dropped.


Modify commands

#Make it the default archive

SQL> alter flashback archive flash_data set default;

Flashback archive altered.

#Add space to it.

SQL> alter flashback archive flash_data add tablespace abc;

Flashback archive altered.

#Change its quota

SQL> alter flashback archive flash_data modify tablespace fda quota 1G;

Flashback archive altered.

#Change retention

SQL> alter flashback archive flash_data modify retention 1 year;

Flashback archive altered.

#Remove tablespace from flashback data archive

SQL> alter flashback archive flash_data
  2  remove tablespace abc;

Flashback archive altered.



#Purge data from flashback data archive


SQL> alter flashback archive flash_data purge all;

Flashback archive altered.

#Purge using interval clause

SQL> alter flashback archive flash_data purge before
  2  timestamp(systimestamp - interval '5' day);

Flashback archive altered.

#Purge using SCN Clause

SQL> alter flashback archive flash_data purge before scn 799684;

Flashback archive altered.



Playing with it.

SQL> create table t1
  2  (id int,name varchar2(30))
  3  flashback archive flash_data;

Table created.


SQL> alter table emp flashback archive;

Table altered.

#Below in the column ARCHIVE_TABLE_NAME tables which have been created in the FBA tablespace can be seen.

SQL> select * from dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME                     FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME             STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ 

--------
T1                             SCOTT                          FLASH_DATA                     SYS_FBA_HIST_74560             

ENABLED
EMP                            SCOTT                          FLASH_DATA                     SYS_FBA_HIST_73181             

ENABLED


SQL> select flashback_archive_name, status from dba_flashback_archive;

FLASHBACK_ARCHIVE_NAME         STATUS
------------------------------ -------
FLASH_DATA                     DEFAULT


SQL> alter table emp no flashback archive;

Table altered.

SQL> alter table emp flashback archive flash_data;

Table altered.


SQL> select * from dba_flashback_archive;

OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME                         LAST_PURGE_TIME         

           STATUS
---------- ---------------------- ------------------ ----------------- ----------------------------------- 

---------------------------------- -------
SYS        FLASH_DATA                              1               365 21-JAN-14 04.09.51.000000000 PM     21-JAN-14 

04.23.59.000000000 PM    DEFAULT



Values usable with systimestamp

systimestamp - interval '60' second/minute/day/month

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME                QUOTA_IN_MB
---------------------- ------------------ ------------------------------ ----------------------------------------
FLASH_DATA                              1 FDA                            1024


SQL> select sal from emp where empno=7934;

       SAL
----------
      1300

SQL> update emp set sal=2000 where empno=7934;

1 row updated.

SQL> commit;

Commit complete.

SQL> select sal from emp where empno=7934;

       SAL
----------
      2000

#After modifying the  we can see the previous state with the as of clause.

SQL> select * from emp as of timestamp(systimestamp - interval '10' minute) where empno=7934;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

Read more >>

ADRCI Walkthrough.

 
Invoke adrci as the oracle user.


> adrci

ADRCI: Release 11.2.0.3.0 - Production on Fri Jan 17 22:38:57 2014

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

ADR base = "/opt/oracle"
adrci> show home
ADR Homes:
diag/diagtool/user_oracle/host_365570786_11
diag/rdbms/orcl/ORCL
diag/asm/+asm/+ASM4
diag/tnslsnr/orcl4/listener_cad

Set the home

adrci> set home diag/rdbms/orcl/ORCL

To check the alert log use the following option.

adrci> show alert -tail -f
2014-01-17 22:28:35.440000 +00:00
ALTER SYSTEM ARCHIVE LOG
2014-01-17 22:28:38.414000 +00:00
Thread 4 advanced to log sequence 1392 (LGWR switch)
  Current log# 11 seq# 1392 mem# 0: +REDO/orcl/redot4g11f1.rdo
  Current log# 11 seq# 1392 mem# 1: +REDO/orcl/redot4g11f2.rdo
Archived Log entry 10628 added for thread 4 sequence 1391 ID 0xc5495541 dest 1:

or

adrci> show alert -tail 

or

adrci> show alert 


To check for severe problems in database.

adrci> show problem

ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
2                    ORA 600 [723]                                               3807                 2007-06-18 21:35:47.862114 -07:00
1                    ORA 29770                                                   24129                2011-10-21 19:08:24.097000 +00:00
2 rows fetched


To check for incedents related to problems.

adrci> show incident

ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
*****************************************************************************
INCIDENT_ID       PROBLEM_KEY               CREATE_TIME
----------------- ------------------------- ---------------------------------
3808              ORA 603                   2007-06-18 21:35:49.322161 -07:00
3807              ORA 600 [723]             2007-06-18 21:35:47.862114 -07:00
3805              ORA 600 [723]             2007-06-18 21:35:25.012579 -07:00
3804              ORA 1578                  2007-06-18 21:35:08.483156 -07:00
4 rows fetched


To get more details on the incident execute the following

adrci> show incident -mode detail -p "incident_id=3807"

adrci> show trace 

This statement lists the names of all trace files related to incident number 1681:

adrci> SHOW TRACEFILE -I 1681




adrci> show control

ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
3783208084           720                  8760                 2013-12-16 19:28:24.827055 +00:00        2014-01-17 21:09:07.527157 +00:00        2014-01-17 10:15:21.303696 +00:00        1                    2                    80                   1                    2013-12-16 19:28:24.827055 +00:00
1 rows fetched

All the tracefiles will stay for 30 days (720 hours),
but all the incident files would be kept for one year (8760 hours) by default.

This can be changed by the following command

adrci> set control (SHORTP_POLICY = 48)

adrci> set control (LONGP_POLICY = 48)

 
Purging manually
 
Files can be purged manually by the following commands. The value "2880" is in minutes.
 
adrci> purge -age 2880 -type incident
adrci> purge -age 2880 -type ALERT
adrci> purge -age 2880 -type TRACE
adrci> purge -age 2880 -type CDUMP
adrci> purge -age 2880 -type HM

Creation of Packages & ZIP files to send to Oracle Support

adrci> set home diag/asm/+asm/+ASM1
adrci> show problem

ADR Home = /opt/oracle/diag/asm/+asm/+ASM1:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY
-------------------- -----------------------------------------------------------
1                    ORA 4030
1 rows fetched


ADR Home = /opt/oracle/diag/asm/+asm/+ASM1:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY
-------------------- -----------------------------------------------------------
1                    ORA 4030
1 rows fetched

adrci> ips create package problem 1 correlate all
Created package 1 based on problem id 1, correlation level all

#Create logical package with correlation level all.

adrci> ips generate package 1 in "/opt/oracle"
Generated package 1 in file /opt/oracle/ORA4030_20140219202848_COM_1.zip, mode complete


#Now create a zip file from the logical package



 

Read more >>

Oracle Undo Tablespace Advisor(DBMS_UNDO_ADV).

As per Doc ID 1580225.1
The package DBMS_UNDO_ADV is undocumented, and it is used internally by the Undo Advisor .

dbms_undo_adv package gives advise based on historical information present in memory or Automatic Workload
Repository. The default retention of AWR is 7 days.

Function longest_query : Returns the length of the longest query for a given period .

Method 1

SELECT dbms_undo_adv.longest_query LONGEST_QUERY FROM dual;

 
Method 2 (using Start/End time)

SELECT dbms_undo_adv.longest_query(SYSDATE-1/24, SYSDATE) LONGEST_QUERY FROM dual;

 
Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.longest_query(345, 768) LONGEST_QUERY FROM dual;


Function required_retention: returns the undo_retention value required for running the longest query.

Method 1

select dbms_undo_adv.required_retention from dual;

Method 2 (using Start/End time)

SELECT Ddbms_undo_adv.required_retention(SYSDATE-1/24, SYSDATE) required_retention FROM dual;

Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.longest_query(345, 768) LONGEST_QUERY FROM dual;



Function best_possible_retention: Returns the best possible undo retention which the current 
undo tablespace can support.

Note:If undo tablespace autoextensible then "maxsize" setting will be taken into account to
 calculate best_possible_retention.


Method 1

SELECT dbms_undo_adv.best_possible_retention best_retention FROM dual;

Method 2 (using Start/End time)

SELECT dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE) best_retention FROM dual;

Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.best_possible_retention(345, 768) best_retention FROM dual; 


Function required_undo_size: Returne the appropriate undo tablespace size to satisfy undo retention value.

Method 1

SELECT dbms_undo_adv.required_undo_size(900) required_undo_size FROM dual;

Method 2 (using Start/End time)

SELECT dbms_undo_adv.required_undo_size(900,SYSDATE-1/24, SYSDATE) required_undo_size FROM dual;

Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.required_undo_size(900, 345, 768) required_undo_size FROM dual;

Read more >>

Starting And Stopping OEM/OMS 12c Agent and Server.

[oracle@afr bin]$ pwd
/u01/app/oracle/Middleware/agent/agent_inst/bin

[oracle@afr bin]$ ./emctl stop agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Stopping agent ..... stopped.

[oracle@afr bin]$ ./emctl start agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Starting agent ........................................... started.



[oracle@afr bin]$ pwd
/u01/app/oracle/Middleware/oms/bin

[oracle@afr bin]$ ./emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.1.0
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down

Read more >>

How To Find Views And What Information They Provide In Oracle Database ?

SQL> desc dba_views;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER
 TEXT                                               LONG
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)
 EDITIONING_VIEW                                    VARCHAR2(1)
 READ_ONLY                                          VARCHAR2(1)


SQL> select view_name from dba_views where view_name like'%ROLE%';


VIEW_NAME
------------------------------
V$XS_SESSION_ROLE
DBA_APPLICATION_ROLES
USER_APPLICATION_ROLES
EXU9NTAROLE
EXU81APPROLE
DBA_ROLES
DBA_ROLE_PRIVS
USER_ROLE_PRIVS
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS

VIEW_NAME
------------------------------
EXU10NTAROLE
SESSION_ROLES
XS_SESSION_ROLES
DBA_SCHEDULER_JOB_ROLES
KU$_DEFROLE_LIST_VIEW
KU$_DEFROLE_VIEW
KU$_DUMMY_ROLE_VIEW
KU$_PROXY_ROLE_LIST_VIEW
KU$_ROLE_VIEW
DBA_CONNECT_ROLE_GRANTEES
PROXY_ROLES

VIEW_NAME
------------------------------
PROXY_USERS_AND_ROLES
ROLE_WM_PRIVS
MGMT$ESA_DBA_ROLE_REPORT

25 rows selected.


SQL> desc dict;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                         VARCHAR2(30)
 COMMENTS                                           VARCHAR2(4000)


SQL> select * from dict where table_name like '%ROLE%';

TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------
DBA_ROLES
All Roles which exist in the database

DBA_ROLE_PRIVS
Roles granted to users and roles

USER_ROLE_PRIVS
Roles granted to current user


TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------
DBA_SCHEDULER_JOB_ROLES
All scheduler jobs in the database by database role

DBA_CONNECT_ROLE_GRANTEES
Information regarding which users are granted CONNECT

ROLE_ROLE_PRIVS
Roles which are granted to roles


TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------
ROLE_SYS_PRIVS
System privileges granted to roles

ROLE_TAB_PRIVS
Table privileges granted to roles

SESSION_ROLES
Roles which the user currently has enabled.


9 rows selected.

Read more >>

Create a System Trigger to Enable SQL TRACE for a session..

The following script will enable session trace for the user scott.

[oracle@canada u01]$ cat trace_trigger.sql
set echo on

spool trace_trigger.log

DROP TRIGGER SYS.trace_trigger;

CREATE OR REPLACE TRIGGER sys.trace_trigger
After logon on database
Begin
            if ( user='SCOTT') then
                execute immediate 'alter session set sql_trace=true';
                execute immediate 'alter session set timed_statistics=true';
                execute immediate 'alter session set tracefile_identifier="SCOTT"';
                execute immediate 'alter session set max_dump_file_size=unlimited';
                execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
            End if;
End;
/

spool off;


[oracle@canada u01]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 22 06:04:31 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @trace_trigger.sql
SQL>
SQL> spool trace_trigger.log
SQL>
SQL> DROP TRIGGER SYS.trace_trigger;
DROP TRIGGER SYS.trace_trigger
*
ERROR at line 1:
ORA-04080: trigger 'trace_trigger' does not exist


SQL>
SQL> CREATE OR REPLACE TRIGGER sys.trace_trigger
  2  After logon on database
  3  Begin
  4              if ( user='SCOTT') then
  5                  execute immediate 'alter session set sql_trace=true';
  6                  execute immediate 'alter session set timed_statistics=true';
  7                  execute immediate 'alter session set tracefile_identifier="SCOTT"';
  8                  execute immediate 'alter session set max_dump_file_size=unlimited';
  9                  execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
 10              End if;
 11  End;
 12  /

Trigger created.

SQL>
SQL> spool off;
SQL>


SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
ENABLED

To disable the trigger.

SQL> alter trigger trace_trigger disable;

Trigger altered.

SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
DISABLED

To enable the trigger.

SQL> alter trigger trace_trigger enable;

Trigger altered.

SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
ENABLED

Read more >>

Rman Disaster Recovery Walkthrough.

On Source


Make a backup location.

mkdir -p /u01/backup

Maker sure database is in archive log mode.

Configure control file backup parameters in Rman.

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> configure controlfile autobackup format for device type disk to '/u01/backup/cf%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/cf%F';
new RMAN configuration parameters are successfully stored


Take backup of database plus archivelog.

[oracle@canada ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 5 19:13:05 2013

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

connected to target database: DELL (DBID=3764293269)

RMAN> run
2> {
3> allocate channel c1 device type disk format '/u01/backup/%U';
4> allocate channel c2 device type disk format '/u01/backup/%U';
5> allocate channel c3 device type disk format '/u01/backup/%U';
6> allocate channel c4 device type disk format '/u01/backup/%U';
7> backup database plus archivelog;
8> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=51 device type=DISK

allocated channel: c2
channel c2: SID=28 device type=DISK

allocated channel: c3
channel c3: SID=49 device type=DISK

allocated channel: c4
channel c4: SID=34 device type=DISK


Starting backup at 05-OCT-13
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=828040174
channel c1: starting piece 1 at 05-OCT-13
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=2 STAMP=828040176
input archived log thread=1 sequence=5 RECID=3 STAMP=828040184
channel c2: starting piece 1 at 05-OCT-13
channel c3: starting archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=4 STAMP=828040185
input archived log thread=1 sequence=7 RECID=5 STAMP=828040187
channel c3: starting piece 1 at 05-OCT-13
channel c4: starting archived log backup set
channel c4: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=6 STAMP=828040188
input archived log thread=1 sequence=9 RECID=7 STAMP=828040446
channel c4: starting piece 1 at 05-OCT-13
channel c1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/01ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:00
channel c2: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/02ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:00
channel c3: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/03ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:00
channel c4: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/04ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c4: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-13

Starting backup at 05-OCT-13
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/dell/system01.dbf
channel c1: starting piece 1 at 05-OCT-13
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/dell/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/dell/users01.dbf
channel c2: starting piece 1 at 05-OCT-13
channel c3: starting full datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/dell/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/dell/undotbs01.dbf
channel c3: starting piece 1 at 05-OCT-13
channel c3: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/07ollp81_1_1 tag=TAG20131005T191408 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:37
channel c2: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/06ollp81_1_1 tag=TAG20131005T191408 comment=NONE
channel c2: backup set complete, elapsed time: 00:01:18
channel c1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/05ollp81_1_1 tag=TAG20131005T191408 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:50
Finished backup at 05-OCT-13

Starting backup at 05-OCT-13
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=8 STAMP=828040560
channel c1: starting piece 1 at 05-OCT-13
channel c1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/08ollpbg_1_1 tag=TAG20131005T191600 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-13

Starting Control File and SPFILE Autobackup at 05-OCT-13
piece handle=/u01/backup/cfc-3764293269-20131005-00 comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-13
released channel: c1
released channel: c2
released channel: c3
released channel: c4

Take backup of spfile.


RMAN> backup spfile to destination '/u01/backup/';

Starting backup at 05-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 05-OCT-13
channel ORA_DISK_1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/DELL/backupset/2013_10_05/o1_mf_nnsnf_TAG20131005T192028_950694xh_.bkp tag=TAG20131005T192028 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-13

Starting Control File and SPFILE Autobackup at 05-OCT-13
piece handle=/u01/backup/cfc-3764293269-20131005-01 comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-13

DBID of database to be restored DBID=3764293269


On new host where database is to be restored
make required directories

[oracle@newyork ~]$ mkdir -p /u01/app/oracle/admin/dell/adump
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/oradata/dell/
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/dell/


Copy backup files to the destination server.

On Destination Server

1) Restore the spfile



[oracle@newyork ~]$ export ORACLE_SID=dell
[oracle@newyork ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 5 19:34:27 2013

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

connected to target database (not started)

RMAN> set dbid=3764293269

executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initdell.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1335192 bytes
Variable Size                 75497576 bytes
Database Buffers              79691776 bytes
Redo Buffers                   2494464 bytes


RMAN> restore spfile from '/u01/backup/cfc-3764293269-20131005-00';

Starting restore at 05-OCT-13
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 /u01/backup/cfc-3764293269-20131005-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 05-OCT-13




RMAN> shutdown immediate

Oracle instance shut down



2) Restore controlfile

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     272011264 bytes

Fixed Size                     1335952 bytes
Variable Size                201330032 bytes
Database Buffers              67108864 bytes
Redo Buffers                   2236416 bytes





RMAN> run
2> {
3> set controlfile autobackup format for device type disk to '/u01/backup/cf%F';
4> restore controlfile from autobackup;
5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 05-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: DELL
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131005
channel ORA_DISK_1: AUTOBACKUP found: /u01/backup/cfc-3764293269-20131005-01
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/backup/cfc-3764293269-20131005-01
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/dell/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/dell/control02.ctl
Finished restore at 05-OCT-13



3) Restore the database.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


RMAN> catalog start with '   ';  (if backup destination is different from source)



RMAN> restore database;

Starting restore at 05-OCT-13
Starting implicit crosscheck backup at 05-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 05-OCT-13

Starting implicit crosscheck copy at 05-OCT-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 05-OCT-13

searching for all files in the recovery area
cataloging files...
no files cataloged

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 00003 to /u01/app/oracle/oradata/dell/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/dell/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/07ollp81_1_1
channel ORA_DISK_1: piece handle=/u01/backup/07ollp81_1_1 tag=TAG20131005T191408
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:23
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 00002 to /u01/app/oracle/oradata/dell/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dell/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/06ollp81_1_1
channel ORA_DISK_1: piece handle=/u01/backup/06ollp81_1_1 tag=TAG20131005T191408
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:37
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/dell/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/05ollp81_1_1
channel ORA_DISK_1: piece handle=/u01/backup/05ollp81_1_1 tag=TAG20131005T191408
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:58
Finished restore at 05-OCT-13



4) Recover the database

RMAN> recover database;

Starting recover at 05-OCT-13
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=10
channel ORA_DISK_1: reading from backup piece /u01/backup/08ollpbg_1_1
channel ORA_DISK_1: piece handle=/u01/backup/08ollpbg_1_1 tag=TAG20131005T191600
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/DELL/archivelog/2013_10_05/o1_mf_1_10_9507y3bg_.arc thread=1 

sequence=10
channel default: deleting archived log(s)
archived log file 

name=/u01/app/oracle/flash_recovery_area/DELL/archivelog/2013_10_05/o1_mf_1_10_9507y3bg_.arc RECID=9 

STAMP=828042523
unable to find archived log
archived log thread=1 sequence=11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/05/2013 19:48:45
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 11 and starting SCN 
of 797450


Note: Ignore the error and relogin to thr rman prompt.





5) open with resetlogs

[oracle@newyork ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 5 19:52:07 2013

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

connected to target database: DELL (DBID=3764293269, not open)

RMAN> sql 'alter database open resetlogs';

using target database control file instead of recovery catalog
sql statement: alter database open resetlogs

RMAN> exit


Recovery Manager complete.


6) verify



[oracle@newyork ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 5 19:54:11 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1


SQL> select name, dbid from v$database;

NAME            DBID
--------- ----------
DELL      3764293269

Read more >>