ORA-19527 and ORA-00312 error resolution examples for 10g DG, ora-19527ora-00312
In the past few days, we have set up a 10g DG Windows 2008 R2 testing environment, mainly to re-build a production database DG for a customer tomorrow. Some problems have been found, so I would like to record it.
Because it is to be deployed to the production environment, we need to consider the online build of DG solution, that is, when the database is not stopped, the main problem is that when RAMN is used to create the STANDBY
Generally, the main steps for building DG online are as follows:
1. Make sure that archiving is enabled for the master database and the force logging mode is enabled.
2. The master database modifies spifle online, alter system set... scope = both; and creates a pfile.
First, make sure that the parameters to be modified can be modified online. You can view the view v $ parameter.
SQL> select distinct issys_modifiable from v $ parameter;
ISSYS_MODIFIABLE
---------------------------
DEFERRED
FALSE
IMMEDIATE
Note:
DFERRED: dynamic parameter. The modified parameter is invalid for the Active session.
FALSE: static parameter. Restart the database after modification.
IMMEDIATE: dynamic parameter. It takes effect for all sessions immediately after modification.
SQL> select name, issys_modifiable, value from v $ parameter where name = 'xxxx'; xxxx is the name of the parameter to be modified
Of the several parameters to be modified, only db_unique_name does not support online modification.
SQL> alter system set LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (ora10gpd, ora10gst) 'scope = both;
SQL> alter system set LOG_ARCHIVE_DEST_1 = 'location = USE_DB_RECOVERY_FILE_DEST VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = ora10gpd 'scope = both;
SQL> alter system set LOG_ARCHIVE_DEST_2 = 'service = ora10gst LGWR ASYNC VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = ora10gst 'scope = both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1 = ENABLE scope = both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2 = ENABLE scope = both;
SQL> alter system set FAL_SERVER = ora10gst scope = both;
SQL> alter system set FAL_CLIENT = ora10gpd scope = both;
SQL> alter system set STANDBY_FILE_MANAGEMENT = 'auto' scope = both;
Therefore, if the production database wants to build the DG without stopping the database, it is necessary to use the original db_unique_name, which is usually the same name as db_name.
After modifying the spfile, use it to create a pfile for the standby database. create pfile from spfile;
3. Create standby redo logfile in the master database, and alter database add standby logfile group # datafile ('xxxx') size 50 M. xxxx specifies the path and file name.
4. Create listener. ora and tnsnames. ora, copy the generated file to the corresponding location of the slave database, and modify
5. The master database creates backup control files, data files, and log file backup sets.
Rman target/
RMAN> backup full database format 'C: \ backup \ full _ % d _ % I _ % U' include current controlfile for standby plus archivelog format 'C: \ backup \ arc _ % d _ % I _ % U ';
Or:
Run
{
Allocate channel d1 type disk;
Backup format 'C: \ backup \ df _ % d _ % I _ % U' database;
SQL 'alter system archive log current ';
Backup format 'C: \ backup \ al _ % d _ % I _ % U' archivelog all;
Backup current controlfile for standby format 'C: \ backup \ cf _ % d _ % I _ % U ';
Release channel d1;
}
6. Copy the pfile parameter file and password file to the backup database % ORACLE_HOME % \ database \.
7. Create a backup database instance (ora10g) and related directories
C: \ Users \ Administrator> oradim-new-sid ora10g
C: \ oracle \ product \ 10.2.0 \ fast_recovery_area
C: \ oracle \ product \ 10.2.0 \ admin \
C: \ oracle \ product \ 10.2.0 \ admin \ adump
C: \ oracle \ product \ 10.2.0 \ admin \ bdump
C: \ oracle \ product \ 10.2.0 \ admin \ cdump
C: \ oracle \ product \ 10.2.0 \ admin \ dpdump
C: \ oracle \ product \ 10.2.0 \ admin \ pfile
C: \ oracle \ product \ 10.2.0 \ admin \ udump
8. Copy the backup set of the master database to the slave database for restoration. There are three steps.
① Recover the backup database control file in nomount state. RMAN> restore controlfile from 'C: \ backup \ xxxx'; xxxx indicates the name of the backup file containing the backup database control file.
② Recover the database in the mount state, and RMAN> restore database;
③ Restore the archive log file of the slave database. RMAN> restore archivelog all;
Or after completing step 1, connect to RMAN in the master database open and slave database nomount status for execution:
Rman target sys/oracle @ ora10gpd auxiliary/
Run
{
Allocate channel C1 device type disk;
Allocate auxiliary channel C2 device type disk;
Duplicate target database for standby nofilenamecheck;
Release channel C1;
Release channel C2;
}
9. Create a standby redo logfile for the slave database. The size and location must be consistent with that of the master database.
10. After the recovery is completed and the mpr0 process is started, enable redo apply and alter database recover managed standby database disconnect from session;
If everything goes well, the slave database will start to apply the archive log files uploaded from the master database one by one.
However, the fact is not so simple, in my actual result is that the redo log file of the master database cannot be passed to the slave database, the Standby database's alert log will report ORA-19527 and ORA-00312 errors, as follows:
ORA-19527: The physical standby redo log must be renamed
ORA-00312: Online log 1 thread 1: 'C: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORA10G \ redo01.log'
This is because oracle will clear the online logfiles before can become a primary to speed up swtichover after 10 Gb. If log_file_name_convert is not set, at this time, oracle may not be able to identify the redo logfile, even if the paths and file names of the cold backup copy are identical. The solution is to add log_file_name_convert to the master database parameters. This parameter is not used in the past, because the log file paths of the master and slave databases are the same (because the same database instance name ora10g is used)
SQL> alter system set log_file_name_convert = 'C: \ oracle \ product \ 10.2.0 \ oradata \ ora10g', 'c: \ oracle \ product \ 10.2.0 \ oradata \ ora10g' scope = spfile;
The parameter log_file_name_convert is not online and can take effect only after being restarted:
This is a bit embarrassing. The production database must be restarted, that is, the database must be stopped for several minutes. However, after the configuration is complete, restart standby and apply logs, clear online logfiles is displayed in the alert log, and the problem is solved.
The standby Database alert log prompts the following content:
Thu Jul 17 10:49:21 2014Thu Jul 17 10:51:25 2014
Note that the time when the redo logfile is cleared and the time when these files are created in the window directory are displayed in the alarm logs of the slave database, that is, when you clear the online log files, A file is generated in the slave database.
That is to say, when redo apply is enabled, the slave database first prompts that the online Log File configured in the parameter cannot be read, and then the log is cleared and the file is automatically generated, that's the process.
Of course, the alert log will continue to prompt that sandby redo logfile does not exist, as shown below:
Media Recovery Waiting for thread 1 sequence 66
Thu Jul 17 10:51:25 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS [2]: Assigned to RFS process 2100
RFS [2]: Identified database type as 'physical standby'
Primary database is in maximum performance mode
Primary database is in maximum performance mode
Thu Jul 17 10:51:25 2014
Errors in file c: \ oracle \ product \ 10.2.0 \ admin \ ora10g \ udump \ ora10g_rfs_2100.trc:
ORA-00313: Unable to open a member of log Group 4 (for thread 1)
ORA-00312: Online log 4 thread 1: 'C: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORA10G \ stdredo04.log'
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/S-Error: (OS 2) the system cannot find the specified file.
This standby redo logfile is somewhat different from the online redo logfile. If it cannot be opened, the database will not be cleared and automatically created. Instead, we need to create it manually.
However, it can be found from the v $ log view that the file already exists in the standby Database Control File (because when the master database creates a backup of the standby Database Control file, you have already created the file.) Then you cannot create the file again using the statement. A message indicating that the file already exists (but the file does not actually exist physically) is displayed. You can drop the file and recreate it, if you want to add or delete the online redo logfile of the master database, you must change the value of the standby_file_management parameter to manual and then change it back to auto.
If the standby redo logfile configuration is incorrect, when the protection mode is switched to maximize availability or maximize protection, the following message is reported:
ORA-16072: a minimum of one standby database destination is required
Even if the LOG_ARCHIVE_DEST_2 has been configured with lgwr sync affrem, the following error is reported when you open the database:
ORA-03113: end-of-file on communication channel
This is because the maximum availability and maximum protection modes must use the LGWR process to write to the standby redo logfile. If this condition is not guaranteed, the database cannot be opened, force disconnect from the database to provide maximum security protection for the database
After these logs are successfully created in the slave database, the slave database can start to synchronize the archive logs of the application master database:
Thu Jul 17 10:51:25 2014
RFS [1]: Archived Log: 'C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORA10GST \ ARCHIVELOG \ 2014_07_17 \ O1_MF_1_66_9WGGKFWB_.ARC'
Thu Jul 17 10:51:30 2014
Media Recovery Log C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORA10GST \ ARCHIVELOG \ 2014_07_17 \ o%mf_%66_9wggkfwb_.arc
Thu Jul 17 10:51:50 2014
RFS [2]: Archived Log: 'C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORA10GST \ ARCHIVELOG \ 2014_07_17 \ O1_MF_1_67_9WGGKFVT_.ARC'
Primary database is in maximum performance mode
Thu Jul 17 10:52:16 2014
Media Recovery Log C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORA10GST \ ARCHIVELOG \ 2014_07_17 \ o%mf_%67_9wggkfvt_.arc
Media Recovery Waiting for thread 1 sequence 68 (in transit)
Thu Jul 17 10:57:20 2014
RFS [2]: Archived Log: 'C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORA10GST \ ARCHIVELOG \ 2014_07_17 \ O1_MF_1_68_9WGGL635_.ARC'
Primary database is in maximum performance mode
Thu Jul 17 10:57:22 2014
Media Recovery Log C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORA10GST \ ARCHIVELOG \ 2014_07_17 \ o%mf_%68_9wggl635_.arc
Media Recovery Waiting for thread 1 sequence 69 (in transit)
Thu Jul 17 10:57:35 2014
RFS [2]: Archived Log: 'C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORA10GST \ ARCHIVELOG \ 2014_07_17 \ o1_mf_201769_9wggwjck_.arc'
Primary database is in maximum performance mode
Thu Jul 17 10:57:38 2014
Media Recovery Log C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORA10GST \ ARCHIVELOG \ 2014_07_17 \ o%mf_%69_9wggwjck_.arc
Media Recovery Waiting for thread 1 sequence 70 (in transit)
Thu Jul 17 10:57:51 2014
RFS [2]: Archived Log: 'C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORA10GST \ ARCHIVELOG \ 2014_07_17 \ O1_MF_1_70_9WGGWZ9C_.ARC'
Primary database is in maximum performance mode
Thu Jul 17 10:57:53 2014
Media Recovery Log C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORA10GST \ ARCHIVELOG \ 2014_07_17 \ o%mf_%70_9wggwz9c_.arc
Media Recovery Waiting for thread 1 sequence 71 (in transit)
We can see that sequence 70 has been applied on the slave database and is waiting for the master database to transmit the log of sequence 71.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
