Cloning a Standalone Database without RMAN
Posted at Sunday, September 21, 2008
This post covers how to clone/duplicate a database the old-fashioned way, without RMAN, using manual scripts, etc.
Backup the Source Database
In my example, I backup a 10.2.0.4 database called 'cubs' on a host called 'rac1', using a series of customizable scripts. Feel free to download and customize these 3 scripts to suit your needs.
Once the scripts have been edited, run the run_hotbkp.bsh script.
The hot backup copies the datafiles, etc. to the backup location of your choosing.
Prepare the Target Host
In my example, I will be cloning the 'cubs' database to the 'rac2' host, renaming it to 'colts.'
Create any necessary directories
Update /etc/oratab
Create a Passwordfile for the Target Database
Create a PFILE for the Target Database
Create a PFILE on the Source host, transfer it to the Target, and modify it as appropriate.
Edit the necessary values; for example:
Transfer the Hot Backup
Move the Hot Backup to the desired location on the Target Host.
Transfer Needed Archive Logs to the Target
Edit the Controlfile Trace
Edit the text-based controlfile script until it resembles the following
I normally recover until cancel because user intervention is often required. This was the case for my recovery, because a datafile was added after the backup.
Run the modified script
Respond to the recovery:
Remedy the file situtaion via the following commands
Resume the recovery
Open the Database RESETLOGS:
Complete Post-Cloning Steps
Add back tempfiles
Change the DBID (Database ID) of the Database via nid
Optionally, create a SPFILE
Change the log_archive_format parameter if necessary
Backup the Source Database
In my example, I backup a 10.2.0.4 database called 'cubs' on a host called 'rac1', using a series of customizable scripts. Feel free to download and customize these 3 scripts to suit your needs.
Once the scripts have been edited, run the run_hotbkp.bsh script.
The hot backup copies the datafiles, etc. to the backup location of your choosing.
ls -lart /u01/app/oracle/orabackup/cubs
total 779072
drwxr-xr-x 4 oracle dba 4096 Sep 21 12:15 ../
-rw-r----- 1 oracle dba 5251072 Sep 21 14:27 users01.dbf
-rw-r----- 1 oracle dba 26222592 Sep 21 14:27 undotbs01.dbf
-rw-r----- 1 oracle dba 503324672 Sep 21 14:27 system01.dbf
-rw-r----- 1 oracle dba 262152192 Sep 21 14:28 sysaux01.dbf
-rw-r--r-- 1 oracle dba 5602 Sep 21 14:28 CUBS.ctlbkp
Prepare the Target Host
In my example, I will be cloning the 'cubs' database to the 'rac2' host, renaming it to 'colts.'
Create any necessary directories
$ mkdir -p $ORACLE_BASE/admin/colts/adump
$ mkdir -p $ORACLE_BASE/admin/colts/bdump
$ mkdir -p $ORACLE_BASE/admin/colts/cdump
$ mkdir -p $ORACLE_BASE/admin/colts/udump
$ mkdir -p $ORACLE_BASE/oradata/colts
$ mkdir -p $ORACLE_BASE/oradata/colts/arch
Update /etc/oratab
echo "colts:/u01/app/oracle/product/10.2:N" >> /etc/oratab
Create a Passwordfile for the Target Database
$ export ORACLE_SID=colts
$ . oraenv
$ orapwd file=$ORACLE_HOME/dbs/orapwcolts password=password;
Create a PFILE for the Target Database
Create a PFILE on the Source host, transfer it to the Target, and modify it as appropriate.
$ export ORACLE_SID=cubs
$ . oraenv
$ sqlplus "/ as sysdba"
SQL> create pfile='/tmp/initcolts.ora' from spfile;
$ scp /tmp/initcolts.ora oracle@rac2:/u01/app/oracle/product/10.2/dbs/.
Edit the necessary values; for example:
*.audit_file_dest='/u01/app/oracle/admin/colts/adump'
*.background_dump_dest='/u01/app/oracle/admin/colts/bdump'
*.control_files='/u01/app/oracle/oradata/colts/control01.ctl','/u01/app/oracle/oradata/colts/control02.ctl','/u01/app/oracle/oradata/colts/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/colts/cdump'
*.db_file_name_convert='/cubs/','/colts/'
*.db_name='colts'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=coltsXDB)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/colts/arch'
*.log_archive_format='colts_%t_%s_%r.dbf'
*.user_dump_dest='/u01/app/oracle/admin/colts/udump'
Transfer the Hot Backup
Move the Hot Backup to the desired location on the Target Host.
$ scp /u01/app/oracle/orabackup/cubs/* oracle@rac2:/u01/app/oracle/oradata/colts/.
oracle@rac2's password:
CUBS.ctlbkp 100% 5602 5.5KB/s 00:00
sysaux01.dbf 100% 250MB 27.8MB/s 00:09
system01.dbf 100% 480MB 25.3MB/s 00:19
undotbs01.dbf 100% 25MB 25.0MB/s 00:01
users01.dbf 100% 5128KB 5.0MB/s 00:00
Transfer Needed Archive Logs to the Target
$ scp -p /u02/oracle/arch/* oracle@rac2:/u01/app/oracle/oradata/colts/arch/.
Edit the Controlfile Trace
$ vi /u01/app/oracle/oradata/colts/CUBS.ctlbkp
Edit the text-based controlfile script until it resembles the following
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "COLTS" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/colts/redo01.log' SIZE 50M REUSE,
GROUP 2 '/u01/app/oracle/oradata/colts/redo02.log' SIZE 50M REUSE,
GROUP 3 '/u01/app/oracle/oradata/colts/redo03.log' SIZE 50M REUSE
DATAFILE
'/u01/app/oracle/oradata/colts/system01.dbf',
'/u01/app/oracle/oradata/colts/undotbs01.dbf',
'/u01/app/oracle/oradata/colts/sysaux01.dbf',
'/u01/app/oracle/oradata/colts/users01.dbf'
CHARACTER SET AL32UTF8
;
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
I normally recover until cancel because user intervention is often required. This was the case for my recovery, because a datafile was added after the backup.
Run the modified script
$ sqlplus "/ as sysdba"
SQL> @CUBS.ctlbkp
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1266416 bytes
Variable Size 62917904 bytes
Database Buffers 104857600 bytes
Redo Buffers 7118848 bytes
Control file created.
ORA-00279: change 485386 generated at 09/21/2008 15:46:08 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_42_666013624.dbf
ORA-00280: change 485386 for thread 1 is in sequence #42
Respond to the recovery:
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/cubs/tools01.dbf'
ORA-01112: media recovery not started Remedy the file situtaion via the following commands
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/colts/system01.dbf
/u01/app/oracle/oradata/colts/undotbs01.dbf
/u01/app/oracle/oradata/colts/sysaux01.dbf
/u01/app/oracle/oradata/colts/users01.dbf
/u01/app/oracle/product/10.2/dbs/UNNAMED00005
SQL> alter database create datafile '/u01/app/oracle/product/10.2/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/colts/tools01.dbf';
Database altered.
Resume the recovery
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 485384 generated at 09/21/2008 15:46:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_42_666013624.dbf
ORA-00280: change 485384 for thread 1 is in sequence #42
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 485411 generated at 09/21/2008 15:46:20 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_43_666013624.dbf
ORA-00280: change 485411 for thread 1 is in sequence #43
ORA-00278: log file
'/u01/app/oracle/oradata/colts/arch/cubs_1_42_666013624.dbf' no longer needed
for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 485415 generated at 09/21/2008 15:46:25 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_44_666013624.dbf
ORA-00280: change 485415 for thread 1 is in sequence #44
ORA-00278: log file
'/u01/app/oracle/oradata/colts/arch/cubs_1_43_666013624.dbf' no longer needed
for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
Open the Database RESETLOGS:
SQL> alter database open resetlogs;
Complete Post-Cloning Steps
Add back tempfiles
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/colts/temp01.dbf' size 250M;
Change the DBID (Database ID) of the Database via nid
SQL> shutdown immediate;
SQL> startup mount;
$ nid target=/
DBNEWID: Release 10.2.0.4.0 - Production on Sun Sep 21 16:55:20 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database COLTS (DBID=2153372661)
Connected to server version 10.2.0
Control Files in database:
/u01/app/oracle/oradata/colts/control01.ctl
/u01/app/oracle/oradata/colts/control02.ctl
/u01/app/oracle/oradata/colts/control03.ctl
Change database ID of database COLTS? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2153372661 to 2820171320
Control File /u01/app/oracle/oradata/colts/control01.ctl - modified
Control File /u01/app/oracle/oradata/colts/control02.ctl - modified
Control File /u01/app/oracle/oradata/colts/control03.ctl - modified
Datafile /u01/app/oracle/oradata/colts/system01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/colts/undotbs01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/colts/sysaux01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/colts/users01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/colts/tools01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/colts/temp01.dbf - dbid changed
Control File /u01/app/oracle/oradata/colts/control01.ctl - dbid changed
Control File /u01/app/oracle/oradata/colts/control02.ctl - dbid changed
Control File /u01/app/oracle/oradata/colts/control03.ctl - dbid changed
Instance shut down
Database ID for database COLTS changed to 2820171320.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
SQL> startup mount;
SQL> alter database open resetlogs;
Optionally, create a SPFILE
SQL> create spfile from pfile;
Change the log_archive_format parameter if necessary
SQL> alter system set log_archive_format='colts_%t_%s_%r.dbf' scope=spfile;
SQL> shutdown immediate;
SQL> startup
Labels: Backup and Recovery
SQL Server: Point in Time Recovery Example
Posted at Thursday, May 29, 2008
This example demonstrates how to backup a SQL Server Database and then restore it to a specific point in time.
The database in question uses the FULL recovery model, which supports such a PITR (Point in Time Recovery). The example uses a simple table to illustrate how all this works.
The example first creates a sample table and performs a full backup on the database. Then, values are inserted into the table periodically, interlaced with transaction log backups.
Set-up the example by running PITRBackupExample.sql.
The script yields the following output.
In this example, I want to restore the database to the point at which the 'Point-in-time I want to recover to!' value was inserted into the sample table. To do this, a restore point just after this transaction, specifically the '2008-05-28 11:58:09.680' timestamp, will need to be specified.
The recovery first places the database in single-user mode and performs a tail-log transaction log backup. Then, the full backup and transaction log backups are applied. For the last transaction log backup the STOPAT option is specified, along with the WITH RECOVERY option, which denotes that this operation is to end the recovery. Afterwards, the database is placed into multi-user mode. Last, we check the sample table to make sure that we recovered successfully to the desired timestamp.
Recover the example by running PITRExample.sql
The script yields the following output
The database in question uses the FULL recovery model, which supports such a PITR (Point in Time Recovery). The example uses a simple table to illustrate how all this works.
The example first creates a sample table and performs a full backup on the database. Then, values are inserted into the table periodically, interlaced with transaction log backups.
Set-up the example by running PITRBackupExample.sql.
The script yields the following output.
In this example, I want to restore the database to the point at which the 'Point-in-time I want to recover to!' value was inserted into the sample table. To do this, a restore point just after this transaction, specifically the '2008-05-28 11:58:09.680' timestamp, will need to be specified.
The recovery first places the database in single-user mode and performs a tail-log transaction log backup. Then, the full backup and transaction log backups are applied. For the last transaction log backup the STOPAT option is specified, along with the WITH RECOVERY option, which denotes that this operation is to end the recovery. Afterwards, the database is placed into multi-user mode. Last, we check the sample table to make sure that we recovered successfully to the desired timestamp.
Recover the example by running PITRExample.sql
The script yields the following output
Labels: Backup and Recovery, SQL Server
How to Backup and Restore a RAC Database
Posted at Saturday, May 24, 2008
This example illustrates how to backup and recover a 2-node, 10gR2 RAC cluster on Linux. Backup and recovery operations for RAC databases are similar to that of single instance databases, however there are some key differences - the goal of this post is to highlight the steps required for recoverying RAC databases.
Backup the Database
Use the method you prefer. I used a NOCATALOG, disk-based backup for this example, using the scripts found here.
Simulate the Failure
Shutdown the database and simulate a failure by dropping and/or corrupting a datafile.
Verify the Failure
You will find something similar to the following in the instance's alert log:
Restore the Database
First, take the database out of cluster mode via:
Then restore the database via RMAN:
Since my database is so small, I will simply restore the entire thing, however, since you know which datafile is missing, you could simply restore it and then recover the database as necessary.
Recover the Database
Afterwards, place the database back into cluster mode and startup both instances:
Backup the Database
Use the method you prefer. I used a NOCATALOG, disk-based backup for this example, using the scripts found here.
Simulate the Failure
Shutdown the database and simulate a failure by dropping and/or corrupting a datafile.
# export ORACLE_SID=em1
# . oraenv
# srvctl stop database -d em
# srvctl status database -d em
Instance em1 is not running on node rac1
Instance em2 is not running on node rac2
# cd /u02/oracle/ora1/em
# rm sysaux01.dbf
Verify the Failure
# srvctl start instance -d em -i em1
PRKP-1001 : Error starting instance em1 on node rac1
CRS-0215: Could not start resource 'ora.em.em1.inst'.
You will find something similar to the following in the instance's alert log:
Sat May 24 16:53:47 2008
Errors in file /u02/oracle/logs/em/bdump/em1_dbw0_16947.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u02/oracle/ora1/em/sysaux01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
Sat May 24 16:53:49 2008
Shutting down instance (abort)
Restore the Database
First, take the database out of cluster mode via:
# sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Sat May 24 17:02:17 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1261300 bytes
Variable Size 251658508 bytes
Database Buffers 8388608 bytes
Redo Buffers 7127040 bytes
SQL> alter system set cluster_database=false scope=spfile sid='*';
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Then restore the database via RMAN:
# rman target=/
Recovery Manager: Release 10.2.0.3.0 - Production on Sat May 24 17:04:10 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 268435456 bytes
Fixed Size 1261300 bytes
Variable Size 251658508 bytes
Database Buffers 8388608 bytes
Redo Buffers 7127040 bytes
RMAN> restore database;
Starting restore at 24-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oracle/ora1/em/system01.dbf
restoring datafile 00002 to /u02/oracle/ora1/em/undotbs01.dbf
restoring datafile 00003 to /u02/oracle/ora1/em/sysaux01.dbf
restoring datafile 00004 to /u02/oracle/ora1/em/users01.dbf
restoring datafile 00005 to /u02/oracle/ora1/em/example01.dbf
restoring datafile 00006 to /u02/oracle/ora1/em/undotbs02.dbf
restoring datafile 00007 to /u02/oracle/ora2/em/mgmt.dbf
restoring datafile 00008 to /u02/oracle/ora1/em/mgmt_ecm_depot1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oracle/ora3/em/backups/0ijh6j4t_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oracle/ora3/em/backups/0ijh6j4t_1_1 tag=FHB_EM1200805241630
channel ORA_DISK_1: restore complete, elapsed time: 00:06:36
Finished restore at 24-MAY-08
Since my database is so small, I will simply restore the entire thing, however, since you know which datafile is missing, you could simply restore it and then recover the database as necessary.
Recover the Database
RMAN> recover database;
Starting recover at 24-MAY-08
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 24-MAY-08
RMAN> alter database open;
database opened
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Afterwards, place the database back into cluster mode and startup both instances:
# sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Sat May 24 17:16:36 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> alter system set cluster_database=true scope=spfile sid='*';
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
# srvctl start database -d em
[oracle@rac1 bdump]$ srvctl status database -d em
Instance em1 is running on node rac1
Instance em2 is running on node rac2
Labels: Backup and Recovery, RAC
Backup and Recovery of SQL Server System Databases
Posted at Monday, May 19, 2008
This posting covers how to backup and restore SQL Servers' system databases - master, msdb, model, etc.
In this example, I backup SQL Server's databases and then replicate a loss/corruption - and subsequent restoration - of the master database's underlying datafiles.
In SQL Server 2000, the rebuildm.exe executable was available to help you rebuild the master database so that you could start the SQL Server and then, in turn, restore the master (and related system databases) database from an available backup.
In SQL Server 2005, the rebuildm.exe executable is not available, rather the approved procedure is to call the setup.exe of the installation binaries, passing the appropriate parameters/switches, etc. in order to rebuild the master database. Afterwards, you could startup the database engine and then restore the system databases from backups. Unfortunately, this process is riddled with bugs, particularly post application of SP2.
Given the aforementioned, my preferred approach is to retain offline copies of the system databases' underlying datafiles. In the event of a loss/corruption of the master database, I simply restore these old datafile copies to the default location. This allows me to startup the database engine and restore the system databases from backups, avoiding the above flawed mechanisms. Such datafile backups should be taken at key times, such as post-upgrade, etc.
All the scripts used in this example are available in the following .zip file:
bckp_restore_sys_dbs_sql2005.zip
My example uses SQL Server 2005 - my instance is named PRIVATE-E3A52F5\SS1
Step 1 - backup_master_files.bat
I first take an offline file backup of the master database's datafiles:
Step 2 - backup_databases.bat
Next, I backup all the SQL Server's databases via a batch file:
This batch file calls the following SQL script:
backup_all_databases.sql
Yielding the following output:
Step 3 - destroy_master.bat
When ready to perform the test, I call this batch file to simulate a destruction/corruption of the master database.
At this point, attempts to start the database engine, would yield something similar to the following with the ERRORLOG:
Step 4 - recover_master.bat
Next, a batch file is called to perform the recovery:
The batch file first restores the offline backups of the master database's datafiles and then performs a recovery of that database via:
restore_master.sql
Yielding the following output:
Next, the batch file restores the other system databases via:
restore_databases.sql
Yielding the following output:
At this point, your SQL Server Instance should be ready for use.
In this example, I backup SQL Server's databases and then replicate a loss/corruption - and subsequent restoration - of the master database's underlying datafiles.
In SQL Server 2000, the rebuildm.exe executable was available to help you rebuild the master database so that you could start the SQL Server and then, in turn, restore the master (and related system databases) database from an available backup.
In SQL Server 2005, the rebuildm.exe executable is not available, rather the approved procedure is to call the setup.exe of the installation binaries, passing the appropriate parameters/switches, etc. in order to rebuild the master database. Afterwards, you could startup the database engine and then restore the system databases from backups. Unfortunately, this process is riddled with bugs, particularly post application of SP2.
Given the aforementioned, my preferred approach is to retain offline copies of the system databases' underlying datafiles. In the event of a loss/corruption of the master database, I simply restore these old datafile copies to the default location. This allows me to startup the database engine and restore the system databases from backups, avoiding the above flawed mechanisms. Such datafile backups should be taken at key times, such as post-upgrade, etc.
All the scripts used in this example are available in the following .zip file:
bckp_restore_sys_dbs_sql2005.zip
My example uses SQL Server 2005 - my instance is named PRIVATE-E3A52F5\SS1
Step 1 - backup_master_files.bat
I first take an offline file backup of the master database's datafiles:
net stop "SQL Server Agent (SS1)"
net stop "SQL Server (SS1)"
copy /Y "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf" "C:\backups\master.mdf"
copy /Y "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf" "C:\backups\mastlog.ldf"
net start "SQL Server (SS1)"
net start "SQL Server Agent (SS1)"
Step 2 - backup_databases.bat
Next, I backup all the SQL Server's databases via a batch file:
sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "C:\Documents and Settings\James\Desktop\backup_all_databases.sql" -o "C:\Documents and Settings\James\Desktop\backup_all_databases.out"
This batch file calls the following SQL script:
backup_all_databases.sql
--Use the foreach stored procedure
--To backup each database to disk - except - tempdb
--Create necessary sub-directories and validate each backup
EXEC master.sys.sp_MSforeachdb @command1='
BEGIN
DECLARE @bkpfile NVARCHAR(1000), @bkploc NVARCHAR(500), @bkpname NVARCHAR(500);
DECLARE @backupsetid INT;
SET @bkploc = ''C:\backups\?\'';
PRINT @bkploc;
SET @bkpname = @bkploc + ''?_backup_''+ cast(datepart(yyyy, getdate()) as nvarchar) + cast(datepart(mm,getdate()) as nvarchar) + cast(datepart(dd, getdate()) as nvarchar) + cast(datepart(hh, getdate()) as nvarchar) + cast(datepart(mi, getdate()) as nvarchar);
PRINT @bkpname;
SET @bkpfile = @bkpname + ''.BAK'';
IF NOT (''?'' = ''tempdb'')
BEGIN
BEGIN
EXECUTE master.dbo.xp_create_subdir @bkploc;
END;
BEGIN
BACKUP DATABASE ? TO DISK = @bkpfile WITH NOFORMAT, NOINIT, NAME = @bkpname, SKIP, REWIND, NOUNLOAD;
END;
BEGIN
SELECT @backupsetid = position FROM msdb.dbo.backupset WHERE database_name=''?'' AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name=''?'' );
RESTORE VERIFYONLY FROM DISK = @bkpfile WITH FILE = @backupsetid, NOUNLOAD, NOREWIND
END;
END;
END';
GO
Yielding the following output:
C:\backups\master\
C:\backups\master\master_backup_20085181746
Processed 352 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 354 pages in 0.517 seconds (5.599 MB/sec).
The backup set on file 1 is valid.
C:\backups\tempdb\
C:\backups\tempdb\tempdb_backup_20085181746
C:\backups\model\
C:\backups\model\model_backup_20085181746
Processed 152 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.285 seconds (4.426 MB/sec).
The backup set on file 1 is valid.
C:\backups\msdb\
C:\backups\msdb\msdb_backup_20085181746
Processed 600 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 6 pages for database 'msdb', file 'MSDBLog' on file 1.
BACKUP DATABASE successfully processed 606 pages in 0.763 seconds (6.500 MB/sec).
The backup set on file 1 is valid.
C:\backups\ReportServer$SS1\
C:\backups\ReportServer$SS1\ReportServer$SS1_backup_20085181746
Processed 288 pages for database 'ReportServer$SS1', file 'ReportServer$SS1' on file 1.
Processed 1 pages for database 'ReportServer$SS1', file 'ReportServer$SS1_log' on file 1.
BACKUP DATABASE successfully processed 289 pages in 0.496 seconds (4.773 MB/sec).
The backup set on file 1 is valid.
C:\backups\ReportServer$SS1TempDB\
C:\backups\ReportServer$SS1TempDB\ReportServer$SS1TempDB_backup_20085181746
Processed 176 pages for database 'ReportServer$SS1TempDB', file 'ReportServer$SS1TempDB' on file 1.
Processed 1 pages for database 'ReportServer$SS1TempDB', file 'ReportServer$SS1TempDB_log' on file 1.
BACKUP DATABASE successfully processed 177 pages in 0.357 seconds (4.061 MB/sec).
The backup set on file 1 is valid.
Step 3 - destroy_master.bat
When ready to perform the test, I call this batch file to simulate a destruction/corruption of the master database.
net stop "SQL Server Agent (SS1)"
net stop "SQL Server (SS1)"
del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf"
del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf"
At this point, attempts to start the database engine, would yield something similar to the following with the ERRORLOG:
2008-05-18 13:09:26.73 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
2008-05-18 13:09:26.73 Server (c) 2005 Microsoft Corporation.
2008-05-18 13:09:26.73 Server All rights reserved.
2008-05-18 13:09:26.73 Server Server process ID is 3360.
2008-05-18 13:09:26.73 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2008-05-18 13:09:26.74 Server This instance of SQL Server last reported using a process ID of 456 at 5/18/2008 1:08:35 PM (local) 5/18/2008 7:08:35 PM (UTC). This is an informational message only; no user action is required.
2008-05-18 13:09:26.74 Server Registry startup parameters:
2008-05-18 13:09:26.74 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2008-05-18 13:09:26.74 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2008-05-18 13:09:26.74 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2008-05-18 13:09:26.74 Server Error: 17113, Severity: 16, State: 1.
2008-05-18 13:09:26.74 Server Error 2(The system cannot find the file specified.) occurred while opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
Step 4 - recover_master.bat
Next, a batch file is called to perform the recovery:
net stop "SQL Server Agent (SS1)"
net stop "SQL Server (SS1)"
net stop "SQL Server Analysis Services (SS1)"
net stop "SQL Server FullText Search (SS1)"
net stop "SQL Server Integration Services"
net stop "SQL Server Reporting Services (SS1)"
copy /Y "C:\backups\master.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf"
copy /Y "C:\backups\mastlog.ldf" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf"
net start "SQL Server (SS1)" /m
sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "restore_master.sql" -o "restore_master.out"
net start "SQL Server (SS1)"
sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "restore_databases.sql" -o "restore_databases.out"
net start "SQL Server Agent (SS1)"
net start "SQL Server Analysis Services (SS1)"
net start "SQL Server FullText Search (SS1)"
net start "SQL Server Integration Services"
net start "SQL Server Reporting Services (SS1)"
The batch file first restores the offline backups of the master database's datafiles and then performs a recovery of that database via:
restore_master.sql
RESTORE DATABASE [master] from DISK = 'C:\backups\master\master_backup_20085181746.BAK' WITH REPLACE ;
GO
Yielding the following output:
Processed 352 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
Next, the batch file restores the other system databases via:
restore_databases.sql
RESTORE DATABASE [msdb] from DISK = 'C:\backups\msdb\msdb_backup_20085181746.BAK' WITH REPLACE ;
GO
RESTORE DATABASE [model] from DISK = 'C:\backups\model\model_backup_20085181746.BAK' WITH REPLACE ;
GO
Yielding the following output:
Processed 600 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 6 pages for database 'msdb', file 'MSDBLog' on file 1.
RESTORE DATABASE successfully processed 606 pages in 0.531 seconds (9.341 MB/sec).
Processed 152 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
RESTORE DATABASE successfully processed 154 pages in 0.127 seconds (9.933 MB/sec).
At this point, your SQL Server Instance should be ready for use.
Labels: Backup and Recovery, SQL Server
How to Backup and Restore via RMAN without a Recovery Catalog
Posted at Sunday, February 24, 2008
Although an RMAN repository is critical for most enterprises nowadays, there are circumstances in which you may want to backup and restore via RMAN utilizing a database's controlfile (i.e. in lieu of a recovery catalog).
It is possible to backup and restore your database via RMAN without a recovery catalog and to do so in such a way that you can recover from a loss of all database files; the following is an example in which I use an 11g (11.1.0.6) database called nf (short for new features):
Backup the ENTIRE database to RMAN backuppieces using your desired device type:
backup_nf.bsh
Of course, you will need to make small changes to the aforementioned to suit your environment. Once you have done so, run the script.
Afterwards, the backup_nf.log should have all the details necessary to troubleshoot, etc.
backup_nf.log
Now that I have a complete backup of the database - datafiles, archivelogs, spfile, and controlfile - I am ready to drop my database and prove that I can restore from a complete loss of all files.
Drop the Database:
delete_nf.bsh
After editing for your environment, run the script.
Now I am ready to restore and recover my database using only the backuppieces resulting from the aforementioned backup.
Restore and recover the Database:
Identify the backuppiece that has a copy of the spfile and controlfile via
The complete path to this backuppiece will need to be passed to the restore script.
Edit the restore script for your environment:
restore_nf.bsh
After editing as appropriate, run the script.
Afterwards, the restore_nf.log should have all the details necessary to troubleshoot, etc.
restore_nf.log
The files for this post - including complete log files - can be found here: rman_nf.tar
It is possible to backup and restore your database via RMAN without a recovery catalog and to do so in such a way that you can recover from a loss of all database files; the following is an example in which I use an 11g (11.1.0.6) database called nf (short for new features):
Backup the ENTIRE database to RMAN backuppieces using your desired device type:
backup_nf.bsh
#!/bin/bash
export ORACLE_SID=nf;
. oraenv;
export TNS_ADMIN=$ORACLE_HOME/network/admin;
export BACKUP_LOC=/u03/app/oracle/orabackup;
$ORACLE_HOME/bin/rman target=/ nocatalog <<EOF
configure backup optimization on;
configure default device type to disk;
crosscheck backup;
crosscheck archivelog all;
delete noprompt force obsolete;
delete noprompt force expired backup;
delete noprompt force expired archivelog all;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '$BACKUP_LOC/%d_%F_ctl.bckp';
backup full database format '$BACKUP_LOC/%d_%U.bckp' tag rman_nocat_${ORACLE_SID} plus archivelog format '$BACKUP_LOC/%d_%U.bckp' delete input tag rman_nocat_${ORACLE_SID} ;
list recoverable backup;
EOF
Of course, you will need to make small changes to the aforementioned to suit your environment. Once you have done so, run the script.
./backup_nf.bsh > backup_nf.log 2>&1 &
Afterwards, the backup_nf.log should have all the details necessary to troubleshoot, etc.
backup_nf.log
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
Recovery Manager: Release 11.1.0.6.0 - Production on Sat Feb 23 22:43:24 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: NF (DBID=2018903642)
using target database control file instead of recovery catalog
RMAN>
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
RMAN>
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
RMAN>
RMAN>
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u03/app/oracle/orabackup/NF_31j9fdf6_1_1.bckp RECID=56 STAMP=647476722
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u03/app/oracle/orabackup/NF_34j9fdlq_1_1.bckp RECID=57 STAMP=647476933
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u03/app/oracle/orabackup/NF_36j9fdn7_1_1.bckp RECID=58 STAMP=647476979
Crosschecked 3 objects
RMAN>
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
specification does not match any archived log in the recovery catalog
RMAN>
RMAN>
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
no obsolete backups found
RMAN>
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
56 47 1 1 EXPIRED DISK /u03/app/oracle/orabackup/NF_31j9fdf6_1_1.bckp
57 48 1 1 EXPIRED DISK /u03/app/oracle/orabackup/NF_34j9fdlq_1_1.bckp
58 49 1 1 EXPIRED DISK /u03/app/oracle/orabackup/NF_36j9fdn7_1_1.bckp
deleted backup piece
backup piece handle=/u03/app/oracle/orabackup/NF_31j9fdf6_1_1.bckp RECID=56 STAMP=647476722
deleted backup piece
backup piece handle=/u03/app/oracle/orabackup/NF_34j9fdlq_1_1.bckp RECID=57 STAMP=647476933
deleted backup piece
backup piece handle=/u03/app/oracle/orabackup/NF_36j9fdn7_1_1.bckp RECID=58 STAMP=647476979
Deleted 3 EXPIRED objects
RMAN>
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
specification does not match any archived log in the recovery catalog
RMAN>
RMAN>
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/app/oracle/orabackup/%d_%F_ctl.bckp';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/app/oracle/orabackup/%d_%F_ctl.bckp';
new RMAN configuration parameters are successfully stored
RMAN>
RMAN>
Starting backup at 23-FEB-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=746 STAMP=647477049
channel ORA_DISK_1: starting piece 1 at 23-FEB-08
channel ORA_DISK_1: finished piece 1 at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_38j9fdpp_1_1.bckp tag=RMAN_NOCAT_NF comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_7_647475391.arc RECID=746 STAMP=647477049
Finished backup at 23-FEB-08
Starting backup at 23-FEB-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u03/app/oracle/oradata/nf/undotbs01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/nf/sysaux01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/nf/users01.dbf
input datafile file number=00001 name=/u03/app/oracle/oradata/nf/system01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/nf/fba_tbs01.dbf
input datafile file number=00007 name=/u03/app/oracle/oradata/nf/enc_tbs02.dbf
input datafile file number=00006 name=/u03/app/oracle/oradata/nf/enc_tbs101.dbf
input datafile file number=00010 name=/u03/app/oracle/oradata/nf/users02.dbf
channel ORA_DISK_1: starting piece 1 at 23-FEB-08
channel ORA_DISK_1: finished piece 1 at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp tag=RMAN_NOCAT_NF comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:11:35
Finished backup at 23-FEB-08
Starting backup at 23-FEB-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=747 STAMP=647477793
channel ORA_DISK_1: starting piece 1 at 23-FEB-08
channel ORA_DISK_1: finished piece 1 at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp tag=RMAN_NOCAT_NF comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_8_647475391.arc RECID=747 STAMP=647477793
Finished backup at 23-FEB-08
Starting Control File and SPFILE Autobackup at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp comment=NONE
Finished Control File and SPFILE Autobackup at 23-FEB-08
RMAN>
RMAN>
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
50 35.00K DISK 00:00:11 23-FEB-08
BP Key: 59 Status: AVAILABLE Compressed: NO Tag: RMAN_NOCAT_NF
Piece Name: /u03/app/oracle/orabackup/NF_38j9fdpp_1_1.bckp
List of Archived Logs in backup set 50
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7 8095380 23-FEB-08 8095522 23-FEB-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 2.56G DISK 00:11:48 23-FEB-08
BP Key: 60 Status: AVAILABLE Compressed: NO Tag: RMAN_NOCAT_NF
Piece Name: /u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp
List of Datafiles in backup set 51
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/system01.dbf
2 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/sysaux01.dbf
3 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/undotbs01.dbf
4 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/users01.dbf
5 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/fba_tbs01.dbf
6 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/enc_tbs101.dbf
7 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/enc_tbs02.dbf
10 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/users02.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
52 834.00K DISK 00:00:12 23-FEB-08
BP Key: 61 Status: AVAILABLE Compressed: NO Tag: RMAN_NOCAT_NF
Piece Name: /u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp
List of Archived Logs in backup set 52
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 8 8095522 23-FEB-08 8096603 23-FEB-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53 Full 9.73M DISK 00:00:13 23-FEB-08
BP Key: 62 Status: AVAILABLE Compressed: NO Tag: TAG20080223T225647
Piece Name: /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
SPFILE Included: Modification time: 23-FEB-08
SPFILE db_unique_name: NF
Control File Included: Ckp SCN: 8096623 Ckp time: 23-FEB-08
RMAN>
Recovery Manager complete.
Now that I have a complete backup of the database - datafiles, archivelogs, spfile, and controlfile - I am ready to drop my database and prove that I can restore from a complete loss of all files.
Drop the Database:
delete_nf.bsh
#!/bin/bash
export ORACLE_SID=nf;
. oraenv;
sqlplus "/ as sysdba" <<EOF
shutdown abort;
EOF
rm -f /u03/app/oracle/oradata/nf/*.dbf*
rm -f /u03/app/oracle/oradata/nf/*.log*
rm -f /u03/app/oracle/oradata/nf/*.ctl*
rm -f /u03/app/oracle/oradata/nf/arch/*
rm -f $ORACLE_HOME/dbs/*${ORACLE_SID}*
After editing for your environment, run the script.
./delete_nf.bsh
Now I am ready to restore and recover my database using only the backuppieces resulting from the aforementioned backup.
Restore and recover the Database:
Identify the backuppiece that has a copy of the spfile and controlfile via
ls -lart /u03/app/oracle/orabackup/*ctl*
The complete path to this backuppiece will need to be passed to the restore script.
Edit the restore script for your environment:
restore_nf.bsh
#!/bin/bash
if [ "$1" == "" ]; then
echo "usage ${0} path to backuppiece with controlfile backup";
exit 1;
fi
CTL_PIECE=${1} ;
echo ${CTL_PIECE} ;
BACKUP_LOC=/u03/app/oracle/orabackup;
export ORACLE_SID=nf;
. oraenv;
export TNS_ADMIN=$ORACLE_HOME/network/admin ;
$ORACLE_HOME/bin/rman nocatalog <<EOF
connect target /;
startup force nomount;
restore spfile from '${CTL_PIECE}';
shutdown immediate;
startup nomount;
restore controlfile from '${CTL_PIECE}';
shutdown immediate;
startup mount;
catalog start with '${BACKUP_LOC}' noprompt;
restore database;
recover database;
sql 'alter database open resetlogs';
EOF
orapwd file=$ORACLE_HOME/dbs/orapw${ORACLE_SID} password=password ignorecase=Y ;
sqlplus "/ as sysdba" <<EOF
alter tablespace temp add tempfile '/u03/app/oracle/oradata/${ORACLE_SID}/temp01.dbf' size 500M reuse;
EOF
After editing as appropriate, run the script.
./restore_nf.bsh /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp >restore_nf.log 2>&1 &
Afterwards, the restore_nf.log should have all the details necessary to troubleshoot, etc.
restore_nf.log
/u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
Recovery Manager: Release 11.1.0.6.0 - Production on Sat Feb 23 23:03:07 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN>
connected to target database (not started)
RMAN>
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u03/app/oracle/product/db/11.1.0.6/dbs/initnf.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1298584 bytes
Variable Size 67112808 bytes
Database Buffers 83886080 bytes
Redo Buffers 6721536 bytes
RMAN>
Starting restore at 23-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 23-FEB-08
RMAN>
Oracle instance shut down
RMAN>
connected to target database (not started)
Oracle instance started
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 381683840 bytes
Database Buffers 33554432 bytes
Redo Buffers 6131712 bytes
RMAN>
Starting restore at 23-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u03/app/oracle/oradata/nf/control01.ctl
output file name=/u03/app/oracle/oradata/nf/control02.ctl
output file name=/u03/app/oracle/oradata/nf/control03.ctl
Finished restore at 23-FEB-08
RMAN>
Oracle instance shut down
RMAN>
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 381683840 bytes
Database Buffers 33554432 bytes
Redo Buffers 6131712 bytes
RMAN>
searching for all files that match the pattern /u03/app/oracle/orabackup
List of Files Unknown to the Database
=====================================
File Name: /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
RMAN>
Starting restore at 23-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 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 /u03/app/oracle/oradata/nf/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u03/app/oracle/oradata/nf/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u03/app/oracle/oradata/nf/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u03/app/oracle/oradata/nf/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/nf/fba_tbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u03/app/oracle/oradata/nf/enc_tbs101.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u03/app/oracle/oradata/nf/enc_tbs02.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u03/app/oracle/oradata/nf/users02.dbf
channel ORA_DISK_1: reading from backup piece /u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp
channel ORA_DISK_1: piece handle=/u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp tag=RMAN_NOCAT_NF
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:37
Finished restore at 23-FEB-08
RMAN>
Starting recover at 23-FEB-08
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: reading from backup piece /u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp
channel ORA_DISK_1: piece handle=/u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp tag=RMAN_NOCAT_NF
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_8_647475391.arc thread=1 sequence=8
unable to find archived log
archived log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/23/2008 23:09:23
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9 and starting SCN of 8096603
RMAN>
sql statement: alter database open resetlogs
RMAN>
Recovery Manager complete.
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Feb 23 23:11:46 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter tablespace temp add tempfile '/u03/app/oracle/oradata/nf/temp01.dbf' size 500M reuse
*
ERROR at line 1:
ORA-01537: cannot add file '/u03/app/oracle/oradata/nf/temp01.dbf' - file
already part of database
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
The files for this post - including complete log files - can be found here: rman_nf.tar
Labels: Backup and Recovery, RMAN
Standby Database Process Flows
Posted at Friday, July 08, 2005
Process Flows for Building 9i Release 2 Physical Standby Database
Process Flows for Building 9i Release 2 Logical Standby Database
Process Flows for Recoverying Standby Database
Process Flows for Building 9i Release 2 Logical Standby Database
Process Flows for Recoverying Standby Database
Labels: 9i, Backup and Recovery, Data Guard, Process Flows
_507.png)
Subscribe
