=================================Autobackup of SPFILE and CONTROLFILE
=================================
Even if you do not use RMAN to take backups of your database, you might find it useful to take snapshots of your SPFILE and CONTROLFILE automatically. You can configure Recovery Manager (RMAN) to automatically create copies of CONTROLFILE and SPFILE when you make changes to database such as:
adding/dropping a new tablespace or datafile
altering the state of a tablespace or datafile (online/offline/read only)
adding a new online redo log,
renaming a file
adding a new redo thread
and many more...
To setup autobackup just start RMAN and connect to database
RMAN target /
set directory and format of backup files:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';
In name of autbackup file you have to use %F. It will be expanded to C-XXXXXXXXX-YYYYMMDD-NN, where:
XXXXXXXXX – database id
YYYYMMDD – day, when backuped
NN – change number during day, starts with 00, and represented in hexadecimal
Activate autobackups:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Now autobackups are active, and every time you change database structure controlfile will be automatically backed up.
If you use RMAN for backups it will create copy of SPFILE or CONTROLFILE also after each backup operation.
I've tested it on Oracle 10g (10.2) but it should work in Oracle since version 8i. Next time i'll write how to restore SPFILE or CONTROLFILE from these backup.
Command Autobackup of SPFILE and CONTROLFILE
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 26 14:36:54 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PME (DBID=941578866)
RMAN> configure controlfile autobackup format for device type disk 'C:\Backup\%F
';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "single-quoted-string": expecting one of: "clear
, to"
RMAN-01007: at line 1 column 62 file: standard input
RMAN> configure controlfile autobackup format for device type disk to 'C:\Backup
\%F';
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';
new RMAN configuration parameters are successfully stored
RMAN>
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
=======================================================
Restore CONTROLFILE And Or SPFILE From Autobackup
=======================================================
adding/dropping a new tablespace or datafile
altering the state of a tablespace or datafile (online/offline/read only)
adding a new online redo log,
renaming a file
adding a new redo thread
and many more...
To setup autobackup just start RMAN and connect to database
RMAN target /
set directory and format of backup files:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';
In name of autbackup file you have to use %F. It will be expanded to C-XXXXXXXXX-YYYYMMDD-NN, where:
XXXXXXXXX – database id
YYYYMMDD – day, when backuped
NN – change number during day, starts with 00, and represented in hexadecimal
Activate autobackups:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Now autobackups are active, and every time you change database structure controlfile will be automatically backed up.
If you use RMAN for backups it will create copy of SPFILE or CONTROLFILE also after each backup operation.
I've tested it on Oracle 10g (10.2) but it should work in Oracle since version 8i. Next time i'll write how to restore SPFILE or CONTROLFILE from these backup.
Command Autobackup of SPFILE and CONTROLFILE
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 26 14:36:54 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PME (DBID=941578866)
RMAN> configure controlfile autobackup format for device type disk 'C:\Backup\%F
';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "single-quoted-string": expecting one of: "clear
, to"
RMAN-01007: at line 1 column 62 file: standard input
RMAN> configure controlfile autobackup format for device type disk to 'C:\Backup
\%F';
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';
new RMAN configuration parameters are successfully stored
RMAN>
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
=======================================================
Restore CONTROLFILE And Or SPFILE From Autobackup
=======================================================
its time to describe how to restore CONTROLFILE and SPFILE from autobackups. If your database is still running and what You want is just get historical version of SPFILE or CONTROLFILE then it is easy task. Just start RMAN:
C:\Documents and Settings\Administrator>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 26 14:36:54 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PME (DBID=941578866)
RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup;
Starting restore at 26-JUL-11
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110726
channel ORA_DISK_1: autobackup found: C:\Backup\c-941578866-20110726-03
channel ORA_DISK_1: control file restore from autobackup complete
Finished restore at 26-JUL-11
The until time clause sets time of validity for CONTROLFILE. By default RAM looks for autobackups for seven days into past starting from that time. It can happen that for several days there were no changes, and autobackup was not invoked. In that case add MAXDAYS clause:
RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup maxdays 100;
Starting restore at 26-JUL-11
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: PME
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110723
channel ORA_DISK_1: looking for autobackup on day: 20110722
channel ORA_DISK_1: looking for autobackup on day: 20110721
channel ORA_DISK_1: looking for autobackup on day: 20110720
channel ORA_DISK_1: looking for autobackup on day: 20110719
channel ORA_DISK_1: looking for autobackup on day: 20110718
channel ORA_DISK_1: looking for autobackup on day: 20110717
channel ORA_DISK_1: looking for autobackup on day: 20110716
channel ORA_DISK_1: looking for autobackup on day: 20110715
channel ORA_DISK_1: looking for autobackup on day: 20110714
channel ORA_DISK_1: looking for autobackup on day: 20110713
channel ORA_DISK_1: looking for autobackup on day: 20110712
channel ORA_DISK_1: looking for autobackup on day: 20110711
channel ORA_DISK_1: looking for autobackup on day: 20110710
channel ORA_DISK_1: looking for autobackup on day: 20110709
channel ORA_DISK_1: looking for autobackup on day: 20110708
channel ORA_DISK_1: looking for autobackup on day: 20110707
channel ORA_DISK_1: looking for autobackup on day: 20110706
channel ORA_DISK_1: looking for autobackup on day: 20110705
channel ORA_DISK_1: looking for autobackup on day: 20110704
channel ORA_DISK_1: looking for autobackup on day: 20110703
channel ORA_DISK_1: looking for autobackup on day: 20110702
channel ORA_DISK_1: looking for autobackup on day: 20110701
channel ORA_DISK_1: looking for autobackup on day: 20110630
channel ORA_DISK_1: looking for autobackup on day: 20110629
channel ORA_DISK_1: looking for autobackup on day: 20110628
channel ORA_DISK_1: looking for autobackup on day: 20110627
channel ORA_DISK_1: looking for autobackup on day: 20110626
channel ORA_DISK_1: looking for autobackup on day: 20110625
channel ORA_DISK_1: looking for autobackup on day: 20110624
channel ORA_DISK_1: looking for autobackup on day: 20110623
channel ORA_DISK_1: looking for autobackup on day: 20110622
channel ORA_DISK_1: looking for autobackup on day: 20110621
channel ORA_DISK_1: looking for autobackup on day: 20110620
channel ORA_DISK_1: looking for autobackup on day: 20110619
channel ORA_DISK_1: looking for autobackup on day: 20110618
channel ORA_DISK_1: looking for autobackup on day: 20110617
channel ORA_DISK_1: looking for autobackup on day: 20110616
channel ORA_DISK_1: looking for autobackup on day: 20110615
channel ORA_DISK_1: looking for autobackup on day: 20110614
channel ORA_DISK_1: looking for autobackup on day: 20110613
channel ORA_DISK_1: looking for autobackup on day: 20110612
channel ORA_DISK_1: looking for autobackup on day: 20110611
channel ORA_DISK_1: looking for autobackup on day: 20110610
channel ORA_DISK_1: looking for autobackup on day: 20110609
channel ORA_DISK_1: looking for autobackup on day: 20110608
channel ORA_DISK_1: looking for autobackup on day: 20110607
channel ORA_DISK_1: looking for autobackup on day: 20110606
channel ORA_DISK_1: looking for autobackup on day: 20110605
channel ORA_DISK_1: looking for autobackup on day: 20110604
channel ORA_DISK_1: looking for autobackup on day: 20110603
channel ORA_DISK_1: looking for autobackup on day: 20110602
channel ORA_DISK_1: looking for autobackup on day: 20110601
channel ORA_DISK_1: looking for autobackup on day: 20110531
channel ORA_DISK_1: looking for autobackup on day: 20110530
channel ORA_DISK_1: looking for autobackup on day: 20110529
channel ORA_DISK_1: looking for autobackup on day: 20110528
channel ORA_DISK_1: looking for autobackup on day: 20110527
channel ORA_DISK_1: looking for autobackup on day: 20110526
channel ORA_DISK_1: looking for autobackup on day: 20110525
channel ORA_DISK_1: looking for autobackup on day: 20110524
channel ORA_DISK_1: looking for autobackup on day: 20110523
channel ORA_DISK_1: looking for autobackup on day: 20110522
channel ORA_DISK_1: looking for autobackup on day: 20110521
channel ORA_DISK_1: looking for autobackup on day: 20110520
channel ORA_DISK_1: looking for autobackup on day: 20110519
channel ORA_DISK_1: looking for autobackup on day: 20110518
channel ORA_DISK_1: looking for autobackup on day: 20110517
channel ORA_DISK_1: looking for autobackup on day: 20110516
channel ORA_DISK_1: looking for autobackup on day: 20110515
channel ORA_DISK_1: looking for autobackup on day: 20110514
channel ORA_DISK_1: looking for autobackup on day: 20110513
channel ORA_DISK_1: looking for autobackup on day: 20110512
channel ORA_DISK_1: looking for autobackup on day: 20110511
channel ORA_DISK_1: looking for autobackup on day: 20110510
channel ORA_DISK_1: looking for autobackup on day: 20110509
channel ORA_DISK_1: looking for autobackup on day: 20110508
channel ORA_DISK_1: looking for autobackup on day: 20110507
channel ORA_DISK_1: looking for autobackup on day: 20110506
channel ORA_DISK_1: looking for autobackup on day: 20110505
channel ORA_DISK_1: looking for autobackup on day: 20110504
channel ORA_DISK_1: looking for autobackup on day: 20110503
channel ORA_DISK_1: looking for autobackup on day: 20110502
channel ORA_DISK_1: looking for autobackup on day: 20110501
channel ORA_DISK_1: looking for autobackup on day: 20110430
channel ORA_DISK_1: looking for autobackup on day: 20110429
channel ORA_DISK_1: looking for autobackup on day: 20110428
channel ORA_DISK_1: looking for autobackup on day: 20110427
channel ORA_DISK_1: looking for autobackup on day: 20110426
channel ORA_DISK_1: looking for autobackup on day: 20110425
channel ORA_DISK_1: looking for autobackup on day: 20110424
channel ORA_DISK_1: looking for autobackup on day: 20110423
channel ORA_DISK_1: looking for autobackup on day: 20110422
channel ORA_DISK_1: looking for autobackup on day: 20110421
channel ORA_DISK_1: looking for autobackup on day: 20110420
channel ORA_DISK_1: looking for autobackup on day: 20110419
channel ORA_DISK_1: looking for autobackup on day: 20110418
channel ORA_DISK_1: looking for autobackup on day: 20110417
channel ORA_DISK_1: looking for autobackup on day: 20110416
channel ORA_DISK_1: looking for autobackup on day: 20110415
channel ORA_DISK_1: no autobackup in 100 days found
channel ORA_DISK_1: looking for autobackup on day: 20110726
channel ORA_DISK_1: autobackup found: C:\Backup\c-941578866-20110726-03
channel ORA_DISK_1: control file restore from autobackup complete
Finished restore at 26-JUL-11
It will force RMAN to look for copies for more days into past. Similarly You can restore SPFILE:
RMAN> restore until time 'sysdate-3'
2> SPFILE to 'c:\temp\SPFILE' from autobackup maxdays 100;
Starting restore at 26-JUL-11
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: PME
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110723
channel ORA_DISK_1: looking for autobackup on day: 20110722
channel ORA_DISK_1: looking for autobackup on day: 20110721
channel ORA_DISK_1: looking for autobackup on day: 20110720
channel ORA_DISK_1: looking for autobackup on day: 20110719
channel ORA_DISK_1: looking for autobackup on day: 20110718
channel ORA_DISK_1: looking for autobackup on day: 20110717
channel ORA_DISK_1: looking for autobackup on day: 20110716
channel ORA_DISK_1: looking for autobackup on day: 20110715
channel ORA_DISK_1: looking for autobackup on day: 20110714
channel ORA_DISK_1: looking for autobackup on day: 20110713
channel ORA_DISK_1: looking for autobackup on day: 20110712
channel ORA_DISK_1: looking for autobackup on day: 20110711
channel ORA_DISK_1: looking for autobackup on day: 20110710
channel ORA_DISK_1: looking for autobackup on day: 20110709
channel ORA_DISK_1: looking for autobackup on day: 20110708
channel ORA_DISK_1: looking for autobackup on day: 20110707
channel ORA_DISK_1: looking for autobackup on day: 20110706
channel ORA_DISK_1: looking for autobackup on day: 20110705
channel ORA_DISK_1: looking for autobackup on day: 20110704
channel ORA_DISK_1: looking for autobackup on day: 20110703
channel ORA_DISK_1: looking for autobackup on day: 20110702
channel ORA_DISK_1: looking for autobackup on day: 20110701
channel ORA_DISK_1: looking for autobackup on day: 20110630
channel ORA_DISK_1: looking for autobackup on day: 20110629
channel ORA_DISK_1: looking for autobackup on day: 20110628
channel ORA_DISK_1: looking for autobackup on day: 20110627
channel ORA_DISK_1: looking for autobackup on day: 20110626
channel ORA_DISK_1: looking for autobackup on day: 20110625
channel ORA_DISK_1: looking for autobackup on day: 20110624
channel ORA_DISK_1: looking for autobackup on day: 20110623
channel ORA_DISK_1: looking for autobackup on day: 20110622
channel ORA_DISK_1: looking for autobackup on day: 20110621
channel ORA_DISK_1: looking for autobackup on day: 20110620
channel ORA_DISK_1: looking for autobackup on day: 20110619
channel ORA_DISK_1: looking for autobackup on day: 20110618
channel ORA_DISK_1: looking for autobackup on day: 20110617
channel ORA_DISK_1: looking for autobackup on day: 20110616
channel ORA_DISK_1: looking for autobackup on day: 20110615
channel ORA_DISK_1: looking for autobackup on day: 20110614
channel ORA_DISK_1: looking for autobackup on day: 20110613
channel ORA_DISK_1: looking for autobackup on day: 20110612
channel ORA_DISK_1: looking for autobackup on day: 20110611
channel ORA_DISK_1: looking for autobackup on day: 20110610
channel ORA_DISK_1: looking for autobackup on day: 20110609
channel ORA_DISK_1: looking for autobackup on day: 20110608
channel ORA_DISK_1: looking for autobackup on day: 20110607
channel ORA_DISK_1: looking for autobackup on day: 20110606
channel ORA_DISK_1: looking for autobackup on day: 20110605
channel ORA_DISK_1: looking for autobackup on day: 20110604
channel ORA_DISK_1: looking for autobackup on day: 20110603
channel ORA_DISK_1: looking for autobackup on day: 20110602
channel ORA_DISK_1: looking for autobackup on day: 20110601
channel ORA_DISK_1: looking for autobackup on day: 20110531
channel ORA_DISK_1: looking for autobackup on day: 20110530
channel ORA_DISK_1: looking for autobackup on day: 20110529
channel ORA_DISK_1: looking for autobackup on day: 20110528
channel ORA_DISK_1: looking for autobackup on day: 20110527
channel ORA_DISK_1: looking for autobackup on day: 20110526
channel ORA_DISK_1: looking for autobackup on day: 20110525
channel ORA_DISK_1: looking for autobackup on day: 20110524
channel ORA_DISK_1: looking for autobackup on day: 20110523
channel ORA_DISK_1: looking for autobackup on day: 20110522
channel ORA_DISK_1: looking for autobackup on day: 20110521
channel ORA_DISK_1: looking for autobackup on day: 20110520
channel ORA_DISK_1: looking for autobackup on day: 20110519
channel ORA_DISK_1: looking for autobackup on day: 20110518
channel ORA_DISK_1: looking for autobackup on day: 20110517
channel ORA_DISK_1: looking for autobackup on day: 20110516
channel ORA_DISK_1: looking for autobackup on day: 20110515
channel ORA_DISK_1: looking for autobackup on day: 20110514
channel ORA_DISK_1: looking for autobackup on day: 20110513
channel ORA_DISK_1: looking for autobackup on day: 20110512
channel ORA_DISK_1: looking for autobackup on day: 20110511
channel ORA_DISK_1: looking for autobackup on day: 20110510
channel ORA_DISK_1: looking for autobackup on day: 20110509
channel ORA_DISK_1: looking for autobackup on day: 20110508
channel ORA_DISK_1: looking for autobackup on day: 20110507
channel ORA_DISK_1: looking for autobackup on day: 20110506
channel ORA_DISK_1: looking for autobackup on day: 20110505
channel ORA_DISK_1: looking for autobackup on day: 20110504
channel ORA_DISK_1: looking for autobackup on day: 20110503
channel ORA_DISK_1: looking for autobackup on day: 20110502
channel ORA_DISK_1: looking for autobackup on day: 20110501
channel ORA_DISK_1: looking for autobackup on day: 20110430
channel ORA_DISK_1: looking for autobackup on day: 20110429
channel ORA_DISK_1: looking for autobackup on day: 20110428
channel ORA_DISK_1: looking for autobackup on day: 20110427
channel ORA_DISK_1: looking for autobackup on day: 20110426
channel ORA_DISK_1: looking for autobackup on day: 20110425
channel ORA_DISK_1: looking for autobackup on day: 20110424
channel ORA_DISK_1: looking for autobackup on day: 20110423
channel ORA_DISK_1: looking for autobackup on day: 20110422
channel ORA_DISK_1: looking for autobackup on day: 20110421
channel ORA_DISK_1: looking for autobackup on day: 20110420
channel ORA_DISK_1: looking for autobackup on day: 20110419
channel ORA_DISK_1: looking for autobackup on day: 20110418
channel ORA_DISK_1: looking for autobackup on day: 20110417
channel ORA_DISK_1: looking for autobackup on day: 20110416
channel ORA_DISK_1: looking for autobackup on day: 20110415
channel ORA_DISK_1: no autobackup in 100 days found
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110726
channel ORA_DISK_1: autobackup found: C:\Backup\c-941578866-20110726-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 26-JUL-11
After restoring SPFILE You can convert it to PFILE, so You can easily read settings:
RMAN> sql "create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''";
sql statement: create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''
Now more complex scenario: You have lost all database files including CONTROLFILE and SPFILE. You only have backups created by RMAN. In that case You have to eventually install Database Software. Then setup environment variables and start recovery. On Windows platform You have to create Service to be able to startup oracle. You probably remember instance name and SID. If You don't it can be usually found in some TNSNAMES files on client machines. But You probably do not know DBID. You can easily find DBID – it'is part of the name of autobackup file. Details in my previous post. So if autobackup file name is 'c-941578866-20110726-03' then DBID is 941578866. Now we can start recovery:
RMAN target=/
At the beginning set Database ID:
RMAN> SET DBID 941578866;
Then startup instance:
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 113246888 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
RMAN>
and run recovery of SPFILE
RMAN> RUN {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';
3> RESTORE SPFILE FROM AUTOBACKUP; }
I suggest also creating PFILE from SPFILE. Then You should check if all file locations are correct, all directories exists and Oracle have rights to write in them.
RMAN> shutdown ;
Oracle instance shut down
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 113246888 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
RMAN> RUN {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';
3> RESTORE CONTROLFILE FROM AUTOBACKUP; }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 26-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: PME
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110726
channel ORA_DISK_1: looking for autobackup on day: 20110725
channel ORA_DISK_1: looking for autobackup on day: 20110724
channel ORA_DISK_1: looking for autobackup on day: 20110723
channel ORA_DISK_1: looking for autobackup on day: 20110722
channel ORA_DISK_1: looking for autobackup on day: 20110721
channel ORA_DISK_1: looking for autobackup on day: 20110720
channel ORA_DISK_1: no autobackup in 100 days found
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110726
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20110726-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 26-JUL-11
Now You can continue recovery of datafiles.
C:\Documents and Settings\Administrator>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 26 14:36:54 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PME (DBID=941578866)
RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup;
Starting restore at 26-JUL-11
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110726
channel ORA_DISK_1: autobackup found: C:\Backup\c-941578866-20110726-03
channel ORA_DISK_1: control file restore from autobackup complete
Finished restore at 26-JUL-11
The until time clause sets time of validity for CONTROLFILE. By default RAM looks for autobackups for seven days into past starting from that time. It can happen that for several days there were no changes, and autobackup was not invoked. In that case add MAXDAYS clause:
RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup maxdays 100;
Starting restore at 26-JUL-11
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: PME
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110723
channel ORA_DISK_1: looking for autobackup on day: 20110722
channel ORA_DISK_1: looking for autobackup on day: 20110721
channel ORA_DISK_1: looking for autobackup on day: 20110720
channel ORA_DISK_1: looking for autobackup on day: 20110719
channel ORA_DISK_1: looking for autobackup on day: 20110718
channel ORA_DISK_1: looking for autobackup on day: 20110717
channel ORA_DISK_1: looking for autobackup on day: 20110716
channel ORA_DISK_1: looking for autobackup on day: 20110715
channel ORA_DISK_1: looking for autobackup on day: 20110714
channel ORA_DISK_1: looking for autobackup on day: 20110713
channel ORA_DISK_1: looking for autobackup on day: 20110712
channel ORA_DISK_1: looking for autobackup on day: 20110711
channel ORA_DISK_1: looking for autobackup on day: 20110710
channel ORA_DISK_1: looking for autobackup on day: 20110709
channel ORA_DISK_1: looking for autobackup on day: 20110708
channel ORA_DISK_1: looking for autobackup on day: 20110707
channel ORA_DISK_1: looking for autobackup on day: 20110706
channel ORA_DISK_1: looking for autobackup on day: 20110705
channel ORA_DISK_1: looking for autobackup on day: 20110704
channel ORA_DISK_1: looking for autobackup on day: 20110703
channel ORA_DISK_1: looking for autobackup on day: 20110702
channel ORA_DISK_1: looking for autobackup on day: 20110701
channel ORA_DISK_1: looking for autobackup on day: 20110630
channel ORA_DISK_1: looking for autobackup on day: 20110629
channel ORA_DISK_1: looking for autobackup on day: 20110628
channel ORA_DISK_1: looking for autobackup on day: 20110627
channel ORA_DISK_1: looking for autobackup on day: 20110626
channel ORA_DISK_1: looking for autobackup on day: 20110625
channel ORA_DISK_1: looking for autobackup on day: 20110624
channel ORA_DISK_1: looking for autobackup on day: 20110623
channel ORA_DISK_1: looking for autobackup on day: 20110622
channel ORA_DISK_1: looking for autobackup on day: 20110621
channel ORA_DISK_1: looking for autobackup on day: 20110620
channel ORA_DISK_1: looking for autobackup on day: 20110619
channel ORA_DISK_1: looking for autobackup on day: 20110618
channel ORA_DISK_1: looking for autobackup on day: 20110617
channel ORA_DISK_1: looking for autobackup on day: 20110616
channel ORA_DISK_1: looking for autobackup on day: 20110615
channel ORA_DISK_1: looking for autobackup on day: 20110614
channel ORA_DISK_1: looking for autobackup on day: 20110613
channel ORA_DISK_1: looking for autobackup on day: 20110612
channel ORA_DISK_1: looking for autobackup on day: 20110611
channel ORA_DISK_1: looking for autobackup on day: 20110610
channel ORA_DISK_1: looking for autobackup on day: 20110609
channel ORA_DISK_1: looking for autobackup on day: 20110608
channel ORA_DISK_1: looking for autobackup on day: 20110607
channel ORA_DISK_1: looking for autobackup on day: 20110606
channel ORA_DISK_1: looking for autobackup on day: 20110605
channel ORA_DISK_1: looking for autobackup on day: 20110604
channel ORA_DISK_1: looking for autobackup on day: 20110603
channel ORA_DISK_1: looking for autobackup on day: 20110602
channel ORA_DISK_1: looking for autobackup on day: 20110601
channel ORA_DISK_1: looking for autobackup on day: 20110531
channel ORA_DISK_1: looking for autobackup on day: 20110530
channel ORA_DISK_1: looking for autobackup on day: 20110529
channel ORA_DISK_1: looking for autobackup on day: 20110528
channel ORA_DISK_1: looking for autobackup on day: 20110527
channel ORA_DISK_1: looking for autobackup on day: 20110526
channel ORA_DISK_1: looking for autobackup on day: 20110525
channel ORA_DISK_1: looking for autobackup on day: 20110524
channel ORA_DISK_1: looking for autobackup on day: 20110523
channel ORA_DISK_1: looking for autobackup on day: 20110522
channel ORA_DISK_1: looking for autobackup on day: 20110521
channel ORA_DISK_1: looking for autobackup on day: 20110520
channel ORA_DISK_1: looking for autobackup on day: 20110519
channel ORA_DISK_1: looking for autobackup on day: 20110518
channel ORA_DISK_1: looking for autobackup on day: 20110517
channel ORA_DISK_1: looking for autobackup on day: 20110516
channel ORA_DISK_1: looking for autobackup on day: 20110515
channel ORA_DISK_1: looking for autobackup on day: 20110514
channel ORA_DISK_1: looking for autobackup on day: 20110513
channel ORA_DISK_1: looking for autobackup on day: 20110512
channel ORA_DISK_1: looking for autobackup on day: 20110511
channel ORA_DISK_1: looking for autobackup on day: 20110510
channel ORA_DISK_1: looking for autobackup on day: 20110509
channel ORA_DISK_1: looking for autobackup on day: 20110508
channel ORA_DISK_1: looking for autobackup on day: 20110507
channel ORA_DISK_1: looking for autobackup on day: 20110506
channel ORA_DISK_1: looking for autobackup on day: 20110505
channel ORA_DISK_1: looking for autobackup on day: 20110504
channel ORA_DISK_1: looking for autobackup on day: 20110503
channel ORA_DISK_1: looking for autobackup on day: 20110502
channel ORA_DISK_1: looking for autobackup on day: 20110501
channel ORA_DISK_1: looking for autobackup on day: 20110430
channel ORA_DISK_1: looking for autobackup on day: 20110429
channel ORA_DISK_1: looking for autobackup on day: 20110428
channel ORA_DISK_1: looking for autobackup on day: 20110427
channel ORA_DISK_1: looking for autobackup on day: 20110426
channel ORA_DISK_1: looking for autobackup on day: 20110425
channel ORA_DISK_1: looking for autobackup on day: 20110424
channel ORA_DISK_1: looking for autobackup on day: 20110423
channel ORA_DISK_1: looking for autobackup on day: 20110422
channel ORA_DISK_1: looking for autobackup on day: 20110421
channel ORA_DISK_1: looking for autobackup on day: 20110420
channel ORA_DISK_1: looking for autobackup on day: 20110419
channel ORA_DISK_1: looking for autobackup on day: 20110418
channel ORA_DISK_1: looking for autobackup on day: 20110417
channel ORA_DISK_1: looking for autobackup on day: 20110416
channel ORA_DISK_1: looking for autobackup on day: 20110415
channel ORA_DISK_1: no autobackup in 100 days found
channel ORA_DISK_1: looking for autobackup on day: 20110726
channel ORA_DISK_1: autobackup found: C:\Backup\c-941578866-20110726-03
channel ORA_DISK_1: control file restore from autobackup complete
Finished restore at 26-JUL-11
It will force RMAN to look for copies for more days into past. Similarly You can restore SPFILE:
RMAN> restore until time 'sysdate-3'
2> SPFILE to 'c:\temp\SPFILE' from autobackup maxdays 100;
Starting restore at 26-JUL-11
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: PME
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110723
channel ORA_DISK_1: looking for autobackup on day: 20110722
channel ORA_DISK_1: looking for autobackup on day: 20110721
channel ORA_DISK_1: looking for autobackup on day: 20110720
channel ORA_DISK_1: looking for autobackup on day: 20110719
channel ORA_DISK_1: looking for autobackup on day: 20110718
channel ORA_DISK_1: looking for autobackup on day: 20110717
channel ORA_DISK_1: looking for autobackup on day: 20110716
channel ORA_DISK_1: looking for autobackup on day: 20110715
channel ORA_DISK_1: looking for autobackup on day: 20110714
channel ORA_DISK_1: looking for autobackup on day: 20110713
channel ORA_DISK_1: looking for autobackup on day: 20110712
channel ORA_DISK_1: looking for autobackup on day: 20110711
channel ORA_DISK_1: looking for autobackup on day: 20110710
channel ORA_DISK_1: looking for autobackup on day: 20110709
channel ORA_DISK_1: looking for autobackup on day: 20110708
channel ORA_DISK_1: looking for autobackup on day: 20110707
channel ORA_DISK_1: looking for autobackup on day: 20110706
channel ORA_DISK_1: looking for autobackup on day: 20110705
channel ORA_DISK_1: looking for autobackup on day: 20110704
channel ORA_DISK_1: looking for autobackup on day: 20110703
channel ORA_DISK_1: looking for autobackup on day: 20110702
channel ORA_DISK_1: looking for autobackup on day: 20110701
channel ORA_DISK_1: looking for autobackup on day: 20110630
channel ORA_DISK_1: looking for autobackup on day: 20110629
channel ORA_DISK_1: looking for autobackup on day: 20110628
channel ORA_DISK_1: looking for autobackup on day: 20110627
channel ORA_DISK_1: looking for autobackup on day: 20110626
channel ORA_DISK_1: looking for autobackup on day: 20110625
channel ORA_DISK_1: looking for autobackup on day: 20110624
channel ORA_DISK_1: looking for autobackup on day: 20110623
channel ORA_DISK_1: looking for autobackup on day: 20110622
channel ORA_DISK_1: looking for autobackup on day: 20110621
channel ORA_DISK_1: looking for autobackup on day: 20110620
channel ORA_DISK_1: looking for autobackup on day: 20110619
channel ORA_DISK_1: looking for autobackup on day: 20110618
channel ORA_DISK_1: looking for autobackup on day: 20110617
channel ORA_DISK_1: looking for autobackup on day: 20110616
channel ORA_DISK_1: looking for autobackup on day: 20110615
channel ORA_DISK_1: looking for autobackup on day: 20110614
channel ORA_DISK_1: looking for autobackup on day: 20110613
channel ORA_DISK_1: looking for autobackup on day: 20110612
channel ORA_DISK_1: looking for autobackup on day: 20110611
channel ORA_DISK_1: looking for autobackup on day: 20110610
channel ORA_DISK_1: looking for autobackup on day: 20110609
channel ORA_DISK_1: looking for autobackup on day: 20110608
channel ORA_DISK_1: looking for autobackup on day: 20110607
channel ORA_DISK_1: looking for autobackup on day: 20110606
channel ORA_DISK_1: looking for autobackup on day: 20110605
channel ORA_DISK_1: looking for autobackup on day: 20110604
channel ORA_DISK_1: looking for autobackup on day: 20110603
channel ORA_DISK_1: looking for autobackup on day: 20110602
channel ORA_DISK_1: looking for autobackup on day: 20110601
channel ORA_DISK_1: looking for autobackup on day: 20110531
channel ORA_DISK_1: looking for autobackup on day: 20110530
channel ORA_DISK_1: looking for autobackup on day: 20110529
channel ORA_DISK_1: looking for autobackup on day: 20110528
channel ORA_DISK_1: looking for autobackup on day: 20110527
channel ORA_DISK_1: looking for autobackup on day: 20110526
channel ORA_DISK_1: looking for autobackup on day: 20110525
channel ORA_DISK_1: looking for autobackup on day: 20110524
channel ORA_DISK_1: looking for autobackup on day: 20110523
channel ORA_DISK_1: looking for autobackup on day: 20110522
channel ORA_DISK_1: looking for autobackup on day: 20110521
channel ORA_DISK_1: looking for autobackup on day: 20110520
channel ORA_DISK_1: looking for autobackup on day: 20110519
channel ORA_DISK_1: looking for autobackup on day: 20110518
channel ORA_DISK_1: looking for autobackup on day: 20110517
channel ORA_DISK_1: looking for autobackup on day: 20110516
channel ORA_DISK_1: looking for autobackup on day: 20110515
channel ORA_DISK_1: looking for autobackup on day: 20110514
channel ORA_DISK_1: looking for autobackup on day: 20110513
channel ORA_DISK_1: looking for autobackup on day: 20110512
channel ORA_DISK_1: looking for autobackup on day: 20110511
channel ORA_DISK_1: looking for autobackup on day: 20110510
channel ORA_DISK_1: looking for autobackup on day: 20110509
channel ORA_DISK_1: looking for autobackup on day: 20110508
channel ORA_DISK_1: looking for autobackup on day: 20110507
channel ORA_DISK_1: looking for autobackup on day: 20110506
channel ORA_DISK_1: looking for autobackup on day: 20110505
channel ORA_DISK_1: looking for autobackup on day: 20110504
channel ORA_DISK_1: looking for autobackup on day: 20110503
channel ORA_DISK_1: looking for autobackup on day: 20110502
channel ORA_DISK_1: looking for autobackup on day: 20110501
channel ORA_DISK_1: looking for autobackup on day: 20110430
channel ORA_DISK_1: looking for autobackup on day: 20110429
channel ORA_DISK_1: looking for autobackup on day: 20110428
channel ORA_DISK_1: looking for autobackup on day: 20110427
channel ORA_DISK_1: looking for autobackup on day: 20110426
channel ORA_DISK_1: looking for autobackup on day: 20110425
channel ORA_DISK_1: looking for autobackup on day: 20110424
channel ORA_DISK_1: looking for autobackup on day: 20110423
channel ORA_DISK_1: looking for autobackup on day: 20110422
channel ORA_DISK_1: looking for autobackup on day: 20110421
channel ORA_DISK_1: looking for autobackup on day: 20110420
channel ORA_DISK_1: looking for autobackup on day: 20110419
channel ORA_DISK_1: looking for autobackup on day: 20110418
channel ORA_DISK_1: looking for autobackup on day: 20110417
channel ORA_DISK_1: looking for autobackup on day: 20110416
channel ORA_DISK_1: looking for autobackup on day: 20110415
channel ORA_DISK_1: no autobackup in 100 days found
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110726
channel ORA_DISK_1: autobackup found: C:\Backup\c-941578866-20110726-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 26-JUL-11
After restoring SPFILE You can convert it to PFILE, so You can easily read settings:
RMAN> sql "create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''";
sql statement: create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''
Now more complex scenario: You have lost all database files including CONTROLFILE and SPFILE. You only have backups created by RMAN. In that case You have to eventually install Database Software. Then setup environment variables and start recovery. On Windows platform You have to create Service to be able to startup oracle. You probably remember instance name and SID. If You don't it can be usually found in some TNSNAMES files on client machines. But You probably do not know DBID. You can easily find DBID – it'is part of the name of autobackup file. Details in my previous post. So if autobackup file name is 'c-941578866-20110726-03' then DBID is 941578866. Now we can start recovery:
RMAN target=/
At the beginning set Database ID:
RMAN> SET DBID 941578866;
Then startup instance:
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 113246888 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
RMAN>
and run recovery of SPFILE
RMAN> RUN {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';
3> RESTORE SPFILE FROM AUTOBACKUP; }
I suggest also creating PFILE from SPFILE. Then You should check if all file locations are correct, all directories exists and Oracle have rights to write in them.
RMAN> shutdown ;
Oracle instance shut down
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 113246888 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
RMAN> RUN {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';
3> RESTORE CONTROLFILE FROM AUTOBACKUP; }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 26-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: PME
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110726
channel ORA_DISK_1: looking for autobackup on day: 20110725
channel ORA_DISK_1: looking for autobackup on day: 20110724
channel ORA_DISK_1: looking for autobackup on day: 20110723
channel ORA_DISK_1: looking for autobackup on day: 20110722
channel ORA_DISK_1: looking for autobackup on day: 20110721
channel ORA_DISK_1: looking for autobackup on day: 20110720
channel ORA_DISK_1: no autobackup in 100 days found
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20110726
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20110726-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 26-JUL-11
Now You can continue recovery of datafiles.
