Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to build Oracle DataGuard". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to build Oracle DataGuard.

I. preparatory work

1. Planning

Main library

Prepare the library

Ip

192.168.131.100

192.168.131.101

Instance_name

Orcl1

Orcl1

Service_names

Db01

Db02

Db_unique_name

Db01

Db02

HostName

Primary

Standby

OS version

Oracle linux 6.4

Oracle linux 6.4

DB version

11.2.0.4

11.2.0.4

two。 Turn off the firewall

Service iptables stop

Chkconfig iptables off

3. Disable selinux Firewall

Vi / etc/selinux/config

Selinux=disabled

2. Open the archiving mode (main and standby database)

1. Create an archive directory

[oracle@primary] # mkdir-p / u01/app/oracle/archivelog

2. Enable archiving mode

(1) enable archiving mode from database to mount status

[root@primary ~] # su-oracle

[oracle@primary ~] $sqlplus / as sysdba

SQL > startup mount

ORACLE instance started.

Total System Global Area 776646656 bytes

Fixed Size 2257272 bytes

Variable Size 507514504 bytes

Database Buffers 264241152 bytes

Redo Buffers 2633728 bytes

Database mounted.

(2) set up the main library archive directory

SQL > alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog'

(3) enable archiving mode

SQL > alter database archivelog

Database altered.

(4) View archive settings

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / u01/app/oracle/archivelog

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

(5) Open the database

SQL > alter database open

Database altered.

3. Force the main library to be force logging mode (main library)

SQL > select force_logging from v$database

FOR

-

NO

SQL > alter database force logging

Database altered.

SQL > select force_logging from v$database

FOR

-

YES

4. HOST file configuration (master / standby library)

Root user (same as master / slave database)

[root@primary ~] # vi / etc/hosts

127.0.0.1 localhost localhost.localdomain

# Primary database IP

192.168.131.100 primary

# Standby database IP

192.168.131.101 standby

5. Configure lintener & tnsnames (main and standby libraries)

Oracle user

1. Listening configuration of master and slave database

(1) Monitoring configuration of the main database

[oracle@primary ~] $vi $ORACLE_HOME/network/admin/listener.ora

(add the following)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl1)

(ORACLE_HOME = / u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl1)

)

)

(2) standby monitoring configuration

[oracle@primary ~] $vi $ORACLE_HOME/network/admin/listener.ora

(add the following)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl1)

(ORACLE_HOME = / u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl1)

)

)

2. Modify the tnsnames.ora file (master / standby library)

(1) modify the main library

[oracle@primary ~] $vi $ORACLE_HOME/network/admin/tnsnames.ora

Db01 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl1) # # here service_name should be equal to the above GLOBAL_ name value

)

)

Db02 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl1)

)

)

(2) prepare for database modification

[oracle@primary ~] $vi $ORACLE_HOME/network/admin/tnsnames.ora

Db01 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl1)

)

)

Db02 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl1)

(UR=A)

)

)

(3) active and standby database detection (active and standby database)

[oracle@primary admin] $tnsping db01

TNS Ping Utility for Linux: Version 11.2.0.4.0-Production on 11-JUL-2018 08:48:56

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = primary) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1))

OK (10 msec)

[oracle@primary admin] $tnsping db02

TNS Ping Utility for Linux: Version 11.2.0.4.0-Production on 11-JUL-2018 08:49:01

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = standby) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1) (UR=A))

OK (10 msec)

6. Modify the parameter file (main and standby library)

1. Modify parameters of main library

(1) generate parameter files

SQL > create pfile from spfile

File created.

(2) modify the parameter file

[oracle@primary ~] $cd / u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@primary dbs] $vi initorcl1.ora

Add the following:

Db_unique_name=db01

Log_archive_config='dg_config= (db01,db02)'

Log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for= (all_logfiles,all_roles) db_unique_name=db01'

Log_archive_dest_2='service=db02 lgwr async valid_for= (online_logfiles,primary_roles) db_unique_name=db02'

Log_archive_dest_state_1=enable

Log_archive_dest_state_2=enable

Db_file_name_convert='/u01/app/oracle/oradata/orcl2','/u01/app/oracle/oradata/orcl1'

Log_file_name_convert='/u01/app/oracle/oradata/orcl2','/u01/app/oracle/oradata/orcl1'

Fal_server=db02

Fal_client=db01

Standby_file_management=auto

(3) generate spfile (shutdown status)

Create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora'

(4) Open the database

Startup (make the new parameter effective)

2. Prepare the database to modify the parameters

(1) generate parameter files

SQL > create pfile from spfile

File created.

(2) modify the parameter file

[oracle@primary ~] $cd / u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@primary dbs] $vi initorcl1.ora

Add the following:

Db_unique_name=db02

Log_archive_config='dg_config= (db01,db02)'

Log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for= (all_logfiles,all_roles) db_unique_name=db02'

Log_archive_dest_2='service=db01 lgwr async valid_for= (online_logfiles,primary_roles) db_unique_name=db01'

Log_archive_dest_state_1=enable

Log_archive_dest_state_2=enable

Db_file_name_convert='/u01/app/oracle/oradata/orcl1','/u01/app/oracle/oradata/orcl2'

Log_file_name_convert='/u01/app/oracle/oradata/orcl1','/u01/app/oracle/oradata/orcl2'

Fal_server=db01

Fal_client=db02

Standby_file_management=auto / / if you want to modify the size of the slave log file, you need to change the auto here to manual

****************

(3) generate spfile (shutdown status)

Create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora'

(4) Open the database

Startup (make the new parameter effective)

7. Copy the database

1. Enable the slave library to nomount mode

SQL > shutdown immediate

SQL > startup nomount

2. Copy RMAN (main library)

[oracle@primary ~] $rman target sys/oracle@db01 auxiliary sys/oracle@db02

RMAN > duplicate target database for standby nofilenamecheck from active database

At this point, the DataGuard build part has been completed!

8. Query the roles of the master and backup database

1. Query the main database

SQL > col db_unique_name for A15

SQL > select db_unique_name,open_mode,database_role,switchover_status from v$database

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--

Db01 READ WRITE PRIMARY SESSIONS ACTIVE

2. Prepare database query

SQL > col db_unique_name for A15

SQL > select db_unique_name,open_mode,database_role,switchover_status from v$database

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--

Db02 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

3. Parameter setting check

SQL > col name for A25

SQL > col value for A30

SQL > select a.name, a.value from v$parameter a where a.name like'% file_name_convert' or a.name like'% fal%' or a.name like 'standby_file%'

NAME VALUE

Db_file_name_convert / u01/app/oracle/oradata/orcl2

/ u01/app/oracle/oradata/orcl1

Log_file_name_convert / u01/app/oracle/oradata/orcl2

/ u01/app/oracle/oradata/orcl1

Fal_client db01

Fal_server db02

Standby_file_management auto

9. Add standby log group (standby database) 1. View database log group.

Check the number and size of log groups in the database, because the number of standby log groups we created is the original log

The number of groups + 1 and the product of thread ((1) * 3), size cannot be less than the size of the original log file.

SQL > select group#,thread#,bytes/1024/1024 Mpart status from v$log

GROUP# THREAD# M STATUS

--

1 1 50 UNUSED

2 1 50 CLEARING

3 1 50 CURRENT

SQL > select member from v$logfile

MEMBER

/ u01/app/oracle/oradata/orcl2/redo03.log

/ u01/app/oracle/oradata/orcl2/redo02.log

/ u01/app/oracle/oradata/orcl2/redo01.log

2. Create a new repository log group

SQL > alter database add standby logfile thread 1 group 4

2 ('/ u01 size) size 50m

Database altered.

SQL > alter database add standby logfile thread 1 group 5

2 ('/ u01 size) size 50m

Database altered.

SQL > alter database add standby logfile thread 1 group 6

2 ('/ u01 size) size 50m

Database altered.

SQL > alter database add standby logfile thread 1 group 7

2 ('/ u01 size) size 50m

Database altered.

3. View log group status

SQL > select group#,status,type,member from v$logfile

GROUP# STATUS TYPE MEMBER

3 ONLINE / u01/app/oracle/oradata/orcl2/redo03.log

2 ONLINE / u01/app/oracle/oradata/orcl2/redo02.log

1 ONLINE / u01/app/oracle/oradata/orcl2/redo01.log

4 STANDBY / u01/app/oracle/oradata/orcl2/redo04.log

5 STANDBY / u01/app/oracle/oradata/orcl2/redo05.log

6 STANDBY / u01/app/oracle/oradata/orcl2/redo06.log

7 STANDBY / u01/app/oracle/oradata/orcl2/redo07.log

10. Prepare the database application log

SQL > alter database recover managed standby database using current logfile disconnect from session

11. Verify database operation

1. Create a user in the main library

SQL > create user test identified by test

User created.

SQL > alter system switch logfile

System altered.

2. Check in the reserve database.

SQL > select username from dba_users where username='TEST'

USERNAME

-

TEST

Thank you for your reading, the above is the content of "how to build Oracle DataGuard", after the study of this article, I believe you have a deeper understanding of how to build Oracle DataGuard, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.