Oracle cold backup

I. Cold backup

When the database is closed, all physical system files are copied, which is also called offline backup.

Suitable for non-archive mode, where the database is in a consistent state

Step 2

First, obtain all the physical file locations of the database running in the running database, and then shut down the database in the plan)

Then copy the physical writer to the backup path or the backup device.

Start the database immediately after the backup is complete to provide normal services.

 

 

Iii. Writing cold backup scripts

First, you should find the database data files, log files, control files, and temporary files in the relevant view.

Note: Do not directly set the CP under oradata. Because the files in the production database are usually distributed on different disks and different places, you can obtain the actual path from the view.

 

-- 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

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

Orcl 10.2.0.1.0 open stopped active

 

SQL> select dbid, name, log_mode from V $ database;

 

Dbid name log_mode

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

1242732291 orcl noarchivelog

 

-- View data files and status information

SQL> select file_name, tablespace_name, status, online_status from dba_data_files;

 

File_name tablespace Status Online _

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

/U01/APP/Oracle/oradata/orcl/undotbs01.dbf undotbs1 available online

/U01/APP/Oracle/oradata/orcl/system01.dbf system available system

/U01/APP/Oracle/oradata/orcl/sysaux01.dbf sysaux available online

/U01/APP/Oracle/oradata/orcl/users01.dbf users available online

/U01/APP/Oracle/oradata/orcl/example01.dbf example available online

/U01/APP/Oracle/oradata/orcl/tbs1_1.dbf tbs1 available online

/U01/APP/Oracle/oradata/orcl/tbs1_2.dbf tbs1 available online

 

-- View data files

SQL> select name from V $ datafile;

 

Name

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

/U01/APP/Oracle/oradata/orcl/system01.dbf

/U01/APP/Oracle/oradata/orcl/undotbs01.dbf

/U01/APP/Oracle/oradata/orcl/sysaux01.dbf

/U01/APP/Oracle/oradata/orcl/users01.dbf

/U01/APP/Oracle/oradata/orcl/example01.dbf

/U01/APP/Oracle/oradata/orcl/tbs1_1.dbf

/U01/APP/Oracle/oradata/orcl/tbs1_2.dbf

 

-- View temporary files

SQL> select name from V $ tempfile;

 

Name

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

/U01/APP/Oracle/oradata/orcl/temp01.dbf

 

-- View log files

SQL> select Member from V $ logfile;

 

Member

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

/U01/APP/Oracle/oradata/orcl/redo2a. rdo

/U01/APP/Oracle/oradata/orcl/redo2b. rdo

/U01/APP/Oracle/oradata/orcl/redo1a. rdo

/U01/APP/Oracle/oradata/orcl/redo3a. rdo

/U01/APP/Oracle/oradata/orcl/redo3b. rdo

/U01/APP/Oracle/oradata/orcl/redo1b. rdo

 

-- View Control Files

SQL> select name from V $ controlfile;

 

Name

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

/U01/APP/Oracle/oradata/orcl/control01.ctl

/U01/APP/Oracle/oradata/orcl/control02.ctl

 

-- Create a backup directory

SQL> Ho mkdir/u01/APP/Oracle/coolbak

-- Use a connector to generate a copy file command

SQL> select 'ho cp' | Name | '/u01/APP/Oracle/coolbak' from V $ controlfile;

 

'Hocp' | Name | '/u01/APP/Oracle/coolbak'

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

Ho CP/u01/APP/Oracle/oradata/orcl/control01.ctl/u01/APP/Oracle/coolbak

Ho CP/u01/APP/Oracle/oradata/orcl/control02.ctl/u01/APP/Oracle/coolbak

 

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

Created file/tmp/tmpbak. SQL

 

SQL> Ho Vim/tmp/tmpbak. SQL -- edit tmpbak. SQL and input the following content to tmpbak. SQL

 

Set feedback off

Set heading off

Set verify off

Set trimspool off

Set pagesize 0

Set linesize 200

Define dir = '/u01/APP/Oracle/coolbak'

Define script = '/tmp/coolbak. SQL'

Spool & script

Select 'ho cp' | Name | '& dir' from V $ controlfile

Union all

Select 'ho cp' | Name | '& dir' from V $ datafile

Union all

Select 'ho cp' | member | '& dir' from V $ logfile

Union all

Select 'ho cp' | Name | '& dir' from V $ tempfile

/

Create pfile = '& DIR/initorcl. ora' from spfile;

Ho CP/u01/APP/Oracle/10g/dbs/orapworcl & dir

Spool off

Shutdown immediate

Start & script

Ho RM & script

Startup

 

-- Execute tmpbak. SQL

SQL> @/tmp/tmpbak. SQL;

 

-- Execution Process and database startup

-- View backup files after startup

SQL> Ho ls/u01/APP/Oracle/coolbak

Control01.ctl orapworcl redo2b. rdo system01.dbf users01.dbf

Control02.ctl redo1a. rdo redo3a. rdo tbs1_1.dbf

Example01.dbf redo1b. rdo redo3b. rdo tbs1_2.dbf

Initorcl. ora redo2a. rdo sysaux01.dbf undotbs01.dbf

 

Iv. Summary

Advantages

The concept is easy to understand in the cold standby mode, that is, copying the files to be backed up to a safe location

The operation is relatively simple and requires no intervention.

It is easy to recover to a certain point in time (you only need to copy the file back)

It can be combined with archiving methods to restore the database to the "latest State.

Disadvantages

During backup, the database must be in the consistent closed state.

Only recovery at a specific time point can be provided

Backup speed is relatively slow, especially when the data volume is large and the performance is greatly affected

Table-based and user-level data recovery is not possible