06.Oracle data backup and recovery RMAN

oracle basic system learning directory

01.CentOS7 silently install oracle11g
02.Oracle startup process
03. Start with simple sql
04.Oracle’s architecture
05.Oracle database objects
06.Oracle data backup and recovery
07.User and permission management
08.Oracle tables
09.Partitioning of Oracle tables
10.Oracle synonyms and sequences
11.Oracle’s view
12.Oracle index
13.Oracle connects to Java through JDBC
14.Transactions in Oracle
15.Oracle11g archiving method and log file related operations
16.Oracle’s data dictionary and dynamic performance view
17. PL/SQL basics of Oracle11g
18.Oracle procedures and functions
19. Cursors in Oracle11g
20. Triggers in Oracle11g
21.Oracle package (Package)
22.Temporary tablespace in Oracle
23. Oracle11g UNDO table space
24.Logical backup and recovery of Oracle11g
25. Oracle’s Recycle Bin
26.Oracle11g data loading
27.Oracle11g Flashback
28.Oracle11g materialized view

Oracle data backup and recovery RMAN

  • oracle basic system learning directory
  • 1. Backup through RMAN
  • 2. Use emp/imp and expdb/impdb tools for backup and recovery
  • 3. Use Data guard for backup and recovery
  • Click here to jump to the next section: 07. User and permission management

1. Backup through RMAN

To back up the Oracle database through RMAN (Oracle Database Backup and Recovery Manager), you can use the following steps:

  1. Start archive mode
    Check whether it is in archive mode:

    SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Current log sequence 13
SQL>

    Close the database and start archive mode

    SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size 2213016 bytes
Variable Size 352324456 bytes
Database Buffers 922746880 bytes
Redo Buffers 8781824 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL>

    Open the database and check whether the modification is successful

    SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL>

If you want to perform a backup in archive mode, you can only start the database in the mount state.
However, in a production environment, generally the database cannot be closed at will, so the archive mode is usually turned on directly after the database is installed. To change the archive, you need to be in mount mode.

  1. Open the RMAN command line interface:

    [oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Nov 3 17:58:54 2023

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

connected to target database: ORCL (DBID=1679473799)

RMAN>

  2. Start backup
    (1) Use backup database; to directly back up the database

    RMAN> backup database;

Starting backup at 03-NOV-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-NOV-23
channel ORA_DISK_1: finished piece 1 at 03-NOV-23
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_nnndf_TAG20231103T194131_ln9q3d1w_.bkp tag=TAG20231103T194131 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-NOV-23
channel ORA_DISK_1: finished piece 1 at 03-NOV-23
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_ncsnf_TAG20231103T194131_ln9q3wok_.bkp tag=TAG20231103T194131 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-NOV-23

RMAN>

    Enter the relevant directory to view the backup results

    [oracle@localhost ~]$ cd /u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/
[oracle@localhost 2023_11_03]$ll
total 1016728
-rw-r----- 1 oracle oinstall 9830400 Nov 3 18:09 o1_mf_ncsnf_TAG20231103T180906_ln9kp3ff_.bkp
-rw-r----- 1 oracle oinstall 9830400 Nov 3 19:25 o1_mf_ncsnf_TAG20231103T192501_ln9p4gqt_.bkp
-rw-r----- 1 oracle oinstall 9830400 Nov 3 19:41 o1_mf_ncsnf_TAG20231103T194131_ln9q3wok_.bkp
-rw-r----- 1 oracle oinstall 1011638272 Nov 3 19:41 o1_mf_nnndf_TAG20231103T194131_ln9q3d1w_.bkp
[oracle@localhost 2023_11_03]$

    Restore data (the database also needs to be in the mount state when restoring data):

    run {
 set until time "to_date('11/22/2023 10:00:00','mm/dd/yyyy hh24:mi:ss')";
 restore database;
 recover database;
 alter database open resetlogs;
}

    rman can not only back up, but also use it for management. Examples are as follows:

    (2) Use backup tablespace USERS; to back up and restore the table space

    RMAN> backup tablespace USERS;

Starting backup at 03-NOV-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-NOV-23
channel ORA_DISK_1: finished piece 1 at 03-NOV-23
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_nnndf_TAG20231103T223829_lnb1h5d1_.bkp tag=TAG20231103T223829 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-NOV-23

RMAN>

    If the table space has been damaged and needs to be restored, you can first use restore tablespace USERS; and then use recover tablespace USERS;
    (3) Use backup archivelog all; to back up archive logs

    RMAN> backup archivelog all;

Starting backup at 07-NOV-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=1 STAMP=1152203517
input archived log thread=1 sequence=14 RECID=2 STAMP=1152231422
channel ORA_DISK_1: starting piece 1 at 07-NOV-23
channel ORA_DISK_1: finished piece 1 at 07-NOV-23
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_07/o1_mf_annnn_TAG20231107T001702_lnl4cy94_.bkp tag=TAG20231107T001702 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-NOV-23

    Use the `CROSSCHECK BACKUP` command to verify the integrity of the backup. RMAN will check the status of the backup file and mark it as EXPIRED or AVAILABLE.

    Examples are as follows:

    RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_ncsnf_TAG20231103T180906_ln9kp3ff_.bkp RECID=1 STAMP=1151950147
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_ncsnf_TAG20231103T192501_ln9p4gqt_.bkp RECID=2 STAMP=1151954702
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_nnndf_TAG20231103T194131_ln9q3d1w_.bkp RECID=3 STAMP=1151955692
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_ncsnf_TAG20231103T194131_ln9q3wok_.bkp RECID=4 STAMP=1151955708
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_nnndf_TAG20231103T223829_lnb1h5d1_.bkp RECID=5 STAMP=1151966309
Crosschecked 5 objects


RMAN>

2. Use emp/imp and expdb/impdb tools for backup and recovery

emp/imp are Oracle's traditional backup and recovery tools, which use binary formats to export and import data.
expdb/impdb are new tools introduced in Oracle 10g and above. They use XML format to export and import data.
Below we will explain in detail how to use these tools for backup and recovery.

  1. Backup and restore using emp/imp

    The emp and imp commands are used to export data from the Oracle database to a file for backup or migration. They can export an entire database or individual tables to a file and then import the file into another database.

    Back up data:

    exp userid=username/password file=d:\backup\emp_backup.dmp full=y

    The above command exports the entire database to the d:\backup\emp_backup.dmp file.

    Data recovery:

    imp userid=username/password file=d:\backup\emp_backup.dmp full=y

    The above command imports the data in the d:\backup\emp_backup.dmp file into the database.

  2. Backup and restore using expdb/impdb

    The expdb and impdb commands are new tools introduced in Oracle 10g and above. They use XML format to export and import data. Their usage is similar to emp/imp.

    Back up data:

    expdp userid=username/password directory=backup_dir dumpfile=expdb_backup.dmp full=y

    The above command exports the entire database to the expdb_backup.dmp file in the backup_dir directory.

    Data recovery:

    impdp userid=username/password directory=backup_dir dumpfile=expdb_backup.dmp full=y

    The above command imports the data in the expdb_backup.dmp file in the backup_dir directory into the database.

3. Use Data guard for backup and recovery

Oracle Data Guard is a high-availability solution provided by Oracle Database, which can realize data backup and recovery functions. It provides data redundancy and disaster recovery capabilities by replicating data from the primary database to one or more standby databases in real time.

The following is a detailed explanation of using Data Guard for backup and recovery:

  1. Configure Data Guard

    First, Data Guard needs to be configured on the primary and standby databases. Do the following on the primary database:

    • Enable archive mode: ALTER DATABASE ARCHIVELOG;

    • Configure the log transport service: ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_db ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby_db';

    Do the following on the standby database:

    • Enable archive mode: ALTER DATABASE ARCHIVELOG;

    • Configure the log application service: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

  2. Start Data Guard

    Start Data Guard on the primary database:

    • Start the log transfer service: ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

    Start Data Guard on the standby database:

    • Start the log application service: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

  3. Data backup and recovery

    After the Data Guard configuration is completed, the data on the primary database will be replicated to the standby database in real time. If the primary database fails, a standby database can be used for data recovery.

    Back up data:

    • Create a backup on the standby database: RMAN> BACKUP DATABASE;

    Data recovery:

    • Failover on the standby database: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

    • Switch the standby database to the primary database: ALTER DATABASE ACTIVATE STANDBY DATABASE;

    • Data recovery on the new standby database: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Click here to jump to the next section: 07. User and permission management