How to check if Log Rotate is working fine?

Log rotate is used to do house keeping of log files in unix servers. Setting up log rotate needs root user privileges.

There are some environments where oracle DBA's will have root access privileges. Please check below to find if log rotate is working good or not.

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Check Log Rotate Script
#########################################

Here we have a file called "listenerlogs" where compress of listener logs are carried out.

cd /etc/logrotate.d

[oracle@host01 logrotate.d]$ more listenerlogs
# Node Listener tracelog
/u01/app/oracle/diag/tnslsnr/host01/listener/trace/listener.log {
weekly
compress
rotate 4
notifempty
missingok
copytruncate
nocreate
}
[oracle@host01 logrotate.d]$

=====================================================================================================================
From above file, we can see that, every week listener.log file will be rotated and it will keep last 4 files
=====================================================================================================================

#########################################
# 2) Check Log files
#########################################

[oracle@host01 logrotate.d]$ ls -ltr /u01/app/oracle/diag/tnslsnr/host01/listener/trace/
total 1620
-rw-r----- 1 oracle oinstall   61879 Apr 27 03:31 listener.log-20140427.gz
-rw-r----- 1 oracle oinstall   63278 May  4 03:17 listener.log-20140504.gz
-rw-r----- 1 oracle oinstall   78429 May 11 03:46 listener.log-20140511.gz
-rw-r----- 1 oracle oinstall   99425 May 18 03:34 listener.log-20140518.gz
-rw-r----- 1 oracle oinstall 1339839 May 20 12:24 listener.log
[oracle@host01 logrotate.d]$ 

=====================================================================================================================
From above output you can see that log files are zipped on a weekly basis, so log rotate is working.
=====================================================================================================================

Remove / Delete / Clean up Orphan Jobs from OEM Repository Database

One of the database has been deleted from 11g OEM. Once the databases are deleted from OEM, the jobs associated with the database will also get deleted.

But sometimes there are chances that Jobs which were scheduled for that targets left orphan without being dropped.

Here we have a database named "ORALIN" which was dropped earlier but the job exist in mgmt_job. When we search for a job in Jobs tab, it doesnt report any jobs.

=====================================================================================================================
Step 1 : Job Information
=====================================================================================================================

set lines 200
col job_name for a35
col job_owner for a25
select job_id, job_name, job_owner,job_status from mgmt_job where job_name ='FULL_DATABASE_ORALIN_EXADATA_WEEKLY';

JOB_ID                           JOB_NAME                            JOB_OWNER                 JOB_STATUS
-------------------------------- ----------------------------------- ------------------------- ----------
C2DF0310F7ED51EWR1E04014AC35966266 FULL_DATABASE_ORALIN_EXADATA_WEEKLY  KASI                               2

Note : Job_status = 2 ( Job is stopped )

#### Job Execution Summary

select EXECUTION_ID, status from MGMT_JOB_EXEC_SUMMARY where job_id ='C2DF0310F7ED51EWR1E04014AC35966266' ; 

EXECUTION_ID                         STATUS
-------------------------------- ----------
D175BEBBEF4B378FEQ04014QAC33961A19         19

=====================================================================================================================
Step 2 : Stop the Job
=====================================================================================================================

As the jobs are not showing in OEM, we cant delete via oem, so we need to delete from OEM Repository Database.

#### Stop the Job Forcefully. ( Some times jobs might show as running instead of scheduled state, in this case its not applicable )

exec mgmt_job_engine.stop_all_executions_with_id('C2DF0310F7ED51EWR1E04014AC35966266');

SQL> exec mgmt_job_engine.stop_all_executions_with_id('C2DF0310F7ED51EWR1E04014AC35966266');
PL/SQL procedure successfully completed.

Where,

C2DF0310F7ED51EWR1E04014AC35966266 - is the job_id from mgmt_job taken from Step 1

#### Job Execution Summary

select EXECUTION_ID, status from MGMT_JOB_EXEC_SUMMARY where job_id ='C2DF0310F7ED51EWR1E04014AC35966266' ; 

EXECUTION_ID                         STATUS
-------------------------------- ----------
D175BEBBEF4B378FEQ04014QAC33961A19         19

=====================================================================================================================
Step 3 : Remove Orphan Jobs
=====================================================================================================================

Below queries should be run as sysman user.

exec mgmt_job_engine.delete_job('C2DF0310F7ED51EWR1E04014AC35966266');

SQL> exec mgmt_job_engine.delete_job('C2DF0310F7ED51EWR1E04014AC35966266');
PL/SQL procedure successfully completed.
SQL>

#### Job Information

set lines 200
col job_name for a35
col job_owner for a25
select job_id, job_name, job_owner,job_status from mgmt_job where job_name ='FULL_DATABASE_ORALIN_EXADATA_WEEKLY';

no rows selected

SQL>

Now we have successfully removed the orphan job from 11g OEM Repository.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ END @@@@@@@@@@@@@@@@@@@

ORA-27086: unable to lock file - already in use Linux-x86_64 Error: 37: No locks available

##########################
## Error
##########################

ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 37: No locks available

### Full Error

Tue May 20 08:58:08 2014
Control file backup creation failed.
Backup target file size found to be zero.
Errors in file /u01/app/oracle/diag/rdbms/oralin/oralin1/trace/oralin1_ckpt_14497.trc:
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 37: No locks available
Additional information: 10

##########################
#  Error Occurred
##########################

Error occured in RMAN FULL backup while taking backup of control file in 11.2.0.3.0 version

##########################
## Command Executed
##########################

Backup of control file

**************************************** Step By Step Analysis ******************************************************

#########################################
# Alert Log
#########################################

Tue May 20 08:58:08 2014
Control file backup creation failed.
Backup target file size found to be zero.
Errors in file /u01/app/oracle/diag/rdbms/oralin/oralin1/trace/oralin1_ckpt_14497.trc:
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 37: No locks available
Additional information: 10

#########################################
# Trace File ( /u01/app/oracle/diag/rdbms/oralin/oralin1/trace/oralin1_ckpt_14497.trc )
#########################################

ORA-27086: unable to lock file - already in use

*** 2014-05-20 08:58:08.285
Linux-x86_64 Error: 37: No locks available
Additional information: 10
Control file enqueue hold time tracking dump at time: 36768

#########################################
# 1) Check the Snapshot Controlfile
#########################################

RMAN> show snapshot controlfile name;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name oralin are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/backup/db/oralin/CONTROLFILE_SNAPSHOT_oralin/snapcf_oralin1.f';

RMAN>

Here, /backup is the shared NFS Mount Point.

=====================================================================================================================

#########################################
# 2) Check NFS lock status
#########################################

service nfslock status

[root@host01 ~]# service nfslock status
rpc.statd is stopped
[root@host01 ~]# 

NFS lock is in stopped state.

[root@host01 ~]# chkconfig --list | grep nfs
nfs             0:off   1:off   2:off   3:off   4:off   5:off   6:off
nfslock         0:off   1:off   2:off   3:off   4:off   5:off   6:off
[root@host01 ~]#

=====================================================================================================================

#########################################
# 3) Reason for Failure
#########################################

Error is occuring when autobackup of controlfile is being done. When RMAN is trying to create a snapshot control file in the shared NFS MP, its says unable to lock file.

From Step 2, we can see that nfs lock service is in stopped state, so RMAN cannot hold a lock.

This server was rebooted recently and nfs lock service didnt come up and also we can see from step 2 that auto start is in OFF

=====================================================================================================================

#########################################
# 4) Reproduce the Error
#########################################

A simple backup of controlfile in NFS Mount Point will help us to reproduce the error.

backup current controlfile format '/backup/db/oralin/CONTROLFILE_SNAPSHOT_oralin/oralin.ctl';

RMAN> backup current controlfile format '/backup/db/oralin/CONTROLFILE_SNAPSHOT_oralin/oralin.ctl';
Starting backup at 20-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4 instance=oralin1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/20/2014 09:27:03
ORA-01580: error creating control backup file /backup/db/oralin/CONTROLFILE_SNAPSHOT_oralin/snapcf_oralin1.f
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 37: No locks available
Additional information: 10

=====================================================================================================================

##########################
## Solution
##########################

#### Start NFS Lock services in the server

[root@host01 ~]# service nfslock start
Starting NFS statd:                                        [  OK  ]
[root@host01 ~]# service nfslock status
rpc.statd (pid 29693) is running...
[root@host01 ~]#

=====================================================================================================================
Test the backup, now it completes successfully...
=====================================================================================================================

RMAN> backup current controlfile format '/backup/db/oralin/CONTROLFILE_SNAPSHOT_oralin/oralin.ctl';

Starting backup at 20-MAY-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 20-MAY-14
channel ORA_DISK_1: finished piece 1 at 20-MAY-14
piece handle=/backup/db/oralin/CONTROLFILE_SNAPSHOT_oralin/oralin.ctl tag=TAG20140520T092820 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20-MAY-14
Starting Control File and SPFILE Autobackup at 20-MAY-14
piece handle=/backup/db/oralin/autobackup/c-419595185-20140520-02 comment=NONE
Finished Control File and SPFILE Autobackup at 20-MAY-14
RMAN>

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

View Hidden Parameter or UnDocumented Parameter Information from Oracle Database ( _gby_hash_aggregation_enabled )

Well most of the time we don't care about what are the hidden parameters a database has and what it actually does and of course oracle kept it hidden for a purpose.

Any modifications done to the hidden parameters without suggestions from Oracle support may or may not result in a disaster of your database.

So now lets see how we can fetch the information for Hidden Parameters.

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Using V$parameter
#########################################

Usually we check in V$parameter for database parameters. Most of the hidden parameters cant be seen in V$parameter

Select name,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name ='_gby_hash_aggregation_enabled';

no rows selected


=====================================================================================================================

#########################################
# 2) How to check Hidden Parameters
#########################################

set lines 200
col Parameter for a40
col "Session Value" for a25
col "Instance Value" for a25

SELECT a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value" FROM   x$ksppi a,x$ksppcv b,x$ksppsv c WHERE  a.indx = b.indx
AND    a.indx = c.indx AND    a.ksppinm LIKE '/_gby%hash%' escape '/';

Parameter                                Session Value             Instance Value
---------------------------------------- ------------------------- -------------------------
_gby_hash_aggregation_enabled            TRUE                      TRUE

=====================================================================================================================
 Now we can see the hidden parameters Set at Database level.

 I didnt get satisfied by just knowing the values set at database level. Then i want to know whether the parameter can be modified at session or system level
=====================================================================================================================

#########################################
# 3) Hidden Parameters Information ( Detailed )
#########################################

set lines 200
col name for a33
col value for a20
col description for a50
col IS_SESSION_MODIFIABLE for a25
col IS_SYSTEM_MODIFIABLE for a25
col IS_MODIFIED for a25

select par.ksppinm name,val.ksppstvl value,par.ksppity type, val.ksppstdf is_default,decode(bitand(par.ksppiflg/256,1), 1,'True', 'False' ) is_session_modifiable,decode(bitand(par.ksppiflg/65536,3), 1,'Immediate', 2,'Deferred' , 3,'Immediate', 'False' ) is_system_modifiable,par.ksppdesc description
from x$ksppi par, x$ksppsv val where par.indx = val.indx  and par.ksppinm = '_gby_hash_aggregation_enabled';

NAME                              VALUE                      TYPE IS_DEFAUL IS_SESSION_MODIFIABLE     IS_SYSTEM_MODIFIABLE      DESCRIPTION
--------------------------------- -------------------- ---------- --------- ------------------------- ------------------------- --------------------------------------------------
_gby_hash_aggregation_enabled     TRUE                          1 TRUE      True                      Immediate                 enable group-by and aggregation using hash scheme

Well i'm happy to get this info after searching for quite some time. Happy Learning....

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ADRCI command Ouput is not fully displayed XX rows fetched (*** more available ***)

ADRCI command Ouput is not fully displayed XX rows fetched (*** more available ***)

Mostly i use ADRCI prompt whenever any one of our databases gives ORA-600 or ORA-7445 for which we knock ORACLE SUPPORT and they need ips pack zip file.

So when i gave show incident command, Only First 50 rows were shown and other rows are not shown. Below is the command to view full list of incidents

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Show Incident
#########################################

show incident

ADR Home = /u01/app/oracle/diag/rdbms/oralin/oralin:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
259193               ORA 600 [15764]                                             2014-03-23 12:11:05.350000 +00:00
...
...
257131               ORA 4030                                                    2014-03-30 10:08:20.329000 +00:00
258929               ORA 4030                                                    2014-03-30 10:08:20.331000 +00:00
256675               ORA 4030                                                    2014-03-30 10:08:20.331000 +00:00
First 50 rows fetched (*** more available ***)
adrci> 

=====================================================================================================================
Looks like there are more number of incidents Available and 1st 50 rows are only shown
=====================================================================================================================

#########################################
# 2) List All Incidents
#########################################

show incident -all

adrci> show incident -all
ADR Home = /u01/app/oracle/diag/rdbms/oralin/oralin:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
259193               ORA 600 [15764]                                             2014-03-23 12:11:05.350000 +00:00
261017               ORA 600 [15764]                                             2014-03-24 02:02:33.078000 +00:00
....
257206               ORA 7445 [__intel_ssse3_rep_memcpy]                         2014-04-10 19:47:51.146000 +00:00
256953               ORA 7445 [rworofprFastUnpackRowsets]                        2014-04-10 19:48:00.127000 +00:00
257952               ORA 600 [qesmmCUpdCurSz2]                                   2014-05-13 20:24:57.304000 +00:00
288270               ORA 7445 [qesmmCGetStats]                                   2014-05-13 20:24:59.921000 +00:00
256617               ORA 600 [qesmmCUpdCurSz2]                                   2014-05-13 20:35:49.036000 +00:00
256618               ORA 7445 [qesmmCGetStats]                                   2014-05-13 20:35:51.013000 +00:00
256619               ORA 600 [17114]                                             2014-05-13 20:41:52.638000 +00:00
296 rows fetched

 A Simple but helpful Command. In step 1 we got only 50 rows and Step 2 gives results for 296 rows.

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================