One, backup mode:
1, in the non-archive mode, can only do cold backup
2, in the archive mode, you can use hot backup, can also do cold backup. (Data file control file temporary file online log file)
Second, cold backup
(do it once a week)
1, first you have to close the database
2, and then copy all the files to the backup folder.
temporary files and online log files may not be copied. However, the difference between the online log file copy and the non-copy is very large, the use of cold backup is only a backup of the content of a point in time, at the time of restoration is only restored to the current state. Data consistency is guaranteed only when data files and control files are available. However, the database can not be opened immediately, you need to repair the online log file
3, repair the online log file
in the Mount mode
> recover Database Until CANCEL/SCN; (based on user interference/SCN)
There is a SCN number in the header of the control file and data file The two are consistent and do not need to be repaired
4, open the database
>alter database open resetlogs;
Three, hot backup
1. Prerequisites: The database must be in archive mode
to see if it is for archive mode
> archive log list;
2. Hot backup: Do hot standby The database is generally hot backed up
Table space-based hot backup
> alter tablespace Tablespace_ Name begin backup;
then go to the physical copy, just copy the dbf file.
> Alter tablespace tablespace_name end backup;
The shorter the time between ps:begin and end, the less log volume is generated, so it is recommended that you use a database backup of the databases. If the database hangs between Begin and end, an error will be made and media recovery is required at this time
related directives: see what table spaces are available
> Desc dba_tablespaces
> select Tablespace_name from Dba_tablespaces
3. Control file Backup
Do not need to shut down the database, need to back up when the database structure changes
For example, increase the table space, increase user groups and so on, as long as the data structure changes need to backup.
Can be backed up using a binary file
> ALTER DATABASE backup Controlfile to '/tmp/control.bak ';
You can also use the form of SQL scripts to back up
> ALTER DATABASE backup Controlfle to trace as '/tmp/control.sql ';
This is not a backup control file, but a record of the space file to re-establish the necessary operations, re-establishment of the time can modify some of the original fixed parameters
The statements are divided into noresetlogs and resetlogs, and use no when you can use No.
The premise of using No: only the control file is lost , but the online log file still exists
4. parameter files and password files, can be backed up, can not be backed up, direct CP on the line
Iv. Recovery
Try to do a full recovery with little impact on the database
Full recovery:
1. In archive mode
2. Need backup file + archive log + online log
Incomplete recovery: The online log file is missing or the archive log file has a fault (incomplete) or the control file is completely damaged
For incomplete recovery, start the time can not be directly started , but need to add parameter Resetlogs
Resetlogs will reset the log sequence, rebuild the online log files and so on, plus this option must be under the premise of incomplete recovery
Types of incomplete recoveries:
1, based on user interference may archive files and online logs are full, and user requirements to this point stop
2, Time-basedRevert to a state that was mistakenly deleted
3. SCN There are functions in the database that can convert the SCN and the time between
Dbms_flshback: There are functions to convert the SCN and time
Instance recovery: Instance recovery is performed by Oracle itself and is a logical error
Media recovery: Requires DBA intervention to recover, is file corruption, will report the following error
Ora-01113:file 7 need MEDIA RECOVERY is displaying a 7th file error
If it is closed when backing up the system table space, it is reported as error 1th
> Shutdown abort;
> desc v$backup;
> Selecct * from V$backup;
> Startup mount;
> Alter tablespace test2 end backup;
> ALTER DATABASE open;
Recovering a Database
When the SYSTEM01.DBF was deleted.
> Recover datafile 1;
> ALTER DATABASE open;
Full recovery in non-system tablespace archive mode
First, in the case of database open
1.1: Scene Simulation
> ALTER DATABASE archivelog;
> Create tablespace Test datafile '/u01/app/oracle/oradata/dodo/test01.dbf ' size 10M;
> 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 the relevant files
> Alter tablespace test end backup;
and then physically delete them.
# #在这个时候查询还是能查出来, because it was queried from the buffer cache,
Sometimes emptying the buffer cache is not enough, so you need to clear all the dirty blocks
> ALTER system flush Buffer_cache;(Empty buffer cache)
> Alter system checkpoint;(Empty all dirty blocks)
> select * from V$recover_file; (if it is normal that there should be no files in it, but we have deleted the DBF file, so this will show 7th file offline)
1.2: Recovery
> ALTER DATABASE datafile 7 offline;(For a file)
> Restore datafile 7;
and copy the backup files.
> Recover datafile 7;
> select * from V$recover_file;(This should show no rows selected)
> ALTER DATABASE datafile 7 online
Actual steps:
(1) offline the corresponding table space or file
(2) to restore
(3) Copy files
(4) Conduct recover
(5) Last online
Second, in the case of errors found after the database is closed
Premise:!!Full recovery without backup
Conditions:cannot be a system tablespace, all archive files are in, control files are up-to-date
1. Scene
Create a good not to do the preparation, directly delete
At the same time, be aware that you must be able to find the revocer_file inside.
The following two instructions are performed in the 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;
It'll be all right this time.
Control file Loss
1, if the control file is not completely lost, you can copy the other control files on the line
2, the complete loss of control files in the actual sense is completely lost
When you back up a binary file
> ALTER DATABASE backup Controlfile to '/u01/control.bak ';(Backup the control file first)
Physically deleting the control file
> Shutdown abort; (This time theoretically normal shutdown is not able to shut down)
Physically copy the control file
$ 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; (Tell the database that the current control file is old)
If the archive has been configured before, you can select Auto on the line
/u01/app/oracle/oradata/dodo/redo02.log
Log applied.
Media recovery complete.
May be error: It may be the time of the damage is too short, have not been able to switch the log group, it may be the control file old, can not find the current group
This is the time to choose FileName.one to try Redo01.log redo02.log Redo03.log
> ALTER DATABASE open resetlogs;
When backing up as a script
> ALTER DATABASE backup Controlfile to trace as '/u01/control.sql ';(Backup control file)
Copy the contents of the script.
$ RM-RF *.ctl
> Shutdown Abort
> Startup Nomount
> @createctl
The control file created at this time is up-to-date and the database is automatically switched to the Mount State
However, this time the temporary files are not available, you need to copy the script inside and the temporary files should be copied or run once
> Desc v$database;
> select Open_mode from V$database;
> Recover database;
> ALTER DATABASE open;
Control files and online files are lost, resetlogs
This time all the files are inconsistent, the original is to rely on the online log file to pull control files to the latest
Cold backup to ensure control files and online log files are maintained once
Delete all control files and online log files on the physical
$ 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 cold backup)
$ CP *dbf/u01/app/oracle/oradata/dodo/
> @createctl (Run script will control file recovery)
> Recover database using Backup controlfile until cancel;
Then select Auto and run the cancel one time
> ALTER DATABASE open resetlogs;
This can only be restored to the latest state of the archive.
Scenario: After the database structure changes, there is no backup control file , this time the control file is lost, in the old control file there is no new tablespace, but the fact that there is a new table space, using the old control file to restore
Create a new table space
to physically delete the control file
> Shutdown Abort
And then physically copy all three of them in.
> Startup mount
> Recover database using Backup Controlfile
First auto can not find the redo
> desc v$datafile;
> select name from V$datafile; You'll find an unhealthy file.
> ALTER DATABASE rename file '/home/oracle ... ' to '/home/xinming ';
> Recover database using Backup controlfile;
Then it will show complete
> ALTER DATABASE open resetlogs;
Not fully recovered
Based on time, based on user intervention, SCN based
Time-based:The data will be lost, in case of forced
For example, deleting a user or something, alter has a table that allows you to find time in it.
$ cd admin/xinming/bdump/
> select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;
Cold and hot backups cannot span multiple lifecycles, and operations such as resetlogs need to be backed up again
> Drop user QQ cascade;
> Shutdown immediate;
Delete all current data files in physics
Copy the data file back from cold
> Recover database until time ' 2015-03-04 11:46:00 ';
This is not fully recovered
> ALTER DATABASE open resetlogs;
Oracle Backup Recovery Details
