Oracle Data Guard configuration note

After many practices, I have read N more articles on the internet ...... The final configuration is not complete. I may be too savvy to take notes from experts. It was finally completed with the help of the predecessors. We use the most practical method to record it, so that we can

Understand.

Conditions for running Data Guard
1. The same version of Oracle Enterprise Edition must be installed on the master database and all slave databases.
2. The master database must run in archive mode.
3. the operating system of the master database and slave database must be the same (different versions are allowed). The slave database can use a different directory structure than the master database.
4. The architecture of the Master/Slave Database hardware system must be the same. For example, if the master database is running on a 64-bit Sun iSCSI system, it is not allowed if the slave database is a 32-bit Linux Intel System. The hardware configurations of the Master/Slave database can be different, such as the number of CPUs, memory size, and storage configuration.
5. Master/Slave databases can be single-instance databases or multi-instance RAC databases.
6. Each Master/Slave database must have its own control file.
7. If you place the Master/Slave database in a system, you must adjust the initialization parameters.
8. As long as Data Guard is used, the master database must be in force logging mode. If no logging is used on the master database, logs are not generated, and the Data is not transmitted to the slave database. 9. The user managing the Master/Slave database must have the SYSDBA permission.

Master Database: win2003 server ora9i (9.2.0.1.0) 192.168.3.135 Host Name: data580 SID: PRIMARY
Slave Database: win2003 server ora9i (9.2.0.1.0) 192.168.3.136 Host Name: data380 SID: STANDBY
1. Select the same installation path E: \ Oracle \ ora92 when installing the master and slave oracle databases. Do not install the database first, select "Install software only" under "Database Configuration" during installation ";
2. Use DBCA (Database Configuration Assistant) on the master Database to create a Database with SID set as PRIMARY;
3. Set the Archive path of the master database to e: \ oracle \ ora92 \ database \ Archive:
SQL> alter system set log_archive_dest_1 = 'location = e: \ oracle \ ora92 \ database \ Archive MANDATORY 'scope = both;
4. Shut down the master database instance and database, and then start the instance, but do not open the database, only start to the database Mount mode:
SQL> shutdown immediate; SQL> startup mount;
5. Set the master database to archive status and automatic archiving mode:
SQL> alter database archivelog;
SQL> alter system set log_archive_start = true scope = spfile;
6. Create the slave Database Control File standby. ctl on the master database:
SQL> alter database create standby controlfile as 'e: \ oracle \ oradata \ standby. ctl ';
7. Create the slave database's initialization parameter file standby. ora on the master database (copy it back to the slave database and convert it to the slave database's spfile ):
SQL> create pfile = 'e: \ oracle \ oradata \ standby. ora 'from spfile;
8. view the data file of the master database and its location: SQL> select name from v $ datafile; write down the location of the data file in the query result.
9. Close the instance:
SQL> shutdown immediate;
10. Set standby in step 1. ctl, standby in step 1. the data files in ora and step 8th are also the online log files (REDO. LOG), E: \ oracle \ ora92 \ database Password File PWDPRIMARY. ora is backed up and copied to the slave database. In the slave database, create path E: \ oracle \ oradata \ PRIMARY to store standby. ctl, data file, and online log file; Create path

E: \ oracle \ admin \ PRIMARY. create the empty folders bdump, cdump, udump, create, and pfile. ora is placed under the root directory of the E drive. ora is renamed PWDSTANDBY. ora is placed under E: \ oracle \ ora92 \ database of the slave database.
11. Return to the master database and start the master database instance and database:
SQL> startup
12. Go back to the slave database and manually create the service OracleServiceSTANDBY:
Oradim-new-sid STANDBY-startmode manual
Set environment variables: Create a value in the "Administrator user variables" column of the environment variable. The variable name is ORACLE_SID and the variable value is STANDBY (Service name ).
13. Create the slave Database Listener OracleOraHome92TNSListener: Enter the Net Configuration Assistant and use "Listener Configuration" to create the listener for the slave database. At the same time, in E: \ oracle \ ora92 \ network \ admin to generate listener. ora file;
14. Configure the local NET service name: Enter the Net Configuration Assistant and use "local NET service name Configuration: \ oracle \ ora92 \ network \ admin generates tnsnames. ora file;
15. Configure tnsnames. ora of the Master/Slave database to set the content in the file as follows:
STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.3.136) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = PRIMARY )))

PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.3.135) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = PRIMARY )))
16. Modify the initialization parameter file standby under the E root directory. ora, which contains the following content (the original control_files parameter is deleted, instance_name is changed to STANDBY, and the bold part of the last seven rows is the newly added content ):
*. Aq_tm_processes = 1
*. Background_dump_dest = 'e: \ oracle \ admin \ PRIMARY \ bdump'
*. Compatible = '9. 2.0.0.0'
*. Core_dump_dest = 'e: \ oracle \ admin \ PRIMARY \ cdump'
*. Db_block_size = 8192
*. Db_cache_size = 728760320
*. Db_domain =''
*. Db_file_multiblock_read_count = 16
*. Db_name = 'primary'
*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = PRIMARYXDB)', '(PROTOCOL = TCP )'
*. Fast_start_mttr_target = 300
*. Hash_join_enabled = TRUE
*. Instance_name = 'standby'
*. Java_pool_size = 20971520
*. Job_queue_processes = 10
*. Large_pool_size = 143654912
*. Log_archive_dest_1 = 'location = e: \ oracle \ ora92 \ database \ Archive MANDATORY'
*. Log_archive_start = TRUE
*. Open_cursors = 300
*. Pga_aggregate_target = 324009984
*. Processses = 150
*. Query_rewrite_enabled = 'false'
*. Remote_login_passwordfile = 'clusive'
*. Shared_pool_size = 242221056
*. Sort_area_size = 52428800
*. Star_transformation_enabled = 'false'
*. Timed_statistics = TRUE
*. Undo_management = 'auto'
*. Undo_retention = 10800
*. Undo_tablespace = 'undotbs1'
*. User_dump_dest = 'e: \ oracle \ admin \ PRIMARY \ udump'
*. Standby_file_management = AUTO
*. Remote_archive_enable = TRUE
*. Lock_name_space = 'standby'
*. Fal_server = 'primary'
*. Fal_client = 'standby'
*. Standby_archive_dest = 'e: \ oracle \ ora92 \ database \ Archive'
*. Control_files = 'e: \ oracle \ oradata \ primary \ STANDBY. CTL'
17. Go to sqlplus and create standby. ora as the spfile of the slave database:
SQL> create spfile from pfile = 'e: \ standby. ora ';
18. Start the slave database instance and set the slave database to standby mode:
SQL> startup nomount; SQL> alter database mount standby database;
19. Start the log application service in the slave database:
SQL> alter database recover managed standby database disconnect from session;
20. Go to the master database and Start Archiving to the physical slave database:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'service = primary' SCOPE = BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE SCOPE = BOTH;
21. Verify the operation of Data Guard:
Master database, archiving the current log:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Check whether the archived logs are normally received from the slave database:
SQL> SELECT SEQUENCE #, FIRST_TIME, NEXT_TIME FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #;
From the database, check whether the new archive log is normal for restoration:
SQL> SELECT SEQUENCE #, APPLIED FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #;
SEQUENCE # APP ------------
8 YES
9 YES
10 YES
11 YES
If all the content above is YES, it will be successful. You can also view the contents in the E: \ oracle \ ora92 \ database \ archive folder of the Master/Slave database. If the contents of the slave database are consistent with those of the master database, the data guard configuration is successful.
22. At this time, the slave database only saves the log file but does not execute the log file. Therefore, operations performed on the master database cannot be obtained from the slave database, that is, data cannot be queried from the slave database. To query data in the slave database, run the following statement: Use the sysdba user to connect to the slave database.
SQL> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
Query data. After the query is completed, the slave database must be restored to the recovery mode.
SQL> alter database recover managed standby database disconnect from session;
This function can be used to query data from the previous day (or a few times ago), such as reports, because only reports earlier than yesterday can be queried today, the requirements for data synchronization are not so strict. After the query, you must set the recovery mode for the slave database.