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

Editor to share with you how Oracle to create a single instance dg for rac and master-slave switching function, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

I. Overview

This article will show you how to build a single-node dg for rac and how to transform its roles. Prior knowledge (rac build, single instance-single instance dg build)

2. Introduction of the experimental environment

Main library rac (rac installed and database orcl already)

Rac1:192.168.56.11,sid1:orcl1,version:11.2.0.4rac2:192.168.56.12,sid2:orcl2,version:11.2.0.4

Slave library (single instance database software installed, no database instance)

Oradg:192.168.56.102,sid:orcldg,version:11.2.0.4

Third, set up dg

* * all the following main library operations are done on node 1. If you need to do it on node 2, I will indicate

1. The main library turns on archive mode and forces log writing

First check the archiving status of the database and whether to enable forced logging. You can see from the following figure that the database does not open archiving, nor does it enable forced logging.

SQL > select log_mode, force_logging from v$database

Database opens archive in mount state

SQL > alter system set log_archive_dest_1='location=+data' sid='*' scope=spfile;SQL > shutdown immediate; # both nodes are off SQL > startup mount; # only enable nodes 1SQL > alter database archivelog;SQL > alter database open

By forcing log writing, the database can be modified in the open state

SQL > alter database force logging;SQL > startup; # when the node 1open is finished, open the database on node 2

Let's check the archiving status of the database and whether to enable forced logging.

SQL > select log_mode, force_logging from v$database

two。 The main library turns on the dataguard switch

SQL > alter system set log_archive_config='dg_config= (orcl,orcldg) 'sid='*'; # orcl is the master library db_unique_name,orcldg is the db_unique_name of the slave library

3. The main library sets up remote archiving

SQL > alter system set log_archive_dest_3='service=orcldg valid_for= (online_logfiles,primary_role) db_unique_name=orcldg' sid='*'

4. Transfer the password file of the master library to the slave library

[oracle@rac1 ~] $scp $ORACLE_HOME/dbs/orapw$ORACLE_SID 192.168.56.102:$ORACLE_HOME/dbs/orapworcldg

5. Prepare parameter files from the library

The parameter file here is no different from building a single instance dg for a single instance, so we will not introduce it in detail.

[root@oradg ~] # vi $ORACLE_HOME/dbs/initorcldg.ora*.audit_file_dest='/u01/app/oracle/admin/orcldg/adump'*.audit_trail='db'*.compatible='11.2.0.4.0'*.control_files='/u01/app/oracle/oradata/orcldg/controlfile/control01.ctl'*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.diagnostic_dest='/u01/app/oracle'*.log_archive_config='dg_config= (orcl Orcldg)'* .log _ archive_dest_2='location=/u01/app/oracle/oradata/orcldg/archstdlog/ valid_for= (standby_logfiles,standby_role) db_unique_name=orcldg'*.memory_target=838860800*.open_cursors=300*.processes=150*.remote_login_passwordfile='exclusive'*.undo_tablespace='UNDOTBS1'standby_file_management=auto # this parameter defaults to manual It needs to be changed to auto, which means that the data file of the main library has been modified (such as new, renamed, etc.) Modify the db_unique_name='orcldg'db_file_name_convert='+DATA/orcl/datafile/','/u01/app/oracle/oradata/orcldg/datafile/','+DATA/orcl/tempfile/','/u01/app/oracle/oradata/orcldg/tempfile/'log_file_name_convert='+DATA/orcl/onlinelog/','/u01/app/oracle/oradata/orcldg/onlinelog/' from the library accordingly.

6. Prepare related directories from the library

[oracle@oradg ~] $mkdir-p / u01/app/oracle/admin/orcldg/adump [oracle@oradg ~] $mkdir-p / u01/app/oracle/oradata/orcldg/controlfile [oracle@oradg ~] $mkdir-p / u01/app/oracle/oradata/orcldg/archstdlog [oracle@oradg ~] $mkdir-p / u01/app/oracle/oradata/orcldg/datafile [oracle@oradg ~] $mkdir-p / u01/app/oracle/oradata/orcldg/tempfile [oracle@oradg ~] $mkdir-p / u01/app/oracle/oradata/orcldg/onlinelog

7. Start the slave instance

[oracle@oradg ~] $export ORACLE_SID= orcldg [oracle @ oradg ~] $sqlplus / as sysdbaSQL > create spfile from pfile;SQL > startup nomount

8. Configure and start the listener from the library

Because the master database data is copied in duplicate mode, the slave library needs to be configured in the form of static registration.

[oracle@oradg ~] $vi $ORACLE_HOME/network/admin/listener.oraSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcldg) (ORACLE_HOME = / u01/app/oracle/product/11.2.0/db_1) (SID_NAME = orcldg)) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.102) (PORT = 1521))) [oracle@oradg ~] $lsnrctl start

9. Main library configuration service naming

# set [oracle@rac1 ~] $vi $ORACLE_HOME/network/admin/tnsnames.oraorcldg = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.102) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldg) [oracle@rac2 ~] $vi $ORACLE_HOME/network/admin/tnsnames.ora) to both nodes

The content is the same as that of rac1

10. The master library uses a network connection to the slave library (test connectivity)

# here the sys password of my master library is 123456, and the slave library is the same as the master library [oracle@rac1 ~] $sqlplus sys/123456@orcldg as sysdba

11. Start the rman replication slave library in the master library

Rman connects master library and slave library

[oracle@rac1 ~] $rman target / auxiliary sys/123456@orcldgRMAN > duplicate target database for standby from active database

twelve。 Add standbylog from the library

The size of the added log is the same as that of the onlinelog of the main database, with two more groups (see v$log for onlinelog information of the main database)

The number of group is not the same as the current onlinelog.

[oracle@oradg ~] $mkdir-p / u01/app/oracle/oradata/orcldg/standbylog/ SQL > alter database add standby logfile group 21'/ u01There is std01.log' size 50M > alter database add standby logfile group 22'/ u01OnOnLigLog size alter database add standby logfile group 02.log' oradataSQL > alter database add standby logfile group 23'/ u01aporadataorcldgstandbyoradataorclds03.log' SQL > alter database add standby logfile group 24'/ u01 apprenticeship size 50m size > alter database add standby logfile group 26'/ u01

13. Open the application log from the library

SQL > alter database recover managed standby database disconnect from session

14. Master Library switch Archive

SQL > alter system switch logfile

15. Open the slave library

When the archive is applied from the library for a period of time, the application archive log is closed and the slave library is opened.

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

16. Verify synchronization

Make changes to the main library

SQL > update scott.emp set sal=2000;SQL > commit

Query from the library

SQL > select * from scott.emp

At this point, building a single instance dg for rac is no different from building a single instance dg for a single instance. Next, I'll show you how to switch.

IV. Master-slave switching preparation

17. Add standby logfile archive path to the main library

Use the grid account to create an archive path

[grid@rac1 ~] $asmcmdASMCMD > lsdg # View the disk group name ASMCMD > cd dataASMCMD > cd orclASMCMD > mkdir ARCHSTDLOG

Use the sys database account to modify database parameters

SQL > alter system set log_archive_dest_2='location=+DATA/ORCL/ARCHSTDLOG/ valid_for= (standby_logfiles,standby_role) db_unique_name=orcl' sid='*'

18. Add standby logfile to the main library

The size of the added log is the same as that of the onlinelog of the main database, with two more groups (see v$log for the onlinelog information of the main database)

SQL > alter database add standby logfile thread 1 group 21 ('+ data') size 50m political SQL > alter database add standby logfile thread 1 group 22 ('+ data') size 50m political SQL > alter database add standby logfile thread 1 group 23 ('+ data') size 50m political SQL > alter database add standby logfile thread 2 group 24 ('+ data') size 50m political SQL > alter database add standby logfile thread 2 group 25 ('+ data') size 50m leading SQL > alter database add standby logfile thread 2 group 26 ('+ data') size 50m

19. Main library modifies parameter file

SQL > alter system set standby_file_management=auto sid='*';SQL > alter system set db_file_name_convert='/u01/app/oracle/oradata/orcldg/datafile/','+DATA/orcl/datafile/','/u01/app/oracle/oradata/orcldg/tempfile/','+DATA/orcl/tempfile/' sid='*' scope=spfile;SQL > alter system set log_file_name_convert='/u01/app/oracle/oradata/orcldg/onlinelog/','+DATA/orcl/onlinelog/' sid='*' scope=spfile

At this point, the preparation for the conversion of the main library into a standby library has been completed!

20. Configure tnsnames.ora from the library

The purpose of configuring tnsnames.ora here is that when the original slave library is transformed into a new master library, the original master database becomes the new slave library, and the new master library needs to send archive logs to the new slave database, so the tnsnames here points to the new slave database, and because the dg library application archive can only be on one node, the toorcl only points to node 1.

[oracle@oradg ~] $vi $ORACLE_HOME/network/admin/tnsnames.oratoorcl = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.11) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

21. Set the archive path for onlinelog from the library

[oracle@oradg] $mkdir-p / u01/app/oracle/oradata/orcldg/archivelogSQL > alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/orcldg/archivelog/ valid_for= (online_logfiles,primary_role) db_unique_name=orcldg'

twenty-two。 Set remote archive parameters from the library

SQL > alter system set log_archive_dest_3='service=toorcl valid_for= (online_logfiles,primary_role) db_unique_name=orcl'

At this point, the preparation for the transition from the library to the main library has been completed!

IV. Master-slave switching

23. View the role transition status of the main library

SQL > select database_role,switchover_status from v$database

24. Master-slave on node 1

When the cluster makes a role transition, only one instance can be open and the rest will be closed, so close the instance of node 2.

SQL > shutdown immediate; # only on Node 2

Execute the following command on node 1, turn the master library into a slave library, and close the instance

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

25. Transfer master from slave to master on node 3

SQL > select database_role,switchover_status from v$database

If "NOT ALLOWED" means that the archive has not been applied yet, you can wait for a period of time

Check the role transition status if all the logs are applied.

SQL > select database_role,switchover_status from v$database

If the role transition state is TO PRIMARY, then the role transition can be performed.

Execute the command from slave to master. After the command is executed successfully, the state of the database will change to mount.

SQL > alter database commit to switchover to primary;SQL > alter database open

twenty-six。 Open the new slave library

On Node 1 and Node 2

SQL > startup

Apply archiving on Node 1

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

Switch archiving on node 3

SQL > alter system switch logfile

twenty-seven。 Verify synchronization

Make changes to the main library

SQL > update scott.emp set sal=3000;SQL > commit

Query from the library

SQL > select * from scott.emp

twenty-eight。 Error handling

If you find that the log cannot be transferred to the standby database, you can check the cause of the error in the main database by using the following command

SQL > select dest_id,dest_name,log_sequence,status,error from v$archive_dest

If you report the above error, you can restart the filing switch.

SQL > alter system set log_archive_dest_state_3='defer' sid='*';SQL > alter system set log_archive_dest_state_3='enable' sid='*'; are all the contents of the article "how to create a single instance dg and master-slave switching function for rac by Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.