Pages

Tuesday, July 15, 2014

OCA 1Z0-051 dumps

1Z0-051 dump pdf free download - Oracle 11g SQL Fundamentals I 

 

Download Here: 1Z0-051-1

Download Here: 1Z0-051-2

 

Exam Details : 
Oracle Database 10g and 11g: SQL Fundamentals I 1Z0-051

Exam Number:
1Z0-051
Associated Certifications:

Exam Product Version:
SQL and PL/SQL,
  Exam Price:
US$ 125






Duration:
120 minutes
Number of Questions:
70
Passing Score:
60%
Validated Against:
Exam 051 has been validated against Oracle Database 10g and 11g Release 2 version 11.2.0.1.0.
format:
Multiple Choice

 Link to get more info on certification: OCA-1Z0-051

For more info on Certification program rules click: here

Exam Topics:

Retrieving Data Using the SQL SELECT Statement 
  • List the capabilities of SQL SELECT statements
  • Execute a basic SELECT statement
Restricting and Sorting Data
  • Limit the rows that are retrieved by a query
  • Sort the rows that are retrieved by a query 
  • Use ampersand substitution to restrict and sort output at runtime
Using Single-Row Functions to Customize Output 
  • Describe various types of functions available in SQL
  • Use character, number, and date functions in SELECT statements
Using Conversion Functions and Conditional Expressions
  • Describe various types of conversion functions that are available in SQL
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions 
  • Apply conditional expressions in a SELECT statement
Reporting Aggregated Data Using the Group Functions 
  • Identify the available group functions
  • Describe the use of group functions 
  • Group data by using the GROUP BY clause 
  • Include or exclude grouped rows by using the HAVING clause
Displaying Data from Multiple Tables 
  • Write SELECT statements to access data from more than one table using equijoins and nonequijoins
  • Join a table to itself by using a self-join 
  • View data that generally does not meet a join condition by using outer joins 
  • Generate a Cartesian product of all rows from two or more tables
Using Subqueries to Solve Queries 
  • Define subqueries
  • Describe the types of problems that the subqueries can solve 
  • List the types of subqueries 
  • Write single-row and multiple-row subqueries
Using the Set Operators 
  • Describe set operators
  • Use a set operator to combine multiple queries into a single query 
  • Control the order of rows returned
Manipulating Data 
  • Describe each data manipulation language (DML) statement
  • Insert rows into a table 
  • Update rows in a table 
  • Delete rows from a table 
  • Control transactions
Using DDL Statements to Create and Manage Tables
  • Categorize the main database objects
  • Review the table structure 
  • List the data types that are available for columns 
  • Create a simple table 
  • Explain how constraints are created at the time of table creation 
  • Describe how schema objects work
Creating Other Schema Objects
  • Create simple and complex views
  • Retrieve data from views 
  • Create, maintain, and use sequences 
  • Create and maintain indexes 
  • Create private and public synonyms

1Z0-052 dump pdf free download


 Oracle Certified Associate - OCA

1Z0-052 dump pdf free download - Oracle 11g Database Administration I




Download Here : 1Z0-052


Exam Details :

Oracle Database 11g: Administration I 1Z0-052

Exam Number:

1Z0-052
Associated Certifications:
Oracle Database 11g Administrator Certified Professional  
Exam Product Version:
Oracle Database 11g,
Exam Price:
US$ 245







Duration:

90 minutes
Number of Questions:
70
Passing Score:
66%
Validated Against:
The exam has been validated against Oracle® Database 11g Release 2 version 11.2.0.1.0.

format:
Multiple Choice

Monday, July 14, 2014

Applying CPU Patch On Primary Database Server Environment

 Applying CPU Patch On Primary Database Server Environment.


 Step 1:

Incase if you have standby server. Now disable log shipping on the primary database by setting the log_archive_dest_state_2 to disable. As I said earlier, parameter log_archive_dest_2 on my primary database is set to point to the standby database.

SQL> alter system set log_archive_dest_state_2=disable;

System altered.

Step 2:

Shutdown the Primary database and stop the listener running on the primary database server.

 
[oracle@PRIM ~]$ sqlplus sys/"as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

Now Stop the Listener.

[oracle@PRIM ~]$ lsnrctl stop

 [oracle@PRIM ~]$ ps -ef | grep pmon

Step 3:

Now apply the CPU patch on the Primary database.



Set the Opatch path.
[oracle@PRIM ~]$ export $ORACLE_HOME=/u01/app/oracle/product/11.2/db_1
[oracle@PRIM ~]$ export PATH=$PATH:$ORACLE_HOME/OPatch

OPatch Version: 11.2.0.3.0

OPatch succeeded.

[oracle@PRIM ~]$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/12827726/
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.

PREREQ session
OPatch version : 11.2.0.3.0
OUI version : 11.2.0.2.0
Log file location : /u01/app/oracle/product/11.2.0.2/db1/cfgtoollogs/opatch/opatch2013-05-18_11-56-11AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

[oracle@PRIM ~]$ export PATH=$PATH:/u01/app/oracle/product/11.2.0.2/db1/OPatch
[oracle@PRIM ~]$ opatch apply /opt/12827726/

 OPatch succeeded.

 Step 4:

Start the listener on the primary database server and also start the Primary database.


[oracle@PRIM ~]$ lsnrctl start

[oracle@PRIM ~]$ sqlplus /"as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

 Step 5:

Incase if you have standby server. Now enable log shipping on the primary database by setting the log_archive_dest_state_2 to "enable". As I said earlier, parameter log_archive_dest_2 on my primary database is set to point to the standby database.

SQL> alter system set log_archive_dest_state_2=enable;


Step 6:

On the primary database, run the patching scripts like “catbundle.sql” in this case.
The script run generates archives and these archives would be shipped and applied to the standby database. So, there is no requriement to run the patching scripts on the standby database.

[oracle@PRIM ~]$ sqlplus /"as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> @?/rdbms/admin/catbundle.sql psu apply

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------

Note : Incase if you want to rollback the patch applied:


  1. Verify that an $ORACLE_HOME/rdbms/admin/catbundle_PSU_<database SID>_ROLLBACK.sql file exists for each database associated with this ORACLE_HOME. If this is not the case, you must execute the steps.
  2. Shut down all instances and listeners associated with the Oracle home that you are updating. 
  3. For more information, see Oracle Database Administrator's Guide.

            Run the OPatch utility specifying the rollback argument as follows.

            opatch rollback -id 12827726


Patch Post-Deinstallation Instructions for a Non Oracle RAC Environment

  1. Start all database instances running from the Oracle home. (For more information, see Oracle Database Administrator's Guide.)
  2. For each database instance running out of the ORACLE_HOME, connect to the database using SQL*Plus as SYSDBA and run the rollback script as follows:
    cd $ORACLE_HOME/rdbms/admin
    sqlplus /nolog
    SQL> CONNECT / AS SYSDBA
    SQL> STARTUP
    SQL> @catbundle_PSU_<database SID>_ROLLBACK.sql
    SQL> QUIT
    

Applying CPU Patch in a dataguard (Physical Standby) environment

Here is a brief explanation on how to apply CPU(Critical Patch Update) in a dataguard environment
In this demo, I am applying PSU 11.2.0.4.2 on the Primary and standby databases.

Standby  Database Name :ORCL_STBY
Primary  Database Name : ORCL


Primary Server:

[oracle@PRIM ~]$ sqlplus sys/oracle@srprim as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 10:43:50 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------ ------------- ---------------
OPEN               ORCL            PRIMARY
 Standby Server:

[oracle@STBY~]$ sqlplus /"as sysdba"
SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 10:46:35 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS   INSTANCE_NAME        DATABASE_ROLE
-------- --------------           ----------------------
MOUNTED      ORCL_STBY         PHYSICAL STANDBY
 
 Step 1:
As a first step we need to disable the log shipping from primary database to the standby database by setting the log_archive_dest_state_2 to “defer” on the primary database.




SQL> alter system set log_archive_dest_state_2=defer;
System altered.
 Step 2:

Now Cancel the Managed Recovery Process on your standby database .




SQL> alter database recover managed standby database cancel;
Database altered.
 Step 3:

CPU (Critical Patch Update)/ Patch Set patches  always needs to be applied first on the standby database and then on the primary database. In order to apply it on the standby database, shutdown the standby database and also the listener running on the standby server.
 SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

[oracle@STBY ~]$ lsnrctl stop

[oracle@STBY ~]$ ps -ef | grep tns
oracle 6958 5107 0 10:52 pts/1 00:00:00 grep tns

[oracle@STBY ~]$

[oracle@STBY ~]$ ps -ef | grep pmon
oracle 6960 5107 0 10:52 pts/1 00:00:00 grep pmon
 
 Step 4:
Now apply the CPU on the standby database.
[oracle@STBY ~]$ export PATH=$PATH:$ORACLE_HOME/OPatch
[oracle@STBY ~]$ opatch version
OPatch Version: 11.2.0.3.0

OPatch succeeded.
 
Step 5: 

Unzip the downloaded patch file.

unzip p18031668_112040_<platform>.zip 
 

cd 18031668
 
Verify pre-reqs:
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Now Apply Patch:

opatch apply
 
Step 6: 

Once the patch has been applied on the standby database,
start the listener and the standby database. 

[oracle@uat ~]$ lsnrctl start
 
[oracle@uat ~]$ sqlplus /"As sysdba"

SQL> startup mount
 
 
Step 7:
 
On Primary Database server, Start the archiving.




SQL> alter system set log_archive_dest_state_2=enable;
 System altered.
 
  
Note: Do not run any patching scripts on the standby database (Example: catbundle.sql). 
We are done with the patching on the standby database. 
 
Click on this Link to read further on how to apply CPU Patch On Primary Production Database Server Environment. 
 


Monday, July 7, 2014

Rman Recovery comands....

Rman Time-based PITR

run
{
allocate channel dev1 type disk;
set until time "to_date('2014-03-24:00:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database; }

or

run
{
allocate channel dev1 type disk;
set until time "to_date('2014-03-25:00:00', 'yyyy-mm-dd:hh24:mi')";
restore database;
recover database; }

Rman Time-based Controlfile PITR

run
{
SET UNTIL TIME "TO_DATE('20140323:12:01','YYYYMMDD:HH24:MI')";
RESTORE CONTROLFILE FROM AUTOBACKUP preview;
}




Oracle Database Load Capture

[oracle@orcl01 dbcapture]$ sqlplus /"as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 20 10:26:01 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

It looked like a nice addition because, as others have pointed out on their blogs, it could prove very useful for capturing an application workload on 10g to test how it will work on 11g before upgrading. There's a genuine business need for that. Then, having updated our first couple of test instances at work, I was reminded of the great care we take with license management at my current site (thanks, Tam) and, on closer inspection of the documentation :-


The Oracle Real Application Testing license is required on both capture and replay systems for Database Replay and is charged by the total number of CPUs on those systems.



I haven't used workload capture on this instance yet, so I thought I'd give it a quick try. Interesting though it is to try this out for the first time, I'm going to keep this post short by referring you to another blog that shows how to use RAT via OEM DB Console. Actually, my first attempt failed with the following error message.

SQL> BEGIN
  DBMS_WORKLOAD_CAPTURE.start_capture (name     => 'test_capture_1',
                                       dir      => 'DBCAPTURE',
                                       duration => NULL);
END;
/  2    3    4    5    6
BEGIN
*
ERROR at line 1:
ORA-15591: cannot start capture because parameter "PRE_11G_ENABLE_CAPTURE" is not enabled
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 799
ORA-06512: at line 2

select name, detected_usages, currently_used,
    first_usage_date, feature_info, last_sample_date,
    sample_interval, description
    from DBA_FEATURE_USAGE_STATISTICS
    where name like 'Database Replay%'
    /

   
    NAME                                                             DETECTED_USAGES                  CURRE FIRST_USA
---------------------------------------------------------------- ---------------                  ----- ---------
FEATURE_INFO                                                                                     LAST_SAMP SAMPLE_INTERVAL
--------------------------------------------------------------------------------                  --------- ---------------
DESCRIPTION
--------------------------------------------------------------------------------                 ------------------------------------------------
Database Replay: Workload Capture                                              0                  FALSE
                                                                                                 18-DEC-13           604800
Database Replay: Workload was ever captured.


you have to reset the value pre_11g_enable_capture to true.

SQL> alter system set pre_11g_enable_capture=true;

System altered.



SQL> BEGIN
  DBMS_WORKLOAD_CAPTURE.start_capture (name     => 'test_capture_1',
                                       dir      => 'DBCAPTURE',
                                       duration => 900);
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

CORRUPTION DETECTED: on Standby server




 CORRUPTION DETECTED: In redo blocks starting at block 2049count 1679 for thread 1



Problem Summary 


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

CORRUPTION DETECTED: In redo blocks starting at block 2049count 1679 for thread 1 sequence 7677

Problem Description
---------------------------------------------------
Primary database is in MAXIMUM PERFORMANCE mode
Thu Jun 05 13:38:37 2014
RFS[2849]: Assigned to RFS process 5367
RFS[2849]: Opened log for thread 1 sequence 7677 dbid 752265984 branch 834506114
RFS[2850]: Assigned to RFS process 5369
RFS[2850]: Selected log 6 for thread 1 sequence 7678 dbid 752265984 branch 834506114
CORRUPTION DETECTED: In redo blocks starting at block 2049count 1679 for thread 1 sequence 7677
Thu Jun 05 13:44:36 2014
RFS[2851]: Assigned to RFS process 5801
RFS[2851]: Opened log for thread 1 sequence 7636 dbid 752265984 branch 834506114
CORRUPTION DETECTED: In redo blocks starting at block 1count 2048 for thread 1 sequence 7636
Thu Jun 05 13:44:38 2014
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance QPUATRD - Archival Error
ORA-16014: log 6 sequence# 7678 not archived, no available destinations
ORA-00312: online log 6 thread 1: '/u02/oracle/oradata/orcl/stdby02.log' 



Suggested Solution:

The suggested solution is make sure the following firewall and router features are disabled.
- SQLNet fixup protocol
- Deep Packet Inspection (DPI)
- SQLNet packet inspection
- SQL Fixup
- SQL ALG (Juniper firewall) 





What happens when corruption occurs in standby?

If the redo gets corrupted before being applied to the standby then it cannot be applied and (hopefully) the standby requests the primary to resend the redo.
MRP cannot continue applying unless it has none corrupted redo to apply. At this point the thing to do is check to see that the standby is in sync.
If so then it is good. Corrupted data is propagated to a standby and applied when it is valid redo. So that is mostly logical corruption in primary database data files that gets propagated and applied.

You can check that the standby is in sync two ways

set lines 500
select * from v$dataguard_stats ;

And these queries: One for the primary and 2 for the standby.

Primary: SQL > select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby: SQL > select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby: SQL > select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and applied='YES'
group by thread# order by 1;

Rman System datafile recovery scenerio

System datafile recovery:


Lets create a scenario where we will drop the system datafile, and we will recover using the rman restore and recover process.Since system datafile is key for database functioning.

Before we start. Lets verify that database is functioning without any problems.

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 20:47:23 2014

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


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

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14


Lets drop system datafile now.

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  users01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  undotbs01.dbf

[oracle@linux1 orcl]$ rm system*

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  users01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf
redo01.log     standby_redo02.log  temp01.dbf
redo02.log     standby_redo03.log  undotbs01.dbf

After dropping system datafile. lets query some data from database. We will now see that database is complaining that it can't access system01.dbf

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 21:18:06 2014

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


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

SQL> select username from dba_users;
select username from dba_users
                     *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Now lets start the recover process.Since this is system datafile it is mandatory that you restart the server in mount state. Even if you try to start the
database using startup, it will just mount the database and fail asking for system.dbf file.

SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort;
ORACLE instance shut down.


Now login into rman session and restore the system datafile.

[oracle@linux1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 7 21:19:20 2014

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

connected to target database: ORCL (DBID=1378116623, not open)


RMAN> run {
2> restore datafile 1;
3> recover datafile 1;
4> }

Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

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/datafiles/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 07-JUL-14

Starting recover at 07-JUL-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 65 is already on disk as file /u01/app/oracle/oraarch/orcl_1_65_850335889.arc
archived log for thread 1 with sequence 66 is already on disk as file /u01/app/oracle/oraarch/orcl_1_66_850335889.arc
archived log for thread 1 with sequence 67 is already on disk as file /u01/app/oracle/oraarch/orcl_1_67_850335889.arc
archived log file name=/u01/app/oracle/oraarch/orcl_1_65_850335889.arc thread=1 sequence=65
media recovery complete, elapsed time: 00:00:05
Finished recover at 07-JUL-14

Note: In case you don't know the datafile number to recover you can use below query.
Most of the time system datafile is the #1. You can run this query  when DB is in mount state.

SQL> select file#,Name, status from v$datafile where name='SYSTEM';

     FILE# Name STATUS
---------- ----------------------------------- -------
         1 /u01/app/oracle/datafiles/orcl/system01.dbf SYSTEM
   


verify that system datafile has been restored and recovered.

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  undotbs01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf        users01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  temp01.dbf


Now open the database and query some random data.

SQL> alter database open;

Database altered.

SQL> select count(username) from dba_users;

COUNT(USERNAME)
---------------
             36

RMAN FULL database recovery scenerio

Full database recovery :

We will create a scenerio where we will drop all the datafiles and do a full database recovery.


  • Database name : ORCL
  • Assuming that you have already taken the full database backup (if not please take the full backup before your perform this test/task).
  • Database is running in archive-log mode, if not to have a consistent backup you need to shutdown the database open it in Mount mode and take a full backup.


-----------      If you haven't taken backup do so (please see below)   ------

[oracle@linux1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 7 20:20:55 2014

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

connected to target database: ORCL (DBID=1378116623)

RMAN>  backup database tag"full_backup";

Starting backup at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/datafiles/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/datafiles/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/datafiles/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/datafiles/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/datafiles/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-JUL-14
channel ORA_DISK_1: finished piece 1 at 07-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:51
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-JUL-14
channel ORA_DISK_1: finished piece 1 at 07-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_ncsnf_FULL_BACKUP_9vpkxymd_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUL-14


----  Oracle DB restore process -------

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 20:18:05 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2214456 bytes
Variable Size             369100232 bytes
Database Buffers           92274688 bytes
Redo Buffers                8241152 bytes

lets check name of database:

SQL> select name from v$database;

NAME
---------
ORCL

Check if the database is in archivelog mode;

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oraarch/
Oldest online log sequence     62
Next log sequence to archive   64
Current log sequence           64


list out all the data-files:

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  undotbs01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf        users01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  temp01.dbf

Now lets drop all the dbf files:

[oracle@linux1 orcl]$ rm *.dbf

lets verify that all files have been dropped :

[oracle@linux1 orcl]$ ls -al
total 368564
drwxr-x--- 2 oracle oinstall     4096 Jul  7 20:29 .
drwxr-xr-x 3 oracle oinstall     4096 Jun 15 20:22 ..
-rw-r----- 1 oracle oinstall  9912320 Jul  7 20:29 control01.ctl
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:25 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:29 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:20 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo04.log

Most times you will see that server is still open, even tough you see that database has crashed and you can't access datafile.
we will now do a full database recovery. since all the datafiles have been lost.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2214456 bytes
Variable Size             369100232 bytes
Database Buffers           92274688 bytes
Redo Buffers                8241152 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'



RMAN> run {
2> restore database;
3> recover database;
4> }

Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

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/datafiles/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/datafiles/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/datafiles/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/datafiles/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/datafiles/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 07-JUL-14

Starting recover at 07-JUL-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 07-JUL-14

Since the full database recovery has been completed successfully. we need to open the database now

SQL> alter database open;

Database altered.

verify that you can access the database without any errors.

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14


14 rows selected.