I have never found a really good and portable way of setting up an Oracle/ASM environment. For sure, there is oraenv but it is based on oratab which is a static hardcoded text file and oraenv does not work well (at all ?) with RAC. Some tries to set up aliases to set up their environment but same, it is hardcoded, Oracle uses hardcoded env files in OCI, etc ... All of this works more and less well but it has always seemed a bit clumsy to me and during a recent email discussion I had with Osman, I decided to jump into that challenge to create something better, easier and more efficient than any of these tools and a fews days later was born oraenv++!
Key features
oraenv++ reads the environmental information from GI/CRS/Oracle Restart so no hardcoded configuration file is needed; sorry I cannot do anything if you do not have GI/CRS/Oracle Restart
oraenv++ sets the ORACLE_SID, ORACLE_HOME, ORACLE_BASE, PATH and can also set the ORACLE_PDB_SID environment variable to be able to directly connect to a PDB (from 18.8)
if you do not know the name of the database you want to set the env up, oraenv++ will show you a menu with a list of databases from your configuration to choose from
you can also grep and grep -v in the database list of your environment (regexp are supported)
GI storing the database names in lowercase regardless of their case, oraenv++ is not key sensitive regarding the database name nor the pattern(s) to grep/ungrep
oraenv++ has to be sourced (. oraenv++) and not executed (./oraenv++); I'll eventually write about the differences in my bash tips
I have tested it with GI 19c and I see no reason why it would not work from any GI 12c+ (including 21c+); not sure for GI 11g which should not be much around any more these days
I have been using oraenv++ for a little while now and I honestly find it
Examples
A first example to simply set up a database environment:
Here, we have set up the prod12a environememnt with the correct instance name running on the host you are connected to. If you do not want to see this output, you can make it silent:
$. oraenv++ prod12a --silent$
or
$OPP_SILENT=True$. oraenv++ prod12a$
If you do not specify a database name, oraenv++ will list all the databases which have an instance on this host from your GI configuration and show you a menu to choose from (let's go with 6 below):
If you have many databases, you can narrow the selection down using --grep or --ungrep (acts as a grep -v); note that these 2 options allow mutiple comma separated pattern to be grepped/ungrepped; they also supports regexp -- this is far overkill but it is fun :)
$. oraenv++ --grep prod,dev --ungrep 12
Database SID ORACLE_HOME
-----------------------------------------------------------------
1/ dev19a DEV19A1 /u01/app/oracle/product/19.0.0.0/db_1
2/ dev19b DEV19B1 /u01/app/oracle/product/19.0.0.0/db_1
3/ prod19a PROD19A1 /u01/app/oracle/product/19.0.0.0/db_1
4/ prod19b PROD19B1 /u01/app/oracle/product/19.0.0.0/db_1
-----------------------------------------------------------------
Which environment you want to set up ? (CTRL+C for exit)
This could also have been written (just to showcase a regexp :)):
$. oraenv++ --grep 19[ab]$
Database SID ORACLE_HOME
-----------------------------------------------------------------
1/ dev19a DEV19A1 /u01/app/oracle/product/19.0.0.0/db_1
2/ dev19b DEV19B1 /u01/app/oracle/product/19.0.0.0/db_1
3/ prod19a PROD19A1 /u01/app/oracle/product/19.0.0.0/db_1
4/ prod19b PROD19B1 /u01/app/oracle/product/19.0.0.0/db_1
-----------------------------------------------------------------
Which environment you want to set up ? (CTRL+C for exit)
If you want to direct connect to a PDB, you can also specify one with the --pdb option which will set the ORACLE_PDB_SID variable and you could then directly connect to this PDB (this works from 18.8):
First of all, feel free to post down below or contact me if you have ideas or remarks.
On my end, one cool feature to implement in a next future would be to list the PDBs per CDB showing a menu to be able to choose from as we have hundreds of PDBs per CDB and we cannot really remember of all them then choosing from a list would be far easier. The thing here is that the PDBs are real GI resource from 21c only, they are listed in the services before 21c which I already use to show them in rac-status.sh. The thing is that crsctl is slow to query all the services when there are a lot of them so this would slow oraenv++ . Having said that, the direct connection to a PDB feature takes few seconds to get connected and ssh in any Cloud I have experienced are also slow verifying your credentials, billing you, etc ... and no one complains about it :)
Where to download ?
You can download oraenv++ from my public git repo or using the direct link to the source code from the Scripts menu on top of this page. In case of issue/question, as usual, feel free to post a comment down below, contact me by email or linkeding chat.
Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Cloud Service - Version N/A and later Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.2.0.1 [Release 10.1 to 12.2] Oracle Database Cloud Schema Service - Version N/A and later Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later Information in this document applies to any platform.
GOAL
When you find the applied sequence# doesn't increase from grid control for a physical standby database, or MRP (Managed Recovery Process ) is stuck and doesn't apply more logs, what do you do to find the cause of the issue and resolve the issue to let your physical standby database in-sync with your primary database?
In general, you could perform the following steps to start with:
Please use one of the following method to find the archive log file that MRP sticks at:
a. Please query v$managed_standby from the standby database if MRP is started.
% ps -ef |grep -i mrp SQL>select process, thread#, sequence#, status from v$managed_standby where process='MRP0';
PROCESS THREAD# SEQUENCE# STATUS -------- --------- ---------- ------------
MRP0 1 548 WAIT_FOR_GAP
Note: Starting from 12.2 use V$DATAGUARD_PROCESS view instead of v$managed_standby
b. Stop the managed recovery and start the manual recovery.
If you use data guard broker, you need to do these from either DGMGRL or grid control.
For 11g data guard broker,
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='APPLY-OFF'; DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='APPLY-ON';
If your standby is a RAC database, then
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='APPLY-ON' WITH APPLY INSTANCE = <instance-name>;
<instance-name> is the name of the instance you want to become the apply instance.
For 10g Data Guard broker,
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='LOG-APPLY-OFF'; DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='ONLINE';
If your standby is a RAC database, then
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='ONLINE' WITH APPLY INSTANCE = <instance-name>;
To change the state of the standby database from grid control for 10g and 11g standby databases, follow these steps:
1. From the Data Guard Overview page, select the standby database you want to change. 2. Click Edit to go to the Edit Properties page. 3. Select Log Apply Off (or Online). 4. Click Apply. 5. When the process completes, a message indicating success is returned.
For 9i Data Guard Broker,
DGMGRL> ALTER RESOURCE '<resource name>' ON SITE '<site name>' SET STATE='PHYSICAL-APPLY-READY'; DGMGRL> ALTER RESOURCE '<resource name>' ON SITE '<site name>' SET STATE='PHYSICAL-APPLY-ON';
To change the state of the standby database from grid control for 9i standby database, follow these steps:
1. In the navigator tree, select the standby database resource. 2. In the right-hand property sheet, click Set State. 3. Click Apply Off (or Online). 4. Click OK.
c. Use the lowest checkpoint_change# in the data file header to find the archive log that the recovery needs to recover from:
SQL>select min(fhscn),fhrba_Seq SEQUENCE from x$kcvfh group by fhrba_Seq;
2. Check whether the archive log file you find from step 1 exists on the standby site in the location defined by the standby init parameter standby_archive_dest or log_archive_dest_1 if standby_archive_dest is not defined on the standby.
If so, then please compare whether it has the same size on the standby as it is on the primary by cksum command. For example
% cksum <archive log file full path/file name>
Note: if cksum is not available on your unix platform please see the document below which provides more commands, such as, SHA-1 and MD5 checksum utilities.
Note 549617.1 How To Verify The Integrity Of A Patch/Software Download?
You could also verify whether the archive log file is corrupted on either standby or primary site by
SQL>alter system dump logfile '<full path/archive log file name>' validate;
If you get the SQL prompt back without error, then the archive log file is not corrupted.
3. If either the archive log file from step 1 doesn't exist on the standby or it is corrupted, then you would need to get a new copy from the primary site. Please refer to the document below on how to resolve a gap manually.
Note 1537316.1 Data Guard Gap Detection and Resolution Possibilities
4. If the archive log file exists on the standby site and is not corrupted, then please check whether it is registered to the standby controlfile by querying v$archived_log. For example,
SQL>select name from v$archived_log where (thread#=1 and sequence#=192917) or (thread#=2 and sequence#=26903);
If the last applied sequence# for thread 1 is 192916 and the last applied sequence# for thread 2 is 26902. Or the thread# and the sequence# are what you find from step 1 which the MRP sticks at. If the archive log file names come back from the query above, then they are registered to the standby controlfile. Otherwise, the standby controlfile doesn't aware of them.
2. Please use the queries below to identify the current sequence# on the primary for each thread, the last received sequence# on the standby and the last applied sequence# on the standby for
each thread so that you will know whether the standby is in sync with the primary, whether there is a transport lag or an apply lag.
Primary: SQL > select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;
<standby>: SQL > select thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;
<standby>: SQL > select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and applied='YES' group by thread# order by 1;
Please see the following Causes and Solutions for the MRP stuck issues on a physical standby database. If the MRP stucking issues still couldn't be resolved, then you could use RMAN incremental backup method to roll forward your physical standby database.
Note: You couldn't use the RMAN incremental backup method to roll forward a logical standby database.
Note 290817.1 Rolling a Standby Forward using an RMAN Incremental Backup in 9i Note 836986.1 Steps to perform for Rolling forward a standby database using RMAN incremental backup when primary and standby are in ASM filesystem
SOLUTION
Cause 1 : Log transport issues.
MRP sticks on the standby because the primary has trouble to ship redo to the standby. You could check the status of remote archive destination on the primary database. For example,
SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;
It could give you various errors and you would need to resolve the log transport errors.
I would only illustrate very common errors related with password file issues here, such as ora-1031, ora-1017, ora-16191, ora-12154 etc.
Solution 1: Please use cksum command to verify the size of password file on the primary and the standby sites, and make sure SEC_CASE_SENSITIVE_LOGON is set to false for 11g or above databases.
- use cksum command to verify the size of password file on the primary and the standby sites
% cd $ORACLE_HOME/dbs
% cksum <password file name> /* cksum command is only available on the UNIX platform */
Note: if cksum is not available on your unix platform please see the document below which provides more commands, such as, SHA-1 and MD5 checksum utilities.
Note 549617.1 How To Verify The Integrity Of A Patch/Software Download?
If the sizes are different, you could create a new password file on one primary node and then copy it to other primary nodes and standby nodes.
Windows orapwd file=$ORACLE_HOME/database/PWD<local ORACLE_SID>.ora password=<sys password> entries=5
Note: if the instance name on the standby database is different from the primary instance name, then their password file names are different.
You could check your local database instance name by
SQL>show parameter instance_name;
- Please make sure SEC_CASE_SENSITIVE_LOGON init parameter is set to false on both the primary and the standby databases for 11g or above databases.
If you have to set SEC_CASE_SENSITIVE_LOGON to true for business security reason, then you must create your password file with ignorecase=y attribute. For example,
Please see Note 799353.1 - How to Resolve Error in Remote Archiving for more about log shipping issues.
Cause 2 : Firewall caused partial archive log transferred.
The contents of the mrp trace file show:
Media Recovery Log /<path>/<datafile_name>.dbf ORA-00332: archived log is too small - may be incompletely archived ORA-00334: archived log: '/<path>/<datafile_name>.dbf' Background Media Recovery terminated with error 332 ORA-00332: archived log is too small - may be incompletely archived ORA-00334: archived log: '/<path>/<datafile_name>.dbf' ----- Redo read statistics for thread 1 -----
The other common error is ORA-3135 and recommend you to check this cause and solution.
ORA-03135: connection lost contact when shipping redo log to standby database
The wording and features can vary by vendor but all the above have some impact on some packets (not all packets are affected). Some firewalls can have an adverse effect on certain SQL packets transported across them (again, some not all).
You could then resolve the gap by following Note 1537316.1 Data Guard Gap Detection and Resolution Possibilities.
Please see the following articles related to ora-3135.
Note 404724.1 ORA-03135 when connecting to Database Note 739522.1 ORA - 03135 connection lost contact while shipping from Primary Server to Standby server Note 730066.1 Diagnosis of ORA-3135/ORA-3136 Connection Timeouts when the Fault is in the Database Note 787354.1 Troubleshooting ORA - 3135 Connection Lost Contact
Cause 3 : ARC process on the primary that is responsible for the heartbeat sticks on the network due to bug 6113783
You could see different symptoms. For example, you get FAL archive error as the primary arch process sticks and it doesn't ship the missing archive log files to the standby. The worst case you see is all arch processes stick and no one does local archiving and all online redo log files are full and that causes the primary database hangs. Please see the following errors related to this cause.
FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 2262-2266 DBID 2591313681 branch 686760021 FAL[client]: All defined FAL servers have been attempted.
SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS'; SQL> select message, timestamp from v$dataguard_status where severity in ('Error','Fatal') order by timestamp;
MESSAGE ------------------------------------------------------------------------------- TIMESTAMP -------------------------- Error 12154 received logging on to the standby OCT-12-2010 15:55:45
PING[ARC1]: Heartbeat failed to connect to standby '<standby_name>'. Error is 12154. OCT-12-2010 15:55:45
Solution 3: The bug 6113783 was fixed in 11.2.0.2. You could workaround the issue by killing the arch processes on the primary database.
This won't harm your primary database at all as arch processes will be respawned automatically immediately by Oracle.
% ps -ef |grep -i arc % kill -9 <ospid of arc process> <ospid of arc process> <ospid of arc process>...
NOTE: If you are on Windows Platform you can use either 'orakill'- command or any OS-Tool to kill the Windows Processes/Threads
Cause 4. The standby recovery asked for old sequence # that had been applied to the standby.
a. This is caused by bug 6029179 that was fixed in 10.2.0.4.1. Bug 6029179 Abstract: MRP WAITING FOR LOGS THAT ARE ALREADY APPLIED (with RAC primary)
b. This could be caused by ARC process that is responsible for standby heartbeat sticking on network as well due to Bug 6113783
Solution 4 : Apply the fix of bug 6029179 or kill the heartbeat arch process of each primary instance if the primary is a RAC database.
a. Apply the fix of Bug 6029179 that was fixed in 10.2.0.4.1.
b. You could identify the heartbeat arch process from the primary alert log file. Look for the last start up information in the alert log file. For example,
Starting ORACLE instance (normal) ... processes = 1800 ..... ARC4 started with pid=27, OS id=8805 ARC2: Archival started ARC3: Archival started ARC3: Becoming the 'no FAL' ARCH ARC3: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH ........... Completed: ALTER DATABASE OPEN
So ARC2 is the heartbeat arch process for this startup. If you checked the previous startup information, it might be a different arch process. Then you could find the ospid of arc2 process and kill it.
% ps -ef |grep -i arc2 % kill -9 <ospid of arc2>
NOTE: If you are on Windows Platform you can use either 'orakill'- command or any OS-Tool to kill the Windows Processes/Threads Please do this for each primary instance if the primary database is a RAC database.
c. The worst case would be to use RMAN incremental backup method to roll forward your standby database. If your primary database is small, you could simply take a full database backup from the primary and restore it to the standby server to refresh or recreate your physical standby database.
Cause 5 Recover from the wrong location.
a. The init parameter standby_archive_dest is not defined on the standby database for 10g or lower version. The default location is $ORACLE_HOME/dbs.
b. You use RMAN or manual method to restore or transfer archive log files to a different location than the local archive location log_archive_dest_1 or standby_archive_dest defined on the standby.
Solution 5 Specify standby_archive_dest the same location as log_archive_dest_1 on the physical standby. Use from 'location' attribute in the manual recovery clause.
a. Always specify standby_archive_dest the same location as the local archive destination log_archive_dest_1 for 10g or lower version physical standby database.
It is obsoleted in 11g.
b. When archive log files are located in a different location than the recovery expects, then you could use from 'location' attribute in the manual recovery clause. For example,
<different_location> is where your archive log files are located.
Cause 6: All standby redo log files are active on the standby database.
So that the standby redo log files couldn't receive new redo from the primary database.
SQL> select group#,thread#,bytes/1024/1024 as mbytes,used,archived,status from v$standby_log
GROUP# THREAD# MBYTES USED ARC STATUS ---------- ---------- ---------- ---------- --- ---------- 15 2 100 33292288 NO ACTIVE 16 2 100 3323904 NO ACTIVE 17 1 100 98733056 NO ACTIVE 18 2 100 3813376 NO ACTIVE 19 2 100 4763648 NO ACTIVE ...........
38 1 100 98072576 NO ACTIVE 39 2 100 7388672 NO ACTIVE 40 2 100 4899328 NO ACTIVE
Solution 6: Please make sure you have enough space in the archive location. log_archive_dest_1 is defined with proper valid_for values and db_unique_name. standby_archive_dest is specified properly.
a. Please make sure you have enough space in the local archive destination specified by log_archive_dest_1 so that the standby redo log files could be archived successfully. Please also make sure you have enough space in the location specified by standby_archive_dest so that the primary arch process could ship archive log files to it successfully. When log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST...', you would need to check the location specified by init parameter db_recovery_file_dest. For example:
b. Please make sure the local archive destination log_archive_dest_1 is defined with VALID_FOR=(ALL_LOGFILES,ALL_ROLES) and DB_UNIQUE_NAME=<local db DB_UNIQUE_NAME>. For example:
Oracle® Data Guard Concepts and Administration 3 Creating a Physical Standby Database
c. Please specify standby_archive_dest properly on the standby database. For example,
STANDBY_ARCHIVE_DEST=/<path>/<db_name>/
When LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=<local database DB_UNIQUE_NAME>', you specify STANDBY_ARCHIVE_DEST='LOCATION=USE_DB_RECOVERY_FILE_DEST'.
The init parameter standby_archive_dest is deprecated in 11g.
You could use alter system command to modify or define LOG_ARCHIVE_DEST_1 or STANDBY_ARCHIVE_DEST. If your database is a RAC database, please use attribute sid='*' so that the change will be made to all RAC instances for the database. For example,
SQL>alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=<standby_name>' scope=both sid='*';
If you use data guard broker, then you need to shutdown the broker first before you issue the command above from sqlplus. For example,
SQL>alter system set dg_Broker_start=false sid='*' scope=both;
Then after the change, you could start the broker by
SQL>alter system set dg_Broker_start=true sid='*' scope=both;
SQL>alter system set standby_archive_dest = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both sid='*';
When 'scope' is not specified, the default value is 'both'.
If you use broker, you could modify the StandbyArchiveLocation property as below:
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET PROPERTY 'StandbyArchiveLocation'='LOCATION=USE_DB_RECOVERY_FILE_DEST';
You would need to issue the command on all standby instances if the standby database is a RAC database.
d. Please make sure the standby redo log file size on the standby database are the same size as the primary online redo log file size and you have one more standby redo log group on the standby than the primary online redo log group for each thread. For example, if you have 2 threads on the primary database and three online redo log groups for each thread on the primary, then you would need 4 standby redo log groups for thread 1 and 4 standby redo log groups for thread 2 on the standby database no matter how many instance you have on the standby database.
In case of switchover and the database roles change, the same principle applies. For example, if your current standby database has only one instance, and three online redo log groups for it, then you would need only 4 standby redo log groups for thread 1 on the current primary database no matter how many instances/threads it has.
Reference:
MAA - Creating a RAC Physical Standby for a RAC Primary (Doc ID 380449.1)
e. Please clear all standby redo log groups by
SQL> alter database clear standby logfile group <#>;
Cause 7 : Partial archive log file is applied on the standby database.
The standby alert log file showed:
Fri Jan 9 20:11:18 2009 MRP0: Background Managed Standby Recovery process started (<standby_name>) Managed Standby Recovery not using Real Time Apply parallel recovery started with 7 processes Media Recovery Log /<path>/<db_name>-656901558185964.arc Fri Jan 9 20:11:24 2009 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION Fri Jan 9 20:11:24 2009 Incomplete Recovery applied until change 14025537844 Fri Jan 9 20:11:24 2009 MRP0: Media Recovery Complete (<standby_name>) Fri Jan 9 20:11:26 2009 MRP0: Background Media Recovery process shutdown (<standby_name>)
You could find which archive log file contains the change# 14025537844 by querying v$archived_log.
SQL>select thread#, sequence#, name, first_change#, next_Change#, deleted, status from v$archived_log where 14025537844 between first_change# and next_Change#;
Solution 7 Use RMAN incremental backup method to roll forward your physical standby database.
If your primary database is small, you could simply take a full database backup from the primary and restore it to the standby server to refresh or recreate your physical standby database.
To prevent this happening again, please perform the following before you cancel the managed recovery on the physical standby database or shutdown the physical standby database.
a. Defer the remote log transport on the primary database. For example, if log_archive_dest_2 is the remote archive destination,
SQL>alter system set log_archive_dest_state_2=defer; (for single instance primary database) SQL>alter system set log_archive_dest_state_2=defer sid='*'; (for RAC primary database)
b. Please make sure the standby recovers the last received archive log file on the standby. You can go to the location defined by log_archive_dest_1 or standby_archive_dest to see the most recent received archive log file. Then check the latest applied sequence on the standby by the query below:
SQL>select thread#, max(sequence#) from v$archived_log where applied='YES' group by thread#;
Cause 8 : The archive log files were transferred to the standby manually, not through data guard log transport service.
So that they are not registered to the standby control file and MRP (Managed Recovery Process) couldn't recognize them. For example, they are transferred from the primary by ftp, or scp, etc. or rman backup them and restores them to the standby.
The standby alert log file showed:
Media Recovery Waiting for thread 1 sequence 13615 (in transit)
Solution 8 Register those archive log files or use manual recovery.
a. Register those manually transferred archive log files and then use managed recovery to recover them.
SQL> alter database recover managed standby database cancel; SQL> alter database register logfile '<full path/archive log file name>';
or you could use one rman command to catalog all the archive logs that were manually transferred and needed to be added to the controlfile at the standby site.
rman> catalog start with 'PATH_TO_ARCHIVELOGS/';
SQL> alter database recover managed standby database disconnect;
b. If you have lots of archive log files to register, then you could simply use manual recovery without registering them. Make sure the archive log files are located in the location specified by log_archive_dest_1 or standby_archive_dest. If not, then you would need to use from 'location' attribute in the manual recovery clause.
SQL> alter database recover managed standby database cancel; SQL> alter database recover automatic standby database; or SQL> alter database recover automatic from '/<path>/' standby database;
/<path>/ is the location where your archive log files are located, which is different from the local archive destination or the standby archive destination.
Cause 9 : The archive log files are deleted from the primary before they are shipped and applied to the standby.
You use RMAN to do backup and archive log files are deleted after backup before they are shipped and applied to the standby.
Solution 9 Restore archive log files from backup, register them and use managed recovery to recover them; or use manual recovery without registering them.
Please refer to the Solution 8 for details.
If you couldn't find the archive log files anywhere, then you have to use RMAN incremental backup to roll forward your physical standby database.
If your primary database is small, you could simply take a full database backup from the primary and restore it to the standby server to refresh or recreate your physical standby database.
To prevent this happens again, please reconsider your RMAN deletion policy.
If your archive log files are configured to be in a flash recovery area, then you could configure RMAN deletion policy to APPLIED ON STANDBY so that the archive log files are deleted after they are applied to the standby database.
Please refer to the document below for details:
Configure RMAN to purge archivelogs after applied on standby (Doc ID 728053.1)
Oracle® Data Guard Concepts and Administration 11g Release 2 (11.2) 11.3.2 RMAN Configurations at the Primary Database
11.3.3 RMAN Configurations at a Standby Database Where Backups are Performed
11.3.4 RMAN Configurations at a Standby Where Backups Are Not Performed
Cause 10 : New datafiles are added to the primary, but they are not added to the standby automatically.
This could be caused by standby_file_managment=manual on the standby database. Sometimes the new datafiles failed to be added to the standby database automatically even though standby_file_managment was set to AUTO.
You could see various errors. Please refer to the documents below:
Note 743101.1 ORA-01110 ORA-1122 ORA-01251 When Restoring With a Backup from a Standby DB Note 304488.1 Using standby_file_management with Raw Devices Note 759181.1 Dataguard Physical Standby occurs ORA-600 [2130] after added new node to primary RAC database.
Bug 3596262 Add DATAFILE on primary can cause standby to raise ORA-1274
ORA-1274 Encountered on Physical Standby After Adding Datafile to Primary (Doc ID 388659.1)
ORA-01110: data file 7: '+<diskgroup>/<path>/<datafile_name>.300.740842175' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 142995 ORA-00600: internal error code, arguments: [6101], [0], [40], [128], [], [], [], [], [], [], [], [] Slave exiting with ORA-10562 exception ORA-10562: Error occurred while applying redo to data block (file# 7, block# 237743) ORA-10564: tablespace <tablespace_name> ORA-01110: data file 7: '+<diskgroup>/<path>/<datafile_name>.300.740842175
Please work with Oracle Corruption team on the root cause of datafile corruptions. Please refer to the
following documents:
Prevention, Detection and Repair of Database Corruption (Doc ID 76375.1)
Best Practices for Corruption Detection, Prevention, and Automatic Repair - in a Data Guard Configuration (Doc ID 1302539.1)
Solution 10 Add the new datafiles to the standby database manually.
1) Please take a hot backup of new datafiles from the primary database.
2) Create a new standby controlfile from the primary database by
SQL>alter database create standby controlfile as '/tmp/controlf.ctl';
If datafiles are on ASM, please follow the note below and you could ignore the rest of steps:
Note 734862.1 Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files
Or you could modify the wrong datafile name in the standby controlfile by alter database rename command. For example,
SQL> ALTER DATABASE RENAME FILE '<path/UNNAMED00003>' to '<path/real datafile name>';
3) If the new datafile location on the primary is different from the standby, please make sure db_file_name_convert init parameter is set on the standby database.
5) set standby_file_management=manual on the standby database and shutdown the standby database.
SQL>alter system set standby_file_management=manual sid='*'; SQL>shutdown immediate;
6) Copy the hot backup of the new datafiles and the new standby controlfile to the standby. Please make sure the controlfiles are located in the right location with right names according to the init parameter control_files. Please make sure the copied datafiles are located in the right location as well according to name from v$datafile.
7) startup the standby database in mount mode and set standby_file_management=auto.
SQL>startup mount; SQL>alter system set standby_file_management=auto sid='*';
Cause 11 : MRP can stall after control file contention at the standby.
MRP is waiting for a log that has already been shipped but does not appear in v$archived_logs and there are ORA-16146 errors reported in the standby alert log.
Solution 11 This is fixed in 10.2.0.4. The workaround is to restart the standby.
You can directly participate in the Discussion about this Note below. The Frame is the interactive live Discussion - not a Screenshot ;-)
Cause 12 : Cancelling managed recovery hangs.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
DGMGRL>EDIT DATABASE '<standby db_unique_name>' SET STATE='APPLY-OFF';
The above commands hang.
Solution 12 Let's shutdown abort the standby, startup mount, then have a clean shutdown, check if there are server processes left over for the standby instance.
- Let's shutdown abort the standby database.
SQL>shutdown abort; SQL>startup mount;
- Then have a clean shutdown of the standby database.
SQL>shutdown immediate;
- Then check if there is any oracle process left over for the standby instance.
