4. User-managed backup

User-managed backup refers to the process of using the OS command (UNIX: CP, Windows: Copy) to back up the physical files of the database.

 

4.1 back up a database

Backing up a database refers to backing up all data files and control files of the database. You should also back up the parameter files and password files. Note: When backing up a database, do not back up the redo log. There is no harm in backing up the redo log ".

4.1.1 Database Consistency backup

Database Consistency backup refers to the method of backing up all data files and control files after the database is disabled. When the shutdown command is used to close the database, the current SCN values of all data files are identical. Therefore, the closed database backup is called Database Consistency backup or cold backup. This backup applies to both archivelog and noarchivelog modes.

The procedure is as follows:

(1) list the data files and control files to be backed up.

SQL> select name from V $ controlfile Union select name from V $ datafile;

Name
--------------------------------------------------------------------------------
C: \ demo \ control02.ctl
D: \ demo \ control01.ctl
D: \ demo \ sysaux01.dbf
D: \ demo \ system01.dbf
D: \ demo \ undotbs01.dbf
D: \ demo \ users01.dbf

You have selected 6 rows.

(2) shut down the database (do not use shutdown abort ).

SQL> shutdown immediate
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.

(3) Copy all data files and control files to the backup directory.

SQL> host copy c: \ demo \ control02.ctl D: \ backup

SQL> host Copy D: \ demo \ control01.ctl D: \ backup

SQL> host Copy D: \ demo \ sysaux01.dbf D: \ backup

SQL> host Copy D: \ demo \ system01.dbf D: \ backup

SQL> host Copy D: \ demo \ undotbs01.dbf D: \ backup

SQL> host Copy D: \ demo \ users01.dbf D: \ backup

 

(4) Start the routine and open the database.

SQL> startup
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1247900 bytes
Variable Size 71304548 bytes
Database buffers 92274688 bytes
Redo buffers 2945024 bytes
The database has been loaded.
The database has been opened.

 

4.1.2 database non-consistent backup

Database non-consistent backup refers to the method of backing up all data files and control files in the open state. Because the database content changes at any time in the open state, resulting in different data files with different current SCN values, the database backup at the time of opening is called the database non-consistent backup.

Consistent backup will interrupt business operations, and non-consistent backup will not affect business operations. Non-consistent backup is only applicable to archivelog mode and not to noarchivelog mode.

The procedure is as follows:

(1) list the data files to be backed up.

SQL> select name from V $ datafile;

Name
----------------------------------------------------------------------
D: \ demo \ system01.dbf
D: \ demo \ undotbs01.dbf
D: \ demo \ sysaux01.dbf
D: \ demo \ users01.dbf

 

(2) Start database backup. After listing the data files to be backed up, set the database to the backup mode. When the database is set to the backup mode, a global check point is issued on the database, and all data file header blocks are locked to ensure that the contents of the data file header blocks remain unchanged during the backup.

SQL> alter database begin backup;

The database has been changed.

 

(3) back up all data files and control files (use the copy command for data files and the alter Database Backup controlfile command for control files ).

SQL> host Copy D: \ demo \ system01.dbf D: \ backup

SQL> host Copy D: \ demo \ undotbs01.dbf D: \ backup

SQL> host Copy D: \ demo \ sysaux01.dbf D: \ backup

SQL> host Copy D: \ demo \ users01.dbf D: \ backup

SQL> alter Database Backup controlfile to 'd: \ backup \ demo. CTL ';

The database has been changed.

(4) end database backup. To ensure the synchronization of data file backup, the current log group should be archived.

SQL> alter Database End backup;

The database has been changed.

SQL> alter system archive log current;

The system has been changed.

4.2 backup tablespace

Backup tablespace refers to the method of backing up its data files when the database is open. Note that tablespace backup is only applicable to archivelog mode, but not to noarchivelog mode. In addition, when you back up a tablespace, you can either back up all data files in the tablespace or a data file in the tablespace.

4.2.1 offline backup

Offline backup refers to the process of backing up all data files or individual data files in the tablespace when the tablespace is offline. Compared with online backup, this backup method generates less redo information, but the disadvantage is that it affects business operations on the tablespace. Because the system tablespace and the in-use undo tablespace cannot be taken offline, this backup method is not applicable to the system tablespace and the in-use undo tablespace.

Take backing up users tablespaces as an example to describe how to perform offline backup. The procedure is as follows:

(1) determine the data files contained in the tablespace.

SQL> select file_name from dba_data_files where tablespace_name = 'users ';

File_name
--------------------------------------------------------------------------------
D: \ demo \ users01.dbf

 

(2) set the tablespace to offline.

SQL> alter tablespace users offline;

The tablespace has been changed.

(3) copy a data file (if the tablespace is backed up, copy all its data files; if the data file is backed up, copy the corresponding data file ).

SQL> host Copy D: \ demo \ users01.dbf D: \ backup

(4) set the tablespace to online.

SQL> alter tablespace users online;

The tablespace has been changed.

4.2.2 online backup

Online backup refers to the process of backing up all data files or individual data files in a tablespace when the tablespace is online. This method can be used to back up all tablespaces and data files in the database. The advantage of online backup is that it does not affect the business operations on the tablespace, but the disadvantage is that it will generate more redo information and archive information.

The following describes how to back up the users tablespace. The procedure is as follows:

(1) determine the data files contained in the tablespace.

SQL> select file_name from dba_data_files where tablespace_name = 'users ';

File_name
--------------------------------------------------------------------------------
D: \ demo \ users01.dbf

 

(2) set the tablespace to the backup mode. After the tablespace is set to the backup mode, the data file header block will be locked so that the data file header block will not change, in addition, the header block records the log serial number, SCN, and other information for future recovery.

SQL> alter tablespace users begin backup;

The tablespace has been changed.

 

(3) copy data files. If you want to back up the tablespace, copy all its data files. If you want to back up the data files, you only need to copy the corresponding data files.

SQL> host Copy D: \ demo \ users01.dbf D: \ backup

 

(4) set the tablespace to the normal mode.

SQL> alter tablespace users end backup;

The tablespace has been changed.

 

4.2.3 online backup processing failed

If a routine fails during online backup, the following error message is displayed when you start the database by running startup:

SQL> startup
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1247900 bytes
Variable Size 71304548 bytes
Database buffers 92274688 bytes
Redo buffers 2945024 bytes
The database has been loaded.
ORA-01113: file 4 requires media recovery
ORA-01110: data file 4: 'd: \ demo \ users01.dbf'

To open the database, you must end the online backup status of these data files. The procedure is as follows:

(1) load the database.

SQL> startup force Mount
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1247900 bytes
Variable Size 71304548 bytes
Database buffers 92274688 bytes
Redo buffers 2945024 bytes
The database has been loaded.

 

(2) determine all data files online.
SQL> SELECT FILE # from V $ backup where status = 'active ';

File #
----------
4

(3) End the online backup status. There are three methods to end the online backup status of data files.

A. if multiple data files are in the online backup status, you can use the alter Database End BACKUP command to end the online backup status.

SQL> alter Database End backup;

The database has been changed.

B. If only one data file is in the online backup status, you can use the alter database datafile... end BACKUP command to end the online backup status.

SQL> alter database datafile 'd: \ demo \ users01.dbf' end backup;

The database has been changed.

C. Use the recover command to end the online backup status of the data file.

SQL> recover datafile 4;
Media recovery is completed.

 

(4) Open the database.

SQL> alter database open;

The database has been changed.

 

4.2.4 backup read-only tablespace

Because read-only and spatial data files do not change, you only need to back up once. The procedure is as follows:

(1) determine the tablespace in the read only status.

SQL> select tablespace_name from dba_tablespaces where status = 'read only ';

Tablespace_name
------------------------------
Query

 

(2) determine the data files contained in the read-only tablespace.

SQL> select file_name from dba_data_files where tablespace_name = 'query ';

File_name
--------------------------------------------------------------------------------
D: \ demo \ QUERY. DBF

(3) copy the data file of the read-only tablespace.

SQL> host Copy D: \ demo \ QUERY. DBF D: \ backup

4.3 backup control file

Control files are used to record and maintain databases. When the database is restored, the server process and background process need to read various backup-related information from the control file. If the control file is corrupted, the backup information will be lost. Although multiple control files can be used to prevent Control File Corruption, control files should be backed up on a regular basis due to the importance of control files. When the database configuration changes, you must back up the control file. The command for database configuration change is as follows:

• Alter database [add | drop] logfile
• Alter database [add | drop] logfile Member
• Alter database [add | drop] logfile Group
• Alter database [noarchivelog | archivelog]
• Alter database rename File
• Create tablespace
• Alter tablespace [add | rername] datafile
• Alter tablespace [Read Write | read only]
• Drop tablespace

You can back up control files when performing Database Consistency backup. When the database is in the open state, the DBA can use the alter database command to back up the control file. This command can be used to create a control file copy or back up the control file information to the tracking file.

4.3.1 create a control file copy

When you use the alter database command to create a control file copy, Oracle stores all the information of the current control file in the control file copy. By using control file copies, You can execute Incomplete recovery based on control files, and generate file tracking files for control files. Note: when the data is in the open state, the control file copy can only be generated using the alter database command, but cannot be directly copied using the OS command.

SQL> alter Database Backup controlfile to 'd: \ backup \ demo. CTL ';
Alter Database Backup controlfile to 'd: \ backup \ demo. CTL'
*
Row 3 has an error:
ORA-01580: Error creating control backup file D: \ backup \ demo. CTL
ORA-27038: The created file already exists
OSD-04010: Specifies the <create> option, but the file already exists

When a control file copy is created, if the control file copy already exists, an error message is displayed. By using the reuse option, you can overwrite the original control file copy.

SQL> alter Database Backup controlfile to 'd: \ backup \ demo. CTL 'reuse;

The database has been changed.

 

4.3.2 back up a trail File

Assuming that all data files and redo logs in the database are in the same intact state, and only the control files are accidentally deleted or damaged, the control files can be restored by re-establishing the control files. The create controlfile command is used to create a control file. However, to execute this command, the DBA must be clear about the data files contained in the database and the detailed list of redo logs. To avoid the trouble of manually setting up a control file, DBA can back up the physical information recorded in the control file to the trace file using the alter Database Backup controlfile to trace command, then, use the trail file to create a control file.

SQL> alter Database Backup controlfile to trace;

The database has been changed.

(1) determine the location of the tracking file. The trace file is stored in the directory corresponding to the initialization parameter user_dump_dest.

SQL> show parameter user_dump_dest

Name type value
-----------------------------------------------------------------------------
User_dump_dest string D: \ oracle \ product \ 10.2.0 \ admin \ demo \ udump

 

(2) determine the name of the trail file. The format of the trail file name is <Sid> _ ora _ <spid>. TRC, where Sid is the routine identifier, that is, the value of the environment variable oracle_sid; and spid is the operating system process number corresponding to the server process.
SQL> select a. spid from V $ process a, V $ session B where a. ADDR = B. paddr and B. Username = 'sys ';

Spid
------------
1240

(3) view the content of the tracking file.
Startup nomount
Create controlfile reuse Database "Demo" noresetlogs archivelog
Maxlogfiles 16
Maxlogmembers 3
Maxdatafiles 100
Maxinstances 8
# Maxloghistory 292
Logfile
Group 1 (
'D: \ demo \ redo01.log ',
'C: \ demo \ redo01_2.log'
) Size 50 m,
Group 2 (
'D: \ demo \ redo02.log ',
'C: \ demo \ redo02_2.log'
) Size 50 m,
Group 3 (
'D: \ demo \ redo03.log ',
'C: \ demo \ redo03_2.log'
) Size 50 m
-- Standby logfile
Datafile
'D: \ demo \ system01.dbf ',
'D: \ demo \ undotbs01.dbf ',
'D: \ demo \ sysaux01.dbf ',
'D: \ demo \ users01.dbf'
Character Set zhs16gbk
;
Recover Database
Alter system archive log all;
Alter database open;
Alter database rename file 'missing00005'
To 'd: \ demo \ QUERY. DBF ';
Alter tablespace "query" online;
Alter tablespace temp add tempfile 'd: \ demo \ temp01.dbf'
Size 22020096 reuse autoextend on next 655360 maxsize 32767 m;
Startup nomount
Create controlfile reuse Database "Demo" resetlogs archivelog
Maxlogfiles 16
Maxlogmembers 3
Maxdatafiles 100
Maxinstances 8
# Maxloghistory 292
Logfile
Group 1 (
'D: \ demo \ redo01.log ',
'C: \ demo \ redo01_2.log'
) Size 50 m,
Group 2 (
'D: \ demo \ redo02.log ',
'C: \ demo \ redo02_2.log'
) Size 50 m,
Group 3 (
'D: \ demo \ redo03.log ',
'C: \ demo \ redo03_2.log'
) Size 50 m
-- Standby logfile
Datafile
'D: \ demo \ system01.dbf ',
'D: \ demo \ undotbs01.dbf ',
'D: \ demo \ sysaux01.dbf ',
'D: \ demo \ users01.dbf'
Character Set zhs16gbk
;
Recover database using backup controlfile
Alter database open resetlogs;
Alter database rename file 'missing00005'
To 'd: \ demo \ QUERY. DBF ';
Alter tablespace "query" online;
Alter tablespace temp add tempfile 'd: \ demo \ temp01.dbf'
Size 22020096 reuse autoextend on next 655360 maxsize 32767 m;
After the control file's description and comments are removed, the rest is used to re-create all the contents of the control file. When the control file contains media corruption, you can run the preceding statements to restore the damaged control file.

 

4.4 back up other files

When preparing backup and recovery plans, you must not only develop backup and recovery plans for data files and control files, but also backup and recovery plans for archiving logs, parameter files, and password files.

1. Back up archived logs

When backing up archived logs, you must first confirm the archived logs to be backed up, and then use the OS command to directly copy the archived log files to the backup directory.

SQL> select name from V $ archived_log where dest_id = 1 and first_time> = sysdate-1;

Name
--------------------------------------------------------------------------------
C: \ demo \ archive \ 4_415664374623.log
C: \ demo \ archive \ 5_415664374623.log
C: \ demo \ archive \ 6_415664374623.log
C: \ demo \ archive \ 7_415664374623.log
C: \ demo \ archive \ 8_415664374623.log
C: \ demo \ archive \ 9_415664374623.log

You have selected 6 rows.

SQL> host copy c: \ demo \ archive \ 4_1_664374623.log D: \ backup

SQL> host copy c: \ demo \ archive \ 5_1_664374623.log D: \ backup

SQL> host copy c: \ demo \ archive \ 6_1_664374623.log D: \ backup

SQL> host copy c: \ demo \ archive \ 7_1_664374623.log D: \ backup

SQL> host copy c: \ demo \ archive \ 8_1_664374623.log D: \ backup

SQL> host copy c: \ demo \ archive \ 9_0000664374623.log D: \ backup

 

2. Backup parameter file

If the parameter file cannot be located when the routine is started, the following error message is displayed:

SQL> startup
ORA-01078: Processing System Parameters failed
LRM-00109: Unable to open the parameter file 'd: \ oracle \ product \ 10.2.0 \ db_1 \ database \ initdemo. ora'

If a routine uses a file parameter file (pfile), use the OS command to copy the file parameter file. If the routine uses a server parameter file (spfile), use the create pfile command to back up the file.

SQL> Create pfile = 'd: \ backup \ demo. Par 'from spfile =' % ORACLE_HOME % \ DBS \ spfiledemo. ora ';

The file has been created.

 

3. Backup the password file

SQL> conn sys/demo @ democlient as sysdba
Error:
ORA-01031: insufficient Permissions

The password file can be backed up directly using the OS command.

SQL> host copy % ORACLE_HOME % \ database \ pwddemo. ora D: \ backup