Test offline backup and recovery of an Oracle database

Environment: Windows7, oracle11g

One, offline backup
An offline backup is a data backup, also known as a cold backup, in the case of a database shutdown.

The backup steps learned in the book:
1. Record the operating system path where the database files are to be backed up;
2, close the database, do not use shutdown abort this mode of closure;
3, copy the database files to the backup directory;
4. Restart the database to complete the backup.

After understanding these steps, a backup test was made and the database to be backed up is testdb.

1. Log the operating system path where the database files are to be backed up

(1) View the path to the data file (connect with the Administrator account)
Note: You can execute the following SQL statement at the sqlplus command line or Plsql

Select File_name,tablespace_name from Dba_data_files;

D:\APP\LC\ORADATA\TESTDB\USERS01. DBF USERS
D:\APP\LC\ORADATA\TESTDB\UNDOTBS01. DBF UNDOTBS1
D:\APP\LC\ORADATA\TESTDB\SYSAUX01. DBF Sysaux
D:\APP\LC\ORADATA\TESTDB\SYSTEM01. DBF SYSTEM

(2) View the path to the control file
Select name from V$controlfile;

D:\APP\LC\ORADATA\TESTDB\CONTROL01. Ctl
D:\APP\LC\FLASH_RECOVERY_AREA\TESTDB\CONTROL02. Ctl

(3) View the path of the redo log file
Select member from V$logfile;

D:\APP\LC\ORADATA\TESTDB\REDO03. LOG
D:\APP\LC\ORADATA\TESTDB\REDO02. LOG
D:\APP\LC\ORADATA\TESTDB\REDO01. LOG

2. Close the database

Sql> Conn System/[email protected] as Sysdba
is connected.
sql> shutdown Immediate
The database is closed.
The database has been uninstalled.
The ORACLE routine has been closed.

3, copy the database files to the backup directory;
If you can use the host copy copy under Sqlplus, you can also select the file copy directly and copy all the files from step 1th to the D:\dbbackup.

4, start the database.

Sql> Startup
The ORACLE routine has been started.
......

Second, database recovery
In order to simulate database recovery, delete the TestDB in the DBCA tool, and after the deletion succeeds, perform the following recovery steps.
1, copy the files in the D:\dbbackup to the original D:\APP\LC\ORADATA\TESTDB and other directories.
2. Create a new instance
With administrator privileges, execute the following statement in the CMD window, where the TestDB name is the same as the name of the backup
Oradim-new-sid TestDB
3, check whether the Oracle service and monitoring start, if not started, to the Control Panel service or CMD Run command start.
Start Service net start Oracleservicetestdb
Start monitoring Lsnrctl Start
4. Start the database
cmd in the command window
Enter set ORACLE_SID=TESTDB carriage return
Re-enter Sqlplus/nolog return
Re-enter Conn/as SYSDBA return
then enter startup return

Then there was an error:
Ora-01078:failure in processing system parameters
Lrm-00109:could not open parameter file ' D:/app/lc/product/11.2.0/dbhome_1/database/inittestdb.ora '

Check this path, no Inittestdb.ora this file.

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

Search online, the solution is basically the same as the following:

Workaround: Copy the init.ora.012009233838 form of the file in the $oracle_base/admin/database name/pfile directory to the $oracle_home/database directory
Initoracle.ora can be. (Note: ORACLE in Initoracle.ora is your instance name Oracle_sid
------------------------------------------------------
On the computer looked under, in the D:\app\LC\admin\orcl\pfile directory has a init.ora.9182016154717 file, copy it to
D:/app/lc/product/11.2.0/dbhome_1/database directory, the contents of all "ORCL" changed to "TestDB", Save as Inittestdb.ora.
The TestDB and its subdirectories pfile are established under the D:\app\LC\admin directory.
This time back to the CMD command-line window to execute startup, finally successfully started the database.

This time with Plsql login but prompted Ora-12154:tns: Unable to resolve the specified connection identifier.
Workaround:
Open D:\app\LC\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora, imitate the inside of the ORCL code, manually add the following statement
TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = TestDB)
)
)

Iii. Summary
In the example above, you know that in addition to backing up data files, control files, redo log files, and back up parameter files.
Then re-establish a database TDB, this time the backup in addition to data files, control files, redo log files, but also back up
(1) 3 files in D:\app\LC\product\11.2.0\dbhome_1\database directory: Hc_tdb.dat, Pwdtdb.ora, Spfiletdb. ORA
(2) D:\app\LC\admin\tdb the entire directory, there are Adump, Dpdump, Pfile a total of 3 directories, of which Pfile has a init.ora.919201613321 file below.
(3) D:\app\LC\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

Restore data files, control files, redo log files, Spfiletdb. ORA, Tnsnames.ora content restore.
Finally successfully recovered the database.
The Hc_tdb.dat, Pwdtdb.ora, and D:\app\LC\admin\tdb directories seem to be useless to restore, but it's best to restore them.

Attached, the command line to be executed at the time of recovery:

C:\windows\system32>oradim-new-sid TDB
The instance has been created.
C:\windows\system32>set Oracle_sid=tdb
C:\windows\system32>sqlplus/nolog
Sql*plus:release 11.2.0.1.0 Production on Wednesday October 19 15:25:45 2016
Copyright (c) 1982, Oracle. All rights reserved.
Sql> Conn/as SYSDBA
Connected to the idle routine.
Sql> Startup
The ORACLE routine has been started.
Total System Global area 1686925312 bytes
Fixed Size 2176368 bytes
Variable Size 989858448 bytes
Database buffers 687865856 bytes
Redo buffers 7024640 bytes
The database is loaded.
The database is already open.
Sql>

Test offline backup and recovery of an Oracle database