Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, December 29, 2011

Delete archivelogs from Standby

How to delete archivelogs from standby:

There different options in oracle to delete the archivelogs automatically from Primary and standby.

Option-1)
Needs restart of the primary & secondary databases if you are on 10g.

SQL> alter system set "_log_deletion_policy"='ALL' scope=spfile; (add sid='*' if you are on RAC

Refer note
Configure RMAN to purge archivelogs after applied on standby [ID 728053.1]

option-2)
set the below in RMAN>
RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

The above will only work if you are taking the backup of the database (primary or secondary.)

In my case we are not taking backup of the secondary so I cannot use option-2 and we are in 10g and don't want to restart the primary database and don't have a test system with standby database to test option-1

so decided to develop custom script to delete the archivelogs from the standby.
Remember since my primary database is getting backed up I am using option-2 in primary.

option-3)
Here I am pulling the max sequence numbers of the archivelog for each thread( since I have 3 -node RAC) and doing minus 10 so that I will have at least 10 archivelogs in my disk at any given time for any thread and then passing these sequence numbers to RMAN to delete the archivelogs.

Below is the script.

#!/bin/ksh
export ORACLE_HOME=/fin02/u0001/oracle/product/10.2.0/db_1
export ORACLE_SID=STANDBYDBY1
export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export TODAY=`date '+%Y%m%d'`
export TIME=`date '+%H%M%S'`
rm /fin02/u0001/scripts/logs/1.tmp
$ORACLE_HOME/bin/sqlplus -s / as sysdba<set heading off
set pages 0 echo off feedback off
spool /fin02/u0001/scripts/logs/1.tmp
select thread#,max(sequence#)-10 from v\$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ;
spool off;
exit;
EOF

seq1=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==1{print $2}'`
seq2=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==2{print $2}'`
seq3=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==3{print $2}'`
echo $seq1 $seq2 $seq3
$ORACLE_HOME/bin/rman msglog /fin02/u0001/scripts/logs/STANDBYDBY_ARC_DEL.${TODAY}_${TIME}.log << EOF
connect target /
run{
delete noprompt archivelog until sequence $seq1 thread 1;
delete noprompt archivelog until sequence $seq2 thread 2;
delete noprompt archivelog until sequence $seq3 thread 3;
}
EOF



Cheers:
Sameer

Wednesday, December 28, 2011

ORA-16014: log not archived, no available destinations ORA-16038: ORA-19815 ORA-19809 ORA-19804

Today we started getting the below errors:

Even If manually tried to archive the current log I am getting the below error.

Wed Dec 28 11:19:41 2011
ORA-16014: log 14 sequence# 35224 not archived, no available destinations
ORA-00312: online log 14 thread 3: '+MYDBNAME_REDO_01/MYDBNAMEy/onlinelog/group_14.270.694773607'
ORA-00312: online log 14 thread 3: '+MYDBNAME_REDO_02/MYDBNAMEy/onlinelog/group_14.270.694773609'

and in the alert_log:


ORACLE Instance MYDBNAME3 - Archival Error
Wed Dec 28 11:19:41 2011
ORA-16038: log 13 sequence# 35232 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 13 thread 3: '+MYDBNAME_REDO_01/MYDBNAMEy/onlinelog/group_13.269.694773601'
ORA-00312: online log 13 thread 3: '+MYDBNAME_REDO_02/MYDBNAMEy/onlinelog/group_13.269.694773603'
Wed Dec 28 11:19:41 2011
Errors in file /fin01/u0001/oracle/admin/MYDBNAME/bdump/MYDBNAME3_arc3_26985.trc:
ORA-16038: log 13 sequence# 35232 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 13 thread 3: '+MYDBNAME_REDO_01/MYDBNAMEy/onlinelog/group_13.269.694773601'
ORA-00312: online log 13 thread 3: '+MYDBNAME_REDO_02/MYDBNAMEy/onlinelog/group_13.269.694773603'
Wed Dec 28 11:19:42 2011
Completed checkpoint up to RBA [0x89a2.2.10], SCN: 12575024869961
Wed Dec 28 11:23:07 2011
ARCH: Archival stopped, error occurred. Will continue retrying

If you read the alert log carefully it is evident that it is not able to archive the logs and the archiver is hung .

My alert log also shows:
Errors in file /fin01/u0001/oracle/admin/MYDBNAME/bdump/MYDBNAME3_arc1_26944.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 348966092800 bytes is 100.00% used, and has 0 remaining bytes available.
Wed Dec 28 11:28:07 2011



from the above it is clear that my FRA got filled up, lets confirm that.


DiskGroup Name %Used TOTAL_MB FREE_MB
-------------------- ----- ------------ ------------
MYDBNAME_FRA_01 85 392,616 59,785


ahh, my FRA is still has space of around 59gb then why I am still getting the alert as cannot archive and db_recovery_file_dest_size is 100% full



lets see here:

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 100 0 638
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

6 rows selected.

Above confirms that we filled up the FRA of the allocated size of 350gb.


Options:

Wed Dec 28 11:28:07 2011
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
***********************************************************************



Here since I already have 59gb left in FRA I picked up option 4:

SQL> alter system set db_recovery_file_dest_size=390gb scope=both sid='*';

Now my database can archive the logs:

SQL>alter system archive log current;

system altered

Now kicked of the RMAN backup which deletes the archivelogs at the end of the backup. This freed up the space in FRA.


If you dont have any space is your FRA to grow then use the below options:

From RMAN:

RMAN> connect target /
RMAN>delete noprompt archivelog until time '(sysdate-1)' backed up 1 times to device type disk;

or if you want to delete the logs older than certain hours ago then use.
This deletes logs older than 2 hours
RMAN>delete noprompt archivelog until time '(sysdate-1/11)' backed up 1 times to device type

or If you don't have any standby in place then backup all the logs and delete them instantly

RMAN>backup archivelog all delete input;

Monday, December 26, 2011

ORA-16055: FAL request rejected with ORA-00270:

On primary database we were getting the below errors in the alert log continuously:


CONTENTS OF ATTACHED FILE: alert_PRIMARYDB3.monitor.diff

ORA-00270: error creating archive log
ORA-16055: FAL request rejected
ORA-00270: error creating archive log


Run the below sql on standby and primary:

select message from v$dataguard_status;

MESSAGE
--------------------------------------------------------------------------------
ARC3: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
ARC3: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
FAL[server, ARC3]: Error 270 creating remote archivelog file 'PRIMARYDBY'


Now see what is the error for the primary for the archive log dest:



from the above message it is clear that it is not able to create/ship log in the destination.

hmmm so it could be network/listener down on standby?


>/usr/sbin/ping -s 99.6.183.183
PING 99.6.183: 56 data bytes
64 bytes from standby (99.6.183.183): icmp_seq=0. time=24.0 ms
64 bytes from standby (99.6.183.183): icmp_seq=1. time=23.9 ms


Ping & tnsping are under the specified time out periods:


Currently MRP is waiting on the below log:
SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
MRP0 WAIT_FOR_GAP 1 138550

Stopped/started the recovery of the logs on the standby:


SQL> alter database recover managed standby database cancel;

started by again:

SQL>alter database recover managed standby database disconnect from session;


still the MRP is on waiting on the same log:

SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
MRP0 WAIT_FOR_GAP 1 138550

confirmed the above log is available on the Primary archivelog destination so we can rule out the "missing logs on the primary" option.

still the standby is not able to receive any logs.

I am missing something here.

2) checking on Space.

On primary everything is under control, like data, FRA and redo disk groups are below thresholds

How about on Standby?

So on standby:
DB instances associated are ..

STANDBY1

ASM Diskgroup information ..


DiskGroup Name %Used TOTAL_MB FREE_MB
-------------------- ----- ------------ ------------
standby_DATA_01 72 2,880,246 795,639
standby_FRA_01 100 392,220 604



There you go FRA on the standby is 100% ahaaa.... that's why Primary is unable to create log on the standby.


So how do we cleanup the FRA on the standby database, By default the ASM instance should have automatically cleaned up the FRA by deleting the unwanted/applied logs from the FRA. In this case looks like it didn't cleanup the FRA this time.


Now get the max sequence number which was applied in the standby.


SQL> select thread#,max(sequence#) from v$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ;

THREAD# MAX(SEQUENCE#)
---------- --------------
1 138549
2 35506
3 34795


from the above we got the max sequence numbers which were applied on the standby and no more needed for recovery. So we can delete archive logs from the ASM upto the above sequence numbers for that particular thread#.


so to delete the logs connect to the RMAN on the standby:


RMAN>connect target / ;

*** Here I am deleting the logs for thread#1 upto the below sequence#

RMAN>delete noprompt archivelog until sequence 138540 thread 1;

output:

deleted archive log
archive log filename=+PRIMARYDBY_FRA_01/yPRIMARYDB/archivelog/2011_12_26/thread_1_seq_138391.3384.770889425 recid=32426 stamp=770889932
deleted archive log
archive log filename=+PRIMARYDBY_FRA_01/yPRIMARYDB/archivelog/2011_12_26/thread_1_seq_138392.3429.770889587 recid=32427 stamp=770890095
====
Deleted 335 objects

RMAN> exit


Recovery Manager complete.



This cleared the FRA.


ASM Diskgroup information ..


DiskGroup Name %Used TOTAL_MB FREE_MB
-------------------- ----- ------------ ------------
standby_DATA_01 72 2,880,246 795,639
standby_FRA_01 9 392,220 355,770




Now verify whether logs are being shipped at getting applied on the standby:






confirm by:

SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
MRP0 WAIT_FOR_GAP 3 34797


Now the logs are being shipped and appplied since the MRP is moving forward.

confirm again by:

SQL> select process,status,thread#,sequence# from v$managed_standby;


PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
MRP0 WAIT_FOR_GAP 3 34800


Make sure you have archive log deletion policy is set on the standby:

RMAN>
CONFIGURE ARCHIVELOG DELETION POLICY TO [CLEAR | NONE | APPLIED ON STANDBY];

Tuesday, December 13, 2011

State: HY000. Code: 0. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. Not enough space (HY000)

Our users were not able to view the session logs via manage sessions in OBIEE.

error:
State: HY000. Code: 0. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. Not enough space (HY000)

screen shot:




Resolution:

Go to your NQSConfig.INI
and check the below parameter.

###############################################################################
#
# User Log Section
#
# The user log NQQuery.log is kept in the server\log directory. It logs
# activity about queries when enabled for a user. Entries can be
# viewed using a text editor or the nQLogViewer executable.
#
###############################################################################

[ USER_LOG ]

USER_LOG_FILE_SIZE = 10 MB; // default size
CODE_PAGE = "UTF8"; // ANSI, UTF8, 1252, etc.

Here you can see the LOG_FILE_SIZE=10mb which is very low and hence users were not able to open the session logs, so I went ahead and increased the size to 100MB


USER_LOG_FILE_SIZE = 100 MB;

and now bounce the obiee services.

and after this change users were able to view the logs.

go to settings->administration->Manage sessions



then

view log;


here we go;




all good..