Oracle Backup and Recovery (Rman chapter)

Introduction: Welcome to add QQ Exchange 1445696451 Weibo address http://blog.51cto.com/13434336
~~~~~~~ Split~~~~~~
Database backup and recovery is what the database administrator must master. No system can be protected from physical damage to the hard disk, careless user error operations, or potential disasters that could threaten to store data. In order to maximize the recovery of database data and ensure the safe operation of the database, the most reasonable backup method should be chosen to prevent the loss of user data caused by various failures.
I. Common data loss scenarios for Oracle:
1. Statement failure
A logical failure occurred during the execution of the SQL statement when the statement failed. For example, inserting invalid data into a table that violates a constraint, or if there is no space in the table space, the data cannot be inserted. At this point, the application person or DBA will handle the situation accordingly and can.
2. User Process failure
When a user program fails to access the Oracle database, a user process failure occurs because of an abnormally disconnected or terminated process, which causes the user process to terminate unexpectedly with the server if the network is not reachable and the client computer restarts.
A user process failure only causes the current user to not operate the database properly, but does not affect other user processes. Process Monitoring Program (Pmon) automatically performs process recovery when a user process fails. Pmon is a background process for Oracle that detects server processes that have lost connectivity to user processes. Pmon will handle the failure by rolling back the transaction, and will also release the resources currently occupied by the process.
3. Instance failure
An instance failure occurs when an Oracle DB instance cannot continue to run due to a hardware failure or a software problem. Hardware issues include accidental power outages, and software problems could be a server operating system crash.
When the database is restarted, Oracle automatically completes the instance recovery if an instance failure is found. Instance recovery restores the database to a state that is consistent with the transaction before the failure, and Oracle automatically rolls back uncommitted data.
4. Media failure
A media failure is a failure that occurs when a database file, a portion of a file, or a disk is unreadable or cannot be written. For example, damage to the hard disk head can result in a complete corruption of the database file. To fix database file corruption due to media failure, you need to use media recovery.
Second, the classification of backup

1, from the physical and logical point of view:
1) Physical Backup: A backup of physical files (such as data files, control files, and log files) of the database operating system. Physical backups can also be broken down into offline backups (cold backups) and online backups (hot backups), which are performed when the database is shut down, which backs up the database that is running in the archive log mode. You can use Oracle's recovery Manager (RMAN) or operating system commands to make a physical backup of the database.
2) Logical Backup: A backup of a database logical component, such as a database object, such as tables and stored procedures. There are many means of logical backup, such as traditional exp, data pump EXPDP, database flashback technology and third-party tools, which can make logical backup of database.
2. From the perspective of backup strategy
1) Full backup: Perform a full backup of the data every time. In the event of a data loss disaster, a full backup can achieve 100% data recovery without relying on additional information, with the shortest recovery time and the most convenient operation.
2) Incremental backup: Only those files that were modified after the last full or incremental backup are backed up. The advantage is that the amount of backup data is small, the time required is short, the drawback is that the recovery needs to rely on the previous backup records, the risk of a larger problem.
3) Differential backup: Backs up files that were modified relative to the last full backup. Recovering a database from a differential backup takes a short time, so only two copies (the last full and last differential) are needed, with the disadvantage that it takes longer to take each backup.
3. Effective backup Strategy recommendations
1) A full backup every 3 days at 2 o'clock.
2) A differential backup of 3 points per day.
3) Specify scheduled tasks based on the backup policy above
Third, recovery
Recovery is the failure to re-establish a complete database using the backed up data or control files. The recovery is divided into the following two types.
1. Instance Recovery: Oracle automates recovery When an Oracle instance fails.
2. Media recovery: When the media that holds the database fails, the recovery is done. Media recovery is also divided into full recovery and incomplete recovery.
Full recovery: The state when the database is restored to a database failure. This recovery is done by loading the database backup and using the full redo log.
Incomplete recovery: Restores the database to the state of a time before the database fails. This recovery is done by loading the database backup and applying a partial redo log. After an incomplete recovery, you must reset the online redo log with resetlogs selection when you start the database.

Rman operations
1. Preparatory work
First, Rman works in archive log mode, so we're going to turn this mode on
[Email protected] uniread-1.01]# uniread sqlplus sys/123456 as Sysdba
sql> shutdown Immediate
Sql> Startup Mount
sql> ALTER DATABASE Archivelog;
sql> ALTER DATABASE open;
Build the/usr/local/rman directory with root and authorize Oracle
[Email protected] local]# mkdir-p/usr/local/rman
[Email protected] local]# chown-r oracle/usr/local/rman/
[Email protected] local]# chmod-r 775/usr/local/rman/
2, Rman official operation
Log in with an Oracle user, create a recovery directory for Rman, and register the target database to the recovery directory
[Email protected] local]$ uniread sqlplus sys/123456 as Sysdba
sql> Create tablespace rman_ts datafile '/usr/local/rman/rman.ora ' size
20M autoextend on next 5M MaxSize Unlimited;
Sql> create user Zhangsan identified by pwd123 default tablespace rman_ts temporary tablespace temp;
Sql> Grant Connect,resource to Zhangsan;
Sql> Grant Recovery_catalog_owner to Zhangsan;
Sql> quit
[Email protected] local]$ Rman catalog zhangsan/pwd123
rman> Create catalog Tablespace rman_ts;
Rman> quit
Switch Login Rman
[Email protected] local]$ Rman catalog zhangsan/pwd123 target/
rman> Register database;
Automatic backup allocation Channel
Rman> Configure device type disk parallelism 5;
Rman> Configure default device type to disk;
Manually backup the allocation channel (manual and Auto-select one)
Rman> RUN
2> {
3> ALLOCATE CHANNEL ch1 DEVICE TYPE disk;
4> ALLOCATE CHANNEL CH2 DEVICE TYPE disk;
5> ALLOCATE CHANNEL CH3 DEVICE TYPE disk;
6>}
Start Backup (The following command means backing up the entire database and the archive log, without specifying that the backup path is backed up to the default path)
Rman> backup Database plus archivelog;
3. Recovering data
View backed-up files

1) Analog fault users01.dbf lost

The database has been unable to shut down, error: Can not find File4 is users01.dbf this file

Perform the following command to recover the lost users01.dbf
[Email protected] local]$ Rman target sys/123456
Rman> Run
2> {
3> SQL ' alter tablespace users offline immediate ';
4> restore tablespace users;
5> Recover tablespace users;
6> SQL ' alter tablespace users online ';
7>}
This can be found, the database can be shut down normally, and then can also open the

2) Analog system01.dbf lost (not sure if you can fix it.) ）

Landing Rman reported a pile of errors, it is estimated that the waste. Well, the key is the courage to try

SYS can't land.

System's not landing.

Note: In fact system01.dbf is lost, if Oracle can be recovered by Rman in Mount state, but this time my Oracle is in open state, so I can not login Rman for recovery operation (*￣ー￣) (￣ー￣〃)
4. Other operations
1) When backing up, specify the backup directory, the name format of the backup file (take/usr/local/rman) as an example
rman> backup databse FORMAT '/usr/local/rman/%u ';
2) backing up a single table space
rman> backup tablespace users;
3) Oracle recovers SYSTEM01.DBF under Mount
[Email protected] local]$ Rman target sys/123456
rman> Restore Database;
rman> Recover database;
rman> ALTER DATABASE open resetlogs;
[Email protected] local]$ uniread sqlplus sys/123456 as Sysdba
sql> ALTER DATABASE open;

Last words:
Some bloggers may notice that I added a uniread to Oracle's statement sqlplus before landing on Oracle, which is actually a clear screen turn-on tool. After loading the SQL statements in Oracle, you can use Ctrl+l and the "Up key" to flip the previously entered SQL statement.
Introduction: Welcome to add QQ Exchange 1445696451 Weibo address http://blog.51cto.com/13434336
~~~~~~~ Split~~~~~~
Database backup and recovery is what the database administrator must master. No system can be protected from physical damage to the hard disk, careless user error operations, or potential disasters that could threaten to store data. In order to maximize the recovery of database data and ensure the safe operation of the database, the most reasonable backup method should be chosen to prevent the loss of user data caused by various failures.
I. Common data loss scenarios for Oracle:
1. Statement failure
A logical failure occurred during the execution of the SQL statement when the statement failed. For example, inserting invalid data into a table that violates a constraint, or if there is no space in the table space, the data cannot be inserted. At this point, the application person or DBA will handle the situation accordingly and can.
2. User Process failure
When a user program fails to access the Oracle database, a user process failure occurs because of an abnormally disconnected or terminated process, which causes the user process to terminate unexpectedly with the server if the network is not reachable and the client computer restarts.
A user process failure only causes the current user to not operate the database properly, but does not affect other user processes. Process Monitoring Program (Pmon) automatically performs process recovery when a user process fails. Pmon is a background process for Oracle that detects server processes that have lost connectivity to user processes. Pmon will handle the failure by rolling back the transaction, and will also release the resources currently occupied by the process.
3. Instance failure
An instance failure occurs when an Oracle DB instance cannot continue to run due to a hardware failure or a software problem. Hardware issues include accidental power outages, and software problems could be a server operating system crash.
When the database is restarted, Oracle automatically completes the instance recovery if an instance failure is found. Instance recovery restores the database to a state that is consistent with the transaction before the failure, and Oracle automatically rolls back uncommitted data.
4. Media failure
A media failure is a failure that occurs when a database file, a portion of a file, or a disk is unreadable or cannot be written. For example, damage to the hard disk head can result in a complete corruption of the database file. To fix database file corruption due to media failure, you need to use media recovery.
Second, the classification of backup

1, from the physical and logical point of view:
1) Physical Backup: A backup of physical files (such as data files, control files, and log files) of the database operating system. Physical backups can also be broken down into offline backups (cold backups) and online backups (hot backups), which are performed when the database is shut down, which backs up the database that is running in the archive log mode. You can use Oracle's recovery Manager (RMAN) or operating system commands to make a physical backup of the database.
2) Logical Backup: A backup of a database logical component, such as a database object, such as tables and stored procedures. There are many means of logical backup, such as traditional exp, data pump EXPDP, database flashback technology and third-party tools, which can make logical backup of database.
2. From the perspective of backup strategy
1) Full backup: Perform a full backup of the data every time. In the event of a data loss disaster, a full backup can achieve 100% data recovery without relying on additional information, with the shortest recovery time and the most convenient operation.
2) Incremental backup: Only those files that were modified after the last full or incremental backup are backed up. The advantage is that the amount of backup data is small, the time required is short, the drawback is that the recovery needs to rely on the previous backup records, the risk of a larger problem.
3) Differential backup: Backs up files that were modified relative to the last full backup. Recovering a database from a differential backup takes a short time, so only two copies (the last full and last differential) are needed, with the disadvantage that it takes longer to take each backup.
3. Effective backup Strategy recommendations
1) A full backup every 3 days at 2 o'clock.
2) A differential backup of 3 points per day.
3) Specify scheduled tasks based on the backup policy above
Third, recovery
Recovery is the failure to re-establish a complete database using the backed up data or control files. The recovery is divided into the following two types.
1. Instance Recovery: Oracle automates recovery When an Oracle instance fails.
2. Media recovery: When the media that holds the database fails, the recovery is done. Media recovery is also divided into full recovery and incomplete recovery.
Full recovery: The state when the database is restored to a database failure. This recovery is done by loading the database backup and using the full redo log.
Incomplete recovery: Restores the database to the state of a time before the database fails. This recovery is done by loading the database backup and applying a partial redo log. After an incomplete recovery, you must reset the online redo log with resetlogs selection when you start the database.

Rman operations
1. Preparatory work
First, Rman works in archive log mode, so we're going to turn this mode on
[Email protected] uniread-1.01]# uniread sqlplus sys/123456 as Sysdba
sql> shutdown Immediate
Sql> Startup Mount
sql> ALTER DATABASE Archivelog;
sql> ALTER DATABASE open;
Build the/usr/local/rman directory with root and authorize Oracle
[Email protected] local]# mkdir-p/usr/local/rman
[Email protected] local]# chown-r oracle/usr/local/rman/
[Email protected] local]# chmod-r 775/usr/local/rman/
2, Rman official operation
Log in with an Oracle user, create a recovery directory for Rman, and register the target database to the recovery directory
[Email protected] local]$ uniread sqlplus sys/123456 as Sysdba
sql> Create tablespace rman_ts datafile '/usr/local/rman/rman.ora ' size
20M autoextend on next 5M MaxSize Unlimited;
Sql> create user Zhangsan identified by pwd123 default tablespace rman_ts temporary tablespace temp;
Sql> Grant Connect,resource to Zhangsan;
Sql> Grant Recovery_catalog_owner to Zhangsan;
Sql> quit
[Email protected] local]$ Rman catalog zhangsan/pwd123
rman> Create catalog Tablespace rman_ts;
Rman> quit
Switch Login Rman
[Email protected] local]$ Rman catalog zhangsan/pwd123 target/
rman> Register database;
Automatic backup allocation Channel
Rman> Configure device type disk parallelism 5;
Rman> Configure default device type to disk;
Manually backup the allocation channel (manual and Auto-select one)
Rman> RUN
2> {
3> ALLOCATE CHANNEL ch1 DEVICE TYPE disk;
4> ALLOCATE CHANNEL CH2 DEVICE TYPE disk;
5> ALLOCATE CHANNEL CH3 DEVICE TYPE disk;
6>}
Start Backup (The following command means backing up the entire database and the archive log, without specifying that the backup path is backed up to the default path)
Rman> backup Database plus archivelog;
3. Recovering data
View backed-up files

1) Analog fault users01.dbf lost

The database has been unable to shut down, error: Can not find File4 is users01.dbf this file

Perform the following command to recover the lost users01.dbf
[Email protected] local]$ Rman target sys/123456
Rman> Run
2> {
3> SQL ' alter tablespace users offline immediate ';
4> restore tablespace users;
5> Recover tablespace users;
6> SQL ' alter tablespace users online ';
7>}
As you can see, the database shuts down normally, and you can then open the

2) Analog system01.dbf lost (not sure if you can fix it.) ）

Landing Rman reported a pile of errors, it is estimated that the waste. ~ Well, the key is the courage to try ~ ~

SYS can't land.

System's not landing.

Note: In fact system01.dbf is lost, if Oracle can be recovered by Rman in Mount state, but this time my Oracle is in open state, so I can not login Rman for recovery operation (*￣ー￣) (￣ー￣〃)
4. Other operations
1) When backing up, specify the backup directory, the name format of the backup file (take/usr/local/rman) as an example
rman> backup databse FORMAT '/usr/local/rman/%u ';
2) backing up a single table space
rman> backup tablespace users;
3) Oracle recovers SYSTEM01.DBF under Mount
[Email protected] local]$ Rman target sys/123456
rman> Restore Database;
rman> Recover database;
rman> ALTER DATABASE open resetlogs;
[Email protected] local]$ uniread sqlplus sys/123456 as Sysdba
sql> ALTER DATABASE open;

Last words:
Some bloggers may notice that I added a uniread to Oracle's statement sqlplus before landing on Oracle, which is actually a clear screen turn-on tool. After loading the SQL statements in Oracle, you can use Ctrl+l and the "Up key" to flip the previously entered SQL statement.

Oracle Backup and Recovery (Rman chapter)