Oracle backup recovery details
I. Backup method:
1. In non-archive mode, only cold backup can be performed
2. In archive mode, hot backup or cold backup can be used. (Online log files of temporary data file control files)
Ii. Cold backup
(Once a week)
1. First, shut down the database.
2. Copy all the files to the backup folder.
Temporary files and online log files can not be copied. However, there is a big difference between copying and not copying online log files. When using cold backup, it is only the backup time point, and it is restored to the current status only. Data Consistency can be ensured when only data files and control files are available. However, the database cannot be enabled immediately. You need to fix online log files.
3. Fix online log files
In mount Mode
> Recover database until cancel/scn; (based on user interference/SCN number)
When both the control file and the data file header have the same SCN number, you do not need to fix it.
4. Enable the database
> Alter database open resetlogs;
Iii. Hot Backup
1. Prerequisites: the database must be in archive mode.
Check whether the archive mode is used
> Archive log list;
2. Hot Backup: Hot Backup is usually used for Database Hot Backup
Table space-based Hot Backup
> Alter tablespace tablespace_name begin backup;
Then copy the file physically. just copy the dbf file.
> Alter tablespace tablespace_name end backup;
PS: The shorter the time between begin and end, the less logs are generated. Therefore, we recommend that you back up one database and one Database. If the database goes down between begin and end, an error is reported, and media recovery is required.
Related commands: view the tablespaces
> Desc dba_tablespaces
> Select tablespace_name from dba_tablespaces
3. control file backup
You do not need to shut down the database. Backup is required when the database structure changes.
For example, if a tablespace is added and a user group is added, backup is required as long as the data structure changes.
Binary files can be used for backup.
> Alter database backup controlfile to '/tmp/control. Bak ';
You can also use SQL scripts to back up data.
> Alter database backup controlfle to trace as '/tmp/control. SQL ';
This is not a backup control file, but a record of the operations required to re-establish the space file, and some fixed parameters can be modified during re-creation.
The preceding statements are divided into noresetlogs and resetlogs. When no is available, try to use no.
The premise of using "no" is that the control file is lost, but the online log file still exists.
4. Both parameter files and password files can be backed up. You can simply run CP without backing up the files.
Iv. Recovery
Make full recovery as much as possible, with little impact on the database
Full recovery:
1. In archive Mode
2. You need to back up files, archive logs, and online logs.
Incomplete recovery: the online log file is lost, the archived log file is faulty (incomplete), or the control file is completely damaged.
For Incomplete recovery, it cannot be started directly at startup, but the resetlogs parameter must be added.
Resetlogs will reset the log sequence and recreate the online log file. This option must be not completely restored.
Types of Incomplete recovery:
1. Archiving files and online logs may be complete based on user interference, and the user must stop at this point.
2. restore to the deleted state by mistake Based on Time
3. There is a function in the database for converting between SCN and time.
Dbms_flshback: contains the function for converting SCN and time.
Instance recovery: oracle performs instance recovery on its own, which is a logical error.
Media recovery: DBAs must be involved in the recovery. If the file is damaged, the following error is reported.
ORA-01113: FILE 7 need media recovery displays FILE 7 errors
If it is disabled when the system tablespace is backed up, a file Error 1 is reported.
> Shutdown abort;
> Desc v $ backup;
> Selecct * from v $ backup;
> Startup mount;
> Alter tablespace test2 end backup;
> Alter database open;
Restore database
When system01.dbf is deleted
> Recover datafile 1;
> Alter database open;
Full recovery in non-system tablespace archive Mode
1. When the database is enabled
1.1: Scenario Simulation
> Alter database archivelog;
> Create tablespace test datafile '/u01/app/oracle/oradata/dodo/test01.dbf' size 10 M;
> Create user test identified by test defult tablespace test;
> Grant connect, resource to test;
> Conn test/test;
> Create table t;
> Commit;
> Conn/as sysdba
> Alter tablespace test begin backup;
Physically copy Related Files
> Alter tablespace test end backup;
Then delete it physically.
# The query can still be found at this time, because it is queried from the buffer cache,
Sometimes it is not enough to clear the buffer cache, so you need to clear all dirty blocks.
> Alter system flush buffer_cache; (clear buffer cache)
> Alter system checkpoint; (clear all dirty blocks)
> Select * from v $ recover_file)
1.2: Recovery
> Alter database datafile 7 offline; (for a file)
> Restore datafile 7;
Then copy the backup file.
> Recover datafile 7;
> Select * from v $ recover_file; (no rows selected should be displayed here)
> Alter database datafile 7 online
Actual steps:
(1) offline the corresponding tablespace or file
(2) restore
(3) copy an object
(4) recover
(5) Last online
2. When an error is found after the database is closed
Prerequisites :!! Complete restoration without backup
Condition: it cannot be a system tablespace. All the archive files are stored, and the control files are the latest.
1. Scenario
If it is created, it will not be used for backup, but will be deleted directly.
At the same time, you must be able to find it in revocer_file before proceeding.
The following two commands are performed in sysdba:
> Alter system checkpoint;
> Select * from v $ recover_file;
> Alter database create datafile '/u01/app/oracle/oradata/dodo/test01.dbf ';
> Recover datafile 6;
> Alter database datafile 6 online;
This is the time.
Control File loss
1. If the control file is not completely lost, you can copy it to another control file.
2. The complete loss of control files is completely lost in the practical sense.
When it is backed up as a binary file
> Alter database backup controlfile to '/u01/control. Bak'; (back up the control file first)
Delete control files physically
> Shutdown abort; (theoretically normal shutdown won't be able to close at this time)
Physically copy Control Files
$ Cp control. bak/u01/app/oracle/oradata/dodo/control01.ctl
$ Cp control. bak/u01/app/oracle/oradata/dodo/control02.ctl
$ Cp control. bak/u01/app/oracle/oradata/dodo/control03.ctl
$ Sqlplus/as sysdba
> Startup mount
> Recover database using backup controlfile; (tells the database that the current control file is old)
If you have configured the archive before, you can select AUTO.
/U01/app/oracle/oradata/dodo/redo02.log
Log applied.
Media recovery complete.
The following error may be reported: it may take too short a period of time to change the log group. It may also be because the control file is old and the current group cannot be found.
In this case, you need to select filename to try redo01.log redo02.log redo03.log one by one.
> Alter database open resetlogs;
When it is backed up as a script
> Alter database backup controlfile to trace as '/u01/control. SQL'; (backup control file)
Copy the content in the script.
$ Rm-rf *. ctl
> Shutdown abort
> Startup nomount
> @ Createctl
The Control File Created at this time is the latest, and the database will automatically switch to the mount status.
However, temporary files are unavailable at this time. You need to copy or run the files related to the temporary files in the script once.
> Desc V $ database;
> Select open_mode from v $ database;
> Recover database;
> Alter database open;
Control Files and online files are lost, resetlogs
At this time, all the files are inconsistent. It turns out that the control file is pulled to the latest by online log files.
Cold backup to ensure that control files and online log files are retained once
Delete all control files and online log files physically
$ Rm-rf * ctl
$ Rm-rf * log
> Shutdown abort
> Startup nomount
$ Rm-rf * dbf
Delete the data file, and then copy the cold backup data file (because it is consistent in the cold backup)
$ Cp * dbf/u01/app/oracle/oradata/dodo/
> @ Createctl (run the script to restore the control file)
> Recover database using backup controlfile until cancel;
Then select auto and run the cancel command once.
> Alter database open resetlogs;
This can only be restored to the latest status of the archive.
Scenario: After the database structure changes, there is no backup control file. In this case, the control file is lost and there is no new tablespace in the old control file, but there is indeed a new tablespace in fact, and the old control file is used for restoration.
Create a tablespace
Delete control files physically
> Shutdown abort
Then copy all three of them physically.
> Startup mount
> Recover database using backup controlfile
Redo cannot be found in auto first
> Desc v $ datafile;
> Select name from v $ datafile; an abnormal file is found.
> Alter database rename file '/home/oracle...' to '/home/xinming ';
> Recover database using backup controlfile;
Then the complete is displayed.
> Alter database open resetlogs;
Incomplete recovery
Time-based, user interference-based, and SCN-based
Time-based: data will be lost and will be performed only when the last resort
For example, if a user is deleted, alter has a table in which time can be searched.
$ Cd admin/xinming/bdump/
> Select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual;
Cold backup and Hot Backup cannot span multiple lifecycles. If there are resetlogs and other operations, you need to back up again
> Drop user qq cascade;
> Shutdown immediate;
Delete all existing data files physically
Copy the data file back from the cold backup
> Recover database until time '2017-03-04 11:46:00 ';
This is incomplete recovery.
> Alter database open resetlogs;
