Shulou(Shulou.com)06/01 Report--

Datagard is a disaster recovery solution of Oracle Enterprise Edition, which is widely used in enterprises, so I will record the building process as a backup.

Hostname database version instance name IP

Db1 Oracle 11G R2 member 172.16.1.250

Db2 Oracle 11G R2 member 172.16.1.251

By default, the above Oracle databases have been installed, but only the database and monitoring are established on db1. Db2 only installs Oracle software, does not build libraries, and does not build listeners.

Table of contents:

Open the forced archive log

Add standby log group

Modify master / standby startup parameters

Processing of password files

Modify snooping

Copy listening files, parameter files, and password files to the standby library

Create a standby library control file

Copy the main library data files and log files to the standby library

Initialize and configure the standby library to do standby

DataGuard test

Active / standby handover test

Basic work:

a. Install CentOS 5.11 x866.64, turn off selinux,iptables, and automatically timing

b. Install Oracle 11G R2 DB1 to install software, monitor and build libraries, db2 only install software but not build libraries

Please refer to: http://fengwan.blog.51cto.com/508652/1330122

Add to / etc/hosts of db1

127.0.0.1 db1

172.16.1.251 db2

Add to / etc/hosts of db2

127.0.0.1 db2

172.16.1.250 db1

Open mandatory Archive (db1)

(db1) SQL > shutdown immediate

(db1) SQL > startup mount

(db1) SQL > alter database force logging

(db1) SQL > alter database archivelog

two。 Create a redo log group (must have one or more groups more than the original redo log, standby redo log is a necessary condition for using Real Time Apply)

(db1) SQL > select group#,member from v$logfile

GROUP# MEMBER

--

3 / opt/oracle/oradata/member/redo03.log

2 / opt/oracle/oradata/member/redo02.log

1 / opt/oracle/oradata/member/redo01.log

It can be seen from the above that there are three groups of redo log.

(db1) SQL > alter database add standby logfile ('/ opt/oracle/oradata/member/standby04.log') size 50m

(db1) SQL > alter database add standby logfile ('/ opt/oracle/oradata/member/standby05.log') size 50m

(db1) SQL > alter database add standby logfile ('/ opt/oracle/oradata/member/standby06.log') size 50m

(db1) SQL > alter database add standby logfile ('/ opt/oracle/oradata/member/standby07.log') size 50m

Check again whether the log group is created successfully.

(db1) SQL > select group#,member from v$logfile

GROUP# MEMBER

--

3 / opt/oracle/oradata/member/redo03.log

2 / opt/oracle/oradata/member/redo02.log

1 / opt/oracle/oradata/member/redo01.log

4 / opt/oracle/oradata/member/standby04.log

5 / opt/oracle/oradata/member/standby05.log

6 / opt/oracle/oradata/member/standby06.log

7 / opt/oracle/oradata/member/standby07.log

You can see the 4 sets of logs we created

3. Modify the startup parameters of the master and standby library

Generate parameter file

(db1) SQL > create pfile='/tmp/member.pfile' from spfile

Exit sqlplus and open / tmp/member.pfile with an editor

[oracle@db1 ~] $vi / tmp/member.pfile

Member.__db_cache_size=331350016

Member.__java_pool_size=4194304

Member.__large_pool_size=4194304

Member.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment

Member.__pga_aggregate_target=339738624

Member.__sga_target=503316480

Member.__shared_io_pool_size=0

Member.__shared_pool_size=150994944

Member.__streams_pool_size=0

* .audit_file_dest='/opt/oracle/admin/member/adump'

* .audit_trail='db'

* .compatible='11.2.0.0.0'

* .control_files='/opt/oracle/oradata/member/control01.ctl','/opt/oracle/flash_recovery_area/member/control02.ctl'

* .db_block_size=8192

* .db_domain=''

* .db_name='member'

* .db_recovery_file_dest='/opt/oracle/flash_recovery_area'

* .db_recovery_file_dest_size=4070572032

* .diagnostic_dest='/opt/oracle'

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=memberXDB)'

* .log_archive_format='%t_%s_%r.dbf'

* .memory_target=839909376

* .open_cursors=300

* .processes=150

* .remote_login_passwordfile='EXCLUSIVE'

* .undo_tablespace='UNDOTBS1'

# add a little bit

* .db_unique_name='db1'

* .archive_lag_target=1800

* .fal_client='db1'

* .fal_server='db2'

* .log_archive_config='DG_CONFIG= (db1,db2)'

* .log_archive_dest_1='location=/opt/oracle/flash_recovery_area/ VALID_FOR= (all_logfiles,all_roles) db_unique_name=db1'

* .log_archive_dest_2='service=db2 lgwr async valid_for= (online_logfiles,primary_role) db_unique_name=db2'

* .log_archive_dest_state_1='enable'

* .log_archive_dest_state_2='enable'

* .log_archive_format='%t_%s_%r.dbf'

* .standby_file_management='auto'

* .db_file_name_convert='/opt/oracle/flash_recovery_area',' / opt/oracle/flash_recovery_area'

* .log_file_name_convert='/opt/oracle/flash_recovery_area','/ opt/oracle/flash_recovery_area'

And copy a startup parameter file as the standby db2

[oracle@db1 ~] $cp / tmp/member.pfile / tmp/db2.pfile

[oracle@db1 ~] $vim / tmp/db2.pfile

Then modify the above addition to

* .db_unique_name='db2'

* .archive_lag_target=1800

* .fal_client='db2'

* .fal_server='db1'

* .log_archive_config='DG_CONFIG= (db1,db2)'

* .log_archive_dest_1='location=/opt/oracle/flash_recovery_area/ VALID_FOR= (all_logfiles,all_roles) db_unique_name=db2'

* .log_archive_dest_2='service=db1 lgwr async valid_for= (online_logfiles,primary_role) db_unique_name=db1'

* .log_archive_dest_state_1='enable'

* .log_archive_dest_state_2='enable'

* .log_archive_format='%t_%s_%r.dbf'

* .standby_file_management='auto'

* .db_file_name_convert='/opt/oracle/flash_recovery_area',' / opt/oracle/flash_recovery_area'

* .log_file_name_convert='/opt/oracle/flash_recovery_area','/ opt/oracle/flash_recovery_area'

Start with a modified / tmp/member.pfile on db1

(db1) SQL > shutdown immediate

(db1) SQL > startup pfile='/tmp/member.pfile' nomount

(db1) SQL > create spfile from pfile='/tmp/member.pfile'

(db1) SQL > shutdown immediate

(db1) SQL > startup

4. Main library password file:

[1] password file exists

[oracle@db1 dbs] $ls $ORACLE_HOME/dbs

Hc_DBUA0.dat hc_member.dat init.ora lkDB1 lkMEMBER orapwmember spfilemember.ora

You can see that there is a password file orapwmember above, which will be created by default when building the library.

=

[2] No password file exists

If not, you can create one manually.

[oracle@db1 dbs] $cd $ORACLE_HOME/dbs

[oracle@db1 dbs] $orapwd file=orapwmember password=123456 entries=3

# Note that the above needs to be established based on the SID name, file=orapwSID

=

5. Modify snooping

[oracle@db1 dbs] $cd $ORACLE_HOME/network/admin

[oracle@db1 admin] $mv listener.ora listener.ora.default

[oracle@db1 admin] $vim listener.ora

LISTENER =

(DESCRIPTION =

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

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = member)

(ORACLE_HOME = / opt/oracle/product/11.2.0/db_1)

(SID_NAME = member)

)

)

# Note the above HOST, which is HOSTNAME

[oracle@db1 admin] $vim tnsnames.ora

MEMBER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomain) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = member)

)

)

Db1 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = db1)

)

)

Db2 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = db2)

)

)

Restart monitoring

[oracle@db1 admin] $lsnrctl stop

[oracle@db1 admin] $lsnrctl start

6. Copy listening files, parameter files, and password files to the standby library

# pay attention to my side $ORACLE_HOME

[oracle@db1 ~] $echo $ORACLE_HOME

/ opt/oracle/product/11.2.0/db_1

# transfer startup parameter file

[oracle@db1 ~] $scp / tmp/db2.pfile db2:~

# transfer password file

[oracle@db1 ~] $scp / opt/oracle/product/11.2.0/db_1/dbs/orapwmember db2:/opt/oracle/product/11.2.0/db_1/dbs/orapwmember

# transfer listening files

[oracle@db1 ~] $scp-r / opt/oracle/product/11.2.0/db_1/network/admin/ {listener.ora,tnsnames.ora} db2:/opt/oracle/product/11.2.0/db_1/network/admin/

Modify / opt/oracle/product/11.2.0/db_1/network/admin/listener.ora on db2 to change db1 to db2

[oracle@db2 ~] $vim / opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

LISTENER =

(DESCRIPTION =

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

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = member)

(ORACLE_HOME = / opt/oracle/product/11.2.0/db_1)

(SID_NAME = member)

)

)

# you only need to modify listener.ora, but tnsnames.ora does not need to move

7. The main library creates standby control files, and we use scp to transfer all the files.

Check the path of the control file

(db1) SQL > select name from v$controlfile

NAME

/ opt/oracle/oradata/member/control01.ctl

/ opt/oracle/flash_recovery_area/member/control02.ctl

(db1) SQL > shutdown immediate

(db1) SQL > startup mount

(db1) SQL > alter database create standby controlfile as'/ opt/oracle/oradata/member/standby.ctl'

# create standby.ctl standby control file under / opt/oracle/oradata/member/ directory

8. Copy the main library data files and log files to the standby library

[oracle@db1] $scp-r / opt/oracle/flash_recovery_area/ / opt/oracle/admin/ / opt/oracle/diag/ / opt/oracle/oradata/ db2:/opt/oracle

9. Initialize standby library

Use the standby control file on the standby to overwrite the original control file. You can learn about the overwrite path by finding the path of the control file in the previous step.

[oracle@db2 ~] $cp / opt/oracle/oradata/member/standby.ctl / opt/oracle/oradata/member/control01.ctl

[oracle@db2 ~] $cp / opt/oracle/oradata/member/standby.ctl / opt/oracle/flash_recovery_area/member/control02.ctl

Db2 using the parameter file previously modified by db2.pfile

(db2) SQL > startup pfile='/home/oracle/db2.pfile' nomount

(db2) SQL > create spfile from pfile='/home/oracle/db2.pfile'

(db2) SQL > shutdown immediate

(db2) SQL > startup nomount

(db2) SQL > alter database mount standby database

(db2) SQL > alter database open read only

There are three ways to apply logs: (choose an or b)

a. Open the real-time application log, so that you can find it on the standby immediately by inserting it into the main database.

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

b. Enable the redolog application log, which can only be queried for a long time.

(db2) SQL > alter database recover managed standby database disconnect from session

c. Stop applying redolog, only accept logs, do not redo

(db2) SQL > alter database recover managed standby database cancel

At this point, the DataGuard is successfully built, create the table on the db1 and insert the data, and then query on the db2 to find it. At the beginning, there was no data, but in the end, it was found that there was a problem with the method of applying the log above.

10.Dataguard Test:

View Standby management processes

(db1) SQL > select process,status from v$managed_standby

PROCESS STATUS

--

ARCH CONNECTED

ARCH CLOSING

ARCH CLOSING

ARCH CLOSING

LNS WRITING

(db2) SQL > select process,status from v$managed_standby

PROCESS STATUS

--

ARCH CLOSING

ARCH CLOSING

ARCH CONNECTED

ARCH CONNECTED

MRP0 APPLYING_LOG

RFS IDLE

RFS IDLE

The above needs to see that there needs to be a LNS process on the host and a RFS process on the standby to receive the redo log. The MRP0 process is responsible for writing the log to the database.

Switch logs on db1, and then check on db2 to see if the logs are working.

(db1) SQL > select sequence#,applied from v$archived_log

(db1) SQL > select max (sequence#) from v$archived_log

MAX (SEQUENCE#)

-

eleven

(db2) SQL > select sequence#,applied from v$archived_log

(db2) SQL > select max (sequence#) from v$archived_log

MAX (SEQUENCE#)

-

eleven

(db1) SQL > alter system switch logfile

(db1) SQL > select max (sequence#) from v$archived_log

MAX (SEQUENCE#)

-

twelve

(db2) SQL > select max (sequence#) from v$archived_log

MAX (SEQUENCE#)

-

twelve

From the above, we can see that the log switch is successful and the DataGuard is running normally.

11. Active / standby handoff test:

Db1---primary/db2---standby = "db2--primary/db1--standby

[oracle@db1 ~] $lsnrctl stop

(db1) SQL > alter database commit to switchover to physical standby with session shutdown

(db1) SQL > shutdown immediate

(db1) SQL > startup mount

(db1) SQL > alter database open read only

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

/ / when executing this article, if it appears

ERROR at line 1:

ORA-01665: control file is not a standby control file

The alter database commit to switchover to physical standby with session shutdown is not executed.

[oracle@db1 ~] $lsnrctl start

(db2) SQL > alter database commit to switchover to primary

Note:

If ORA-16139: media recovery required appears, execute the following statement:

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

SQL > alter database commit to switchover to primary

If so, it may be that the session is already open, plus with session shutdown forces painting to be closed

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

SQL > alter database commit to switchover to primary WITH SESSION SHUTDOWN

(db2) SQL > shutdown immediate

(db2) SQL > startup

The above is the process of active / standby switching.

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.