Shulou(Shulou.com)06/01 Report--

In the day-to-day management of ORACLE, backups need to be tested regularly to see if backup files are available. The following is a brief introduction to common recovery scenarios.

I. preparation before backup

1. View the location of the parameter file

SQL > show parameter spfile

2. View the location of the control file

SQL > show parameter control

3. View the location of the data file

SQL > select name from v$datafile

SQL > select file_name from dba_data_files

4. View the location of temporary files

SQL > select name from v$tempfile

SQL > select file_name from dba_temp_files

5. View the dump directory

SQL > show parameter dump

6. Check the database name and dbid

SQL > select name,dbid from v$database

Second, perform a full database backup

Run {

Backup database format'd:\ rman\ full_%d_%T_%s_%p'

Sql 'alter system archive log current'

Sql 'alter system archive log current'

Sql 'alter system archive log current'

Backup archivelog all format'd:\ rman\ arch_%d_%T_%s_%p'

Backup current controlfile format'd:\ rman\ ctl_%d_%T_%s_%p'

}

Note: automatic backup of control files is opened during backup, so that control files and parameter files can be automatically backed up.

III. Backup and recovery of common situations

1. Recovery experiment after a data file is corrupted (non-system and Undo)

1) enter the directory of the data file and delete a data file (non-system and Undo files)

Note: in windows environment, the data file cannot be deleted without closing the database. You can close the database before deleting the file.

2) if you close the database and start it again, an error will be reported

SQL > shutdown immediate

SQL > startup

3) start the database to mount state, and set the deleted data file to offline

SQL > alter database mount

SQL > alter database datafile 'delete the absolute path of the file + file name' offline

4) Open the database

SQL > alter database open

5) restore and restore deleted data files

RMAN > restore datafile 'delete the absolute path of the file + file name'

RMAN > recover datafile 'delete the absolute path of the file + file name'

6) set the deleted data file to online status

SQL > alter database datafile 'delete the absolute path of the file + file name' online

2. Recovery experiment after all data files are damaged

1) enter the directory of the data files and delete all the data files.

2) close the database

SQL > shutdown immediate

Note: steps 1 and 2 need to be performed in reverse when operating on the windows platform.

3) start the database to mount status

SQL > startup mount

4) restore and restore the database

RMAN > restore database

RMAN > recover database

5) Open the database

SQL > alter database open

3. Recovery experiment after SYSTEM or UNDO damage

1) enter the directory of the data file and delete the SYSTEM01.DBF file

2) close the database.

SQL > shutdown immediate

Note: steps 1 and 2 need to be performed in reverse when operating on the windows platform.

3) start the database to mount status

SQL > startup mount

4) restore and restore SYSTEM01.DBF files

RMAN > restore datafile 'file path / SYSTEM01.DBF'

RMAN > recover datafile 'file path / SYSTEM01.DBF'

5) Open the database

SQL > alter database open

4. Recovery experiment after parameter file damage.

1) full library backup: data file + archive log + control file + parameter file (process brief)

2) consistently shut down the database

SQL > shutdown immediate

3) Delete parameter files initSID.ora and spfileSID.ora

4) manually create a parameter file in the $ORACLE_HOME/dbs directory: initSID.ora, with the following contents

* .db_name=' database name'

5) start the database to nomount status

SQL > startup nomount

6) restore parameter files

RMAN > restore spfile from'd:\ rman\ cMurxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

7) close the database

SQL > shutdown immediate

8) Open the database

SQL > startup

5. Control the recovery of damaged files

1) perform a full library backup: data file + archive log + control file + parameter file

2) consistently shut down the database

SQL > shutdown immediate

3) delete all control files

4) start the database to nomount status

SQL > startup nomount

5) restore control files

RMAN > restore controlfile from'd:\ rman\ ctl-xxxx'; Note: ctl-xxxx is the control file for the last backup

6) start the database to mount status

SQL > alter database mount

7) restore and restore the database

RMAN > restore database

RMAN > recover database

8) Open the database

SQL > alter database open resetlogs

Note: after opening it in resetlogs, you need to make a full backup of the database immediately.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.