Backing up and recovering databases

Source: Internet
Author: User

View instance number, name and log mode:
SYS as [email protected]>select dbid,name,log_mode from V$database;

DBID NAME Log_mode
---------- --------- ------------

1 row selected.

[[email protected] ~]$ Rman
Rman> Connect TARGET///connection to local database
Connected to target DATABASE:ORCL (dbid=1391294860)

Backing up the database in Archivelog mode will back up the entire database and its archive redo log files to the default backup device

Back up the database in no ARCHIVELOG mode: Only consistent backup, for consistent backup, first to shut down the database, and then load does not open the database
1. Start Rman and connect to the database you want to back up, then execute the following command
[[email protected] ~]$ Rman
Rman>connnect TARGET/
Rman>shutdown IMMEDIATE;
Rman>startup Force DBA;
Rman>shutdown IMMEDIATE;
Rman>startup MOUNT;
Rman>backup DATABASE; Backing up the database directly
Rman>backup as COPY DATABASE; Create an image copy of all data files when backing up a database
After the backup is complete, execute the following statement to open the database

To perform an incremental backup:
To create an incremental backup of a database using Backup incremental
Backup level:
Level 0: Level 0 Backup is consistent with full backup
Level 1: Contains only blocks that have changed since the previous incremental backup, and Oracle automatically performs a level 0 backup if a Level 1 backup is in progress and no level 0 backup is performed
Cumulative incremental backup (cumulative INCREMENTAL Backup): Includes all data blocks that have changed since level 0 backup
Differential incremental backup (defferential INCREMENTAL Backup): Includes only blocks of data that have changed since the most recent differential backup
Perform a level 0 incremental backup with Rman
Perform a 1-level cumulative incremental backup with Rman
Use Rman to perform a level 1 differential incremental backup

Verifying database files and backups
Check all database files and archive redo log files for physical and logical corruption
Check LOGICAL: Indicates whether there is a logical block corruption
VALIDATE: The specified block can be checked
Check the 10-13 data blocks of data file No. 4th:
Rman> VALIDATE datafile 4 BLOCK ten to 13;

Display backup information in Rman:
Displays the backup set in the current database:

To display the backup information in a file:

Displays comprehensive information about the backup

To display copy information for a data file:
LIST COPY of datafile 1;

Back up tablespace and various database files:

Backup table Space:
BACKUP tablespace UNDOTBS1; When you back up a table space, Oracle automatically converts it to a data file in the backup table space

You can specify the data files to be backed up:
BACKUP datafile 00001; 00001 the backup number for the data file

Control files can be backed up while the database is processing load and open state

You can use the Include current Controlfile backup control file when you back up the tablespace
BACKUP tablespace USERS INCLUDE current controlfile;

Backup control Files:
BACKUP as COPY current controlfile;

A complete instance of backing up the database using the Rman command:
1. Switch the data to archive log mode:
To view the Log archive mode:
SYS as [email protected]>select dbid,name,log_mode from V$database;

DBID NAME Log_mode
---------- --------- ------------

1 row selected.
2. If the Noarchivelog mode is set to Archivelog mode, use the following method:
A.shutdown IMMEDIATE
B.startup MOUNT

3. Create a tablespace that holds the Rman data
CREATE tablespace rman_ts datafile '/u01/app/oracle/oradata/orcl/rman_ts.dbf ' SIZE 200M;
4. Create an Rman user and authorize
SYS as [email protected]>create USER RMAN identified by RMAN DEFAULT tablespace rman_ts temporary tablespace TEMP;

User created.

SYS as [email protected]>grant connect,recovery_catalog_owner,resource to RMAN;

Grant succeeded.
Connect: Database objects can be connected to a database, create tables, views, etc.
Recovery_catalog_owner: The recovery directory can be managed
RESOURCE: You can create database objects such as tables, views, and so on

5. Create a recovery directory
[Email protected] ~]$ Rman catalog Rman/rman TARGET ORCL

Recovery manager:release on Thu Dec 18 09:34:19 2014

Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved.

Target Database Password:

rman> CREATE CATALOG tablespace rman_ts;

Recovery Catalog Created

6 Registering the target database
Only registered databases can be backed up and restored

Database registered in Recovery catalog
Starting full resync of recovery catalog
Full Resync Complete

Make a full database backup:
Rman> Run {
2> Allocate channel DEV1 type disk;
3> backup Database;
4> release channel Dev1;
To back up the archive log file separately:
Rman> Run {
2> Allocate channel DEV1 type disk;
3> backup Archivelog all;
4> release channel Dev1;

Restore the entire database:
Restore: Restore the required files from the backup file according to the entries in the Rman repository
RECOVER (recovery): Use data files and redo log files for full or point-in-time database media recovery
1. Close the database and start the database in Mount mode

SYS as [email protected]>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS as [email protected]>startup Force MOUNT
ORACLE instance started.

Total System Global area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 352323968 bytes
Database buffers 62914560 bytes
Redo buffers 6094848 bytes
Database mounted.

[[email protected] ~]$ Rman

Recovery manager:release on Thu Dec 18 09:50:39 2014

Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved.

Rman> Connect Target/

Connected to target DATABASE:ORCL (dbid=1391294860, not open)



[Email protected] ~]$ Sqlplus/nolog
@>conn/as SYSDBA
SYS as [email protected]>alter database Open

To recover a table space:
Database to be restored with open, need to set the table space to be restored to offline state

[[email protected] ~]$ Rman
Rman> Connect Target/
rman> RESTORE tablespace USERS;
rman> RECOVER tablespace USERS;
Rman> SQL "ALTER tablespace USERS ONLINE";

To recover a single block of data:
Single data blocks can be recovered in Rman
1. Recover all corrupted data blocks
rman> RECOVER corruption LIST;
2. Restore the specified corrupted data block, you can find the corrupted block in the trace file and warning log.
To view the location of trace files and warning logs
SELECT Name,value from V$diag_info;
The data file number and block number can be specified when the data block is restored:
Rman> RECOVER datafile 1 Block 233,235 datafile 2 block 200;
To restore the archive log:
Rman> Run {
2> Allocate channel DEV1 type disk;
3> restore Archivelog all;
4> release channel Dev1;

Perform a full database backup in Enterprise Manager

SYS as [email protected]>grant SYSDBA to RMAN;

Grant succeeded.

SYS as [email Protected]>execute mgmt_user. Make_em_user (' RMAN ');

PL/SQL procedure successfully completed.

Using the Flashback database:
The Oracle database can be quickly regressed to a previous time
To view the initialization parameter values for the Quick recovery area:
SYS as [email protected]>show PARAMETER db_recovery_file_dest;

------------------------------------ ----------- ------------------------------
Db_recovery_file_dest String/u01/app/oracle/flash_recovery
Db_recovery_file_dest_size Big Integer 10G

1.SYS as [email protected]>shutdown immediate
SYS as [email protected]>startup mount
SYS as [email protected]>alter DATABASE FLASHBACK on
SYS as [email protected]>alter DATABASE OPEN;
SYS as [email protected]>select name,log_mode from V$database;

NAME Log_mode
--------- ------------

2. Create a Test table:
SYS as [email protected]>create TABLE HR. MYDEP as SELECT * from HR. Departments;

14:08:57 SYS as [email protected]>drop TABLE HR. MYDEP;

14:42:34 SYS as [email Protected]>desc HR. MYDEP;
Ora-04043:object HR. MYDEP does not exist

3.14:09:40 SYS as [email Protected]rcl>shutdown IMMEDIATE
14:10:00 SYS as [email protected]>startup MOUNT

Set the display format for the current system date:
14:11:55 SYS as [email protected]>alter SESSION SET nls_date_format= ' yyyy-mm-dd HH24:MI:SS ';

To view the Flashback database log information in V$flashback_database_log
14:16:14 SYS as [email protected]>select * from V$flashback_database_log;

OLDEST_FLASHBACK_SCN Oldest_flashback_ti retention_target flashback_size estimated_flashback_size
-------------------- ------------------- ---------------- -------------- ------------------------
1759563 2014-12-18 14:04:51 1440 8192000 0

1 row selected.

14:16:52 SYS as [email protected]>flashback DATABASE to TIMESTAMP (to_date (' 2014-12-18 14:05:00 ', ' yyyy-mm-dd hh24:mi: SS '));

4. Open the database to see if the table exists:
ALTER DATABASE OPEN resetlogs| Noresetlogs

Flash Back table:
You can restore data, indexes, triggers, and so on in the specified table to a specified point in time
FLASHBACK table< Table name >
To[before DROP [RENAME to < table alias;] | [scn| TIMESTAMP] < expressions >
[Enable|disable triggers]
1. Insert Data:
15:04:22 SYS as [email protected]>insert into HR. MY VALUES (1, ' RSC ', 1, 1);
2. Query data:
15:04:47 SYS as [email protected]>select * from HR. MY;
1 RSC 1 1
3. Flashback table: Insert data is 15:04:22, if you want to restore the previous data, that is, delete the inserted data, the flashback time to the time before the insertion of data can be
FLASHBACK TABLE HR. MY to TIMESTAMP (to_date (' 2014-12-18 15:04:00 ', ' yyyy-mm-dd HH24:MI:SS '));
4. View data, no data just inserted

Flash back to Recycle Bin:
When a table or database is deleted, the system is not completely deleted, but is placed in the Recycle Bin
You might use flashback to restore a data object to the appropriate table space
FLASHBACK Table < name > to before DROP;

The operation is as follows:
1. Delete Table HR.MYDEP:
15:20:08 SYS as [email protected]>drop TABLE HR. MYDEP;

2. View the deleted tables in the Recycle Bin:
15:21:22 SYS as [email protected]>select object_name,original_name,createtime,droptime from Dba_recyclebin;
object_name original_name Createtime Droptime
Bin$cnls7xzrl7hgukjahsgy7g==$0 MYDEP 2014-12-18:14:07:50 2014-12-18:15:20:23

3. Flash back to the table specified in the Recycle Bin:
15:28:05 SYS as [email protected]>flashback TABLE HR. MYDEP to before DROP;

4. View the recovered Table objects:
5:28:23 SYS as [email Protected]>desc HR. MYDEP;
Name Null? Type
----------------------------------------------------------------- -------- -------------------------------------- ------
Department_name not NULL VARCHAR2 (30)
MANAGER_ID Number (6)
LOCATION_ID Number (4)

You can delete data from the Recycle Bin with the Purge table statement:

Use purge Dba_recyclebin to empty the entire Recycle Bin:
Normal user can use: PURGE recyclebin command to empty the entire Recycle Bin

Flashback queries (Flashback query)
The data can be found before the misoperation, providing a basis for recovering the data
Statement: A flashback query is the addition of the as of TIMESTAMP in a traditional SELECT statement
SELECT * from HR. Ryx as of TIMESTAMP to_timestamp (' 2014-12-18 15:42:00 ', ' yyyy-mm-dd HH24:MI:SS ');
Criteria for Flashback query:
1. The initialization parameter undo_management must be set to auto

2. The initialization parameter undo_retention determines the maximum time to forward the query, the greater the value can be flashed forward back to the query for the maximum time, but the more disk space occupied

1. Create a table
15:40:18 SYS as [email protected]>create TABLE HR. Ryx (ID number,name VARCHAR2 (20));

2. Insert Data:
15:41:20 SYS as [email protected]>insert into HR. Ryx VALUES (1, ' 1 ');

3. Delete the rows specified in the created table
15:42:02 SYS as [email protected]>delete from HR. Ryx WHERE id=1;

4. View deleted rows;
SELECT * from HR. Ryx as of TIMESTAMP to_timestamp (' 2014-12-18 15:04:00 ', ' yyyy-mm-dd HH24:MI:SS ' 2014-12-18 15:41:35 ', ' yyyy-mm-dd HH24: Mi:ss ');

Flash back version query:
Flashback version query can only query the data after committing (commit)
Method: Use the versions between clause in the SELECT statement

1. Create a table:
15:46:46 SYS as [email protected]>create TABLE HR. TEST (score number);
2. Inserting data
15:56:31 SYS as [email protected]>insert into HR. TEST VALUES (100);
3. Update the data:
15:56:54 SYS as [email protected]>update HR. TEST SET score=110 WHERE score=100;
4. Delete data:
15:57:22 SYS as [email protected]>delete from TEST WHERE score>0;
5. Flash back version query:
15:57:45 SYS as [email protected]>select versions_starttime,versions_operation,score from HR. TEST VERSIONS between TIMESTAMP MINVALUE and MAXVALUE;

Versions_starttime V Score
--------------------------------------------------------------------------- - ----------
18-dec-14 03.57.43 PM D 110
18-dec-14 03.57.19 PM U 110
18-dec-14 03.56.51 PM I 100

Flashback transaction Query:
For committed transactions, you can also query by flashback, Flash back to the Transaction save table Flashback_transaction_query, you can query this table to understand the transactions that have occurred

16:01:14 SYS as [email protected]>select table_name,undo_sql from Flashback_transaction_query WHERE rownum<3;

Backing up and recovering databases

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.