Oracle Technologies Blog

By ASKM

Exalogic Apps-to-Disk Monitoring Setup Using OEM12c

Posted by Srikrishna Murthy Annam on December 26, 2016

Remote DBA services and IT online training solutions for oracle technologies. We provide Virtual Labs from our cloud data center.

Source: Exalogic Apps-to-Disk Monitoring Setup Using OEM12c

Advertisements

Posted in Uncategorized | Leave a Comment »

Migrating Level-4 OEM to New Datacenter With Zero Downtime

Posted by Srikrishna Murthy Annam on December 16, 2016

Remote DBA services and IT online training solutions for oracle technologies. We provide Virtual Labs from our cloud data center.

Source: Migrating Level-4 OEM to New Datacenter With Zero Downtime

Posted in Uncategorized | Leave a Comment »

How To Restore Database From Different Incarnation

Posted by Srikrishna Murthy Annam on April 4, 2013

What is incarnation?
When we open database with RESETLOGS option, the database starts a new incarnation. This new incarnation resets the log sequence number to 1, and then gives the online redo logs a new time stamp and SCN.

When the database starts a new incarnation, are we able to use the old backups taken in previous incarnation for restore and recovery purpose as the present log sequence number is reset to 1.

The present article discusses about the details of how to use the backups in old incarnation for restore and recovery purpose after the database has been opened with resetlogs for many times.

I will explain the details considering a practical approach in our training environment. First i will explain the existing environment and available backups.
Then i will take you to details of how these backups are used to restore and recover to the same server (or to different server) considering that the database has been opened many times with resetlogs option.

Environment Details :
Let us assume that we have a training environment where we have some gold backups for the database. Whenever we do any modification to the database, we will take a new goldbackup and we will keep restoring this latest new backup every week twice( Thursday night and Saturday night).

Following Details shows the different goldbackups available for the training environment.

RMAN> list backup summary;List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — — – ———– ————— ——- ——- ———- —
327559  B  0  A SBT_TAPE    04-AUG-12       1       1       YES        NOEXPIRE
327560  B  0  A SBT_TAPE    04-AUG-12       1       1       YES        NOEXPIRE
327561  B  0  A SBT_TAPE    04-AUG-12       1       1       YES        NOEXPIRE
327562  B  0  A SBT_TAPE    04-AUG-12       1       1       YES        NOEXPIRE
327785  B  F  A SBT_TAPE    04-AUG-12       1       1       NO         NOEXPIRE
351357  B  0  A SBT_TAPE    13-OCT-12       1       1       YES        NOEXPIRE
351358  B  0  A SBT_TAPE    13-OCT-12       1       1       YES        NOEXPIRE
351359  B  0  A SBT_TAPE    13-OCT-12       1       1       YES        NOEXPIRE
351360  B  0  A SBT_TAPE    13-OCT-12       1       1       YES        NOEXPIRE
351583  B  F  A SBT_TAPE    13-OCT-12       1       1       NO         NOEXPIRE
355951  B  0  A SBT_TAPE    27-OCT-12       1       1       YES        NOEXPIRE
355952  B  0  A SBT_TAPE    27-OCT-12       1       1       YES        NOEXPIRE
355953  B  0  A SBT_TAPE    27-OCT-12       1       1       YES        NOEXPIRE
355954  B  0  A SBT_TAPE    27-OCT-12       1       1       YES        NOEXPIRE
356177  B  F  A SBT_TAPE    27-OCT-12       1       1       NO         NOEXPIRE
386046  B  0  A SBT_TAPE    26-JAN-13       1       1       YES        NOEXPIRE
386047  B  0  A SBT_TAPE    26-JAN-13       1       1       YES        NOEXPIRE
386048  B  0  A SBT_TAPE    26-JAN-13       1       1       YES        NOEXPIRE
386049  B  0  A SBT_TAPE    26-JAN-13       1       1       YES        NOEXPIRE
386275  B  F  A SBT_TAPE    26-JAN-13       1       1       NO         NOEXPIRE
387232  B  0  A SBT_TAPE    29-JAN-13       1       1       YES        NOEXPIRE
387233  B  0  A SBT_TAPE    29-JAN-13       1       1       YES        NOEXPIRE
387234  B  0  A SBT_TAPE    29-JAN-13       1       1       YES        NOEXPIRE
387235  B  0  A SBT_TAPE    29-JAN-13       1       1       YES        NOEXPIRE
387462  B  F  A SBT_TAPE    29-JAN-13       1       1       NO         NOEXPIRE
392057  B  0  A SBT_TAPE    09-FEB-13       1       1       YES        NOEXPIRE
392058  B  0  A SBT_TAPE    09-FEB-13       1       1       YES        NOEXPIRE
392059  B  0  A SBT_TAPE    09-FEB-13       1       1       YES        NOEXPIRE
392060  B  0  A SBT_TAPE    09-FEB-13       1       1       YES        NOEXPIRE
392286  B  F  A SBT_TAPE    09-FEB-13       1       1       NO         NOEXPIRE
392347  B  F  A SBT_TAPE    09-FEB-13       1       1       NO         TAG20130209T021024

RMAN>

So everytime when we restore the database from the latest gold backup, we will open the database with resetlogs option and it starts a new incarnation.
These details are shown below ..

RMAN> list incarnation of database TRNGDB;List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
——- ——- ——– —————- — ———- ———-
……
…….
158938  325435  TRNGDB   3679252776       ORPHAN  88080533256 29-JUL-12
158938  326880  TRNGDB   3679252776       PARENT  88080533256 02-AUG-12
158938  323440  TRNGDB   3679252776       ORPHAN  88080533256 26-JUL-12
158938  330059  TRNGDB   3679252776       ORPHAN  88080774587 09-AUG-12
158938  332058  TRNGDB   3679252776       ORPHAN  88080774587 12-AUG-12
158938  335023  TRNGDB   3679252776       ORPHAN  88080774587 23-AUG-12
158938  334177  TRNGDB   3679252776       ORPHAN  88080774587 19-AUG-12
158938  350823  TRNGDB   3679252776       PARENT  88080774587 11-OCT-12
158938  350133  TRNGDB   3679252776       ORPHAN  88080774587 07-OCT-12
158938  337328  TRNGDB   3679252776       ORPHAN  88080774587 26-AUG-12
158938  338003  TRNGDB   3679252776       ORPHAN  88080774587 30-AUG-12
158938  339685  TRNGDB   3679252776       ORPHAN  88080774587 02-SEP-12
158938  340363  TRNGDB   3679252776       ORPHAN  88080774587 06-SEP-12
158938  342063  TRNGDB   3679252776       ORPHAN  88080774587 09-SEP-12
158938  342743  TRNGDB   3679252776       ORPHAN  88080774587 13-SEP-12
158938  344429  TRNGDB   3679252776       ORPHAN  88080774587 16-SEP-12
158938  346103  TRNGDB   3679252776       ORPHAN  88080774587 23-SEP-12
158938  347243  TRNGDB   3679252776       ORPHAN  88080774587 27-SEP-12
158938  348499  TRNGDB   3679252776       ORPHAN  88080774587 30-SEP-12
158938  349183  TRNGDB   3679252776       ORPHAN  88080774587 04-OCT-12
158938  352901  TRNGDB   3679252776       ORPHAN  88081045250 18-OCT-12
158938  354443  TRNGDB   3679252776       ORPHAN  88081045250 21-OCT-12
158938  355286  TRNGDB   3679252776       PARENT  88081045250 25-OCT-12
158938  364387  TRNGDB   3679252776       ORPHAN  88081234176 22-NOV-12
158938  363526  TRNGDB   3679252776       ORPHAN  88081234176 18-NOV-12
158938  362107  TRNGDB   3679252776       ORPHAN  88081234176 15-NOV-12
158938  361247  TRNGDB   3679252776       ORPHAN  88081234176 11-NOV-12
158938  359827  TRNGDB   3679252776       ORPHAN  88081234176 08-NOV-12
158938  358965  TRNGDB   3679252776       ORPHAN  88081234176 04-NOV-12
158938  357545  TRNGDB   3679252776       ORPHAN  88081234176 01-NOV-12
158938  365807  TRNGDB   3679252776       ORPHAN  88081234176 25-NOV-12
158938  384807  TRNGDB   3679252776       PARENT  88081234176 24-JAN-13
158938  383953  TRNGDB   3679252776       ORPHAN  88081234176 20-JAN-13
158938  382547  TRNGDB   3679252776       ORPHAN  88081234176 17-JAN-13
158938  381687  TRNGDB   3679252776       ORPHAN  88081234176 13-JAN-13
158938  380267  TRNGDB   3679252776       ORPHAN  88081234176 10-JAN-13
158938  379411  TRNGDB   3679252776       ORPHAN  88081234176 06-JAN-13
158938  378007  TRNGDB   3679252776       ORPHAN  88081234176 03-JAN-13
158938  377152  TRNGDB   3679252776       ORPHAN  88081234176 30-DEC-12
158938  375747  TRNGDB   3679252776       ORPHAN  88081234176 27-DEC-12
158938  374892  TRNGDB   3679252776       ORPHAN  88081234176 23-DEC-12
158938  373487  TRNGDB   3679252776       ORPHAN  88081234176 20-DEC-12
158938  372631  TRNGDB   3679252776       ORPHAN  88081234176 16-DEC-12
158938  371227  TRNGDB   3679252776       ORPHAN  88081234176 13-DEC-12
158938  366667  TRNGDB   3679252776       ORPHAN  88081234176 29-NOV-12
158938  368086  TRNGDB   3679252776       ORPHAN  88081234176 02-DEC-12
158938  368947  TRNGDB   3679252776       ORPHAN  88081234176 06-DEC-12
158938  370366  TRNGDB   3679252776       ORPHAN  88081234176 09-DEC-12
158938  391514  TRNGDB   3679252776       PARENT  88082816786 07-FEB-13
158938  387719  TRNGDB   3679252776       ORPHAN  88082816786 01-FEB-13
158938  390618  TRNGDB   3679252776       ORPHAN  88082816786 03-FEB-13
158938  404336  TRNGDB   3679252776       ORPHAN  88082894636 21-FEB-13
158938  403425  TRNGDB   3679252776       ORPHAN  88082894636 17-FEB-13
158938  401240  TRNGDB   3679252776       ORPHAN  88082894636 14-FEB-13
158938  416836  TRNGDB   3679252776       CURRENT 88082894636 21-MAR-13
158938  412180  TRNGDB   3679252776       ORPHAN  88082894636 10-MAR-13
158938  409274  TRNGDB   3679252776       ORPHAN  88082894636 03-MAR-13
158938  415096  TRNGDB   3679252776       ORPHAN  88082894636 17-MAR-13
158938  413356  TRNGDB   3679252776       ORPHAN  88082894636 14-MAR-13
158938  406349  TRNGDB   3679252776       ORPHAN  88082894636 24-FEB-13
158938  407256  TRNGDB   3679252776       ORPHAN  88082894636 28-FEB-13
158938  410176  TRNGDB   3679252776       ORPHAN  88082894636 07-MAR-13

RMAN>

As long as i am restoring the latest goldbackups to the training environment, it uses the current incarnation and able to restore the latest backups and able to open the database with resetlogs option.

But if i want to restore any previous backups(backups taken at a different incarnation) to the training environment,i may not be able to restore the backups to the current incarnation.

Let us assume that i have my latest backups available on 09-OCT-2013 as per the above details and my current incarnation is 416836 (on 21-MAR-2013).
Now i have a requirement where i need to restore the backups available on 27-OCT-2012 to the same server. So as long as my current incarnation is 416836, i will not be able to use these backups on 27-OCT-2012 to restore the database.
So i need to first change the incarnation to be able to restore the backups on 27-OCT-2012.

For your easy understanding to identify to which incarnation to change, i have mixed backup details and incarnation details and orderded the details date wise below ….

158938  349183  TRNGDB   3679252776       ORPHAN  88080774587 04-OCT-12
13-OCT-12  <<<== Date on which we have Backups
158938  350133  TRNGDB   3679252776       ORPHAN  88080774587 07-OCT-12
158938  350823  TRNGDB   3679252776       PARENT  88080774587 11-OCT-12
158938  352901  TRNGDB   3679252776       ORPHAN  88081045250 18-OCT-12
158938  354443  TRNGDB   3679252776       ORPHAN  88081045250 21-OCT-12
158938  355286  TRNGDB   3679252776       PARENT  88081045250 25-OCT-12
27-OCT-12 <<<== Date on which we have  Backups
158938  357545  TRNGDB   3679252776       ORPHAN  88081234176 01-NOV-12
158938  358965  TRNGDB   3679252776       ORPHAN  88081234176 04-NOV-12
158938  359827  TRNGDB   3679252776       ORPHAN  88081234176 08-NOV-12
158938  361247  TRNGDB   3679252776       ORPHAN  88081234176 11-NOV-12
158938  362107  TRNGDB   3679252776       ORPHAN  88081234176 15-NOV-12
158938  363526  TRNGDB   3679252776       ORPHAN  88081234176 18-NOV-12
158938  364387  TRNGDB   3679252776       ORPHAN  88081234176 22-NOV-12
158938  365807  TRNGDB   3679252776       ORPHAN  88081234176 25-NOV-12
158938  366667  TRNGDB   3679252776       ORPHAN  88081234176 29-NOV-12

When a database goes through multiple incarnations, some backups can become orphaned. The simple way to find non-orphaned backups and orphaned backups are to form a table to list the incarnation number and orphaned and non-orphaned backups to each incarnation.
In our case, in simple words, we need to restore backups from 27-OCT-12. So identify the incarnation number for which this backup is non-orphaned.

I am considering the incarnation before and after the 27-OCT-12 to identify orphaned and non-orphaned backups. We have incarnations 357545(on 01-NOV-12)  and 355286 (25-OCT-12).

For incarnation 357545(on 01-NOV-12) : All the backups taken after the date 01-NOV-2012 are non-orphaned and all the backups before this incarnation are orphaned backups assuming that there is no other incarnation after the incarnation 357545. So in our case the backups on 27-OCT-2012 are orphaned backups if i set incarnation 357545.
For incarnation 355286(on 25-OCT-12) : All the backups taken after the date 25-OCT-2012 are non-orphaned and all the backups before this incarnation are orphaned backups assuming that there is no other incarnation after the incarnation 355286. So in our case the backups on 27-OCT-2012 are non-orphaned backups if i set incarnation 355286.

Now we have the details, which incarnation we need to set for restoring the 27-OCT-2012 backups.

Also get the controlfile information from the 27-OCT-2012 backups using

RMAN> list backup of controlfile;

NOTE : In our case the controlfile details are ‘4vnoosbv_1_1’

Then use the following syntax to restore and recover the database…

RMAN> set dbid=3679252776executing command: SET DBID
database name is “TRNGDB” and DBID is 3679252776

RMAN> RESET DATABASE TO INCARNATION 355286;

database reset to incarnation 355286

RMAN>  run {
2> allocate channel t1 type ‘SBT_TAPE’ parms=”ENV=(TDPO_OPTFILE=/tmp/askm/TRNGDB_tdpo.opt)”;
3> restore controlfile from ‘4vnoosbv_1_1’;
4> }

allocated channel: t1
channel t1: SID=396 device type=SBT_TAPE
channel t1: Data Protection for Oracle: version 5.5.2.0

Starting restore at 22-MAR-13

channel t1: restoring control file
channel t1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/TRNGDB/control01.ctl
output file name=/u01/oradata/TRNGDB/control02.ctl
output file name=/u01/oradata/TRNGDB/control03.ctl
Finished restore at 22-MAR-13
released channel: t1

RMAN> run {
2> allocate channel t1 type ‘SBT_TAPE’ parms=”ENV=(TDPO_OPTFILE=/tmp/askm/TRNGDB_tdpo.opt)”;
3> restore controlfile from ‘4vnoosbv_1_1’;
4> alter database mount;
5> restore database;
6> recover database noredo;
7> release channel t1;
8> }

allocated channel: t1
channel t1: SID=396 device type=SBT_TAPE
channel t1: Data Protection for Oracle: version 5.5.2.0

Starting restore at 22-MAR-13

channel t1: restoring control file
channel t1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/TRNGDB/control01.ctl
output file name=/u01/oradata/TRNGDB/control02.ctl
output file name=/u01/oradata/TRNGDB/control03.ctl
Finished restore at 22-MAR-13

database mounted

Starting restore at 22-MAR-13

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00003 to /u01/oradata/TRNGDB/sysaux01.dbf
channel t1: restoring datafile 00004 to /u01/oradata/TRNGDB/psdefault.dbf
channel t1: restoring datafile 00009 to /u01/oradata/TRNGDB/aalarge.dbf
channel t1: restoring datafile 00011 to /u01/oradata/TRNGDB/adapp.dbf
channel t1: restoring datafile 00012 to /u01/oradata/TRNGDB/adapp001002.dbf
channel t1: restoring datafile 00014 to /u01/oradata/TRNGDB/avapp.dbf
channel t1: restoring datafile 00015 to /u01/oradata/TRNGDB/avapp001002.dbf
channel t1: restoring datafile 00017 to /u01/oradata/TRNGDB/avapp001004.dbf
channel t1: restoring datafile 00022 to /u01/oradata/TRNGDB/ccapp.dbf
channel t1: restoring datafile 00030 to /u01/oradata/TRNGDB/cularg3.dbf
channel t1: restoring datafile 00031 to /u01/oradata/TRNGDB/diapp.dbf
channel t1: restoring datafile 00036 to /u01/oradata/TRNGDB/faapp001002.dbf
channel t1: restoring datafile 00038 to /u01/oradata/TRNGDB/fgapp.dbf
channel t1: restoring datafile 00041 to /u01/oradata/TRNGDB/giapp.dbf
channel t1: restoring datafile 00043 to /u01/oradata/TRNGDB/glapp.dbf
channel t1: restoring datafile 00047 to /u01/oradata/TRNGDB/hpapp001002.dbf
channel t1: restoring datafile 00054 to /u01/oradata/TRNGDB/hrapp4001002.dbf
channel t1: restoring datafile 00063 to /u01/oradata/TRNGDB/hrwork001002.dbf
channel t1: restoring datafile 00065 to /u01/oradata/TRNGDB/htapp001002.dbf
channel t1: restoring datafile 00067 to /u01/oradata/TRNGDB/inlarge.dbf
channel t1: restoring datafile 00068 to /u01/oradata/TRNGDB/paapp.dbf
channel t1: restoring datafile 00069 to /u01/oradata/TRNGDB/paapp001001.dbf
channel t1: restoring datafile 00070 to /u01/oradata/TRNGDB/palarge.dbf
channel t1: restoring datafile 00077 to /u01/oradata/TRNGDB/piwork001002.dbf
channel t1: restoring datafile 00081 to /u01/oradata/TRNGDB/psindx001002.dbf
channel t1: restoring datafile 00085 to /u01/oradata/TRNGDB/psindx001006.dbf
channel t1: restoring datafile 00086 to /u01/oradata/TRNGDB/psindx001007.dbf
channel t1: restoring datafile 00089 to /u01/oradata/TRNGDB/psindx001010.dbf
channel t1: restoring datafile 00098 to /u01/oradata/TRNGDB/ptcmstar.dbf
channel t1: restoring datafile 00100 to /u01/oradata/TRNGDB/ptprc.dbf
channel t1: restoring datafile 00107 to /u01/oradata/TRNGDB/pttlrg001002.dbf
channel t1: restoring datafile 00115 to /u01/oradata/TRNGDB/pywork.dbf
channel t1: restoring datafile 00121 to /u01/oradata/TRNGDB/saapp001005.dbf
channel t1: restoring datafile 00125 to /u01/oradata/TRNGDB/salarge.dbf
channel t1: restoring datafile 00136 to /u01/oradata/TRNGDB/tlapp001002.dbf
channel t1: restoring datafile 00140 to /u01/oradata/TRNGDB/tlwork001001.dbf
channel t1: restoring datafile 00142 to /u01/oradata/TRNGDB/waapp.dbf
channel t1: restoring datafile 00147 to /u01/oradata/TRNGDB/rbks001002.dbf
channel t1: restoring datafile 00149 to /u01/oradata/TRNGDB/epapp001001.dbf
channel t1: restoring datafile 00161 to /u01/oradata/TRNGDB/eoeiapp001001.dbf
channel t1: restoring datafile 00162 to /u01/oradata/TRNGDB/eodsapp001001.dbf
channel t1: restoring datafile 00166 to /u01/oradata/TRNGDB/hrslarge001001.dbf
channel t1: restoring datafile 00170 to /u01/oradata/TRNGDB/eopplrg001001.dbf
channel t1: restoring datafile 00174 to /u01/oradata/TRNGDB/eocuapp001001.dbf
channel t1: restoring datafile 00178 to /u01/oradata/TRNGDB/eoeilrg001001.dbf
channel t1: restoring datafile 00182 to /u01/oradata/TRNGDB/eoiuwrk001001.dbf
channel t1: restoring datafile 00186 to /u01/oradata/TRNGDB/erwork001001.dbf
channel t1: restoring datafile 00190 to /u01/oradata/TRNGDB/hrlarg1001001.dbf
channel t1: restoring datafile 00192 to /u01/oradata/TRNGDB/psimgr001001.dbf
channel t1: restoring datafile 00196 to /u01/oradata/TRNGDB/pswork001001.dbf
channel t1: restoring datafile 00200 to /u01/oradata/TRNGDB/hrapp001004.dbf
channel t1: restoring datafile 00203 to /u01/oradata/TRNGDB/clnd001001.dbf
channel t1: restoring datafile 00205 to /u01/oradata/TRNGDB/clnd001002
channel t1: reading from backup piece backup_TRNGDB_3679252776_set155_piece1_copy1_20121027_4rnoorig_1_1
channel t1: piece handle=backup_TRNGDB_3679252776_set155_piece1_copy1_20121027_4rnoorig_1_1 tag=NOEXPIRE

..
….
RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit

So we are able to successfully restore the backups from a different incarnation. The same procedure can be applied if you plan to restore the database to a different server as long as the tsm configuration is properly done.

Hope It Helps

SRI

Posted in RMAN | Tagged: , , , | Leave a Comment »

Troubleshooting RAC Services Startup

Posted by Srikrishna Murthy Annam on April 3, 2013

Recently we had a DR(Disaster Recovery) testing going on and we had RAC replica at the DR site for DR testing. The RAC servers are copied to DR setup, bit by bit. Then tried to start the RAC services at DR site and could not start them due to some issues.

Then I tried to troubleshoot the issue …..

This article gives you the troubleshooting steps for this particular issue. These steps may also help you to troubleshoot if you have issues starting RAC services.

STEP 1: Reboot the rac servers and Check services after re-booting the servers. The services are still down.

STEP 2: Verify storage on both rac servers

[grid@askmdbrac01 ~]$ /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[grid@askmdbrac01 ~]$ /etc/init.d/oracleasm listdisks
CRSVOL1
DISK1
FRADISK1
[grid@askmdbrac01 ~]$ multipath -ll
-bash: multipath: command not found
[grid@askmdbrac01 ~]$ exit
logout
[oracle@askmdbrac01 ~]$ sudo su –
[root@askmdbrac01 ~]# multipath -ll
vpath2 (360050768019280fb280000000000055e) dm-10 IBM,2145
[size=200G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=50][active]
\_ 2:0:1:2 sdf        8:80  [active][ready]
\_ 3:0:1:2 sdl        8:176 [active][ready]
\_ round-robin 0 [prio=10][enabled]
\_ 2:0:0:2 sdc        8:32  [active][ready]
\_ 3:0:0:2 sdi        8:128 [active][ready]
vpath1 (360050768019280fb280000000000055d) dm-12 IBM,2145
[size=200G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=50][active]
\_ 3:0:0:1 sdh        8:112 [active][ready]
\_ round-robin 0 [prio=10][enabled]
\_ 2:0:1:1 sde        8:64  [active][ready]
\_ 3:0:1:1 sdk        8:160 [active][ready]
vpath0 (360050768019280fb280000000000055c) dm-11 IBM,2145
[size=10G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=50][active]
\_ 2:0:1:0 sdd        8:48  [active][ready]
\_ 3:0:1:0 sdj        8:144 [active][ready]
\_ round-robin 0 [prio=10][enabled]
\_ 3:0:0:0 sdg        8:96  [active][ready]
[root@askmdbrac01 ~]# exit[grid@askmdbrac02 ~]$ /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[grid@askmdbrac02 ~]$ /etc/init.d/oracleasm listdisks
CRSVOL1
DISK1
FRADISK1
[grid@askmdbrac02 ~]$ multipath -ll
-bash: multipath: command not found
[grid@askmdbrac02 ~]$ exit
logout
[oracle@askmdbrac02 ~]$ sudo su –
[root@askmdbrac02 ~]# multipath -ll
vpath2 (360050768019280fb280000000000055e) dm-10 IBM,2145
[size=200G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=50][active]
\_ 2:0:1:2 sdf        8:80  [active][ready]
\_ 3:0:0:2 sdi        8:128 [active][ready]
\_ round-robin 0 [prio=10][enabled]
\_ 2:0:0:2 sdc        8:32  [active][ready]
\_ 3:0:1:2 sdl        8:176 [active][ready]
vpath1 (360050768019280fb280000000000055d) dm-12 IBM,2145
[size=200G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=50][active]
\_ 3:0:1:1 sdk        8:160 [active][ready]
\_ round-robin 0 [prio=10][enabled]
\_ 2:0:1:1 sde        8:64  [active][ready]
\_ 3:0:0:1 sdh        8:112 [active][ready]
vpath0 (360050768019280fb280000000000055c) dm-11 IBM,2145
[size=10G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=50][active]
\_ 2:0:1:0 sdd        8:48  [active][ready]
\_ 3:0:0:0 sdg        8:96  [active][ready]
\_ round-robin 0 [prio=10][enabled]
\_ 3:0:1:0 sdj        8:144 [active][ready]
[root@askmdbrac02 ~]#

Observation : Storage is fine and i am able to see ASM disks from both the nodes. Storage is configured with multipathing. I also verified multipathing and status shows fine.

STEP 3: Check high availability services and cluster ready services on both nodes with the following commands

[grid@askmdbrac01 ~]$ crsctl check has
CRS-4638: Oracle High Availability Services is online
[grid@askmdbrac01 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager
[grid@askmdbrac01 ~]$
[grid@askmdbrac01 ~]$ crsctl check cluster
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager
[grid@askmdbrac01 ~]$[grid@askmdbrac02 ~]$ crsctl check has
CRS-4638: Oracle High Availability Services is online
[grid@askmdbrac02 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager
[grid@askmdbrac02 ~]$
[grid@askmdbrac02 ~]$ crsctl check cluster
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager
[grid@askmdbrac02 ~]$

Observation : The high availability services are up , but crs services are down.

STEP 4: Do ping and nslookup for other rac nodes from each node to make sure that each node in cluster is accessible from every other node. Check this for private ips, virtual ips and for scan ips.

Observation : The ping and nslookup are working fine and all the nodes are reachable from every other node.

STEP 5 : Then verify the log files from grid infrastructure and see if you can find any error messages from log files. The cluster log files are located at $ORACLE_HOME/log/askmdbrac01 and $ORACLE_HOME/log/askmdbrac02.

[cssd(11175)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /o001/home/11.2.0.2/grid/log/askmdbrac01/cssd/ocssd.log
2013-04-02 19:38:27.425
[ohasd(6876)]CRS-2765:Resource ‘ora.diskmon’ has failed on server ‘askmdbrac01’.
2013-04-02 19:38:39.988
[cssd(11175)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /o001/home/11.2.0.2/grid/log/askmdbrac01/cssd/ocssd.log
2013-04-02 19:38:55.007
[cssd(11175)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /o001/home/11.2.0.2/grid/log/askmdbrac01/cssd/ocssd.log
2013-04-02 19:39:10.025
…..
…..
[cssd(11175)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /o001/home/11.2.0.2/grid/log/askmdbrac01/cssd/ocssd.log
2013-04-02 19:48:23.836
[/o001/home/11.2.0.2/grid/bin/cssdagent(11163)]CRS-5818:Aborted command ‘start for resource: ora.cssd 1 1’ for resource ‘ora.cssd’. Details at (:CRSAGF00113:) {0:27:4} in /o001/home/11.2.0.2/grid/log/askmdbrac01/agent/ohasd/oracssdagent_root/oracssdagent_root.log.
2013-04-02 19:48:23.837
[cssd(11175)]CRS-1656:The CSS daemon is terminating due to a fatal error; Details at (:CSSSC00012:) in /o001/home/11.2.0.2/grid/log/askmdbrac01/cssd/ocssd.log
2013-04-02 19:48:23.838
[cssd(11175)]CRS-1603:CSSD on node askmdbrac01 shutdown by user.
2013-04-02 19:48:25.730
[cssd(11175)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /o001/home/11.2.0.2/grid/log/askmdbrac01/cssd/ocssd.log
2013-04-02 19:48:29.863
[ohasd(6876)]CRS-2765:Resource ‘ora.cssdmonitor’ has failed on server ‘askmdbrac01’.
2013-04-02 19:48:30.126
[ohasd(6876)]CRS-2771:Maximum restart attempts reached for resource ‘ora.cssd’; will not restart.
2013-04-02 19:48:35.833
[ohasd(6876)]CRS-2765:Resource ‘ora.diskmon’ has failed on server ‘askmdbrac01’.

Observation : Found from the cluster alert log file (alertaskmdbrac01.log) that the cluster services can’t find voting disk. So it could be due the in-accessible asm storage or due to currupted voting disks.

Then i tried to see if i can see voting disks through command.

SETP 6 :  Check the voting disks availability ( 11gR2 has voting disks in asm. I don’t have asm instance up now. So i will get error if i try to query the voting disks on asm)

[root@askmdbrac01 ~]# /o001/home/11.2.0.2/grid/bin/crsctl query css votedisk
Unable to communicate with the Cluster Synchronization Services daemon.
[root@askmdbrac01 ~]#[grid@askmdbrac02 ~]$ /o001/home/11.2.0.2/grid/bin/crsctl query css votedisk
Unable to communicate with the Cluster Synchronization Services daemon.
[grid@askmdbrac02 ~]$

Observation : I am not able to query the voting disks. Now i have only one option to look at the asm storage accessibility.

STEP 7 :  Tried re-enabling the asmlibs as follows on both the nodes …

[root@askmdbrac01 ~]# /etc/init.d/oracleasm restart
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]
[root@askmdbrac01 ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no
[root@askmdbrac01 ~]# /etc/init.d/oracleasm start
[root@askmdbrac01 ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no
[root@askmdbrac01 ~]# /etc/init.d/oracleasm enable
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@askmdbrac01 ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@askmdbrac01 ~]# /etc/init.d/oracleasm listdisks
CRSVOL1
DISK1
FRADISK1
[root@askmdbrac01 ~]#[root@askmdbrac02 ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@askmdbrac02 ~]# /etc/init.d/oracleasm restart
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@askmdbrac02 ~]#  /etc/init.d/oracleasm listdisks
CRSVOL1
DISK1
FRADISK1
[root@askmdbrac02 ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@askmdbrac02 ~]#

Observation : Not able to start services even after restarting the asmlib.

STEP 8 : Tried to re-configure the asmlib as below …..

[root@askmdbrac01 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets (‘[]’).  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [y]:
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@askmdbrac01 ~]#
[root@askmdbrac01 ~]# /etc/init.d/oracleasm stop
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [FAILED]
[root@askmdbrac01 ~]# /etc/init.d/oracleasm start
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@askmdbrac01 ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@askmdbrac01 ~]#

[root@askmdbrac02 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets (‘[]’).  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [y]:
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@askmdbrac02 ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@askmdbrac02 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets (‘[]’).  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface [grid]:
Default group to own the driver interface [asmadmin]:
Start Oracle ASM library driver on boot (y/n) [y]:
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@askmdbrac02 ~]#
[root@askmdbrac02 ~]# /etc/init.d/oracleasm stop
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [FAILED]
[root@askmdbrac02 ~]# /etc/init.d/oracleasm start
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@askmdbrac02 ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@askmdbrac02 ~]#

Observation : Identified that the asm library drivers configuration is wrong and i re-configured with correct user and group. See the difference in the lines when it is asking for prompt on second node. I executed this command twice to show you the difference when it is properly configured and when it is not properly configured.

STEP 9 : Stop and Start cluster services on both the nodes.

[root@askmdbrac01 ~]#  /o001/home/11.2.0.2/grid/bin/crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘askmdbrac01’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.racdb.racdb_service.svc’ on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘askmdbrac01’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘askmdbrac01’ succeeded
CRS-2677: Stop of ‘ora.LISTENER_SCAN1.lsnr’ on ‘askmdbrac01’ succeeded
CRS-2673: Attempting to stop ‘ora.scan1.vip’ on ‘askmdbrac01’
CRS-2677: Stop of ‘ora.scan1.vip’ on ‘askmdbrac01’ succeeded
CRS-2672: Attempting to start ‘ora.scan1.vip’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.racdb.racdb_service.svc’ on ‘askmdbrac01’ succeeded
CRS-2673: Attempting to stop ‘ora.askmdbrac01.vip’ on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.CRS.dg’ on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.racdb.db’ on ‘askmdbrac01’
CRS-2677: Stop of ‘ora.askmdbrac01.vip’ on ‘askmdbrac01’ succeeded
CRS-2672: Attempting to start ‘ora.askmdbrac01.vip’ on ‘askmdbrac02’
CRS-2676: Start of ‘ora.scan1.vip’ on ‘askmdbrac02’ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN1.lsnr’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.racdb.db’ on ‘askmdbrac01’ succeeded
CRS-2673: Attempting to stop ‘ora.FRA.dg’ on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.PRDSHR_DATA.dg’ on ‘askmdbrac01’
CRS-2676: Start of ‘ora.askmdbrac01.vip’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.PRDSHR_DATA.dg’ on ‘askmdbrac01’ succeeded
CRS-2676: Start of ‘ora.LISTENER_SCAN1.lsnr’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.FRA.dg’ on ‘askmdbrac01’ succeeded
CRS-2677: Stop of ‘ora.CRS.dg’ on ‘askmdbrac01’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘askmdbrac01’
CRS-2677: Stop of ‘ora.asm’ on ‘askmdbrac01’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘askmdbrac01’
CRS-2677: Stop of ‘ora.ons’ on ‘askmdbrac01’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘askmdbrac01’
CRS-2677: Stop of ‘ora.net1.network’ on ‘askmdbrac01’ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘askmdbrac01’ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘askmdbrac01’ succeeded
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.crf’ on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.asm’ on ‘askmdbrac01’
CRS-2677: Stop of ‘ora.mdnsd’ on ‘askmdbrac01’ succeeded
CRS-2677: Stop of ‘ora.crf’ on ‘askmdbrac01’ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘askmdbrac01’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘askmdbrac01’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘askmdbrac01’
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘askmdbrac01’ succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘askmdbrac01’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘askmdbrac01’
CRS-2677: Stop of ‘ora.cssd’ on ‘askmdbrac01’ succeeded
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘askmdbrac01’
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘askmdbrac01’
CRS-2677: Stop of ‘ora.gipcd’ on ‘askmdbrac01’ succeeded
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘askmdbrac01’
CRS-2677: Stop of ‘ora.gpnpd’ on ‘askmdbrac01’ succeeded
CRS-2677: Stop of ‘ora.diskmon’ on ‘askmdbrac01’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘askmdbrac01’ has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@askmdbrac01 ~]# /o001/home/11.2.0.2/grid/bin/crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
[root@askmdbrac01 ~]#[root@askmdbrac02 ~]# /o001/home/11.2.0.2/grid/bin/crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘askmdbrac02’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.askmdbrac02.vip’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.oc4j’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN3.lsnr’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.cvu’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN2.lsnr’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.CRS.dg’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.racdb.racdb_service.svc’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.askmdbrac02.vip’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.racdb.racdb_service.svc’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.racdb.db’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.LISTENER_SCAN1.lsnr’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.scan1.vip’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.askmdbrac01.vip’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.scan1.vip’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.LISTENER_SCAN2.lsnr’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.scan2.vip’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.askmdbrac01.vip’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.LISTENER_SCAN3.lsnr’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.scan3.vip’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.scan2.vip’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.scan3.vip’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.racdb.db’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.FRA.dg’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.PRDSHR_DATA.dg’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.FRA.dg’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.PRDSHR_DATA.dg’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.oc4j’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.cvu’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.CRS.dg’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.asm’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.ons’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.net1.network’ on ‘askmdbrac02’ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘askmdbrac02’ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.asm’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.evmd’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.mdnsd’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.cssd’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘askmdbrac02’
CRS-2673: Attempting to stop ‘ora.crf’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.crf’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.diskmon’ on ‘askmdbrac02’ succeeded
CRS-2677: Stop of ‘ora.gipcd’ on ‘askmdbrac02’ succeeded
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘askmdbrac02’
CRS-2677: Stop of ‘ora.gpnpd’ on ‘askmdbrac02’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘askmdbrac02’ has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@askmdbrac02 ~]# /o001/home/11.2.0.2/grid/bin/crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
[root@askmdbrac02 ~]#

Observation : Cluster ready services started without any issues. Now i need to verify the services status.

STEP 10 :  Verify the status.

[root@askmdbrac01 ~]# /o001/home/11.2.0.2/grid/bin/crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@askmdbrac01 ~]# /o001/home/11.2.0.2/grid/bin/crs_stat -t
Name           Type           Target    State     Host
————————————————————
ora.CRS.dg     ora….up.type ONLINE    ONLINE    askmdbrac01
ora.FRA.dg     ora….up.type ONLINE    ONLINE    askmdbrac01
ora….ER.lsnr ora….er.type ONLINE    ONLINE    askmdbrac01
ora….N1.lsnr ora….er.type ONLINE    OFFLINE
ora….N2.lsnr ora….er.type ONLINE    ONLINE    askmdbrac01
ora….N3.lsnr ora….er.type ONLINE    ONLINE    askmdbrac01
ora….DATA.dg ora….up.type ONLINE    ONLINE    askmdbrac01
ora.asm        ora.asm.type   ONLINE    ONLINE    askmdbrac01
ora.cvu        ora.cvu.type   ONLINE    ONLINE    askmdbrac01
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora….network ora….rk.type ONLINE    ONLINE    askmdbrac01
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    askmdbrac01
ora.ons        ora.ons.type   ONLINE    ONLINE    askmdbrac01
ora….SM1.asm application    ONLINE    ONLINE    askmdbrac01
ora….01.lsnr application    ONLINE    ONLINE    askmdbrac01
ora….l01.gsd application    OFFLINE   OFFLINE
ora….l01.ons application    ONLINE    ONLINE    askmdbrac01
ora….l01.vip ora….t1.type ONLINE    ONLINE    askmdbrac01
ora….SM2.asm application    ONLINE    ONLINE    askmdbrac02
ora….02.lsnr application    ONLINE    OFFLINE
ora….l02.gsd application    OFFLINE   OFFLINE
ora….l02.ons application    ONLINE    OFFLINE
ora….l02.vip ora….t1.type ONLINE    OFFLINE
ora….ry.acfs ora….fs.type ONLINE    OFFLINE
ora.scan1.vip  ora….ip.type ONLINE    OFFLINE
ora.scan2.vip  ora….ip.type ONLINE    ONLINE    askmdbrac01
ora.scan3.vip  ora….ip.type ONLINE    ONLINE    askmdbrac01
ora.racdb.db ora….se.type ONLINE    ONLINE    askmdbrac01
ora….ice.svc ora….ce.type ONLINE    ONLINE    askmdbrac01
[root@askmdbrac01 ~]# sudo su – grid
[grid@askmdbrac01 ~]$ . ./profile.grid
[grid@askmdbrac01 ~]$ Check_CRS.txt
HA Resource                                        Target     State
———–                                        ——     —–
ora.CRS.dg                                         ONLINE     ONLINE on askmdbrac01
ora.FRA.dg                                         ONLINE     ONLINE on askmdbrac01
ora.LISTENER.lsnr                                  ONLINE     ONLINE on askmdbrac01
ora.LISTENER_SCAN1.lsnr                            ONLINE     ONLINE on askmdbrac02
ora.LISTENER_SCAN2.lsnr                            ONLINE     ONLINE on askmdbrac01
ora.LISTENER_SCAN3.lsnr                            ONLINE     ONLINE on askmdbrac01
ora.PRDSHR_DATA.dg                                 ONLINE     ONLINE on askmdbrac01
ora.asm                                            ONLINE     ONLINE on askmdbrac01
ora.cvu                                            ONLINE     ONLINE on askmdbrac01
ora.gsd                                            OFFLINE    OFFLINE
ora.net1.network                                   ONLINE     ONLINE on askmdbrac01
ora.oc4j                                           ONLINE     ONLINE on askmdbrac01
ora.ons                                            ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.ASM1.asm                           ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.LISTENER_askmdbrac01.lsnr          ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.gsd                                OFFLINE    OFFLINE
ora.askmdbrac01.ons                                ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.vip                                ONLINE     ONLINE on askmdbrac01
ora.askmdbrac02.ASM2.asm                           ONLINE     ONLINE on askmdbrac02
ora.askmdbrac02.LISTENER_askmdbrac02.lsnr          ONLINE     ONLINE on askmdbrac02
ora.askmdbrac02.gsd                                OFFLINE    OFFLINE
ora.askmdbrac02.ons                                ONLINE     ONLINE on askmdbrac02
ora.askmdbrac02.vip                                ONLINE     ONLINE on askmdbrac02
ora.registry.acfs                                  ONLINE     OFFLINE
ora.scan1.vip                                      ONLINE     ONLINE on askmdbrac02
ora.scan2.vip                                      ONLINE     ONLINE on askmdbrac01
ora.scan3.vip                                      ONLINE     ONLINE on askmdbrac01
ora.racdb.db                                     ONLINE     ONLINE on askmdbrac01
ora.racdb.racdb_service.svc                    ONLINE     ONLINE on askmdbrac02
[grid@askmdbrac01 ~]$ Check_CRS.txt
HA Resource                                        Target     State
———–                                        ——     —–
ora.CRS.dg                                         ONLINE     ONLINE on askmdbrac01
ora.FRA.dg                                         ONLINE     ONLINE on askmdbrac01
ora.LISTENER.lsnr                                  ONLINE     ONLINE on askmdbrac01
ora.LISTENER_SCAN1.lsnr                            ONLINE     ONLINE on askmdbrac02
ora.LISTENER_SCAN2.lsnr                            ONLINE     ONLINE on askmdbrac01
ora.LISTENER_SCAN3.lsnr                            ONLINE     ONLINE on askmdbrac01
ora.PRDSHR_DATA.dg                                 ONLINE     ONLINE on askmdbrac01
ora.asm                                            ONLINE     ONLINE on askmdbrac01
ora.cvu                                            ONLINE     ONLINE on askmdbrac01
ora.gsd                                            OFFLINE    OFFLINE
ora.net1.network                                   ONLINE     ONLINE on askmdbrac01
ora.oc4j                                           ONLINE     ONLINE on askmdbrac01
ora.ons                                            ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.ASM1.asm                           ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.LISTENER_askmdbrac01.lsnr          ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.gsd                                OFFLINE    OFFLINE
ora.askmdbrac01.ons                                ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.vip                                ONLINE     ONLINE on askmdbrac01
ora.askmdbrac02.ASM2.asm                           ONLINE     ONLINE on askmdbrac02
ora.askmdbrac02.LISTENER_askmdbrac02.lsnr          ONLINE     ONLINE on askmdbrac02
ora.askmdbrac02.gsd                                OFFLINE    OFFLINE
ora.askmdbrac02.ons                                ONLINE     ONLINE on askmdbrac02
ora.askmdbrac02.vip                                ONLINE     ONLINE on askmdbrac02
ora.registry.acfs                                  ONLINE     OFFLINE
ora.scan1.vip                                      ONLINE     ONLINE on askmdbrac02
ora.scan2.vip                                      ONLINE     ONLINE on askmdbrac01
ora.scan3.vip                                      ONLINE     ONLINE on askmdbrac01
ora.racdb.db                                     ONLINE     ONLINE on askmdbrac01
ora.racdb.racdb_service.svc                    ONLINE     ONLINE on askmdbrac02
[grid@askmdbrac01 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node askmdbrac01
Instance racdb2 is running on node askmdbrac02
[grid@askmdbrac01 ~]$[root@askmdbrac02 ~]# /o001/home/11.2.0.2/grid/bin/crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@askmdbrac02 ~]# /o001/home/11.2.0.2/grid/bin/crs_stat -t
Name           Type           Target    State     Host
————————————————————
ora.CRS.dg     ora….up.type ONLINE    ONLINE    askmdbrac01
ora.FRA.dg     ora….up.type ONLINE    ONLINE    askmdbrac01
ora….ER.lsnr ora….er.type ONLINE    ONLINE    askmdbrac01
ora….N1.lsnr ora….er.type ONLINE    ONLINE    askmdbrac02
ora….N2.lsnr ora….er.type ONLINE    ONLINE    askmdbrac01
ora….N3.lsnr ora….er.type ONLINE    ONLINE    askmdbrac01
ora….DATA.dg ora….up.type ONLINE    ONLINE    askmdbrac01
ora.asm        ora.asm.type   ONLINE    ONLINE    askmdbrac01
ora.cvu        ora.cvu.type   ONLINE    ONLINE    askmdbrac01
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora….network ora….rk.type ONLINE    ONLINE    askmdbrac01
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    askmdbrac01
ora.ons        ora.ons.type   ONLINE    ONLINE    askmdbrac01
ora….SM1.asm application    ONLINE    ONLINE    askmdbrac01
ora….01.lsnr application    ONLINE    ONLINE    askmdbrac01
ora….l01.gsd application    OFFLINE   OFFLINE
ora….l01.ons application    ONLINE    ONLINE    askmdbrac01
ora….l01.vip ora….t1.type ONLINE    ONLINE    askmdbrac01
ora….SM2.asm application    ONLINE    ONLINE    askmdbrac02
ora….02.lsnr application    ONLINE    ONLINE    askmdbrac02
ora….l02.gsd application    OFFLINE   OFFLINE
ora….l02.ons application    ONLINE    ONLINE    askmdbrac02
ora….l02.vip ora….t1.type ONLINE    ONLINE    askmdbrac02
ora….ry.acfs ora….fs.type ONLINE    OFFLINE
ora.scan1.vip  ora….ip.type ONLINE    ONLINE    askmdbrac02
ora.scan2.vip  ora….ip.type ONLINE    ONLINE    askmdbrac01
ora.scan3.vip  ora….ip.type ONLINE    ONLINE    askmdbrac01
ora.racdb.db ora….se.type ONLINE    ONLINE    askmdbrac01
ora….ice.svc ora….ce.type ONLINE    ONLINE    askmdbrac01
[root@askmdbrac02 ~]# sudo su – grid
[grid@askmdbrac02 ~]$ . ./profile.grid
[grid@askmdbrac02 ~]$ Check_CRS.txt
HA Resource                                        Target     State
———–                                        ——     —–
ora.CRS.dg                                         ONLINE     ONLINE on askmdbrac01
ora.FRA.dg                                         ONLINE     ONLINE on askmdbrac01
ora.LISTENER.lsnr                                  ONLINE     ONLINE on askmdbrac01
ora.LISTENER_SCAN1.lsnr                            ONLINE     ONLINE on askmdbrac02
ora.LISTENER_SCAN2.lsnr                            ONLINE     ONLINE on askmdbrac01
ora.LISTENER_SCAN3.lsnr                            ONLINE     ONLINE on askmdbrac01
ora.PRDSHR_DATA.dg                                 ONLINE     ONLINE on askmdbrac01
ora.asm                                            ONLINE     ONLINE on askmdbrac01
ora.cvu                                            ONLINE     ONLINE on askmdbrac01
ora.gsd                                            OFFLINE    OFFLINE
ora.net1.network                                   ONLINE     ONLINE on askmdbrac01
ora.oc4j                                           ONLINE     ONLINE on askmdbrac01
ora.ons                                            ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.ASM1.asm                           ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.LISTENER_askmdbrac01.lsnr          ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.gsd                                OFFLINE    OFFLINE
ora.askmdbrac01.ons                                ONLINE     ONLINE on askmdbrac01
ora.askmdbrac01.vip                                ONLINE     ONLINE on askmdbrac01
ora.askmdbrac02.ASM2.asm                           ONLINE     ONLINE on askmdbrac02
ora.askmdbrac02.LISTENER_askmdbrac02.lsnr          ONLINE     ONLINE on askmdbrac02
ora.askmdbrac02.gsd                                OFFLINE    OFFLINE
ora.askmdbrac02.ons                                ONLINE     ONLINE on askmdbrac02
ora.askmdbrac02.vip                                ONLINE     ONLINE on askmdbrac02
ora.registry.acfs                                  ONLINE     OFFLINE
ora.scan1.vip                                      ONLINE     ONLINE on askmdbrac02
ora.scan2.vip                                      ONLINE     ONLINE on askmdbrac01
ora.scan3.vip                                      ONLINE     ONLINE on askmdbrac01
ora.racdb.db                                     ONLINE     ONLINE on askmdbrac01
ora.racdb.racdb_service.svc                    ONLINE     ONLINE on askmdbrac02
[grid@askmdbrac02 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node askmdbrac01
Instance racdb2 is running on node askmdbrac02
[grid@askmdbrac02 ~]$

This concludes the article on troubleshooting RAC services startup issue.

Thanks
SRI

Posted in 11gR2 RAC, RAC | Tagged: , , , | 3 Comments »

RMAN recovery scenarios

Posted by Srikrishna Murthy Annam on March 27, 2013

Recently i took RMAN sessions to some group of people and as a part of it i explained various scenarios of rman restore and recovery operation. I tried to record the video for the purpose of the people who follow my blog.

In this present session we will see various recovery scenarios mentioned below.

  1. Complete database restore and recovery
  2. Loss of System Datafile
  3. Non-System datafile ( closed and Open )
  4. Restoring a tablespace ( closed and Open )
  5. Restoring a datafile if no backups
  6. Restoring a datafile to different location
  7. Restoring controlfile
  8. restoring spfile
  9. Restoring online redologs
  10. Restoring temporary datafiles
  11. Incomplete Recovery ( time based , Change based and SCN based )
  12. Recovering archived logs

The following video demo gives you detailed explanation of each scenario and how to restore and recover under different scenarios.

Setup:
Let me explain you the environment used for this practice. I am using a database with ORACLE_SID=orcl as my database for testing all the different scenarios mentioned above. I am using a recovery catalog for my rman configuration and its details are ORACLE_SID=rmandb and schema used for rman catalog data is rman and the tns alias to connect to catalog database is rmandb.

I have both my databases orcl and rmandb in archive log mode and i also have backup for my database orcl.

For all my restore and recover operations i am using the following syntax to connect to target database and catalog database.

rman target / catalog rman/*******@rmandb

Complete database restore

SQL> select name,user from v$database;NAME USER
——— ——————————
ORCL SYSSQL> select open_mode from v$database;OPEN_MODE
——————–
READ WRITESQL> select username,default_tablespace from dba_users where username=’SH’;

USERNAME DEFAULT_TABLESPACE
—————————— ——————————
SH USERS

SQL> conn scott/oracle
Connected.
SQL> select count(1) from emp;

COUNT(1)
———-
14

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> cl scr

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ cd /home/oracle/app/oracle/oradata/orcl/
[oracle@localhost orcl]$ ls -lrt
total 2754360
-rw-r—– 1 oracle oracle 239869952 Mar 23 21:40 users01.dbf
-rw-r—– 1 oracle oracle 52429312 Mar 23 21:40 redo03.log
-rw-r—– 1 oracle oracle 52429312 Mar 23 21:40 redo02.log
-rw-r—– 1 oracle oracle 85991424 Mar 23 21:40 example01.dbf
-rw-rw—- 1 oracle oracle 8396800 Mar 23 21:40 APEX_1265209995679366.dbf
-rw-rw—- 1 oracle oracle 2105344 Mar 23 21:40 APEX_1246426611663638.dbf
-rw-r—– 1 oracle oracle 165683200 Mar 23 22:40 temp01.dbf
-rw-r—– 1 oracle oracle 845160448 Mar 23 23:05 system01.dbf
-rw-r—– 1 oracle oracle 1158684672 Mar 23 23:07 sysaux01.dbf
-rw-r—– 1 oracle oracle 144711680 Mar 23 23:08 undotbs01.dbf
-rw-r—– 1 oracle oracle 52429312 Mar 23 23:08 redo01.log
-rw-r—– 1 oracle oracle 9748480 Mar 23 23:08 control01.ctl
[oracle@localhost orcl]$ rm *.dbf
[oracle@localhost orcl]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.1.0.7.0 – Production on Sat Mar 23 23:09:18 2013

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

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

SQL> conn scoot/tiger
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn scott/oracle
Connected.
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> alter system flush sga;
alter system flush sga
*
ERROR at line 1:
ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

SQL> alter system flush shared pool;
alter system flush shared pool
*
ERROR at line 1:
ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

SQL> conn /as sysdba
Connected.
SQL> alter system flush shared pool;
alter system flush shared pool
*
ERROR at line 1:
ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

SQL> alter system flush shared_pool;

System altered.

SQL> conn scott/oracle
ERROR:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: ‘/home/oracle/app/oracle/oradata/orcl/system01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> exit
Disconnected
[oracle@localhost orcl]$ ps -ef | grep -i pmon
oracle 2309 1 0 21:39 ? 00:00:11 ora_pmon_orcl
oracle 3513 3193 0 23:12 pts/1 00:00:00 grep -i pmon
[oracle@localhost orcl]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.1.0.7.0 – Production on Sat Mar 23 23:12:55 2013

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

Connected.
SQL> shut immediate
ORA-01116: error in opening database file 1
ORA-01110: data file 1: ‘/home/oracle/app/oracle/oradata/orcl/system01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> startup
ORA-01081: cannot start already-running ORACLE – shut it down first
SQL> shut abort
ORACLE instance shut down.

========================== break =================================================
[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 – Production on Sat Mar 23 23:13:11 2013

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: ‘/home/oracle/app/oracle/oradata/orcl/system01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
[oracle@localhost ~]$

========================= break ==============================

SQL> startup nomount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
SQL>

============================= Break ===========================================

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

database mounted
released channel: ORA_DISK_1

Starting restore at 23-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf RECID=0 STAMP=0
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 /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.dbf
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_21/o1_mf_nnnd0_FULL_WEEKLY_HOT_8lcsnr7j_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_21/o1_mf_nnnd0_FULL_WEEKLY_HOT_8lcsnr7j_.bkp tag=FULL_WEEKLY_HOT
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:26
Finished restore at 23-MAR-13

Starting recover at 23-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /home/oracle/app/oracle/oradata/orcl/system01.dbf
destination for restore of datafile 00002: /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
destination for restore of datafile 00003: /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
destination for restore of datafile 00005: /home/oracle/app/oracle/oradata/orcl/example01.dbf
destination for restore of datafile 00006: /home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.dbf
destination for restore of datafile 00007: /home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_nnnd1_LVL1C_FULL_DB_8lmjd52r_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_nnnd1_LVL1C_FULL_DB_8lmjd52r_.bkp tag=LVL1C_FULL_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=360
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=361
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=362
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=363
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp tag=TAG20130224T034327
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_360_8nx712yq_.arc thread=1 sequence=360
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_360_8nx712yq_.arc RECID=17 STAMP=810861876
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_361_8nx7131f_.arc thread=1 sequence=361
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_361_8nx7131f_.arc RECID=14 STAMP=810861875
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_362_8nx713x6_.arc thread=1 sequence=362
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_362_8nx713x6_.arc RECID=15 STAMP=810861876
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_363_8nx714b8_.arc thread=1 sequence=363
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_363_8nx714b8_.arc RECID=16 STAMP=810861876
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
media recovery complete, elapsed time: 00:00:14
Finished recover at 23-MAR-13
starting full resync of recovery catalog
full resync complete

RMAN> alter database open;

database opened

RMAN>

============================ Break =============================

SQL> select name,user,open_mode from v$database;

NAME USER OPEN_MODE
——— —————————— ——————–
ORCL SYS READ WRITE

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

COUNT(1)
———-
14

SQL> select count(1) from scott.dept;

COUNT(1)
———-
4

SQL>

=======================================================

System Datafile

[oracle@localhost orcl]$ ls -lrt
total 2593432
-rw-rw—- 1 oracle oracle 239869952 Mar 23 23:25 users01.dbf
-rw-r—– 1 oracle oracle 52429312 Mar 23 23:25 redo03.log
-rw-r—– 1 oracle oracle 52429312 Mar 23 23:25 redo01.log
-rw-rw—- 1 oracle oracle 85991424 Mar 23 23:25 example01.dbf
-rw-rw—- 1 oracle oracle 8396800 Mar 23 23:25 APEX_1265209995679366.dbf
-rw-rw—- 1 oracle oracle 2105344 Mar 23 23:25 APEX_1246426611663638.dbf
-rw-rw—- 1 oracle oracle 20979712 Mar 23 23:25 temp01.dbf
-rw-rw—- 1 oracle oracle 144711680 Mar 23 23:31 undotbs01.dbf
-rw-rw—- 1 oracle oracle 845160448 Mar 23 23:32 system01.dbf
-rw-rw—- 1 oracle oracle 1158684672 Mar 23 23:34 sysaux01.dbf
-rw-r—– 1 oracle oracle 52429312 Mar 23 23:35 redo02.log
-rw-r—– 1 oracle oracle 9748480 Mar 23 23:35 control01.ctl
[oracle@localhost orcl]$ rm system01.dbf
[oracle@localhost orcl]$ sqlplus ‘/as sysdba’SQL*Plus: Release 11.1.0.7.0 – Production on Sat Mar 23 23:35:12 2013Copyright (c) 1982, 2008, Oracle. All rights reserved.ERROR:
ORA-01075: you are currently logged onEnter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@localhost orcl]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 – Production on Sat Mar 23 23:35:22 2013

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

SQL> conn /as sysdba
ERROR:
ORA-01075: you are currently logged on

SQL> shut abort
ORACLE instance shut down.
SQL> startup mount
ORA-00000: normal, successful completion
SQL> select open_mode from v$database;
SP2-0640: Not connected
SQL> SELECT file#, name FROM v$datafile;
SP2-0640: Not connected
SQL> conn ‘/as sysdba’
Enter password:
ERROR:
ORA-01005: null password given; logon denied

SQL> shut abort
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> SELECT file#, name FROM v$datafile;
SELECT file#, name FROM v$datafile
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> shut abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> SELECT file#, name FROM v$datafile;

FILE#
———-
NAME
——————————————————————————–
1
/home/oracle/app/oracle/oradata/orcl/system01.dbf

2
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

3
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

FILE#
———-
NAME
——————————————————————————–
4
/home/oracle/app/oracle/oradata/orcl/users01.dbf

5
/home/oracle/app/oracle/oradata/orcl/example01.dbf

6
/home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.dbf

FILE#
———-
NAME
——————————————————————————–
7
/home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.dbf

7 rows selected.

SQL> desc v$datafile
Name Null? Type
—————————————– ——– —————————-
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
FOREIGN_DBID NUMBER
FOREIGN_CREATION_CHANGE# NUMBER
FOREIGN_CREATION_TIME DATE
PLUGGED_READONLY VARCHAR2(3)
PLUGIN_CHANGE# NUMBER
PLUGIN_RESETLOGS_CHANGE# NUMBER
PLUGIN_RESETLOGS_TIME DATE

SQL> select file#,name,status from v$datafile;

FILE#
———-
NAME
——————————————————————————–
STATUS
——-
1
/home/oracle/app/oracle/oradata/orcl/system01.dbf
SYSTEM

2
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
ONLINE

FILE#
———-
NAME
——————————————————————————–
STATUS
——-

3
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
ONLINE

4
/home/oracle/app/oracle/oradata/orcl/users01.dbf

FILE#
———-
NAME
——————————————————————————–
STATUS
——-
ONLINE

5
/home/oracle/app/oracle/oradata/orcl/example01.dbf
ONLINE

6

FILE#
———-
NAME
——————————————————————————–
STATUS
——-
/home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.dbf
ONLINE

7
/home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.dbf
ONLINE

7 rows selected.

SQL>

====================== break ===============================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 – Production on Sat Mar 23 23:39:06 2013

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

connected to target database: ORCL (DBID=1229390655, not open)
connected to recovery catalog database

RMAN> run {
2> restore datafile 1;
3> recover datafile 1;
4> alter database open;
5> }

Starting restore at 23-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 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 /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_21/o1_mf_nnnd0_FULL_WEEKLY_HOT_8lcsnr7j_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_21/o1_mf_nnnd0_FULL_WEEKLY_HOT_8lcsnr7j_.bkp tag=FULL_WEEKLY_HOT
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:06
Finished restore at 23-MAR-13

Starting recover at 23-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_nnnd1_LVL1C_FULL_DB_8lmjd52r_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_nnnd1_LVL1C_FULL_DB_8lmjd52r_.bkp tag=LVL1C_FULL_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=360
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=361
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=362
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=363
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp tag=TAG20130224T034327
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_360_8nx83bfk_.arc thread=1 sequence=360
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_360_8nx83bfk_.arc RECID=22 STAMP=810862973
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_361_8nx83bjo_.arc thread=1 sequence=361
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_361_8nx83bjo_.arc RECID=19 STAMP=810862971
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_362_8nx83cvw_.arc thread=1 sequence=362
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_362_8nx83cvw_.arc RECID=20 STAMP=810862972
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_363_8nx83d7w_.arc thread=1 sequence=363
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_363_8nx83d7w_.arc RECID=21 STAMP=810862972
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
media recovery complete, elapsed time: 00:00:04
Finished recover at 23-MAR-13

database opened

RMAN>

============================= break ===========================

SQL> SELECT file#, name,status FROM v$datafile;

FILE# NAME STATUS
—– ———————————————————— ———-
1 /home/oracle/app/oracle/oradata/orcl/system01.dbf SYSTEM
2 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
3 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
4 /home/oracle/app/oracle/oradata/orcl/users01.dbf ONLINE
5 /home/oracle/app/oracle/oradata/orcl/example01.dbf ONLINE
6 /home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.d ONLINE
bf

7 /home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.d ONLINE
bf

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=’SYSTEM’;

TABLESPACE_NAME STATUS
—————————— ———-
SYSTEM ONLINE

Non-System datafile ( closed and Open )

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost orcl]$ ls -lrt
total 2593432
-rw-rw—- 1 oracle oracle 20979712 Mar 23 23:25 temp01.dbf
-rw-r—– 1 oracle oracle 52429312 Mar 23 23:43 redo01.log
-rw-rw—- 1 oracle oracle 239869952 Mar 23 23:43 users01.dbf
-rw-r—– 1 oracle oracle 52429312 Mar 23 23:43 redo02.log
-rw-rw—- 1 oracle oracle 85991424 Mar 23 23:43 example01.dbf
-rw-rw—- 1 oracle oracle 8396800 Mar 23 23:43 APEX_1265209995679366.dbf
-rw-rw—- 1 oracle oracle 2105344 Mar 23 23:43 APEX_1246426611663638.dbf
-rw-rw—- 1 oracle oracle 845160448 Mar 23 23:49 system01.dbf
-rw-rw—- 1 oracle oracle 144711680 Mar 23 23:49 undotbs01.dbf
-rw-rw—- 1 oracle oracle 1158684672 Mar 23 23:49 sysaux01.dbf
-rw-r—– 1 oracle oracle 52429312 Mar 23 23:49 redo03.log
-rw-r—– 1 oracle oracle 9748480 Mar 23 23:49 control01.ctl
[oracle@localhost orcl]$ rm users01.dbf
[oracle@localhost orcl]$ sqlplus ‘/as sysdba’SQL*Plus: Release 11.1.0.7.0 – Production on Sat Mar 23 23:50:30 2013Copyright (c) 1982, 2008, Oracle. All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> col file# for 999
SQL> col name for a60
SQL> col status for a10
SQL> set linesize 120
SQL> SELECT file#, name,status FROM v$datafile where tablespace_name=’USERS’;
SELECT file#, name,status FROM v$datafile where tablespace_name=’USERS’
*
ERROR at line 1:
ORA-00904: “TABLESPACE_NAME”: invalid identifier

SQL> DESC V$DATAFILE
Name Null? Type
—————————————————————– ——– ——————————————–
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
FOREIGN_DBID NUMBER
FOREIGN_CREATION_CHANGE# NUMBER
FOREIGN_CREATION_TIME DATE
PLUGGED_READONLY VARCHAR2(3)
PLUGIN_CHANGE# NUMBER
PLUGIN_RESETLOGS_CHANGE# NUMBER
PLUGIN_RESETLOGS_TIME DATE

SQL> desc dba_data_files
Name Null? Type
—————————————————————– ——– ——————————————–
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)

SQL> select file_name,file_id,tablespace_name from dba_data_files where tablespace_name=’USERS’;

FILE_NAME
————————————————————————————————————————
FILE_ID TABLESPACE_NAME
———- ——————————
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name=’USERS’;

FILE_NAME
————————————————————————————————————————
FILE_ID TABLESPACE_NAME STATUS
———- —————————— ———-
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,TS#,status fro v$datafile where file#=4;
select file#,TS#,status fro v$datafile where file#=4
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
—– ———- ———-
4 4 ONLINE

SQL> alter system flush shared_pool;

System altered.

SQL> select count(1) from scott.emp;
select count(1) from scott.emp
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/home/oracle/app/oracle/oradata/orcl/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL>
SQL> select name,user ,open_mode from v$database;

NAME USER OPEN_MODE
———————————————————— —————————— ——————–
ORCL SYS READ WRITE

SQL>

======================== break =============================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 – Production on Sat Mar 23 23:55:32 2013

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

connected to target database: ORCL (DBID=1229390655)
connected to recovery catalog database

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

Starting restore at 23-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/users01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/23/2013 23:56:44
ORA-19573: cannot obtain exclusive enqueue for datafile 4
ORA-19600: input file is datafile-copy 2 (/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf)
ORA-19601: output file is datafile 4 (/home/oracle/app/oracle/oradata/orcl/users01.dbf)

RMAN>

=========================== break =============================

SQL> ALTER DATABASE DATAFILE 4 OFFLINE;

Database altered.

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name=’USERS’;

FILE_NAME
————————————————————————————————————————
FILE_ID TABLESPACE_NAME STATUS
———- —————————— ———-
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
—– ———- ———-
4 4 RECOVER

SQL> select name,user ,open_mode from v$database;

NAME USER OPEN_MODE
———————————————————— —————————— ——————–
ORCL SYS READ WRITE

SQL>

============================== break =========================================

RMAN> run {
2> restore datafile 4;
3> recover datafile 4;
4> sql ‘atler database datafile 4 online’;
5> }

Starting restore at 24-MAR-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf RECID=0 STAMP=0
Finished restore at 24-MAR-13

Starting recover at 24-MAR-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
archived log for thread 1 with sequence 368 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc thread=1 sequence=366
media recovery complete, elapsed time: 00:00:03
Finished recover at 24-MAR-13
starting full resync of recovery catalog
full resync complete

sql statement: atler database datafile 4 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/24/2013 00:01:08
RMAN-11003: failure during parse/execution of SQL statement: atler database datafile 4 online
ORA-00900: invalid SQL statement

RMAN>

RMAN> run {
2> sql ‘alter database datafile 4 online’;
3> }

sql statement: alter database datafile 4 online

RMAN>

========================== break =============================

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name=’USERS’;

FILE_NAME
————————————————————————————————————————
FILE_ID TABLESPACE_NAME STATUS
———- —————————— ———-
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
—– ———- ———-
4 4 ONLINE

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

COUNT(1)
———-
14

SQL> create table test (sno number) tablespace users;

Table created.

SQL>

============================================================================================

Lost non system datafile 😦 Online method )

[oracle@localhost orcl]$ ls -lrt
total 2593488
-rw-r—– 1 oracle oracle 52429312 Mar 23 23:43 redo01.log
-rw-r—– 1 oracle oracle 52429312 Mar 23 23:43 redo02.log
-rw-rw—- 1 oracle oracle 85991424 Mar 23 23:43 example01.dbf
-rw-rw—- 1 oracle oracle 8396800 Mar 23 23:43 APEX_1265209995679366.dbf
-rw-rw—- 1 oracle oracle 2105344 Mar 23 23:43 APEX_1246426611663638.dbf
-rw-rw—- 1 oracle oracle 20979712 Mar 24 00:00 temp01.dbf
-rw-rw—- 1 oracle oracle 239869952 Mar 24 00:06 users01.dbf
-rw-rw—- 1 oracle oracle 1158684672 Mar 24 00:10 sysaux01.dbf
-rw-rw—- 1 oracle oracle 144711680 Mar 24 00:11 undotbs01.dbf
-rw-rw—- 1 oracle oracle 845160448 Mar 24 00:11 system01.dbf
-rw-r—– 1 oracle oracle 52429312 Mar 24 00:11 redo03.log
-rw-r—– 1 oracle oracle 9748480 Mar 24 00:12 control01.ctl
[oracle@localhost orcl]$ rm users01.dbf
[oracle@localhost orcl]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.1.0.7.0 – Production on Sun Mar 24 00:12:39 2013

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

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

SQL> col file# for 999
col name for a60
col status for a10
set linesize 120
select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name=’USERS’;SQL> SQL> SQL> SQL>

FILE_NAME
————————————————————————————————————————
FILE_ID TABLESPACE_NAME STATUS
———- —————————— ———-
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
—– ———- ———-
4 4 ONLINE

SQL> alter system flush shared_pool;

System altered.

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
—– ———- ———-
4 4 ONLINE

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

COUNT(1)
———-
14

SQL> alter system flush shared_pool;

System altered.

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

COUNT(1)
———-
14

SQL> drop table test;

Table dropped.

SQL> create table test (sno number) tablespace users;
create table test (sno number) tablespace users
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/home/oracle/app/oracle/oradata/orcl/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> select name,user ,open_mode from v$database;

NAME USER OPEN_MODE
———————————————————— —————————— ——————–
ORCL SYS READ WRITE

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
—– ———- ———-
4 4 ONLINE

SQL> shut immediate
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/home/oracle/app/oracle/oradata/orcl/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> startup mount
ORA-01081: cannot start already-running ORACLE – shut it down first
SQL> shut abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
—– ———- ———-
4 4 ONLINE

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name=’USERS’;
select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name=’USERS’
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
—– ———- ———-
4 4 ONLINE

SQL>

====================== break ==============================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 – Production on Sun Mar 24 00:18:11 2013

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

connected to target database: ORCL (DBID=1229390655, not open)
connected to recovery catalog database

RMAN> run {
2> restore datafile 4;
3> recover datafile 4;
4> sql ‘alter database datafile 4 online’;
5> }

Starting restore at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf RECID=0 STAMP=0
Finished restore at 24-MAR-13

Starting recover at 24-MAR-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
archived log for thread 1 with sequence 368 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc thread=1 sequence=366
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-MAR-13
starting full resync of recovery catalog
full resync complete

sql statement: alter database datafile 4 online

RMAN>

========================================== break ===================================================

SQL> alter database open;

Database altered.

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name=’USERS’;

FILE_NAME
————————————————————————————————————————
FILE_ID TABLESPACE_NAME STATUS
———- —————————— ———-
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
—– ———- ———-
4 4 ONLINE

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

COUNT(1)
———-
14

SQL> create table test (sno number) tablespace users;

Table created.

SQL> drop table test;

Table dropped.

SQL> sho parameter recycle

NAME TYPE VALUE
———————————— ———– ——————————
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on
SQL> purge recyclebin;

Recyclebin purged.

SQL>

Restoring a tablespace ( closed and Open )

Restoring a tablespace : (open mode)[oracle@localhost orcl]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.1.0.7.0 – Production on Sun Mar 24 00:32:07 2013

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

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

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=’USERS’;

TABLESPACE_NAME STATUS
—————————— ———
USERS ONLINE

SQL> desc dba_tablespaces
Name Null? Type
—————————————– ——– —————————-
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(12)

SQL> desc v$tablespace
Name Null? Type
—————————————– ——– —————————-
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)

SQL> select ts#,name from v$tablespace where name=’USERS’;

TS# NAME
———- ——————————
4 USERS

SQL> desc dba_data_files
Name Null? Type
—————————————– ——– —————————-
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name=’USERS’;

FILE_NAME
——————————————————————————–
FILE_ID TABLESPACE_NAME STATUS
———- —————————— ———
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> desc v$datafile
Name Null? Type
—————————————– ——– —————————-
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
FOREIGN_DBID NUMBER
FOREIGN_CREATION_CHANGE# NUMBER
FOREIGN_CREATION_TIME DATE
PLUGGED_READONLY VARCHAR2(3)
PLUGIN_CHANGE# NUMBER
PLUGIN_RESETLOGS_CHANGE# NUMBER
PLUGIN_RESETLOGS_TIME DATE

SQL> select file#,ts#,status from v$datafile where ts#=4;

FILE# TS# STATUS
———- ———- ——-
4 4 ONLINE

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost orcl]$ rm users01.dbf
[oracle@localhost orcl]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.1.0.7.0 – Production on Sun Mar 24 00:35:42 2013

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

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

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=’USERS’;

TABLESPACE_NAME STATUS
—————————— ———
USERS ONLINE

SQL> select ts#,name from v$tablespace where name=’USERS’;

TS# NAME
———- ——————————
4 USERS

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name=’USERS’;

FILE_NAME
——————————————————————————–
FILE_ID TABLESPACE_NAME STATUS
———- —————————— ———
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,ts#,status from v$datafile where ts#=4;

FILE# TS# STATUS
———- ———- ——-
4 4 ONLINE

SQL> create table test (sno number) tablespace users;
create table test (sno number) tablespace users
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/home/oracle/app/oracle/oradata/orcl/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/home/oracle/app/oracle/oradata/orcl/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> alter tablespace users offline force;
alter tablespace users offline force
*
ERROR at line 1:
ORA-02141: invalid OFFLINE option

SQL> alter tablespace users force offline;
alter tablespace users force offline
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/home/oracle/app/oracle/oradata/orcl/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> shut abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01109: database not open

SQL>

================================= break ===========================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 – Production on Sun Mar 24 00:41:20 2013

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

connected to target database: ORCL (DBID=1229390655, not open)
connected to recovery catalog database

RMAN> run {
2> restore tablespace users;
3> recover tablespace users;
4> sql ‘alter database open’;
5> }

Starting restore at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf RECID=0 STAMP=0
Finished restore at 24-MAR-13

Starting recover at 24-MAR-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
archived log for thread 1 with sequence 368 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
archived log for thread 1 with sequence 369 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc thread=1 sequence=366
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc thread=1 sequence=367
media recovery complete, elapsed time: 00:00:03
Finished recover at 24-MAR-13
starting full resync of recovery catalog
full resync complete

sql statement: alter database open

RMAN>

=================================== Break ==============================================

SQL> select name,user ,open_mode from v$database;

NAME USER OPEN_MODE
——— —————————— ——————–
ORCL SYS READ WRITE

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=’USERS’;

TABLESPACE_NAME STATUS
—————————— ———
USERS ONLINE

SQL> select ts#,name from v$tablespace where name=’USERS’;

TS# NAME
———- ——————————
4 USERS

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name=’USERS’;

FILE_NAME
——————————————————————————–
FILE_ID TABLESPACE_NAME STATUS
———- —————————— ———
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,ts#,status from v$datafile where ts#=4;

FILE# TS# STATUS
———- ———- ——-
4 4 ONLINE

SQL> create table test (sno number) tablespace users;

Table created.

SQL> drop table test;

Table dropped.

SQL> purge recyclebin;

Recyclebin purged.

SQL>

Restoring a datafile if no backups

Restoring a datafile if no backups[oracle@localhost orcl]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.1.0.7.0 – Production on Sun Mar 24 01:52:39 2013

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

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

SQL> create tablespace test_ts datafile ‘/home/oracle/app/oracle/oradata/orcl/test_ts01.dbf’ size 5m;

Tablespace created.

SQL> create table test tablespace test_ts as select * from scott.emp;

Table created.

SQL> select * from test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost orcl]$ rm test_ts01.dbf
[oracle@localhost orcl]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.1.0.7.0 – Production on Sun Mar 24 01:54:06 2013

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

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

SQL> select count(1) from test;

COUNT(1)
———-
14

SQL> select * from test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> alter system flush shared_pool;

System altered.

SQL> select * from test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> select segment_name,tablespace_name from dba_segments where segment_name=’TEST’;

SEGMENT_NAME
——————————————————————————–
TABLESPACE_NAME
——————————
TEST
TEST_TS

SQL> shut immediate
ORA-01116: error in opening database file 8
ORA-01110: data file 8: ‘/home/oracle/app/oracle/oradata/orcl/test_ts01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL>

=============================================== Break =========================================================

RMAN> run {
2> sql ‘alter database datafile 8 offline’;
3> restore datafile 8;
4> recover datafile 8;
5> sql ‘alter database datafile 8 online’;
6> }

starting full resync of recovery catalog
full resync complete
sql statement: alter database datafile 8 offline

Starting restore at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK

creating datafile file number=8 name=/home/oracle/app/oracle/oradata/orcl/test_ts01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 24-MAR-13

Starting recover at 24-MAR-13
using channel ORA_DISK_1

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

Finished recover at 24-MAR-13

sql statement: alter database datafile 8 online

RMAN>

============================================== Break ==========================================

SQL> alter database open;

Database altered.

SQL> create table test tablespace test_ts as select * from scott.emp;
create table test tablespace test_ts as select * from scott.emp
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> create table test1 tablespace test_ts as select * from scott.emp;

Table created.

SQL>
SQL> drop table test;

Table dropped.

SQL> drop table test1;

Table dropped.

SQL>

SQL> drop tablespace test_ts including contents and datafiles;

Tablespace dropped.

SQL>

Restoring a datafile to different location

Restoring a datafile to different location[oracle@localhost orcl]$ clear
[oracle@localhost orcl]$ ls -lrt user*
-rw-rw—- 1 oracle oracle 239869952 Mar 24 02:01 users01.dbf
[oracle@localhost orcl]$ rm users01.dbf
[oracle@localhost orcl]$ sqlplus ‘/as sysdba’SQL*Plus: Release 11.1.0.7.0 – Production on Sun Mar 24 02:10:36 2013Copyright (c) 1982, 2008, Oracle. All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table scott.test tablespace uses as select * from scott.emp;
create table scott.test tablespace uses as select * from scott.emp
*
ERROR at line 1:
ORA-00959: tablespace ‘USES’ does not exist

SQL> create table scott.test tablespace users as select * from scott.emp;
create table scott.test tablespace users as select * from scott.emp
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/home/oracle/app/oracle/oradata/orcl/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> alter database datafile 4 offline;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost orcl]$ mkdir askm
[oracle@localhost orcl]$ cd askm
[oracle@localhost askm]$ pwd
/home/oracle/app/oracle/oradata/orcl/askm
[oracle@localhost askm]$ ls
[oracle@localhost askm]$

=============================================== Break ==========================================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 – Production on Sun Mar 24 02:12:20 2013

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

connected to target database: ORCL (DBID=1229390655)
connected to recovery catalog database

RMAN> run {
2> set newname for datafile 4 to ‘/home/oracle/app/oracle/oradata/orcl/askm/users01.dbf’;
3> restore datafile 4;
4> switch datafile 4;
5> recover datafile 4;
6> sql ‘alter database datafile 4 online’;
7> }

executing command: SET NEWNAME
starting full resync of recovery catalog
full resync complete

Starting restore at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/askm/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/app/oracle/oradata/orcl/askm/users01.dbf RECID=10 STAMP=810872051
Finished restore at 24-MAR-13

datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=810872055 file name=/home/oracle/app/oracle/oradata/orcl/askm/users01.dbf
starting full resync of recovery catalog
full resync complete

Starting recover at 24-MAR-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
archived log for thread 1 with sequence 368 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
archived log for thread 1 with sequence 369 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc
archived log for thread 1 with sequence 370 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_370_8nxclvch_.arc
archived log for thread 1 with sequence 371 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc thread=1 sequence=366
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc thread=1 sequence=367
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc thread=1 sequence=368
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc thread=1 sequence=369
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-MAR-13
starting full resync of recovery catalog
full resync complete

sql statement: alter database datafile 4 online

RMAN>

================================= Break ======================================================

[oracle@localhost askm]$ pwd
/home/oracle/app/oracle/oradata/orcl/askm
[oracle@localhost askm]$ ls -lrt
total 234484
-rw-rw—- 1 oracle oracle 239869952 Mar 24 02:14 users01.dbf
[oracle@localhost askm]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.1.0.7.0 – Production on Sun Mar 24 02:15:49 2013

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

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

SQL> create table scott.test tablespace users as select * from scott.emp;

Table created.

SQL> drop table scott.test;

Table dropped.

SQL>

Restoring controlfile

Restoring controlfile
=======================SQL> desc v$controlfile
Name Null? Type
—————————————– ——– —————————-
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBERSQL> select name,status from v$controlfile;NAME
——————————————————————————–
STATUS
——-
/home/oracle/app/oracle/oradata/orcl/control01.ctl/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost askm]$ rm /home/oracle/app/oracle/oradata/orcl/control01.ctl /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
[oracle@localhost askm]$ ps -ef | grep -i pmon
oracle 5502 1 0 01:56 ? 00:00:04 ora_pmon_orcl
[oracle@localhost askm]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.1.0.7.0 – Production on Sun Mar 24 02:25:15 2013

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

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

SQL> create table scott.test tablespace users as select * from scott.emp;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> shut immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/home/oracle/app/oracle/oradata/orcl/control01.ctl’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL>

================================== Break =================================================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 – Production on Sun Mar 24 02:29:14 2013

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

connected to target database: ORCL (not mounted)
connected to recovery catalog database

RMAN> set dbid=1229390655

executing command: SET DBID
database name is “ORCL” and DBID is 1229390655

RMAN> restore controlfile;

Starting restore at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_ncsn1_LVL1C_FULL_DB_8lmjhknt_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_ncsn1_LVL1C_FULL_DB_8lmjhknt_.bkp tag=LVL1C_FULL_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 24-MAR-13

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/24/2013 02:30:27
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> recover database;

Starting recover at 24-MAR-13
Starting implicit crosscheck backup at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 24-MAR-13

Starting implicit crosscheck copy at 24-MAR-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-MAR-13

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_372_8nxkowqt_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_370_8nxclvch_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_ncsn1_LVL1C_FULL_DB_8lmjhknt_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T033253_8lmym81j_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp

using channel ORA_DISK_1
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=810873044 file name=/home/oracle/app/oracle/oradata/orcl/askm/users01.dbf

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
archived log for thread 1 with sequence 368 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
archived log for thread 1 with sequence 369 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc
archived log for thread 1 with sequence 370 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_370_8nxclvch_.arc
archived log for thread 1 with sequence 371 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc
archived log for thread 1 with sequence 372 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_372_8nxkowqt_.arc
archived log for thread 1 with sequence 373 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo01.log
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=360
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=361
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=362
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=363
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp tag=TAG20130224T034327
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_360_8nxky72y_.arc thread=1 sequence=360
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_360_8nxky72y_.arc RECID=22 STAMP=810873048
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_361_8nxky74t_.arc thread=1 sequence=361
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_361_8nxky74t_.arc RECID=19 STAMP=810873048
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_362_8nxky831_.arc thread=1 sequence=362
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_362_8nxky831_.arc RECID=20 STAMP=810873048
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_363_8nxky876_.arc thread=1 sequence=363
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_363_8nxky876_.arc RECID=21 STAMP=810873048
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc thread=1 sequence=366
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc thread=1 sequence=367
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc thread=1 sequence=368
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc thread=1 sequence=369
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_370_8nxclvch_.arc thread=1 sequence=370
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc thread=1 sequence=371
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc thread=1 sequence=371
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc thread=1 sequence=371
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_372_8nxkowqt_.arc thread=1 sequence=372
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo01.log thread=1 sequence=373
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-MAR-13

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/24/2013 02:31:09
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found “identifier”: expecting one of: “resetlogs, ;”
RMAN-01008: the bad identifier was: resetlog
RMAN-01007: at line 1 column 21 file: standard input

RMAN>

============================================ Break ========================================================

SQL> alter database open resetlogs;

Database altered.

SQL> select name,user,open_mode from v$database;

NAME USER OPEN_MODE
——— —————————— ——————–
ORCL SYS READ WRITE

SQL> archive list logs;
SP2-0734: unknown command beginning “archive li…” – rest of line ignored.
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>

============================================== Break ==========================================

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 18 ORCL 1229390655 PARENT 1 13-AUG-09
1 2 ORCL 1229390655 CURRENT 754488 30-OCT-09

RMAN>

============================================== Break ===========================================

restoring spfile
Refer video
Restoring online redologs
Refer Video
Restoring temporary datafiles
Refer Video
Incomplete Recovery ( time based , Change based and SCN based )
Refer Video
Recovering archived logs
Refer Video

Hope it helps
SRI

Posted in RMAN | Tagged: , , , , , , , , , | Leave a Comment »

Edit Add Modify Delete Oem 12c Agent Registration Password

Posted by Srikrishna Murthy Annam on January 19, 2013

Posted in Cloud Control 12c, OEM | Tagged: , , , , , | 1 Comment »

Sun Oracle Servers Animated App For Your iPhone

Posted by Srikrishna Murthy Annam on January 17, 2013

I have an interesting iPhone app on my phone which shows  animated graphical representation of all the Sun Oracle servers. It reminds me of my old working experience on telephone exchanges installation. Particularly OCB and E10B telephone exchanges racks and their cabling between different racks are similar to the sun oracle server racks. It gives me more interest to go through these server details.

But its a worth looking at this app if you are working on oracle technologies. Neatly animated , compact and small application with good valuable content.

https://itunes.apple.com/us/app/oracle-hardware-virtual-tour/id535920742?mt=8

Thanks

SRI

 

Posted in OTHERS, Uncategorized | Tagged: , , , , , , | Leave a Comment »

How To Setup CISCO VPN On iPhone

Posted by Srikrishna Murthy Annam on January 3, 2013

Apple announced that it can support cisco SSL VPN on iPhone from iOS 4 onwards.

http://blogs.cisco.com/news/apple_announces_cisco_ssl_vpn_support_on_iphone_os_4/

Now in this article, i am going to guide, the steps involved in configuring CISCO VPN on your iPhone , iPod Touch and in iPad.

Navigate to  “Settings -> General -> VPN” and then tap “ON”. It will give you different options to configure VPN. Tap on  “IPSec”

For the configuration of the VPN on iPhone, we need to be ready with the following details. All these details are obtained from the VPN profile provided to us. It is a normal file with “.pcf” extention and you can open it with any notepad.

Description : This is the smal description of the
Server : Host value from .pcf file
Account : username used to connect to VPN
Password : Password provided to you to connect to VPN using above username
Group Name : This is value of “GroupName” in vpn profile. ( .pcf  file )
Secret : This is the most important part of this article. This is the value for “enc_GroupPwd” in encrypted form in “<vpnprofile>.pcf” file. We need to decrypt this value before entering in iPhone. I used the following url to decrypt the password and it worked for me all the time.

http://www.unix-ag.uni-kl.de/~massar/bin/cisco-decode/

You can use any tool that can decrypt this password for you. Just open google and type “ipsec password decrypter” and you will get number of options.

After entering the secret value, save the values. It will then ask you for the password, enter your vpn password.

You should all be fine to use your private network  now.

Thanks
SRI

Posted in OTHERS | Tagged: , , , , , , , , , , , , | Leave a Comment »

Blocking And Unblocking OEM Agent in OEM 12c

Posted by Srikrishna Murthy Annam on December 31, 2012

In this present short demo, i am going to show you how to block and unblock an oem agent manually in oem 12c. There might be various reasons an oem agent may into a blocked state and this demo shows you how to unblock it.

The reasons that the agent may go into blocked state are ..
If there is any network break between oem agent and oms server
If there is any space issue on oms server and oem agent can’t upload the metrics data to oms server

This demo does not contain any voice and the navigations are self explanatory. Leave your comments if you have any doubts.

 

Thanks

SRI

 

Posted in Cloud Control 12c | Tagged: , , , | Leave a Comment »

Oracle OpenWorld 2012 Update

Posted by Srikrishna Murthy Annam on October 1, 2012

Larry Ellison’s major  announcements in OracleOpenWorld-2012

  1. IaaS  => Infrastructure as a Service

  2. Private Clouds

  3. Oracle Database 12c

  4. Exadata X3 machine.

Thanks

SRIKRISHNA MURTHY

Posted in RAC | Tagged: | Leave a Comment »

COPY Schema into Same Database with impdp

Posted by Srikrishna Murthy Annam on June 7, 2012

In this article I am going to explain the procedure to copy a schema to a different schema in the same database without exporting the source schema. This procedure works fine if you want to copy the schema to a different database.

1. Create a directory pointing to any physical directory on the server
2. Create a loopback database link. It means, you have to create a database link pointing to the same database.
   NOTE : If your requirement is to copy schema to a different schema, create a database link pointing to target database.
3. Use the impdp to copy schema to another schema.

Assume that the database name is DB-A. I am trying to copy a schema SCOTT to a different schema SCOTT_NEW in the same database.

SQL> create a directory TEST_DIR as ‘/xxxx/exp-dir/’;

SQL> create a database link SCOTT_DB_LINK connect to SYSTEM identified by  xxxxxxx using ‘DB-A’;

If you are trying to copy schema to a different database , say DB-B , create database link as follows.

SQL> create a database link SCOTT_DB_LINK connect to system identified by xxxxx using ‘DB-B’;

(NOTE : We should be able to resolve the alias DB-A to database A and DB-B to database B.)

Verify the database link

SQL> select * from dual@SCOTT_DB_LINK;

Use the following syntax to copy schema,

impdp system/xxxxxx schemas=SCOTT directory=TEST_DIR network_link=SCOTT_DB_LINK  remap_schema=SCOTT:SCOTT_NEW logfile=TEST_DIR:SCOTT_NEW.log

Verify the new schema with the source schema with following commands.

SQL> select object_type,count(1) from dba_objects where owner=’SCOTT’ and status=’INVALID’ group by object_type;
SQL> select object_type,count(1) from dba_objects where owner=’SCOTT_NEW’ and status=’INVALID’ group by object_type;
SQL> select object_name,object_type from dba_objects where owner=’SCOTT’ and status=’INVALID’;
SQL> select object_name,object_type from dba_objects where owner=’SCOTT_NEW’ and status=’INVALID’;

Observations:

We are eliminating the creation of dump file, by using database link with the parameter network_link

Hope it helps

SRI

Posted in Database Articles, ORACLE Tech | Tagged: , , , , , , | 2 Comments »

12c oem template export and import using emcli

Posted by Srikrishna Murthy Annam on May 15, 2012

This demo explains the steps to export and import a template in oem 12c using emcli command line tool.

 

Thanks

SRI

Posted in Cloud Control 12c | Tagged: , , , , | Leave a Comment »

Oracle VM templates for Cloud Control

Posted by Srikrishna Murthy Annam on May 2, 2012

Oracle VM templates for Cloud Control oem 12c are now available for download from edelivery.

Oracle VM Template for Enterprise Manager Cloud Control 12c Media Pack for x86 (64 bit)

Oracle VM Template for Enterprise Manager Cloud Control 12c (Agent only) Media Pack for x86 (64 bit)

Hope it helps

SRI

 

Posted in Cloud Control 12c | Tagged: , , | Leave a Comment »

CLOUD CONTROL INTEGRATION WITH OVM

Posted by Srikrishna Murthy Annam on May 1, 2012

OEM 12C CLOUD CONTROL INTEGRATION WITH OVM

  1. Configure Oracle Software Library    2
  2. Setting up the infrastructure for Online mode    3
  3. Setup MOS(My Oracle Support) Credentials    6
  4. Deploying the Cloud Plug-ins    6
  5. Deploying the Cloud Plug-ins to the Management Server    9
  6. Deploying the Oracle Virtualization Plug-in to the Management Agent    11
  7. Oracle VM Manager 3.0 Registration Prerequisites    12
  8. Registering the Oracle VM Manager in oem 12c    13
  9. Synchronizing the OVM Manager Targets    15

 

 

 

 

 

 

 

 

 

 

  1. Configure Oracle Software Library

    Oracle Software Library (Software Library) is a repository that stores software patches, virtual appliance images, reference gold images, application software and their associated directive scripts.

    Verify whether you already have the Software Library configured. If not follow the below steps to configure:

    From the Enterprise menu, select Provisioning and Patching, then click Software Library.

    From there choose actions > administration.

    If there is nothing configured yet, click Add then choose OMS Shared Filesystem location.

 

 

Upon addition of the location, a job executes to configure the Software Library. Under this location, all required entities for EMs features like Provisioning, Patching, and Cloud Management are populated.

Once the job is successful, you will see the components configured in the Software Library.

To view the Software Library, navigate to: Enterprise Manager > Patching and Provisioning > Software Library


 

 

  1. Setting up the infrastructure for Online mode

    If the enterprise manager is configured to connect to MOS , plugins or patches can be automatically downloaded. Follow the following sequence of steps ..

    1. Set up connection to MOS (Online Mode)

      Check to see if the connection to My Oracle Support is set to “Online”. Navigate to

      Setup > Provisioning and Patching > Offline Patching.

       

    2. The connection setting has to be “Online”. If not set, set it to Online and click Apply.

       

    3. Select the adjacent tab My Oracle Support and Proxy Connection

       

    4. Configure your Proxy Connection to connect to My Oracle Support. Provide the required Proxy Server, Port and Realm details, then click Apply.

       

    5. Once the configuration is done, click Test to check if the connectivity works correctly.

  2. Setup MOS(My Oracle Support) Credentials

  3. Deploying the Cloud Plug-ins

    Plug-ins must first be downloaded to the OMS using Self Update before they can be deployed. To enable monitoring of virtualization targets, you must download the plug-ins and deploy them. The following plug-ins must be deployed on the Management Server:

  • Oracle Virtualization
  • Oracle Cloud Application
  • Oracle Chargeback and Capacity Planning
  • Oracle Exalogic


Select one of the above plugins (list specified above) , then click Download. The Schedule Download dialog appears. In this example, “Oracle Virtualization” plug-in is selected.


Then it changes the status from “Available” to “Downloaded”

Repeat the same step to all the other plugins (Oracle Cloud Application, Oracle Chargeback and Capacity Planning, Oracle Exalogic)

Once an entity has been downloaded to the Software Library, it is ready to be applied to your installation. Select an update from the list whose status is Downloaded, then click Apply.

 


You will be redirected to the plug-in deployment page.


  1. Deploying the Cloud Plug-ins to the Management Server

    Complete the below steps to all the plugins mentioned in step 4.

    Select the plugin and deploy it on OMS. Right click the plugin ->Deploy on->Management server


     

    Plugin pre-requisites will be executed. After successful pre-requisite checks, click on deploy.

    NOTE : OMS will be bounced now.

    Once the OMS is up, navigate to Setup->Extensibility->Plug-in . The Plugin version on OMS is now 12.1.0.2.

 

  1. Deploying the Oracle Virtualization Plug-in to the Management Agent

Click on Add to add the management agent where plugin will be deployed

Once you have selected the agents, click continue

It runs the plugin pre-requisites and click next

Click Deploy, to deploy the plug-in on the agents

  1. Oracle VM Manager 3.0 Registration Prerequisites

    The following prerequisites must be meet before Oracle VM Manager can be registered in Oracle Enterprise Manager 12c Cloud Control.

    1. The Oracle Virtualization plug-in must be install and enabled on the Oracle Management Service.
    2. The Oracle VM Manager host must have the Oracle Management Agent (OMA) and be a monitored target in Oracle Enterprise Manager 12c Cloud Control.
    3. The Oracle VM Manager host must have the Oracle Virtualization plug-in.
    4. The preferred credentials for the Oracle VM Manager host must be configured in Oracle Enterprise Manager 12c Cloud Control.
    5. The Oracle Management Service and the Oracle VM Manager host must have consistent name resolution using DNS with both forward and reverse lookups.
  2. Registering the Oracle VM Manager in oem 12c

     

     

Table 1

Oracle VM Manager Registration Page Title

Entry Selections and Entry Syntax

  1. Name

The name entered in the “Name” text box is displayed on the Infrastructure Cloud page.

  1. EM Agent URL

The EM Agent URL is selected by clicking the magnifying glass icon. A pop-up window will appear, select the Oracle VM Manager agent from the list. The EM Agent URL syntax is: “https://<AGENT-FQDN&gt;:3872/emd/main/”

Note: The Oracle VM Manager host can be selected as long as the host is managed target by OEM with the Oracle Virtualization plug-in.

  1. Oracle VM Manager Core

Enter “http://<ORACLE VM MANAGER HOST FQDN>:7001” in the
Oracle VM Manager Core URL text box.

  1. Automatic Synchronization

Select the Automatic Synchronization check box to enable automated synchronization between OEM and the Oracle VM API Core.

  1. Monitoring Credentials for Oracle VM Manager

Enter the Oracle VM Manager admin user name and the admin
password in the Monitoring Credentials for Oracle VM Manager text fields.

  1. Administration Credentials for Oracle VM Manager / Use Administration Credentials

Enter the Oracle VM Manager admin user name and the admin password in the Administration Credentials for Oracle VM Manager text fields.

 
 

Select the Use Administration Credentials check box.

  1. Oracle VM Manager Console URL

Enter the non-SSL Oracle VM Manager URL, i.e.http://<ORACLE VM MANAGER HOST FQDN>:7001/ovm/console/faces/login.jspx in the Oracle VM Manager Console URL text box.

  1. Submit

Once all of the information has been entered, click the Submit button to start the registration job.

  1. Synchronizing the OVM Manager Targets

Posted in Cloud Control 12c, OEM, Uncategorized | Tagged: , , | Leave a Comment »

Cloud Control OEM 12c Installation

Posted by Srikrishna Murthy Annam on April 28, 2012

OEM 12C CLOUD CONTROL INSTALLATION

1.Software    2

2.OS installation    2

3.Database Installation    2

4.Oem 12c Prerequisites    2

a.Hardware Requirements    2

b.Operating System Requirements    2

c.Package Requirements    2

d.Kernel Parameter Requirements    3

e.Library Requirements    3

f.Operating System Groups and Users Requirements    3

g.Unique Host Name and Static IP Address Requirements    4

h.Temporary Directory Space Requirements    4

i.Central Inventory Requirements    4

j.UMASK Value Requirements    4

k.File Descriptor Requirements    4

l.Existing Database Version Requirements    5

m.Host File Requirements    5

n.Port Requirements    5

o.Internet Connection Requirements    5

5.Oem 12c Installation Procedure    6

6.Verification Checks    16

7.Starting and Stopping oem 12c Services    17

 

  1. Software

The required software for the oem 12c cloud control are :

  1. Oracle Linux 6.1 (x86_64) Download
  2. Oracle Database 11gR2 (x86_64) Download
  3. Enterprise Manager Cloud Control 12c (12.1.0.1) (x86_64) with BP1 patch Download
  4. OS installation :

Oracle Linux 6.1 (x86_64) installation :

  1. Database Installation :

Oracle Database 11gR2 installation :

Below links help to install database

Documentation

OBE

Video

  1. Oem 12c Prerequisites :

 

  1. Hardware Requirements :

CPU, RAM, Heap Size, and Hard Disk Space requirements vary depending on the type of installation we are choosing. Please review the here to know the exact hardware requirements.

 

  1. Operating System Requirements :

Ensure that you install Enterprise Manger Cloud Control only on certified operating system as mentioned in the Enterprise Manager Certification Matrix.

  1. Package Requirements :

    make-3.81

    binutils-2.17.50.0.6

    gcc -4.1.1

    libaio-0.3.106

    glibc-common-2.3.4

    libstdc++ -4.1.1

    setarch-1.6

    sysstat-5.0.5

    rng-utils-2.0

    libXtst-1.0.1-3.1(x86_64)

    xorg-x11-utils (This package is required only for GUI-based interactive installation, and not for silent installation)

     

    In addition, install the 32-bit version as well as the 64-bit version of glibc-devel-2.5-49 to avoid any linking errors while installing the OMS. The installer checks for this package, so if this package is not installed, the installer displays an error.

     

  2. Kernel Parameter Requirements

For installing an OMS or a Management Agent on Unix operating systems (32-bit or 64-bit), set the kernel.shmmax parameter to a value 1 byte less than 4 GB or 4294967295.

 

To verify the value assigned to kernel.shmmax parameter, run the following command:

cat /proc/sys/kernel/shmmax

 

To set the value for kernel.shmmax parameter, run the following command:

sysctl -w kernel.shmmax=<new value>

 

 

  1. Library Requirements :

    Install glibc-2.5.12 package

 

  1. Operating System Groups and Users Requirements:

# /usr/sbin/groupadd oinstall

 

# /usr/sbin/useradd -g oinstall oracle

 

# passwd oracle

  1. Unique Host Name and Static IP Address Requirements :

Ensure that you check the network configuration to verify that the host on which you are installing resolves to a unique host name and a static IP address that is visible to other hosts in the network.

You must use a static IP address. If you use a dynamic IP address, the installation might fail.

Ensure that the host name specified in /etc/hosts file is unique, and that it maps to the correct IP address. Following is the recommended format of the /etc/hosts file:

<ip address> <fully_qualified_host_name> <short_host_name>

 

If the DNS server is configured, then it should resolve the host name for the OMS machine correctly. For example, all these command must return the same output:

nslookup host1

nslookup host1.foo.com

nslookup 141.81.5.71

  1. Temporary Directory Space Requirements :

Ensure that you allocate 400 MB of space for a temporary directory where the executables can be copied.

  1. Central Inventory Requirements :

Ensure that you allocate 100 MB of space for the central inventory directory.

Also ensure that the central inventory directory is not on a shared file system. If it is already on a shared file system, then switch over to a non-shared file system by following the instructions outlined in My Oracle Support note 1092645.1.

To determine whether the Oracle Inventory group exists, enter the following command:

$ more /etc/oraInst.loc

If the oraInst.loc file exists, then the output from this command looks like:

inventory_loc=/u01/app/oracle/oraInventory

inst_group=oinstall

  1. UMASK Value Requirements:

Ensure that you set the default file mode creation mask (umask) to 022 in the shell startup file.

  1. File Descriptor Requirements :

If the current value is less than 4096, then as root user, update the /etc/security/limits.conf file as shown below:

<UID> soft nofile 4096

<UID> hard nofile 4096

  1. Existing Database Version Requirements :

Ensure that the database is patched with all the Patch Set Updates (PSU) or Critical Patch Updates (CPU) released for that release. Also ensure that you apply the patches.

If you use Oracle Database 11g Release 2 (11.2.0.1), then ensure that you apply the patches for bugs 10014178 and 8799099.

If you use Oracle Database 11g Release 1 (11.1.0.7), then ensure that you apply Patch Set Update 2 and the patches for bugs 8644757 and 7525072.

The database can also have the Automatic Memory Management (AMM) feature enabled.

  1. Host File Requirements :

Ensure that the host name specified in the /etc/hosts file is unique, and ensure that it maps to the correct IP address of that host. Otherwise, the installation can fail on the product-specific prerequisite check page.

The following is the recommended format of the /etc/hosts file:

<ip> <fully_qualified_host_name> <short_host_name>

  1. Port Requirements :

Ensure that the ports you assign (within the given range) to the following components are free and available:

 

Admin Server HTTP SSL Port = 7101 – 7200

Enterprise Manager Upload HTTP Port = 4889 – 4898

Enterprise Manager Upload HTTP SSL Port = 1159, 4899 – 4908

Enterprise Manager Central Console HTTP Port = 7788 – 7798

Enterprise Manager Central Console HTTP SSL Port = 7799 – 7809

Oracle Management Agent Port = 3872, 1830 – 1849

 

To verify if a port is free, run the following command:

On Unix:

netstat -anp | grep <port no>

  1. Internet Connection Requirements :

Oracle recommends that the host from where you are running the installer have a connection to the Internet so that the configuration information can be automatically collected and uploaded to My Oracle Support.

  1. Oem 12c Installation Procedure :

  1. Verification Checks :

    Access the following URL with sysman user and navigate to different pages to verify everything looks good.


https://<hostname with FQDN>:7803/em

  1. Starting and Stopping oem 12c Services :

Use the following commands to start all components :

$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

$ export OMS_HOME=/u01/app/oracle/Middleware/oms

$ export AGENT_HOME=/u01/app/oracle/Middleware/agent/core/12.1.0.1.0

 

# Start everything

$ $ORACLE_HOME/bin/sqlplus ‘/as sysdba’

SQL> startup

 

$lsnrctl start listener

 

$ $OMS_HOME/bin/emctl start oms

 

$ $AGENT_HOME/bin/emctl start agent

 

Use the following commands to stop all components :

$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

$ export OMS_HOME=/u01/app/oracle/Middleware/oms

$ export AGENT_HOME=/u01/app/oracle/Middleware/agent/core/12.1.0.1.0

 

# Stop everything

$ $OMS_HOME/bin/emctl stop oms -all

 

$ $AGENT_HOME/bin/emctl stop agent

 

$ $ORACLE_HOME/bin/sqlplus ‘/as sysdba’

SQL> shut immediate

 

$ $lsnrctl stop listener


 

Posted in Cloud Control 12c, OEM, Uncategorized | Tagged: , , | Leave a Comment »