Steps To Enable Auditing on RAC Database

Auditing has two types, there are

• standard - auditing based on statement, privilege and object-level
• fine-grained - auditing, which is based on content

Standard auditng has 3 levels.

statement - audit all actions on any type of object

privilege - audit actions that stem from system privileges

object-level - specific audit actions like select, update, insert or delete

Auditing depends on which value we pass on audit_trail

the values are..

• none - disables auditing
• os - records the information to a o/s file (uses parameter audit_file_dest for the file location)
• db - records the information in the database, use the view dba_audit_trail ( view accesses table sys.aud$ ) to display audit information.
• db, extended - as per the db value but also populates the sqlbind and sqltext clob columns
• xml - audits to the o/s file but in xml format (uses parameter audit_file_dest for the file location)
• xml,extended - as per xml option but also populates the sqlbind and sqltext clob columnstandard

For all 3 levels of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session).

Steps to enable auditing ...   Ex  11gR2 2 Node RAC setup.

We had to drop old audit (aud$) table, for that we need to keep our database in restricted mode.

Steps:

srvctl stop listener -n nodename1

srvctl stop listener -n nodename2

sqlplus>

alter system set cluster_database=false scope=spfile

alter system set audit_trail=db,extended scope=spfile

$srvctl stop database -d dbname -o immediate

>startup restrict

>truncate table aud$

>alter system set cluster_database=true scope=spfile

>shu immediate;

$srvctl start database -d dbname

$srvctl start listener -n nodename1

$srvctl start listener -n nodename2

create users

----------------------------------------

AUDIT ALL BY username BY ACCESS;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY username BY ACCESS;

AUDIT EXECUTE PROCEDURE BY username BY ACCESS;

 

opidcl aborting process unknown ospid (27181) as a result of ORA-2396

If you face the above error,

You can safely ignore.

It was written in alertlog because of timed out sessions. If you dont want this in alertlog you may have to reset your idle time to unlimited which is not possible in huge environment. So it is better to ignore the above error message.

Thanks.

Experts comments welcome.

Error 0 in kwqmnpartition-update_aqs_instance, aborting txn

If you face above error in your alert log file,

                It would be because of some sys invalid objects. It would end up with error when end users access something through application.

                To rectify this first we have to run utlrp.sql script which is located in ORACLE_HOME/rdbms/admin path.

If that doesn't work, we have to take downtime and disable all the triggers then run catproc.sql script which is also in the same location.

Thanks. 

CRS is not coming up.. hitting a bug

RAC - Windows server 2003 - CR Service is not coming up. - On 2 node RAC one node is down.

When try start CR service on node 2 (which is down) Both nodes are down with Blue screen error(OS error)

10.2.0.1

When I check OCR logs..

2012-10-09 21:54:46.058: [ CRSMAIN][632]32Initializing OCR
2012-10-09 21:54:46.073: [ OCROSD][632]utgdv:11:could not read reg value ocrmirrorconfig_loc os error= The system could not find the environment option that was entered.

2012-10-09 21:54:46.073: [ OCROSD][632]utgdv:11:could not read reg value ocrmirrorconfig_loc os error= The system could not find the environment option that was entered.
2012-10-09 21:54:46.073: [ OCRRAW][632]proprioo: for disk 0 (E:\cdata\crs\data.ocr), id match (1), my id set (1830547516,1028247821) total id sets (1), 1st set (1830547516,1028247821), 2nd set (0,0) my votes (2), total votes (2)
2012-10-09 21:54:46.089: [ OCRMAS][2872]th_master:12: I AM THE NEW OCR MASTER at incar 1. Node Number = 1
2012-10-09 21:54:46.089: [ OCROSD][2872]utgdv:11:could not read reg value ocrmirrorconfig_loc os error= The system could not find the environment option that was entered.

I had a doubt it would not able to access OCR.loc So get into ocr file on both nodes and check whether it is accessible or not.

To find the locaton u can use ocrcheck command..

or on windows ---> run ---->regedit ----> HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\OCR

Ocr.loc  In my enviroment  E:\cdata\crs\data.ocr in this format and it was accessible.

To check votedisk location You can use..     crsctl query css votedisk

Checked--- All ip's were pinging --- no network related issue.

When checking css logs, We got to know that It was 10.2.0.1 bug. We were hitting a bug.

Log for reference

StartCMMon(): clssnmNMDetach failed - 2 10.2.0.1

Bug 4714940: CLSSGMSTARTNMMON: TIMED OUT WAITING ON NESTED NM RECONFIG. SELF-SACRIFICING
-> duplicate of
Bug 4682525: STARTCMMON(): CLSSNMNMDETACH FAILED, IN CSS
-> duplicate of
Bug 4682514: CLSSGMSLAVECMSYNC: RECONFIG TIMEOUT, IN CSS
-> fixed by 10202

To fix this bug minimum required patchset is 10.2.0.2 we decided to go for 10.2.0.5 which is the latest.
Patch # 4547817 10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
Patch # 7213942 ORACLE 10.2.0.2 PATCH 18 BUG FOR WINDOWS-64 AMD64 AND INTEL EM64T XP AND 2003 (latest minipatch over 10202)

patch # 8202632 10.2.0.5.0 PATCH SET FOR ORACLE DATABASE SERVER
patch # 14408636 ORACLE 10G 10.2.0.5 PATCH 18 BUG FOR WINDOWS (64-BIT AMD64 AND INTEL EM64) (latest minipatch over 10205)

Thanks.....





 

ERROR : Connection was refused with error ORA-12519

Please check MOS id:552765.1


One of the most common reasons for the TNS-12516 and/or TNS-12519 errors being reported is the configured maximum number of PROCESSES and/or SESSIONS limitation being reached. When this occurs, the service handlers for the TNS listener become "Blocked" and no new connections can be made. Once the TNS Listener receives an update from the PMON process associated with the Database instance telling the TNS Listener the thresholds are below the configured limit, and the database is now accepting connections connectivity resumes.

In this case please check v$resource_limt view to find maximum utilization and available utilization.

For this,


Select resource_name,current_utilization,max_utilization ,limit_value from v$resource_limit;

If current utilization reaches the limit then increase the appropriate value, then if necessary bounce back the database.

In my case it processes limit has been exceeded,  so i increase both nodes and restarted the RAC DB.

Check lsnrctl status,

It may show status is blocked.

In my case;

On both nodes ---
SQL> alter system set sessions=800 scope=spfile;

Sistema modificado.

SQL> alter system set processes=1000 scope=spfile;


shutdown immediate
startup ------------  using svctl

processes integer 1000
sessions integer 1500
Because sessions are derived from processes.. Keep in mind...

ASM insufficient Privileges and while starting syntax error or unresolved network name 'LISTENER_+ASM'

For the above error,

as well as [oracle@racdr ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 8 13:00:14 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup mount pfile ='/u01/app/oracle/diag/asm/+asm/+ASM/trace/init+ASM.ora';
ORA-01031: insufficient privileges
SQL> conn sys as sysasm
Enter password:
ERROR:
ORA-01031: insufficient privileges

We  need to export all the home paths corectly

For ex.. while starting ASM.(11g)

ORACLE HOME should be grid home and also Grid home should be grid home..
export ORACLE_SID as +ASM


If you have any parameter like sqlnet.authentication_services inside your sqlnet.ora file comment that one. And also comment the LOCAL_LISTENER 'LISTENER_+ASM' (if exist ) in the pfile.

Better U create a pfile using ASM alertlog File

Following Steps has to be done.

 $export ORACLE_SID=+ASM
$export ORACLE_HOME=<Your Grid Oracle Home>
$export PATH=$PATH:$ORACLE_HOME/bin

Create the pfile with below values under $ORACLE_HOME/dbs

$cd $ORACLE_HOME/dbs
$vi init+ASM.ora

large_pool_size = 12M
instance_type = "asm"
remote_login_passwordfile= "EXCLUSIVE"
asm_power_limit = 1
diagnostic_dest = "/u01/app/oracle"
asm_diskstring='/dev/oracleasm/disks/*'

$sqlplus "/as sysasm"

sql>startup pfile='?/dbs/init+ASM.ora';

sql>alter diskgroup data mount; (Mention ur diskgroup name)


To catch details of ur ASM

You can use following commands for Linux.
# rpm -qa |grep oracleasm
# /etc/init.d/oracleasm status
# lsmod |grep -i asm
# cat /proc/filesystems |grep -i asm
# ls -l /usr/sbin/oracleasm-discover
# /etc/init.d/oracleasm listdisks
$ /etc/init.d/oracleasm listdisks | xargs /etc/init.d/oracleasm querydisk -p
# ls -l /dev/oracleasm/disks/*
# cat /proc/partitions
# cat /etc/sysconfig/oracleasm
# ls -l /etc/sysconfig/oracleasm*
Ur comments are welcome


 

Migration of 10.2.0.3(32-Bit) database to 11.2.0.3(64-Bit)

Test Setup

Initial Platform : Redhat Linux 5 update 6 (32 – bit)

Since Oracle DB 10.2.0.3 is not a complete patch set, we need to install the base version that is 10.2.0.1 and create a test database ( here RMS), later upgrade it into 10.2.0.3 for test setup.

Before installing 10g, do the prerequisites, and also install all the required rpms which you can find inside RHEL media Server directory.

After the Successful installation of Oracle 10g & creation of test database,

Download the patchset for 10.2.0.3 and unzip the same.

Before installing the patchset.

1.Stutdown the database.

2.Stop the listener >> lsnrctl stop

3.stop dbconsole >> emctl stop dbconsole

4.stop isqlplusctl >> isqlplusctl stop

[oracle@localhost ~]$ . .bash_profile

[oracle@localhost ~]$ emctl status dbconsole

TZ set to Asia/Calcutta

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

http://localhost.localdomain:1158/em/console/aboutApplication

Oracle Enterprise Manager 10g is running.

Logs are generated in directory /home/oracle/oracle/product/10.2.0/db_1/localhost.localdomain_rms/sysman/log

[oracle@localhost ~]$ emctl stop dbconsole

TZ set to Asia/Calcutta

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

http:// localhost.localdomain:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Database Control ...

... Stopped.

[oracle@localhost ~]$ emctl status dbconsole

TZ set to Asia/Calcutta

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

http://localhost.localdomain:1158/em/console/aboutApplication

Oracle Enterprise Manager 10g is not running.

Unzip the Patch set for 10.2.0.3 u can get Disk1 directory.

Install the patch set in the existing path, where 10.2.0.1 binaries installed. It will automatically update the binaries. Or else you can use Opatch utility.

[oracle@localhost ~]$ cd Disk1/

[oracle@localhost Disk1]$ ./runInstaller

Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2

Passed

All installer requirements met

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-08-20_05-33-38AM. Please wait ...[oracle@localhost Disk1]$ Oracle Universal Installer, Version 10.2.0.3.0 Production

Copyright (C) 1999, 2006, Oracle. All rights reserved.

Asia/Calcutta

[oracle@localhost Disk1]$

For Ex.. Updation of binaries process..

Updating jar file "/home/oracle/oracle/product/10.2.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_jobs/_jobsList.class"

Updating jar file "/home/oracle/oracle/product/10.2.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_jobs/_jobsList$__jsp_StaticText.class"

After the successful upgradation of binaries, You can start the database with upgrade mode.

Or you can upgrade using dbua utility.

sql> startup upgrade;

Run Catupgrd.sql

@?/rdbms/admin/catupgrd.sql

Oracle Database Server VALID 10.2.0.3.0 00:08:09

JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:03:05

Oracle XDK VALID 10.2.0.3.0 00:00:41

Oracle Database Java Packages VALID 10.2.0.3.0 00:00:48

Oracle Text VALID 10.2.0.3.0 00:00:26

Oracle XML Database VALID 10.2.0.3.0 00:01:38

Oracle Data Mining VALID 10.2.0.3.0 00:00:22

Oracle Rule Manager VALID 10.2.0.3.0 00:00:12

.

Total Upgrade Time: 00:25:08

DOC>#######################################################################

DOC>#######################################################################

Bounceback the database with normal mode then run utlrp.sql script to validate invalid objects.

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Startup;

SQL> select count(*) from dba_objects where status = 'INVALID';

COUNT(*)

506

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

 

SQL> select count(*) from dba_objects where status = 'INVALID';

COUNT(*)

0

Check the status and version of all components

SQL> select comp_name , status ,version from dba_registry;

COMP_NAME STATUS VERSION

Oracle Database Catalog Views VALID 10.2.0.3.0

Oracle Database Packages and Types VALID 10.2.0.3.0

Oracle Workspace Manager VALID 10.2.0.1.0

Server JAVA Virtual Machine VALID 10.2.0.3.0

Oracle XDK VALID 10.2.0.3.0

Oracle Database Java Packages VALID 10.2.0.3.0

Oracle Expression Filter VALID 10.2.0.3.0

Oracle Data Mining VALID 10.2.0.3.0

Oracle Text VALID 10.2.0.3.0

Oracle XML Database VALID 10.2.0.3.0

Oracle Rule Manager VALID 10.2.0.3.0

Oracle interMedia VALID 10.2.0.3.0

OLAP Analytic Workspace VALID 10.2.0.3.0

Oracle OLAP API VALID 10.2.0.3.0

OLAP Catalog VALID 10.2.0.3.0

Spatial VALID 10.2.0.3.0

Oracle Enterprise Manager VALID 10.2.0.3.0

Everything is fine now..

Download Oracle DB 11.2.0.3 and install it in a different path

After Installing 11.2.0.3.. Check the prerequisites to upgrade to 11g for the existing 10g database.

Inside 11g ORACLE_HOME/rdbms/admin path you can find utlu112i.sql script copy it into temporary location and run it on 10g database to check the prerequisites.

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Aug 21 05:43:27 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> @/tmp/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 08-21-2012 05:44:01

Script Version: 11.2.0.3.0 Build: 001

**********************************************************************

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

Components: [The following database components will be upgraded or installed]

**********************************************************************

--> Oracle Catalog Views [upgrade] VALID

--> Oracle Packages and Types [upgrade] INVALID

 

Changes will need to be made in the init.ora or spfile. Check the timezone

SQL> select count(*) from dba_objects where status='INVALID';

632

SQL> select * from v$timezone_file;

timezlrg.dat 3

Before Upgrade Purge the recycle bin and also gather the statistics for all schemas.

SQL> purge dba_recyclebin;

Upgradation using DBUA

Export the Oracle 11g Home path in the bash Profile.

In 11g home invoke dbua...

1.select the database which needed to upgrade..

(For our test DB RMS has been selected)

2.Select the timezone too.

4.Select the degree of parallelism

It will take long time to complete.

Everything will be upgraded automaticallically, when you use dbua including timezone.

Export 11g Home and check the status of all components.

SQL> col comp_name for a40;

SQL> /

COMP_NAME STATUS VERSION
 

Oracle Enterprise Manager VALID 11.2.0.3.0

OLAP Catalog VALID 11.2.0.3.0

Spatial VALID 11.2.0.3.0

Oracle Multimedia VALID 11.2.0.3.0

Oracle XML Database VALID 11.2.0.3.0

Oracle Text VALID 11.2.0.3.0

Oracle Data Mining VALID 11.2.0.3.0

Oracle Expression Filter VALID 11.2.0.3.0

Oracle Rule Manager VALID 11.2.0.3.0

Oracle Workspace Manager VALID 11.2.0.3.0

Oracle Database Catalog Views VALID 11.2.0.3.0

Oracle Database Packages and Types VALID 11.2.0.3.0

JServer JAVA Virtual Machine VALID 11.2.0.3.0

Oracle XDK VALID 11.2.0.3.0

Oracle Database Java Packages VALID 11.2.0.3.0

OLAP Analytic Workspace VALID 11.2.0.3.0

Oracle OLAP API VALID 11.2.0.3.0

17 rows selected.

SQL> select * from v$timezone_file;

FILENAME VERSION

-------------------- ----------

timezlrg_14.dat 14

We have upgraded the database from 10.2.0.3(32-Bit) to 11.2.0.3(32-Bit).

Now, We have to migrate this database to 64-bit

 

1. Install 64-bit RHEL 5.6 and get the server ready.

2. Do the prerequisites before install 11g like kernel settings

3. Install all the required rpms.( u can find inside server directory of RHEL Media)

4. Create oracle user under dba group

5. Give the appropriate privileges to Oracle user, in which software has to be installed.

6. Install Oracle 11.2.0.3 64-Bit Software.

 

Clone the database from 32-bit server to 64 – Bit Server by creating appropriate directory.

(Using SCP copy all the data, control , redo and spfiles to 64- bit Server)

(Comment audit_trail_file parameter in pfile and create spfile by the same)

In the New Server After the Cloning,(Export Bash profile)

Startup Upgrade;

Run

@?/rdbms/admin/utlirp.sql -------

PL/SQL procedure successfully completed.

DOC>#######################################################################

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

8841

Normal Shutdown

Shutdown;

startup;

SQL> @?/rdbms/admin/utlrp.sql

2 threads pls_integer := &&1;

3 BEGIN

4 utl_recomp.recomp_parallel(threads);

5 END;

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 23 01:58:50 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from dba_objects where status ='INVALID';

COUNT(*)
 

0

SQL> select platform_name from v$database;

PLATFORM_NAME

--------------------------------------------------------------------------------

Linux x86 64-bit

SQL> select comp_name ,version,status from dba_registry;

COMP_NAME VERSION STATUS

------------------------------ -----------

Oracle Enterprise Manager 11.2.0.3.0 VALID

OLAP Catalog 11.2.0.3.0 VALID

Spatial 11.2.0.3.0 VALID

Oracle Multimedia 11.2.0.3.0 VALID

Oracle XML Database 11.2.0.3.0 VALID

Oracle Text 11.2.0.3.0 VALID

Oracle Data Mining 11.2.0.3.0 VALID

Oracle Expression Filter 11.2.0.3.0 VALID

Oracle Rule Manager 11.2.0.3.0 VALID

Oracle Workspace Manager11.2.0.3.0 VALID

Oracle Database Catalog Views11.2.0.3.0 VALID

Oracle Database Packages and Types11.2.0.3.0 VALID

JServer JAVA Virtual Machine11.2.0.3.0 VALID

Oracle XDK 11.2.0.3.0 VALID

Oracle Database Java Packages11.2.0.3.0 VALID

OLAP Analytic Workspace 11.2.0.3.0 VALID

Oracle OLAP API 11.2.0.3.0 VALID

17 rows selected.

SQL> select ( case length(address) when 16 then '64 - bit oracle'

2 when 8 then '32-bit'

3 when 32 then '128-bit' end) wordsize

4 from v$sql

5 where rownum < 2;

WORDSIZE

---------------

64 - bit oracle