Detailed description of Oracle DataGuard data backup solution

Oracle elastic uard is a database-level HA solution. Its primary functions include redundancy, data protection, and fault recovery.

When the "transaction consistency" of the production database is used, the physical full backup (or physical COPY) of the production database is used to create the standby database, the slave database automatically maintains the slave database through the archive logs (or redo entries) transmitted from the production database. Apply the redo data to the slave database. This document describes how to use RMAN backup to create a standby uard ).

I. Overview of Oracle DataGuard Environment

1. Software Environment

Operating System Red Linux Enterprise as 5

Database Version: Oracle 10g release 2

2. primary databae

IP: 192.168.18.1

ORACLE_SID = db1

Db_unique_name = db1

3. standby database

IP: 192.168.18.2

ORACLE_SID = standby

Db_unique_name = standby

2. Preparations for the primary database (db1)

1. Set the master database to Force logging

 
 

  
  
SQL> alter database force logging;  
 
 

2. Create a password file

 
 

  
  
cd $ORACLE_HOME/dbs/  
  
  
orapwdfile=orapwdb1 password=123456 force=y  
 
 

3. Modify the initialization parameters of the master database.

 
 

  
  
alter system set log_archive_config='dg_config=(db1,standby)' scope=both;  
  
  
alter system set log_archive_dest_1='location=/u01/db1/arch' scope=both;  
  
  
alter system set db_unique_name='db1' scope=both;  
 
 

4. Generate Database Backup

 
 

  
  
RMAN> connect target sys/123456  
  
  
RMAN> backup database format='/oracle/rmanback/%d_%s.dbf' plus archivelog;  
  
  
[oracle@oracle rmanback]$ ls  
  
  
DB1_1.dbfDB1_2.dbf  
 
 

5. Generate the control file of the slave Database

 
 

  
  
SQL>alter database create standby controlfile as '/oracle/rmanback/ctontrl01.ctl  
 
 

6. Configure the listener. ora and tnsnames. ora files.

Start lintener. ora,

 
 

  
  
[oracle@oracle dbs]$ lsnrctl status  
  
  
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 14-JUN-2009 02:54:29  
  
  
Service "db1" has 1 instance(s).  
  
  
Instance "db1", status READY, has 1 handler(s) for this service...  
  
  
Service "db1_XPT" has 1 instance(s).  
  
  
Instance "db1", status READY, has 1 handler(s) for this service...  
  
  
The command completed successfully  
 
 

Configure rnsnames. ora

 
 

  
  
vi $ORACLE_HOME/network/admin/tnsnames.ora  
  
  
db1 =  
  
  
  (DESCRIPTION =  
  
  
    (ADDRESS_LIST =  
  
  
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.1)(PORT = 1521))  
  
  
    )  
  
  
    (CONNECT_DATA =  
  
  
      (SERVICE_NAME = db1)  
  
  
standby =  
  
  
  (DESCRIPTION =  
  
  
    (ADDRESS_LIST =  
  
  
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.2)(PORT = 1521))  
  
  
    )  
  
  
    (CONNECT_DATA =  
  
  
      (SERVICE_NAME = standby)  
  
  
    )  
  
  
  ) 
 
 

3. Create a standby database

1. Set environment variables and install oracle software

 
 

  
  
# Set the environment variable to the same as that of the master database 
  
  
# Installing only the uninstallation Software 
  
  
./RunInstaller-silent-responseFile/tmp/installoracle. rsp
 
 

2. Create related directories

 
 

  
  
Cd $ ORACLE_HOME/dbs/
  
  
Orapwdfile = orapwSID password = 123456 force = y
  
  
# Note that the password must be the same as that of the master database; otherwise, the archive fails. 
 
 

3. Create a password file

 
 

  
  
Cd $ ORACLE_HOME/dbs/
  
  
Orapwd file = orapwSID password = 123456 force = y
  
  
# Note that the password must be the same as that of the master database; otherwise, the archive fails. 
 
 

3. Create a parameter file (pfile)

 
 

  
  
db_name = standby   
  
  
shared_pool_size = 120M  
  
  
undo_management = AUTO  
  
  
undo_tablespace = undotbs  
  
  
sga_max_size = 200M  
  
  
sga_target = 160M  
  
  
db_2k_cache_size = 4M  
  
  
   
  
  
standby_file_management=AUTO  
  
  
fal_server='db1' 
  
  
fal_client='standby' 
  
  
log_archive_dest_1='location=/u01/app/oracle/product/10.2.0/dbs/arch' 
  
  
log_archive_dest_2='SERVICE=db1 REOPEN=300' 
  
  
log_archive_dest_state_1='ENABLE' 
  
  
log_archive_dest_state_2='ENABLE'  
 
 

4. CP master database RMAN backup and control file to slave Database

Note: The backup storage location must be the same as that of the primary database RMAN backup file. The storage location of the control file must be the same as that of the standby database controlfile generated.

 
 

  
  
scp /oracle/rmanback/*.dbf root@192.168.18.2:/oracle/rmanback/  
  
  
scp /oracle/rmanback/*.ctl root@192.168.18.2:/oracle/oracle/oradata/standby/  
 
 

5. Use the backup control file to enable the backup database to mount

 
 

  
  
SQL>connect / as sysdba  
  
  
connnpcted to an idle instance．  
  
  
SQL>startup nomount pfile=$ORACLE_HOME/dbs/initstandby.ora  
  
  
SQL>alter database mount standby database 
 
 

6. Configure the listener. ora and tnsnames. ora files.

Start listener as the master database, and configure tnsnames. ora as the master database,

When the listener of the Master/Slave database is started, perform the test. The test is successful in the following example.

 
 

  
  
tnsping db1  
  
  
tnsping standby  
  
  
SQL> sqlplus sys/123456@db1  
  
  
SQL> sqlplus sys/123456@standby  
 
 

7. Dump the database

 
 

  
  
RMAN>connect target /  
  
  
connected to target database：TEST(DBID=788075692)  
  
  
RMAN> restore database  
 
 

8. Restore the database.

 
 

  
  
SQL> recover managed standby database disconnect from session;
  
  
# If you want to apply logs manually, run the following command: 
  
  
SQL> recover standic standby database;
 
 

9. Check whether the standby database is successfully created.

A. Switch logs on the primary database

 
 

  
  
SQL> alter system switch logfile  
 
 

B. Run the following statement on primary database

 
 

  
  
SQL> select max(sequence#) from v$archived_log;  
  
  
MAX(SEQUENCE#)  
  
  
--------------  
  
  
17  
 
 

C. Run the following statement on standby database

 
 

  
  
SQL> select sequence# ,applied from v$archived_log order by sequence#;  
  
  
 SEQUENCE# APP  
  
  
--------------------- ---  
  
  
15YES  
  
  
16YES  
  
  
17YES  
 
 

If the status of the max sequence # app in the previous step is YES, the standby database is successfully created.

10. Start with spfile and set it to read-only

 
 

  
  
SQL> create spfile from pfile;  
  
  
SQL> shutdown immedaite  
  
  
SQL> startup mount  
  
  
SQL> alter database recover managed standby database disconnect from session;  
  
  
SQL> alter database open read only;  
 
 

The above is the procedure for Oracle DataGuard data backup. This article is from the "pursuit" blog.

1. OOP concepts in Oracle databases
2. Effect of disk sorting on Oracle database performance (1)
3. Prospective role in Oracle Database Maintenance
4. Use Resource Manager to optimize Oracle Performance
5. Rollback Segment optimization for Oracle Performance Optimization