Standby Database Archived Redo Logs Gap and Incremental Restore

Archive log gap is a very common issue that DBAs face every now and then. Normally standby instance would automatically fetch the required archived logs in case of any gap, but there could be scenarios when missing archived logs cannot be found, restored or fetched. In some scenarios, standby database me be several weeks behind the primary and restoring and applying huge number of archived logs could take very long time. In such case, we can take incremental backup of primary database and restore it on the standby to make in in sync with primary. I will explain this procedure in this article.

ORA-17627: ORA-01017: invalid username/password; logon denied

 If you are doing a duplicate database from active database and commands fails with a combination of ORA-17627: ORA-01017, as follows

RMAN> duplicate target database to dupdb from active database;  Starting Duplicate Db at 23-FEB-18 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=4201 device type=DISK allocated channel: ORA_AUX_DISK_2 channel ORA_AUX_DISK_2: SID=4306 device type=DISK allocated channel: ORA_AUX_DISK_3 channel ORA_AUX_DISK_3: SID=4411 device type=DISK allocated channel: ORA_AUX_DISK_4 channel ORA_AUX_DISK_4: SID=4516 device type=DISK current log archived at primary database

Checking Redo Log Switch and Redo Generating by Session

Focus of this article is to check the redo log switch frequency as well as the sessions causing huge redo logs generation. A sudden surge in archived log generation can make DBA look into the issue to find out since when redo log generation has spiked. Following is the query we can easily find everywhere on the internet to find out the redo logs switch frequency. Of course this is not written by me. I do not know who is the original writer of this query therefore I can refer to that great guy or website.

Dataguard out of sync OR Log shipping not working

 You can seek help from this (single node) and this (RAC) document for setting up dataguard. But if dataguard has become out of sync, there are several things that you might need to look at before you fix it. In the following I am providing a checklist that you can take a look to find out the issue and thus implement a solution.

ORA-17502 and ORA-15001

 If you are creating a file on an ASM diskgroup, and you receive ORA-17502 along with ORA-15001, there is one reason of ORA-15001 already explained here. Also check if diskgroup mounted. But, if diskgroup is mounted and yet you receive this error, another reason of this could be the ownership set for “oracle” executable found under the $ORACLE_HOME from where ASM instance is running. When we install Grid Infrastructure and RDBMS softwares using different users, (GI home owned by grid user and RDBMS home and oracle user), then it is required that group ownership of “oracle” executable under RDBMS home is set to SYSASM privilege OS group which is ASMADMIN.

ORA-15039 and ORA-15001 while Dropping an ASM disk group

I was trying to drop a diskgroup and failed with ORA-15039 and ORA-15000. Following is what was returned.

SQL> drop diskgroup oradata; drop diskgroup oradata * ERROR at line 1: ORA-15039: diskgroup not dropped ORA-15001: diskgroup "ORADATA" does not exist or is not mounted

ORA-15032 ORA-15040 ORA-15042 while mounting a diskgroup

 You may face these 3 error messages together if you are trying to mount an ASM diskgroup and one or more disks of this diskgroup are missing. In case of an external redundancy diskgroup, you need at least one ASM disk to be available to mount a diskgroup. For a normal redundancy diskgroup, you need at least 2 disks for the ASM diskgroup before it can be mounted. And in case of a high redundancy diskgroup, you need at least 3 disks to mount a diskgroup. After an ASM instance restart, an ASM diskgroup may not get mounted automatically if it is not fulfilling the requirements explained above.

ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged

 

SQL> alter pluggable database pdb2 close; Pluggable database altered. SQL> drop pluggable database pdb2; drop pluggable database pdb2 * ERROR at line 1: ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged

 

ORA-19809: limit exceeded for recover files

 I faced ORA-19809 when backup of one of my databases failed during archived log backup phase. In our backup script we had a statement “ALTER SYSTEM ARCHIVE LOG CURRENT” just before the backup command for archived redo logs. When ALTER SYSTEM ARCHIVE LOG CURRENT statement was executed, following error stack was returned.

ORA-10485: Real-Time Query cannot be enabled while applying migration redo

This error message may appear in the alert log file of standby database after an upgrade/PSU etc. is applied on the primary database and if real time apply is enabled on the standby database.

The procedure to apply a PSU or other (rolling) patches is that it is first applied on the standby database server and after that on the primary database, and any post patch (or upgrade) scripts are executed on the primary database. Once this completes, all database changes happened during patch/upgrade scripts execution are shipped through redo logs to the standby database and standby database eventually becomes in sync with the primary.