Test the hot backup and complete recovery of Oracle databases

Database Hot Backup and complete recovery test:

1. First, make sure that the database is in archive mode.

2. for hot backup data files, use alter tablespace XXX begin backup to set the tablespace to the backup state. You can manually or run host copy in SQL to copy the data files to a specified place, finally, change the tablespace to the normal state (alter tablepace XXX end backup ).

3. database recovery: Use the statement rcover datafile and alter database open to completely restore the database.

The procedure is as follows:

SQL> Conn/As sysdba

Connected.

// Confirm that the database is in archive mode.

SQL> select log_mode from V $ database;

Log_mode

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

Archivelog

// Query the data file corresponding to the table space. Generally, the tablespace name is the same as the corresponding data file name. skip this step.

SQL> select v $ tablespace. Name, V $ datafile. name from V $ tablespace, V $ datafile

2 Where V $ tablespace. Ts # = V $ datafile. Ts #;

Name

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

Name

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

System

F: \ oracle \ oradata \ ebook \ system01.dbf

Undotbs1

F: \ oracle \ oradata \ ebook \ undotbs01.dbf

Cwmlite

F: \ oracle \ oradata \ ebook \ cwmlite01.dbf

Drsys

F: \ oracle \ oradata \ ebook \ drsys01.dbf

Example

F: \ oracle \ oradata \ ebook \ example01.dbf

Indx

F: \ oracle \ oradata \ ebook \ indx01.dbf

ODM

F: \ oracle \ oradata \ ebook \ odm01.dbf

Tools

F: \ oracle \ oradata \ ebook \ tools01.dbf

Users

F: \ oracle \ oradata \ ebook \ users01.dbf

XDB

F: \ oracle \ oradata \ ebook \ xdb01.dbf

10 rows have been selected.

// Set the tablespace to the backup state.

SQL> alter tablespace system begin backup;

The tablespace has been changed.

// Back up the data file to the directory F: \ oracle \ backuparea.

SQL> host copy F: \ oracle \ oradata \ ebook \ system01.dbf: \ oracle \ backuparea \

1 file has been copied.

SQL> alter tablespace system end backup;

The tablespace has been changed.

// The purpose of table creation test is to test whether the restoration is complete.

SQL> Create Table Scott. Test (T INT );

The table has been created.

SQL> insert into Scott. Test values (1 );

One row has been created.

SQL> insert into Scott. Test values (2 );

One row has been created.

SQL> commit;

Submitted.

SQL> shutdown immediate

The database has been closed. The database has been detached. The Oracle routine has been disabled. // Delete system01.dbf or move it to another directory, causing database crash. The SQL> startup Oracle routine has been started. Total system global area 135338868 bytes fixed size 453492 bytes variable size 109051904 bytes database buffers 25165824 bytes redo buffers 667648 bytes database load is complete. ORA-01157: unable to identify/lock data file 1-see dbwr trace file ORA-01110: Data File 1: 'f: \ oracle \ oradata \ ebook \ system01.dbf '// copy the backup data file back to the directory F: \ oracle \ oradata \ ebook. SQL> $ copy F: \ oracle \ backuparea \ system01.dbf F: \ oracle \ oradata \ ebook \ one file has been copied. // Restore the data file system01.dbf. SQL> recover datafile 'f: \ oracle \ oradata \ ebook \ system01.dbf'; restore media. SQL> alter database open; the database has been changed. // This indicates that the database is completely recovered. SQL> select * from Scott. Test; t ---------- 1 2 Conclusion: 1. Hot Backup must be performed in database archive Mode 2. database users can perform operations while backing up. Therefore, the content of the physical files corresponding to the database is constantly changing, and the updates to the content of these physical files are retained until the relevant operations have been written to the redo day to the file before proceeding. (This sentence is based on others' remarks. I have doubts about whether the last half is correct or not. I hope you will discuss it ). 3. the archived logs must be all days after the backup before the database can be completely restored. 4. All data files except temporary files can be backed up. If multiple data files are damaged during the restoration process, you can adopt the restoration method of one data file. In particular, if the corrupted tablespace is not the system tablespace, you can take the corrupted tablespace file offline. In this case, you can open the database before restoring the corrupted file. For example, SQL> alter tablespace tools begin backup; The tablespace has been changed. SQL> $ copy F: \ oracle \ oradata \ ebook \ tools01.dbf F: \ oracle \ backuparea has copied one file. SQL> alter tablespace tools end backup; The tablespace has been changed. SQL> shutdown the immediate database has been disabled. The database has been detached. The Oracle routine has been disabled. // Delete tools01.dbf, causing the database to fail to start normally. SQL> the startup Oracle routine has been started. Total system global area 135338868 bytes fixed size 453492 bytes variable size 109051904 bytes database buffers 25165824 bytes redo buffers 667648 bytes database load is complete. ORA-01157: unable to identify/lock data file 8-see dbwr trace file ORA-01110: Data File 8: 'f: \ oracle \ oradata \ ebook \ tools01.dbf 'SQL> alter database datafile 'f: \ oracle \ oradata \ ebook \ tools01.dbf' offline; the database has been changed. SQL> alter database open; the database has been changed. SQL> $ copy F: \ oracle \ backuparea \ tools01.dbf F: \ oracle \ oradata \ ebook \ one file has been copied. SQL> recover datafile 'f: \ oracle \ oradata \ ebook \ tools01.dbf'; media Recovery completed.