To build a RAC-DG for the customer encountered some problems, set up rac-dg
Yesterday I went to a customer to build a physical standby database of the RAC-DG, here I will list the general process again, for does not involve leakage of privacy, the main parameters have been replaced by the name, the specific path is not consistent. Because the customer's environment does not allow the user to use the network to connect to the Intranet, it is impossible to cut off all kinds of alarm logs and trace files in case of a fault, and it can only be roughly written by memories.
1. The master database modifies the spfile parameters online.
Alter database force logging;
Alter system set log_archive_config = 'dg _ CONFIG = (DG, dgdg )';
Alter system set log_archive_dest_1 = 'location = USE_DB_RECOVERY_FILE_DEST VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = dg ';
Alter system set log_archive_dest_2 = 'service = dgdg lgwr async VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = dgdg ';
Alter system set log_archive_dest_state_1 = 'enable ';
Alter system set log_archive_dest_state_2 = 'enable ';
Alter system set standby_file_management = 'auto ';
Alter system set fal_server = 'dgdg ';
Restart parameters:
Alter system set db_file_name_convert = 'C: \ app \ administrator \ oradata \ dg ',' + data/dg/datafile' scope = spfile;
Alter system set log_file_name_convert = 'C: \ app \ administrator \ oradata \ dg ',' + data/dg/onlinelog 'scope = spfile;
2. Create pfile
SQL> create pfile from spfile;
3. Modify the pfile used by the standby Database
*. _ Db_cache_size = 0
*. _ Java_pool_size = 0
*. _ Large_pool_size = 0
*. _ Oracle_base = 'C: \ app \ administrator '# ORACLE_BASE set from environment
*. _ Pga_aggregate_target = 0
*. _ Sga_target = 0
*. _ Shared_io_pool_size = 0
*. _ Shared_pool_size = 0
*. _ Streams_pool_size = 0
*. Audit_file_dest = 'C: \ app \ administrator \ admin \ dg \ adump'
*. Audit_trail = 'db'
*. Cluster_database = false -- indicates a single-instance database. Otherwise, an error is reported during startup.
*. Compatible = '11. 2.0.0.0'
*. Control_files = 'C: \ app \ administrator \ oradata \ dg \ control01.ctl ', 'c: \ app \ administrator \ oradata \ dg \ control02.ctl'
*. Db_block_size = 8192
*. Db_create_file_dest = 'C: \ app \ administrator \ oradata'
*. Db_domain =''
*. Db_name = 'dg'
*. Db_recovery_file_dest = 'C: \ app \ administrator \ flash_recovery_area'
*. Db_recovery_file_dest_size = 3908042752
*. Diagnostic_dest = 'C: \ app \ administrator'
*. Dispadghers = '(PROTOCOL = TCP) (SERVICE = DGXDB )'
*. Fal_server = 'dg'
*. Instance_number = 1
*. Log_archive_config = 'dg _ CONFIG = (DG, dgdg )'
*. Log_archive_dest_1 = 'location = c: \ archivelog VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = dgdg'
*. Log_archive_dest_2 = 'service = dg lgwr async VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = dg'
*. Log_archive_dest_state_1 = 'enable'
*. Log_archive_dest_state_2 = 'enable'
*. Log_archive_format = 'arc % S _ % R. % t'
*. Memory_target = 8577351680
*. Nls_language = 'simplified CHINESE'
*. Nls_territory = 'China'
*. Open_cursors = 300
*. Processses = 150
*. Remote_login_passwordfile = 'clusive'
*. Standby_file_management = 'auto'
*. Undo_tablespace = 'undotbs1'
*. Db_file_name_convert = '+ data/dg/datafile', 'c: \ app \ administrator \ oradata \ dg'
*. Log_file_name_convert = '+ data/dg/onlinelog', 'c: \ app \ administrator \ oradata \ dg'
*. Db_unique_name = dgdg -- if this parameter is not set for the copied pfile, the default value is dg.
4. Modify tnsnamesl. ora
# Tnsnames. ora Network Configuration File: C: \ app \ administrator \ product \ 11.2.0 \ dbhome_1 \ network \ admin \ tnsnames. ora
# Generated by Oracle configuration tools.
Dg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = dg-cluster-scan) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg)
)
)
DGDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = dgP) (HOST = dgdg) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = dg)
)
)
5. Modify listener. ora (only for the standby database, the master database can not configure static listening)
# Listener. ora Network Configuration File: C: \ app \ administrator \ product \ 11.2.0 \ dbhome_1 \ NETWORK \ ADMIN \ listener. ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dg)
(ORACLE_HOME = C: \ app \ administrator \ product \ 11.2.0 \ dbhome_1)
(SID_NAME = dg)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = dgdg) (PORT = 1521 ))
)
ADR_BASE_LISTENER = C: \ app \ administrator \ product \ 11.2.0 \ dbhome_1 \ log
6. Create a directory for the slave Database
C: \ archivelog -- specifies a local archive path. The Archive logs received by the slave database and the archive logs generated by the slave database are stored here.
C: \ app \ administrator \ admin \ dg \ adump
C: \ app \ administrator \ admin \ dg \ dpdump
C: \ app \ administrator \ admin \ dg \ hdump
C: \ app \ administrator \ admin \ dg \ pfile
C: \ app \ administrator \ flash_recovery_area
C: \ app \ administrator \ oradata \ dg
7. The master database performs rman full backup.
RMAN> backup as compressed backupset full database format 'C: \ bak \ full _ % d _ % I _ % T _ % U'
8. Create a slave Database Control file for the master database
SQL> alter database create standby controlfile as 'C: \ control01.ctl ';
SQL> alter database create standby controlfile as 'C: \ control02.ctl ';
9. Copy the backup file, password file, pfile file, tnsnames. ora, and listener. ora to the corresponding location of the slave database.
10. Create an instance from the slave Database
Oradim-new-sid dg-startmode manual-spfile;
11. Start the listener
Lsntrctl start
12. Start the instance to mount
Set oracle_sid = dg
Sqlplus/as sysdba
SQL> startup mount
13. Restore the database
RMAN> catalog start with 'd: \ bak '; -- if this parameter is not specified, the system prompts that the database cannot be restored.
RMAN> restore database;
14. Add standby redo logfile to the standby Database
SQL> alter database add standby logfile 'C: \ app \ administrator \ oradata \ dg \ std_05.log 'size 50 m;
SQL> alter database add standby logfile 'C: \ app \ administrator \ oradata \ dg \ std_06.log 'size 50 m;
SQL> alter database add standby logfile 'C: \ app \ administrator \ oradata \ dg \ std_07.log 'size 50 m;
SQL> alter database add standby logfile 'C: \ app \ administrator \ oradata \ dg \ std_08.log 'size 50 m;
SQL> alter database add standby logfile 'C: \ app \ administrator \ oradata \ dg \ std_09.log 'size 50 m;
15. Enable redo apply
SQL> alter database recover managed standby database disconnect from session;
16. Create a spfile for the standby database (optional)
SQL> create spfile from pfile;
The following are several problems encountered during the configuration process:
1. The patch cannot be created using the opatch apply command.
Cause:11.2.0.3 the default version of opatch after installation is 11.2.0.1.7. patch 27 needs to be installed on this version.
Solution:Decompress the later version of The opatch installation package and overwrite the original opatch directory.
2. the standby Database alert. log prompts that the automatic backup path of the control file cannot be found.
Cause:The automatic RMAN backup script has been deployed on the master database of RAC, and the automatic backup path of the control file is specified, but the backup database does not have this path.
Solution:Go to RMAN and change this parameter to the directory in which the standby database is located.
3. the GAP caused by incorrect parameter settings causes the automatic backup script to stop running.
Cause:When setting parameters, set the log_archive_dest_1 parameter of the master database to a local path for archiving, for example:
Alter system set log_archive_dest_1 = 'location = C: \ archivelog VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = dg ';
As RAC, if the archive path is local, other nodes will not be able to read it. After the archive path is reset to USE_DB_RECOVERY_FILE_DEST, the local archive logs will become gaps and cannot be passed to the standby database.
Solution:Manually copy all the missing xxx files to the specified location, and then manually execute the RMAN automatic backup script.
Note:Because the RMAN automatic backup script is configured with 7 copies of redundancy, and the full-database backup is manually executed when the DG is built, these manual backups are also included in 7 copies of redundancy, in order not to occupy the quota for normal backup, it is recommended that you physically Delete the DG after it is set up, and then crossecheck and clear it.
4. TNS Errors often occur in the alert. log of the master and slave databases.
Fatal NI connect error 12547
TNS-12547 TNS: Lost connection
Ns secondary err code: 12560
Ns main err code: 517
TNS-00517 TNS: Lost connection
Nt secondary err code: 54
Nt OS err code: 0
Cause:Node 2 is not configured with tnsnames. ora, which makes the archived logs of thread 2 unable to be transferred to the slave database. At the same time, the logs of the master database can be transferred but cannot be applied.
Solution:Copy tnsnames. ora of Node 1 to node 2.
Note:In fact, this is also the biggest cause of the GAP in the standby database application. Because Node 2 logs cannot be transferred to the standby database, even though the attribute values in the corresponding applied columns of the previous several archive log sequences are YES, however, the logs of Node 1 will not be applied. Even if Node 1 is archived many times, the applied column will always show NO, but the logs can be transmitted normally.
