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

Note: if the following sql is executed incorrectly, please pay attention to the semicolon (;), forward the document, please indicate the author and source. 1. DG environment 1, system: both primary and standby are windows64 operating systems (if any readers are using the linux operating system, the following operations are also available, but some folders need to be created later) 2. Oracle version: 11.2.0.1.0 db_unique_name:primary 64bit 3, primary parameters: (1) db_name:primary (2) db_unique_name:primary (3) sid:primary (4) since there is no db_domain, both instance_name,service_name and global_dbname are primary; 4, and standby parameters: (1) db_name:primary (2) db_unique_name:standby (3) sid:standby (4) because there is no db_domain, both instance_name,service_name and global_dbname are primary 5. Master library IP:192.169.105.67 slave library IP:192.169.99.105 6. Points to pay attention to: (1) the db_name of primary and standby databases must be the same. Many people think that since I want to do dg, then my two libraries will set up their own, and at that time, as long as the data is transmitted in real time, in fact, the establishment of dg master-slave library db_name must be the same. (2) the db_unique_name of primary and standby databases must be different, because the difference between primary and standby is based on the parameter db_unique_name. This parameter can only be configured by modifying the parameter file after building the database, and it is not possible to enter it directly when building the database. (3) to ensure that the sys passwords of primary and standby libraries are the same, it is best to use a password when setting the password when building the database. (4) it is recommended that the installation path, data file location and folder location of the master-slave database should be the same, and it will be set up later, of course, it can be different. You can set the replacement path in the later parameter settings. 2. Formally set up DG 1 and configure the listener configuration of master / slave libraries listener (listening) and tnsnames (network service name) (1). The following is the configuration text:

Note: please pay attention to my red configuration. For DG, we must configure static monitoring. Here, considering that some readers may not quite understand the difference between static monitoring and dynamic monitoring, it does not matter, as long as you know that SID_LIST_LISTENER is static monitoring, LISTENER is dynamic monitoring, according to my configuration. Of course, I can say by the way why I need to configure static monitoring, because after all the later things are configured, I need to copy some files of the master library to the slave library. I need to use rman. The slave library is in the nomount state. In the nomount state, the direct rman target will report an error. This error means that the service cannot be recognized, but only after my library is in mount and the pmon dynamic registration service will not report this error. But my slave library does not allow mount, which is contradictory, so I need a static registration service, and then rman can be done from the library in nomount. (2) from the listener configuration of the library, the following is the configuration text:

(3) tnsnames configuration of main library

(4) from the library tnsnames configuration, directly copy the tnsnames of the master library to the past; (5) tnsping to see whether the master and slave libraries can communicate, in both the master library and the slave library tnsping. Here I tnsping the slave library in the master library: as shown in the figure: when you see OK, you can prove that you can communicate. 2. Configuration unique to the main database (1) the main library must be in the archived state 1. Check whether the primary database is archived SQL > archivelog list;. If the primary database is not in archived mode, you can put the database in archived mode with the following command: SQL > shutdown immediate; SQL > startup mount; SQL > alter database archivelog; SQL > alter database open.

(2) View and set mandatory archiving SQL > alter database force logging; to check whether mandatory archiving has been done: SQL > select force_logging from vested database. If YES is displayed, it proves that the mandatory archiving is successful. (3) add the main library standby online logs. Here I add three groups of SQL > alter database add standby logfile group 4'D:\ app\ Administrator\ oradata\ primary\ redo04.log' size 50m; SQL > alter database add standby logfile group 5'D:\ app\ Administrator\ oradata\ primary\ redo05.log' size 50m; SQL > alter database add standby logfile group 6'D:\ app\ Administrator\ oradata\ primary\ redo06.log' size 50m (4) it should be noted that all databases in the same DG configuration must have separate key files and must be guaranteed in the same DG configuration. Sys users of all database servers have the same password to ensure the smooth transmission of redo data. Because the redo transport service transmits redo data through an authenticated network session, the session uses the sys user password contained in the key file to authenticate.

3. Parameter configuration of master-slave library (1) configuration of master library parameters, mainly configuring the following points 1, SQL > create pfile='d:/pfile.ora' from spfile; 2. After generating the text, add or modify the following parameters * .db_unique_name='primary'-- * .db _ unique_name=' master library unique name'* .log_archive_config='dg_config= (primary,standby)'--* .log _ archive_config='dg_config= (master library unique name, slave library unique name) * .log_archive_dest_1='location=D:\ app\ Administrator\ flash_recovery_area\ primary\ ARCHIVELOG VALID_FOR= (ONLINE_LOGFILES ALL_ROLES) DB_UNIQUE_NAME=primary'-- * .log _ archive_dest_1='location= local archive path VALID_FOR= (ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= main library unique name' That is to say, whether it is the master library or the slave library, put the online in the local archive path * .log_archive_dest_2='service=standby_dg valid_for= (online_logfiles,primary_role) db_unique_name=standby'-- * .log _ archive_dest_2='service= the tnsnames valid_for= (online_logfiles,primary_role) db_unique_name= name of the slave library, when the role is the master library. Online_redolog * .log_archive_dest_state_1=enable will be sent from the tnsnames of the library-- whether the redo transport service is allowed to transfer redo data to the specified path. Here I set the enable, which has a total of four attribute values with different functions. Readers can check the official document * .log_archive_dest_state_2=enable * .standby_file_management='auto'-if the primary database data file is modified (such as new, renamed, etc.), it will be modified in the standby database according to the settings of this parameter. Set it to auto for automatic management, and set it to manual for manual management. 3. After the parameters are set, replace the dynamic parameter file with this static parameter file

(1) SQL > shutdown immediate; (2) after shutting down the database, delete the dynamic parameter file manually. The location of the dynamic parameter file of my main library is: d:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ database\ SPFILEPRIMARY.ORA. After deletion, rebuild the dynamic parameter file. (3) SQL > create spfile='D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ database\ SPFILEPRIMARY.ORA' from pfile='d:\ pfile.ora'; (4) SQL > startup; (2) slave library parameter configuration, please pay attention to configure the following points 1, SQL > create pfile='d:/pfile.ora' from spfile; 2, after generating the text, add or modify the following parameters * .db_unique_name='standby' * .log_archive_config='dg_config= (primary,standby)'* .log_archive_dest_1='location=D:\ app\ Administrator\ flash_recovery_area\ primary\ ARCHIVELOG valid_for= (all_logfiles,all_roles) db_unique_name=standby'-- has been explained when explaining the master library parameters above, but it is important to note that archiving is not enabled from the slave library. So there is no ARCHIVELOG folder, so this folder must be built manually under this path, otherwise an error will be reported. * .log_archive_dest_state_1='enable' * .log_file_name_convert='D:\ app\ Administrator\ oradata\ primary','D:\ app\ Administrator\ oradata\ primary'--*.log_file_name_convert=' the path of the log file in the main library', 'the path of the log file in the slave library' * .db_file_name_convert='D:\ ORADATA','D:\ ORADATA'--*.db_file_name_convert=' the path of the data file in the main library' 'The path to the data file from the library'* .standby_file_management='auto' 3, after the parameters are set Replace the dynamic parameter file with this static parameter file (1) SQL > shutdown immediate; (2) after shutting down the database, delete the dynamic parameter file manually. The location of the dynamic parameter file from the library is D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ database\ SPFILESTANDBY.ORA. After deletion, the dynamic parameter file is rebuilt. (3) SQL > create spfile='D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ database\ SPFILESTANDBY.ORA' from pfile='d:\ pfile.ora'; (4) SQL > alter database nomount; (5) next open another command window to rman under the operating system interface. Third, rman duplicate replication database, mainly to copy the data related to the master database to the slave database, this step is to operate on the slave database (1) rman target sys/sys@primary_dg auxiliary sys/sys@standby_dg nocatalog (2) rman > duplicate target database for standby from active database nofilenamecheck;-if the location of the database of the slave database is the same as the location of the master database, then you have to add the nofilenamecheck file name without checking (3) then quietly wait for the replication to succeed. Note: first, the first step will often make errors, reporting errors that cannot identify the service, and the solution is to configure static monitoring; second, the second step will often make mistakes, reporting the database name error in the slave database control file, and the solution is to change the DB_NAME of the master-slave database to the same. Fourth, debugging the database is worth mentioning that the slave library is already in the mount state at this time, and the archive of the slave library has been opened automatically. The following operations are performed on the slave library. (1) SQL > alter database open; (2) SQL > select process,client_process,sequence#,status from v$managed_standby;-- View the background process from the library (3) at this time, the background process is displayed as follows:

(4) SQL > alter database recover managed standby database disconnect from session;-- start MRP service (5) check the following background process again to see SQL > select process,client_process,sequence#,status from v$managed_standby; (6) at this time, the background process is displayed as follows: the MRP0 process appears, which proves that the MRP service is enabled successfully.

5. Modify the data in the master database to see if it can be transferred to the slave database (1) log in to the master database as a user, and create a new table dg_test SQL > create table dg_test (id varchar2 (20), name varchar2 (20)); (2) insert test data SQL > insert into dg_test values ('1Zhi Jing' Zhang San'); SQL > insert into dg_test values ('2Zhi Jing' Li Si') SQL > commit; (3) SQL > alter system switch logfile;-the write disk of the master library will trigger the MRP process (4) to log in as this user on the slave library. Check the table below, as shown in the figure, and the data has been synchronized successfully.

(5) since then, the physical standby has been successfully built.

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.