User-managed backup and recovery for Oracle databases

OracleIn the database,Backup and recoveryThere are many ways to achieve this. This article introducesUser Management. User-managed backup refers to the use of OS commands to back up physical database files.

Back up database

Database Consistency backup: After the database is closed, the physical files of the database are backed up. At this time, the scn values of the data files are exactly the same. Therefore, it is called Database Consistency backup or cold backup, which is suitable for archive mode and non-archive mode.

Temporary understanding of user-managed cold backup: Use the OS command to directly copy physical database files in the database shutdown state.

Database non-consistent backup: backs up the physical files of the database in the open state, and the database content may change, resulting in inconsistent data file scn. Therefore, it is called the database's non-consistent backup or hot backup, only applicable to archive mode.

Full recovery archive mode based on user management)

1. Back up the physical files of the database in the open state, non-consistent backup of the database, hot backup, without affecting business operations)

2. deleting data files to simulate file loss only deletes all data files. Other files such as control files are in normal state)

3. Restore data files

4. Application archiving logs

5. Check whether the data is completely restored.

 
 

  
  
SQL> conn evan/evan
  
  
 
  
  
Connected.
  
  
 
  
  
SQL> insert into t_evan values ('oracle ');
  
  
 
  
  
1 row created.
  
  
 
  
  
SQL> insert into t_evan values ('java ');
  
  
 
  
  
1 row created.
  
  
 
  
  
SQL> commit;
  
  
 
  
  
Commit complete.
  
  
 
  
  
SQL> select * from t_evan;
  
  
 
  
  
TEXT
  
  
 
  
  
--------------------------------------------------------------------------------
  
  
 
  
  
Oracle
  
  
 
  
  
Java
  
  
 
  
  
-- Sysdba backup
  
  
 
  
  
SQL> conn/as sysdba
  
  
 
  
  
Connected.
  
  
 
  
  
SQL> select name from v $ datafile;
  
  
 
  
  
NAME
  
  
 
  
  
--------------------------------------------------------------------------------
  
  
 
  
  
/Oracle/10g/oracle/product/10.2.0/oradata/oralife/system01.dbf
  
  
 
  
  
/Oracle/10g/oracle/product/10.2.0/oradata/oralife/undotbs01.dbf
  
  
 
  
  
/Oracle/10g/oracle/product/10.2.0/oradata/oralife/sysaux01.dbf
  
  
 
  
  
/Oracle/10g/oracle/product/10.2.0/oradata/oralife/users01.dbf
  
  
 
  
  
/Oracle/10g/oracle/product/10.2.0/oradata/oralife/example01.dbf
  
  
 
  
  
SQL> alter database begin backup;
  
  
 
  
  
Database altered.
  
  
 
  
  
SQL> host cp/oracle/10g/oracle/product/10.2.0/oradata/oralife/*. dbf/oracle/10g/oracle/bakup/database/-- back up all data files
  
  
 
  
  
SQL> alter database backup controlfile to '/oracle/10g/oracle/bakup/database/oralife. ctl'; -- backup control file
  
  
 
  
  
Database altered.
  
  
 
  
  
Certificate -----------------------------------------------------------------------------------------------------------------------------------------
  
  
 
  
  
Forget alter database end backup;
  
  
 
  
  
Certificate -----------------------------------------------------------------------------------------------------------------------------------------
  
  
 
  
  
SQL> alter system archive log current; -- archive the current log Group
  
  
 
  
  
System altered.
 
 

Use rm to delete all data files (*. dbf );

 
 

  
  
SQL> conn evan/evan
  
  
 
  
  
Connected.
  
  
 
  
  
SQL> select * from t_evan;
  
  
 
  
  
TEXT
  
  
 
  
  
--------------------------------------------------------------------------------
  
  
 
  
  
Oracle
  
  
 
  
  
Java
  
  
 
  
  
SQL> insert into t_evan values ('spring ');
  
  
 
  
  
1 row created.
  
  
 
  
  
SQL> commit;
  
  
 
  
  
Commit complete.
  
  
 
  
  
SQL> ALTER SYSTEM CHECKPOINT; -- refreshes modified data from the cache to the disk, and updates control files and data files.
  
  
 
  
  
System altered.
  
  
 
  
  
SQL> ALTER SYSTEM SWITCH LOGFILE; -- log SWITCH
  
  
 
  
  
System altered.
 
 

View alter_oralife.log error message: some files do not exist

Complete recovery

Copy the backup data file to the specified destination location for restoration:

 
 

  
  
SQL> recover database -- open status
  
  
 
  
  
ORA-00283: recovery session canceled due to errors
  
  
 
  
  
ORA-01124: cannot recover data file 1-file is in use or recovery
  
  
 
  
  
ORA-01110: data file 1:
  
  
 
  
  
'/Oracle/10g/oracle/product/10.2.0/oradata/oralife/system01.dbf'
  
  
 
  
  
SQL> shutdown immediate -- open status
  
  
 
  
  
ORA-01122: database file 1 failed verification check
  
  
 
  
  
ORA-01110: data file 1: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/system01.dbf'
  
  
 
  
  
ORA-01208: data file is an old version-not accessing current version
  
  
 
  
  
SQL> startup force mount; -- enters the mount State to restore
  
  
 
  
  
ORACLE instance started.
  
  
 
  
  
Total System Global Area 528482304 bytes
  
  
 
  
  
Fixed Size 1220360 bytes
  
  
 
  
  
Variable Size 163578104 bytes
  
  
 
  
  
Database Buffers 356515840 bytes
  
  
 
  
  
Redo Buffers 7168000 bytes
  
  
 
  
  
Database mounted.
  
  
 
  
  
SQL> recover database
  
  
 
  
  
Media recovery complete.
  
  
 
  
  
SQL> alter database open;
  
  
 
  
  
Database altered.
  
  
 
  
  
SQL> conn evan/evan -- use evan to log on to view the recovery status
  
  
 
  
  
Connected.
  
  
 
  
  
SQL> select * from t_evan;
  
  
 
  
  
TEXT
  
  
 
  
  
--------------------------------------------------------------------------------
  
  
 
  
  
Oracle
  
  
 
  
  
Java
  
  
 
  
  
Spring
 
 

There are no application archiving logs. There are several ways to archive application logs:

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}

When restoring a data file in the open state, you should offline it and restore it online. All the recovery should be completed in the open state as much as possible.

To understand checkpoint, alter system archive log current;, alter system switch logfile.

We will introduce Oracle database backup and recovery based on user management. We hope this introduction will bring you some benefits.