Backing up and recovering databases

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

DBID NAME Log_mode
---------- --------- ------------
1391294860 ORCL ARCHIVELOG

1 row selected.

RMAN:
Login:
[[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
BACKUP DATABASE PLUS ARCHIVELOG;

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
ALTER DATABASE OPEN;

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
rman> BACKUP INCREMENTAL level 0 DATABASE;
Perform a 1-level cumulative incremental backup with Rman
Rman> BACKUP INCREMENTAL Level 1 cumulative DATABASE;
Use Rman to perform a level 1 differential incremental backup
Rman> BACKUP INCREMENTAL level 1 DATABASE;


Verifying database files and backups
Check all database files and archive redo log files for physical and logical corruption
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG all;
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:
LIST Backup of DATABASE by BACKUP

To display the backup information in a file:
LIST BACKUP by FILE;

Displays comprehensive information about the backup
LIST BACKUP SUMMARY;

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
---------- --------- ------------
1391294860 ORCL ARCHIVELOG

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

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.

elapsed:00:00:00.45
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 11.2.0.1.0-production 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
Rman> REGISTER DATABASE;

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;
5>}
To back up the archive log file separately:
Rman> Run {
2> Allocate channel DEV1 type disk;
3> backup Archivelog all;
4> release channel Dev1;
5>}


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 11.2.0.1.0-production 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)

Rman> RESTORE DATABASE;

Rman> RECOVER DATABASE;

[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> SQL "ALTER tablespace USERS OFFLINE IMMEDIATE";
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;
5>}


Perform a full database backup in Enterprise Manager

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

Grant succeeded.

elapsed:00:00:00.08
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;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_recovery_file_dest String/u01/app/oracle/flash_recovery
_area
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
--------- ------------
ORCL ARCHIVELOG

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;
ERROR:
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
DESC HR. MYDEP;

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_ID Number (4)
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:
PURGE TABLE HR. Mydep

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
Big


Test:
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);
COMMIT
3. Update the data:
15:56:54 SYS as [email protected]>update HR. TEST SET score=110 WHERE score=100;
COMMIT
4. Delete data:
15:57:22 SYS as [email protected]>delete from TEST WHERE score>0;
COMMIT
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