Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to achieve cold backup and recovery of Oracle", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to achieve cold backup and recovery of Oracle" this article.

I. the concept of cold backup

Cold backup refers to the physical copy, also known as offline backup, made when the database is shut down.

It is suitable for backups in non-archive mode, and can only be backed up in this way.

Files that need to be backed up

Files that must be backed up:

Data files and control files

Files that can be backed up:

Redo log files, temporary files, binary parameter files (spfile), password files

III. Steps for cold backup

1. Find the backup file you need

two。 Shut down the database normally

3. Backup files to the specified backup path

4. Restart the database

IV. How to write a cold script

4.1 View file location and status

-- View information about instances and databases

SQL > select instance_name,version,status,archiver,database_status from v$instance

INSTANCE_NAME VERSION STATUS ARCHIVE DATABASE_STATUS

--

Dbsrv1 11.2.0.1.0 OPEN STOPPED ACTIVE

SQL > select dbid,name,log_mode from v$database

DBID NAME LOG_MODE

294555525 DBSRV1 NOARCHIVELOG

-- View data files and status information

SQL > col file_name for A50

SQL > col tablespace_name for A15

SQL > selectfile_name,tablespace_name,status,online_status from dba_data_files

FILE_NAME TABLESPACE_NAME STATUS ONLINE_

/ opt/oracle/app/oradata/dbsrv1/users01.dbf USERS AVAILABLE ONLINE

/ opt/oracle/app/oradata/dbsrv1/undotbs01.dbf UNDOTBS1 AVAILABLE ONLINE

/ opt/oracle/app/oradata/dbsrv1/sysaux01.dbf SYSAUX AVAILABLE ONLINE

/ opt/oracle/app/oradata/dbsrv1/system01.dbf SYSTEM AVAILABLE SYSTEM

/ opt/oracle/app/oradata/costctl/COSTCTL_TBS.dbf COSTCTL_TBS AVAILABLE ONLINE

/ opt/oracle/app/oradata/dbsrv1/DEV_DATA.DBF DEV_DATA AVAILABLE ONLINE

/ opt/oracle/app/oradata/pdms/PDMS_DATA.dbf PDMS_DATA AVAILABLE ONLINE

View data files

SQL > col name for A50

SQL > selectfile#, name, status from v$datafile

FILE# NAME STATUS

-

1/opt/oracle/app/oradata/dbsrv1/system01.dbf SYSTEM

2/opt/oracle/app/oradata/dbsrv1/sysaux01.dbf ONLINE

3/opt/oracle/app/oradata/dbsrv1/undotbs01.dbf ONLINE

4/opt/oracle/app/oradata/dbsrv1/users01.dbf ONLINE

5/opt/oracle/app/oradata/costctl/COSTCTL_TBS.dbf ONLINE

6/opt/oracle/app/oradata/dbsrv1/DEV_DATA.DBF ONLINE

7/opt/oracle/app/oradata/pdms/PDMS_DATA.dbf ONLINE

-- View temporary files

SQL > col name for A60

SQL > select name from v$tempfile

NAME

/ opt/oracle/app/oradata/dbsrv1/temp01.dbf

/ opt/oracle/app/oradata/costctl/COSTCTL_TBS_temp.dbf

/ opt/oracle/app/oradata/pdms/PDMS_DATA_TEMP.dbf

-- View log files

SQL > select member from v$logfile

MEMBER

/ opt/oracle/app/oradata/dbsrv1/redo03.log

/ opt/oracle/app/oradata/dbsrv1/redo02.log

/ opt/oracle/app/oradata/dbsrv1/redo01.log

-- View control files

SQL > select name from v$controlfile

NAME

/ opt/oracle/app/oradata/dbsrv1/control01.ctl

/ opt/oracle/app/flash_recovery_area/dbsrv1/control02.ctl

-- View the parameter file

SQL > show parameter pfile

NAME TYPE VALUE

-

Spfile string / opt/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledbsrv1.ora

4.2 make a backup

Option 1.

-- create a backup directory

SQL > ho mkdir-p / u03/backup/coolbak

-- use connectors to generate copy file commands

SQL > select'ho cp' | | name | |'/ u03/backup/coolbak'from v$controlfile

'HOCP' | | NAME | |' / U03BACKUPUniCOOLBAK'

Ho cp/opt/oracle/app/oradata/dbsrv1/control01.ctl/u03/backup/coolbak

Ho cp/opt/oracle/app/flash_recovery_area/dbsrv1/control02.ctl/u03/backup/coolbak

SQL > save/tmp/tmpbak.sql;-- Save the above input as tmpbak.sql

Created file/tmp/tmpbak.sql

Edit coolbak.sql

Pay attention to the location of the password file and the name of pfile

Set feedback off

Set heading off

Set verify off

Set trimspool off

Set pagesize 0

Set linesize 200

Define dir ='/ u03Compact BackupCompact Bak`

Define script ='/ u03 Universe BackupCompact Bak.txt'

Define passwd ='/ u01amp appActionoracleproductUniverse 11.2.0Uniplication1anddbsUnigram orapworcl'

Spool & script

Select'ho cp-pv'| | name | |'& dir'from v$controlfile

Unionall

Select'ho cp-pv'| | name | |'& dir'from v$datafile

Unionall

Select'ho cp-pv'| | member | |'& dir' from v$logfile

Unionall

Select'ho cp-pv'| | name | |'& dir'from v$tempfile

/

Create pfile ='& dir/initorcl.ora'from spfile

Ho cp-pv & passwd & dir

Spool off

Shutdown immediate

Ho mkdir-pv / u03/backup/coolbak

Ho rm-rf / u03Universe Backup *

Start & script

Startup

-- execute coolbak.sql

SQL > @ / tmp/coolbak.sql

-- execution process and database startup strategy

-- View the backed up files after startup

It should be noted that this approach is not conducive to recovery, you have to find the recovery directory, it is best to set up the same directory as the database after cool.

Steps for cold recovery

To restore offline to its original location:

1. If the database is not closed, you need to shut down the database

two。 Copy all backup data files and backup control files to the original location of the database

3. You can also copy all other backup files to the original location of the database (this operation is not required)

4. Restart the database

After the restore is successful, the database is restored to the last backup, and the time required to restore is the time it takes to copy the files.

To restore offline to a non-original location:

Sometimes the disk on which the data file is stored is broken, and it may be necessary to change the recovery location of the data file

1. Restore backup files to a normal disk

two。 Load the database as mount state (startup mount)

3. Alter database rename file 'u01andxxx.DBF'to 'u02andxxx.DBF' (U01 disk is damaged)

4. Alter database open

The above is all the contents of the article "how to achieve cold backup and recovery of Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.