Wednesday, November 14, 2012

How to restore from a loss of all current control files to a non default location using a backup piece

Here again with a scenario simulating a loss of all the control files and restoring them to a non-default location: differently from previous posts (here and here) you don't have an autobackup control file available, but just a backup piece.

First of all just create a location outside the flash recovery area where to save the backup piece containing the current control file:
[oracle@localhost oracle]$ pwd
/home/oracle/app/oracle/
[oracle@localhost oracle]$ mkdir controlfile_copy
[oracle@localhost oracle]$ cd controlfile_copy/
[oracle@localhost controlfile_copy]$ ll
total 0
Now it's time to create the backup piece with the current control file.
RMAN> backup current controlfile to destination '/home/oracle/app/oracle/controlfile_copy';

Starting backup at 09-11-2012 06:29:08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 09-11-2012 06:29:10
channel ORA_DISK_1: finished piece 1 at 09-11-2012 06:29:11
piece handle=/home/oracle/app/oracle/controlfile_copy/ORCL/backupset/2012_11_09/o1_mf_ncnnf_TAG20121109T062908_89t4sp41_.bkp tag=TAG20121109T062908 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-11-2012 06:29:11
In your local directory RMAN was able to create your backup piece as written in the above log and as you can verify moving through those directories:
[oracle@localhost controlfile_copy]$ ll
total 4
drwxrwx--- 3 oracle oracle 4096 Nov  9 06:29 ORCL
[oracle@localhost controlfile_copy]$ cd ORCL/backupset/2012_11_09/
[oracle@localhost 2012_11_09]$ ll
total 9680
-rw-rw---- 1 oracle oracle 9895936 Nov  9 06:29 o1_mf_ncnnf_TAG20121109T062908_89t4sp41_.bkp
Let's simulate the loss of all current control files.
[oracle@localhost ~]$ rm /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl
Instance is still running, so I have to kill it.
[oracle@localhost ~]$ ps -ef|grep smon
oracle    2384     1  0 03:58 ?        00:00:03 ora_smon_orcl
oracle    6758  3039  0 06:35 pts/1    00:00:00 grep smon
[oracle@localhost ~]$ kill -9 2384
[oracle@localhost ~]$ ps -ef|grep smon
oracle    6762  3039  0 06:35 pts/1    00:00:00 grep smon
Under my non_default_location directory I will restore the control file: currently it's empty.
[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ll
total 0
Use RMN to connect to your target instance.
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Nov 9 07:46:58 2012

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

connected to target database (not started)
Start the database in NOMOUNT mode.
RMAN> startup nomount;

Oracle instance started

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                364907192 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6008832 bytes
Use the following RESTORE CONTROLFILE command to get the control file backup from your backup piece and restore it to your non default location:
RMAN> restore controlfile to '/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' from '/home/oracle/app/oracle/controlfile_copy/ORCL/backupset/2012_11_09/o1_mf_ncnnf_TAG20121109T062908_89t4sp41_.bkp';

Starting restore at 09-11-2012 07:51:43
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 09-11-2012 07:51:46
After the restore process finishes you can find the control file under non_default_location directory
[oracle@localhost non_default_location]$ ll
total 9632
-rw-rw---- 1 oracle oracle 9846784 Nov  9 07:51 control01.ctl
Instance is still not able to go to the next mode because it doesn't know where restored control file is located.
RMAN> alter database mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/09/2012 07:54:04
ORA-00205: error in identifying control file, check alert log for more info
Use sqlplus to connect the instance ...
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 9 07:54:50 2012

Copyright (c) 1982, 2010, 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
... have a look at the control_files initialization parameter written in spfile ...
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /home/oracle/app/oracle/oradata/orcl/control01.ctl, 
                                                 /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
... and modify it according to your new control file location.
SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' scope=spfile;

System altered.
Close the instance ...
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
.. and start it again in MOUNT mode using RMAN.
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Nov 9 07:56:56 2012

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                364907192 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6008832 bytes
You have to perform a recovery of the database after the restore of the control file, even if any datafile was restored.
RMAN> recover database;

Starting recover at 09-11-2012 07:57:34
Starting implicit crosscheck backup at 09-11-2012 07:57:34
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 09-11-2012 07:57:36

Starting implicit crosscheck copy at 09-11-2012 07:57:36
using channel ORA_DISK_1
Finished implicit crosscheck copy at 09-11-2012 07:57:36

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/backupset/2012_11_07/o1_mf_ncnnf_TAG20121107T223343_89pnl8z3_.bkp.old
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789209074_80omm3d0_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795852591_86xq10nr_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795834324_86x564xb_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_09_26/o1_mf_s_795045371_867q3d12_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp

using channel ORA_DISK_1
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo03.log
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo03.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:13
Finished recover at 09-11-2012 07:57:52
After the restore of the control file you have to open the database with RESETLOGS option.
RMAN> alter database open resetlogs;

database opened
If you want to come back to the original settings because the original location is available again, you have to modify again the control_files initialization parameter value.
SQL> show parameter control_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl
Set the control_files parameter to the original configuration:
SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.
Close your instance
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Copy your current control file to the original locations:
[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ll
total 9632
-rw-rw---- 1 oracle oracle 9846784 Nov  9 08:01 control01.ctl
[oracle@localhost non_default_location]$ cp control01.ctl /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
[oracle@localhost non_default_location]$ cp control01.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl
Connect to the instance...
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 9 08:07:28 2012

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

Connected to an idle instance.
... and start it
SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             369101496 bytes
Database Buffers           79691776 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /home/oracle/app/oracle/oradata/orcl/control01.ctl,                                                
                                                 /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
That's all.

44 comments:

Anonymous said...

buy tramadol online tramadol withdrawal panic attacks - tramadol withdrawal methadone

Anonymous said...

xanax online kind high xanax - xanax drug hair test

Anonymous said...

buy tramadol online tramadol withdrawal how long does it last - tramadol for dogs expiration date

Anonymous said...

generic xanax xanax side effects hallucinations - xanax withdrawal help

Anonymous said...

buy tramadol online tramadol 50 mg veterinary use - tramadol hcl 50 mg for migraines

Anonymous said...

buy tramadol online tramadol 50mg an 627 - life after tramadol addiction

Anonymous said...

buy tramadol online cheap can you buy tramadol in usa - tramadol cream

Anonymous said...

generic xanax xanax dosage and frequency - xanax class

Anonymous said...

buy tramadol online buy tramadol with american express - buy tramadol cod overnight delivery

Anonymous said...

carisoprodol no prescription carisoprodol schedule - do they drug test carisoprodol

Anonymous said...

buy tramadol online buy tramadol online usa - buy tramadol online australia

Anonymous said...

xanax online 20mg hydrocodone and 1 mg xanax - does walmart drug test xanax

Anonymous said...

xanax online 1mg xanax much klonopin - side effects of xanax .25 mg

Anonymous said...

buy cialis online cialis vs viagra price - cialis online singapore

Anonymous said...

buy xanax bars online no prescription does xanax pills look like - what is the best generic xanax

Anonymous said...

buy cialis online cialis 5mg online usa - buy generic viagra cialis online

Anonymous said...

cialis online cialis online no prescription overnight - generic cialis for daily use

Anonymous said...

http://landvoicelearning.com/#63987 tramadol for dogs sleepy - tramadol make you high

Anonymous said...

buy tramadol online tramadol hcl bp 100mg - tramadol for dogs in liquid

Anonymous said...

http://buytramadolonlinecool.com/#50897 pet meds tramadol 50mg - buy real tramadol

Anonymous said...

buy tramadol can overdose tramadol kill you - bad tramadol addiction

Anonymous said...

can you buy tramadol online legally can you buy tramadol online no prescription - order tramadol from usa

Anonymous said...

learn how to buy tramdadol tramadol information - can you buy tramadol in usa

Anonymous said...

buy tramadol tramadol hcl xr side effects - tramadol 627

Anonymous said...

Your own гeροrt fеаtures confirmed neceѕsary to mysеlf.
It’s quitе usеful anԁ you're simply naturally quite educated in this area. You possess exposed my personal eye in order to numerous opinion of this kind of subject together with intriguing and sound content.
Also visit my website Valium

Anonymous said...

buy tramadol online zydol tramadol dosage - tramadol highest dosage

Anonymous said...

buy ativan online ativan prescribing information - how to buy lorazepam

Anonymous said...

http://buytramadolonlinecool.com/#59473 tramadol for dogs usa - neurontin and tramadol high

Anonymous said...

http://reidmoody.com/#92465 ativan 321 - ativan to clonazepam conversion

Anonymous said...

tramadol no rx buy tramadol online from usa - tramadol hydrochloride 50mg high

Anonymous said...

http://staam.org/#56274 buy tramadol saturday delivery - tramadol hcl obat apa

Anonymous said...

http://ranchodelastortugas.com/#72895 xanax side effects baby - xanax dosage to sleep

Anonymous said...

Your own post hаs establіѕhed
useful tο mе. Ӏt’s vеrу uѕeful and yοu are ceгtainly
extremely knowledgeable in this field. You poѕѕess popped my οwn eуe to
different thοughtѕ about thiѕ subject using
interestіng and reliаble сontent.
Also visit my web-site cpbackup.info

Anonymous said...

order tramadol online mastercard buy tramadol tablets - buy tramadol no prescription

Anonymous said...

generic cytotec misoprostol - buy cytotec australia - cytotec buy usa

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

Badar Din said...

https://bzounr.blogspot.com/2016/09/loss-of-all-controlfiles-no-catalog.html

Unknown said...

Recovery your Oracle Backup File with Cognegic's DB Recovery Support?

Searching for Oracle Backup record yet not ready to recoup it? At that point no stress, simply contact to Cognegic's DB Recovery Services or Exchange Database Recovery to fast recuperation of Oracle Backup record. Here clients will get authorities help at whatever point you require it to recuperate your information. Rapidly get guidance and help you require from our appointed proficient specialists 24*7 and illuminate your reinforcement related issues. We likewise give reinforcement support to MySQL, Cassandra, MongoDB, and MS SQL Server et cetera. Our Backup Recovery support is dependable and under your financial plan.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Arun Gupta said...

file data recovery
corrupted file recovery
how to recover deleted files
how to restore deleted files
recover my deleted files
recover my files
recover deleted files






































































Arun Gupta said...

Get my data
data recovery services
professional data recovery company
data recovery usa
data recovery services near me
Recover my data
Get my data back
recovery lost data

Arun Gupta said...

nice.
recover deleted files from phone memory

Arun Gupta said...

Nice
recover storage device
external data recover

yanmaneee said...

balenciaga trainers
kobe shoes
nike air max 2019
yeezy boost 350 v2
nike air max 270
russell westbrook shoes
jordans
cheap nfl jerseys
coach outlet online
adidas superstars