Build Oracle DataGuard (using Rman Backup)

Build Oracle DataGuard (using Rman Backup)

Operating system: Windows 2008

Database: 11.1.0.6

I. Information collection

1. Configuration information Collection

Description Host name IP address db_name SID db_unique_name Service_Name

Main Library DG1 192.168.0.11 ORCL ORCL orcldg1 ORCLDG1

Standby library DG2 192.168.0.22 ORCL ORCL orcldg2 orcldg2

2. Main Library Information collection

2.1 Viewing database version information

Sql> SELECT * from V$version;

2.2 Viewing Database status

Sql> SELECT name,open_mode,database_role,force_logging,log_mode,flashback_on from V$database;

sql> SHOW PARAMETER NAME;

Ii. preparatory work

1. The Standby library installs the Oracle database software, does not create the database, guarantees two databases the installation path to be consistent;

Standby Settings Environment variables:

Oracle_home to D:\app\Administrator\product\11.1.0.6\db_1

Oracle_sid to ORCL

2. The repository uses the net Configuration Assistant in the Oracle Configuration and Migration Tool to create the listener;

3. Master Repository Creation Directory

Cmd> MD D:\app\Administrator\product\11.1.0.6\archive\arch

Cmd> MD D:\APP\ADMINISTRATOR\DATABASE\DG

Third, build Dataguard

1. Configuring the Listener.ora and Tnsnames.ora files for the master and standby libraries

1.1 Modify Listener.ora File

Sid_list_listener =

(Sid_list =

(Sid_desc =

(Sid_name = Plsextproc)

(Oracle_home = D:\app\Administrator\product\11.1.0.6\db_1)

(program = Extproc)

)

# # #主备库都新增内容

(Sid_desc =

(Global_dbname = ORCL)

(Oracle_home = D:\app\Administrator\product\11.1.0.6\db_1)

(Sid_name = ORCL)

)

#####

)

LISTENER =

(Description_list =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.11) (PORT = 1521))

)

)

Adr_base_listener = D:\app\Administrator

1.2 Modify Tnsnames.ora File

The main repository adds the following:

ORCLDG1 =

(DESCRIPTION =

(Address_list =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.11) (PORT = 1521))

)

(Connect_data =

(SERVER = dedicated)

(service_name = ORCLDG1)

)

)

ORCLDG2 =

(DESCRIPTION =

(Address_list =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.22) (PORT = 1521))

)

(Connect_data =

(SERVER = dedicated)

(service_name = ORCLDG2)

)

)

1.3 Rebooting the Master and Standby repository for monitoring

Cmd>lsnrctl stop

Cmd>lsnrctl start

1.4 Checking the Sql*net configuration

Use the following command to check the sql*net configuration in the primary and standby databases

Cmd>tnsping ORCLDG1

Cmd>tnsping ORCLDG2

1.5 Testing Remote Logins

Cmd>sqlplus Sys/[email protected] as Sysdba

Cmd>sqlplus Sys/[email protected] as Sysdba

2. Modify the main library parameters

sql> ALTER SYSTEM SET instance_name= ' ORCL ' scope=spfile; --Can not, use default settings

sql> ALTER SYSTEM SET db_unique_name= ' ORCLDG1 ' scope=spfile;

sql> ALTER SYSTEM SET local_listener= ' ORCLDG1 ' scope=spfile; --with TNS Name, or the default settings are not used

sql> ALTER SYSTEM SET log_archive_config= ' dg_config= (ORCLDG1,ORCLDG2) ' Scope=spfile; --This is db_unique_name!. If the db_unique_name is consistent, this parameter does not require a

sql> ALTER SYSTEM SET log_archive_dest_1= ' Location=d:\app\administrator\product\11.1.0.6\archive\arch LGWR VALID_ For= (all_logfiles,all_roles) db_unique_name=orcldg1 ' scope=spfile;

sql> ALTER SYSTEM SET log_archive_dest_2= ' service=orcldg2 ASYNC valid_for= (online_logfiles,primary_role) DB_UNIQUE _name=orcldg2 ' Scope=spfile;

sql> ALTER SYSTEM SET log_archive_dest_state_1= ' ENABLE ' scope=spfile;

sql> ALTER SYSTEM SET log_archive_dest_state_2= ' DEFER ' scope=spfile;--delay synchronization

sql> ALTER SYSTEM SET standby_file_management= ' AUTO ' scope=spfile;

sql> ALTER SYSTEM SET fal_client= ' ORCLDG1 ' scope=spfile; --With the main library TNS Name

sql> ALTER SYSTEM SET fal_server=orcldg2; scope=spfile;--with library TNS Name

sql> ALTER SYSTEM SET db_file_name_convert= ' D:\app\Administrator\database\dg ', ' D:\APP\ADMINISTRATOR\DATABASE\DG \ ' Scope=spfile;

sql> ALTER SYSTEM SET log_file_name_convert= ' D:\app\Administrator\database\dg ', ' D:\app\Administrator\database\ Dg\ ' Scope=spfile;

sql> ALTER SYSTEM SET standby_archive_dest= ' D:\app\Administrator\product\11.1.0.6\archive\arch ' scope=spfile; --Match the path to log_archive_dest_1 or not, use default settings

sql> ALTER SYSTEM SET log_archive_format= ' Arc_%t_%s_%r.arc ' scope=spfile; --Can not, use default settings

sql> ALTER SYSTEM SET log_archive_max_processes=5 scope=spfile; --Can not, use default settings

Note:

1) in Dataguar, there is no special case. Be sure to set the Standby_file_management parameter to auto and set the values for Db_file_name_convert and Log_file_name_convert.

If standby_file_management=manual, even if the main repository data file path is consistent, the main library to add data files, the repository is not automatically added, must be done manually.

If Standby_file_management=auto, the main library adds data files, the repository automatically creates the data files to the correct path based on the Db_file_name_convert parameter.

2) Db_file_name_convert and Log_file_name_convert are the locations used to convert data and standby log files online. Mainly used in the physical standby database and Rman database replication and TSPITR operations.

Their role is: The Master library path is not the same, the repository will be based on these two parameters to the data file and the redo file to the correct location.

3) Db_file_name_convert and Log_file_name_convert must be directories when specified, cannot be a file, or there will be a ORA-01678 error when starting the database.

3. Main Library enable force logging mode

sql> ALTER DATABASE Force LOGGING;

4. Turn on archive mode

Sql> SHUTDOWN IMMEDIATE;

Sql> STARTUP MOUNT;

sql> ALTER DATABASE ARCHIVELOG;

sql> ALTER DATABASE OPEN;

5. Make a full backup of the main library and transfer the backup set to the same location in the repository

Cmd> RMAN TARGET/

RUN

{

ALLOCATE CHANNEL C0 DEVICE TYPE DISK;

ALLOCATE CHANNEL C1 DEVICE TYPE DISK;

ALLOCATE CHANNEL C2 DEVICE TYPE DISK;

ALLOCATE CHANNEL C3 DEVICE TYPE DISK;

CONFIGURE Controlfile autobackup on;

CONFIGURE controlfile autobackup FORMAT for DEVICE TYPE DISK to ' D:\backup\%F ';

BACKUP DATABASE FORMAT ' D:\backup\ora11g_full_db_%d_%T_%u.bak ';

BACKUP ARCHIVELOG all FORMAT ' D:\backup\ora11g_arc_%s_%p_%t.bak ';

}

If the main library backup disk space is not enough can use compressed backup, but the time is slightly longer;

Compress Backup:

Modify the Backup database format in the above script to the backup as compressed database format

The backup takes a long time and the following configuration can continue during the backup process.

6. Copy the password file from the main library to the repository location

Password file default location: D:\app\Administrator\product\11.1.0.6\db_1\database\PWDorcl.ora

If it does not exist, you can generate the file as follows

cmd> orapwd file= ' $ORACLE _home/dbs/pwd%oracle_sid% ' password=xxxx entries=10 force=y

7. Generate a standby pfile file

7.1 Create a standby pfile file in the main library:

sql> CREATE pfile= ' D:\app\Administrator\product\11.1.0.6\db_1\dbs\init2.ora ' from SPFILE;

7.2 Copy the generated Init2.ora to the repository at the appropriate location

7.3 Modify Init2.ora File

Note : The following parameters are modified (as are the parameters that are modified when the main library is configured, the values are specific)

#DG CONFIG

*.log_archive_config= ' dg_config= (ORCLDG1,ORCLDG2) '

*.log_archive_dest_1= ' Location=d:\app\administrator\product\11.1.0.6\archive\arch LGWR VALID_FOR= (ALL_LOGFILES, All_roles) Db_unique_name=orcldg2 '

*.log_archive_dest_2= ' SERVICE=ORCLDG1 ASYNC valid_for= (online_logfiles,primary_role) DB_UNIQUE_NAME=ORCLDG1 '

*.standby_file_management= ' AUTO '

*.log_archive_dest_state_1= ' ENABLE '

*.log_archive_dest_state_2= ' ENABLE '

*.fal_server=orcldg1

*.db_unique_name=orcldg2

8. Start Standby monitoring

Cmd> Lsnrctl Start

9. Recovery of the Standby repository

Attention:

1) Although this is a recovery repository, the Rman command needs to be executed on the main library;

2) When recovering, copy the archived log files generated on the library to the repository, so that the differential data will be restored to the standby repository when the repository is restored.

Cmd> RMAN TARGET/

rman> CONNECT Auxiliary sys/[email protected]

Rman> DUPLICATE TARGET DATABASE for STANDBY Nofilenamecheck;

10. When the repository is restored, set the log_archive_dest_state_2 of the main library to enable

sql> ALTER SYSTEM SET log_archive_dest_state_2= ' ENABLE ';

11. Add standby Redo log to the main standby library

11.1 Viewing redo log information

View Log Group members

Sql> SELECT MEMBER from V$logfile;

View Log size

Sql> SELECT group,bytes/1024*1024*1024,members from V$log;

View Standby Redo Log

Sql> SELECT group#, BYTES from V$standby_log;

11.2 New Standby Redo Log

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ' D:\app\Administrator\database\dg\stdredo04.log ' SIZE 50M;

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ' D:\app\Administrator\database\dg\stdredo05.log ' SIZE 50M;

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ' D:\app\Administrator\database\dg\stdredo06.log ' SIZE 50M;

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ' D:\app\Administrator\database\dg\stdredo07.log ' SIZE 50M;

Note:

Standby Redo Log Group number reference formula: (online redo Log group number + 1) * Number of database threads, the number of single-machine threads is 1,rac generally 2.

Standby Redo Log has the same number of members and sizes as possible with the online redo log.

12. The Standby library is enabled

Start the standby to the (standby state) Mount mode

sql> ALTER database MOUNT STANDBY database;

Set up a repository for app log status

sql> ALTER database RECOVER MANAGED STANDBY database DISCONNECT from SESSION;

Automatic recovery of the standby library

sql> ALTER database RECOVER MANAGED STANDBY database CANCEL;

Start Standby to ReadOnly mode

sql> ALTER DATABASE OPEN;

Set up a standby to apply log status

sql> ALTER database RECOVER MANAGED STANDBY database USING current LOGFILE DISCONNECT from SESSION;

Reference:

Blog.chinaunix.net/uid-23284114-id-4142519.html

blog.itpub.net/25105315/viewspace-710330/

blog.csdn.net/hj402555749/article/details/17654769

blog.itpub.net/12679300/viewspace-1127775/

blog.csdn.net/yuzhenhuan01/article/details/6450565

Blog.chinaunix.net/uid-15108676-id-4134935.html

This article is from the "4699096" blog, please be sure to keep this source http://4709096.blog.51cto.com/4699096/1761537

Build Oracle DataGuard (using Rman Backup)