Friday, 27 December 2013

Finally I became Oracle Certified Master OCM 11G DBA

Well after waiting for almost 1 month i have got congratulations Email from Oracle regarding my OCM Oracle Certified Master 11G result. I dedicate this victory to my God and my Parents, specially my Mother, this credit surely goes to my Great Mother Sudesh Dodwal, she has always prayed for my success and she kept on praying and praying. Whenever i am at work she prays for my success and because of this i have always succeeded, I have always achieved excellence because of her blessings, On my OCM exam day she had kept a fast, so it has only become possible because of her.. It took lot of pain to prepare for this exam and i felt the taste of this big success and i know how much i had struggled for the past 1 year in preparation for this exam. OCM exam is a 2 day exam and it is a practical exam, you will be given a lab where you have to battle with hundreds of challenges and scenarios, You have to be excellent on both days. For the preparation i studied so many white papers, Oracle Documentation, Metalink Notes and implemented everything related to the modules  for this exam like RAC, Data Guard, Warehouse Administration and so on... As it was OCM exam and nothing in this world comes easy, As its the highest advanced certification of oracle so i could sense how tough this exam would be, I made plans and documented them on the papers and practised during late hours of night, As i am a DBA Consultant so travelling is the first thing which i had to do, so the only time which i had was in the flights and night as in day i used to work and in weekends too for the preparation, Places i used to travel was mostly across the whole asia pacific and middle east.

The pain for this preparation was so much that sometimes my eyes used to pain but i was just unstoppable and after preparing all the technologies related to the exam i decided to book it, In India that time the exam was available in Bangalore, I booked the hotel room before the exam, and i planned to reach 1 day before the exam. Sadly i reached at 1 AM in the hotel room and next morning was my exam, so had very less time to sleep. But they say when you want something badly you are a stranger to sleep,  I wasnt able to sleep properly,  because i was excited and willing to go to the examination hall for this exam and i wokeup at 5 am only, i reached on time at 8 30 AM at exam venue, however at the exam venue i met a  person who was supposed to give the exam along with me and strangely he was sent back by the proctor because he did not bring his original Photo Id proof which in his case it had to be the passport as he came from another country, so i suggest you all get the originals not the photocopy, else you might have to wait for sometime to re-appear, So then i appeared for the exam for the same and next day and i made sure my mind was working like a computer and my fingers were typing at the speed of the mind and guess what i nailed it, I was confident that my result would be a success but still i waited for Oracle's Email. Thanks so much everyone for your support and prayers.

Monday, 9 September 2013

Expert 12C RAC BOOK

As we all know 12C is out there now and Oracle Database is running on top always for mission critical applications and every oracle database enthusiastic is waiting to read more about 12C, wouldnt it be great if we have 4 ORACLE ACE DIRECTORS writing up a book on the hottest selling technology of RAC worldwide that too on 12C, the book is EXPERT 12C RAC, We know that a cluster database is implemented with a shared multiple instances architecture which overcomes the limitations of old traditional shared-nothing and shared-disk approaches and which infact does provide highly scalable and available solutions for all the business and mission critical applications but wouldnt you grab a chance to know more in depth about Oracle RAC that too with new features of 12C like Oracle Flex Cluster and Oracle Flex Automatic Storage Management (ASM) which are designed to provide scalable and high-availability cluster infrastructure for database cloud and application cloud. When i purchased this book i first opened chapter 4 New Features in RAC 12 as i know some great features of 11G R2 RAC like GNS, OCR and Voting on ASM storage , Intelligent Platform Management interface (IPMI) and 1 of my favourite Rebootless Restart and i couldnt stop myself reading 12C new features first..

Another excellent chapter 13 is Clusterware and Database Upgrades, Any DBA has a challenge to upgrade clusterware and RDBMS as new versions come, staying up to date with the latest versions with standards and security policies too is a critical deal and to make things good for the readers they have done an excellent job by explaining all the aspects for upgrading like for example explaining upgrading Restrictions, Pre-Upgrade Checklist, Cluster and ASM Oracle 12c Upgrade Compatibility Matrix and so on...

Going more deep into this book i came to chapter 2 Clusterware Stack Management and Troubleshooting, This is one of the best chapter of the book for the ones who are willing to learn startup sequence of clusterware stack correctly and with that help they are able to troubleshoot Oracle 12C RAC correctly, saving critical down time for their business by understanding properly the stack of clusterware . As most DBA's still today are unaware of how 11G RAC and 12C RAC has been enhanced, the major differences , perfectly they have explained High Availability Service Technology Stack and Cluster Ready Service Technology Stack, both of them, Another great chapter 6 is on Application Design Issues, the flaws in application design phase which can be avoided on the oracle clusterware. I recommend this book to every Oracle DBA on this planet, 2 of the authors's blog links are as  and

Monday, 15 July 2013

Problems found with DBSNMP user account and how to fix them ..

Well there have been lot of issues related to some versions of oracle database till like Grid Control Agent Crashes with Core Dump if the Password of the Monitoring Configuration (DBSNMP) user in any Monitored Database is in Grace Period or may be a nightmare with Grid Control Shows DBSNMP Account Locked ORA-28000 Or Agent Connection to Instance Failed ORA-28001... So there have been some workarounds but before that let us take a look on some things..

There have been lot myths of what is a DBSNMP user and why it is needed and what are the bugs and corruptions caused by some of the changes in the password of this user account. Well to be simple folks DBSNMP is a user account which is actually used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database. Sometimes there is a bit confusion among the DBA's about SNMP process and DBSNMP user, SNMP is a process which will make sure metrics of the database will be forwarded to other SNMP monitoring tools because the administrators can store and analyze historical data that has been obtained through SNMP. Its still has to be searched in some of the docs of EM and metalink notes whether SNMP and DBSNMP have any internal relation between them...

Having said some lines about DBSNMP, this user has SELECT ANY DICTIONARY system privilege which can read the passwords from SYS.USER$ and would therefore will be handling the intelligent agent stuff mentioned above. The default passwords are very simple and has to be changed by the security administrators however changing this password itself is not a single step procedure. We need to perform several steps to make sure the password is changed successfuly. It would have been good if it was done at DB creation not later.

The DBCA and Oracle universal installer will ask to you for the passwords for SYS, SYSTEM, SYSMAN and DBSNMP anyways, so its good to provide a strong password in that case and moreover there is also a myth that Enterprise pages are found corrupted when we change DBNSMP password in not a proper manner, that is not the case, Actually a user named as MGMT_VIEW which is offcourse having a password definately, corruptions happen when password of this user is changed so it is advisable not to change the password of MGMT_VIEW user at all. So corrupt pages of EM will actually be found when the password of MGMT_VIEW is changed, so best is to have a special profile for this account in which password never expires because it gets MONITORING_PROFILE profile by default which specifies PASSWORD_LIFE_TIME is for 180 days, however FAILED_LOGIN_ATTEMPTS are set to unlimited in that case. This user account has read only access to tables which contain management data like percent of CPU used over time for the database server.

For operating systems like Linux the problems mentioned above have been solved with one-off Patch 8300283 on top of the Grid Control Agent Home. This patch is available for the and Grid Control Agent on major Unix / Linux platforms. Its best to see instructions for the respective patches.

So let us take a look at how to change a password of DBSNMP user, the following are the steps :=

    Stop the EM dbconsole
    On Linux - DB Control Release up to 11.1.x

    [oracle@dodwal ~]$ export ORACLE_SID=prod
    [oracle@dodwal ~]$ emctl stop dbconsole

    On Linux - DB Control Release 11.2.x and higher

    [oracle@dodwal ~]$ export ORACLE_SID=prod
    [oracle@dodwal ~]$ export ORACLE_UNQNAME=prod
    [oracle@dodwal ~]$ emctl stop dbconsole

    SQL> alter user dbsnmp identified by new_password ;

    SQL> connect dbsnmp/@prod

    Go to $ORACLE_HOME/host_sid/sysman/emd
        Save the file targets.xml to targets.xml.orig
        Edit the file targets.xml with your favorite text editor
            Search for the line:
            <property name="password" value="<encrypted_string>" encrypted="TRUE">
            Replace the encrypted value by the new password value
            Replace TRUE by FALSE
        Restart the standalone dbconsole
        On Linux - DB Control Release up to 11.1.x

        [oracle@dodwal ~]$ export ORACLE_SID=prod
        [oracle@dodwal ~]$ emctl start dbconsole

        On Linux - DB Control Release 11.2.x and higher

        [oracle@dodwal ~]$ export ORACLE_SID=prod
        [oracle@dodwal ~]$ export ORACLE_UNQNAME=prod
        [oracle@dodwal ~]$ emctl start dbconsole
        Check that the password has been encrypted
        Edit the file targets.xml
        Search for the line: <property name="password" value="<encrypted_string>" encrypted="TRUE">
        Check that the password VALUE is encrypted
        Check that the value of ENCRYPTED is TRUE

Note: In case of RAC DB, the dbsnmp password should be changed in targets.xml not only for instances(oracle_database), but also for rac_database target. The file targets.xml needs to be updated on all the RAC Cluster nodes.

Steps for Changing the DBSNMP Account Password in an Oracle Database Vault Environment:

Before you can change the password for the DBSNMP user account, you must revoke the DV_MONITOR role from this account. In an Oracle Database Vault environment, the DBSNMP user account is granted the DV_MONITOR role. (The DBSNMP user can change it's own password directly in sqlplus, without having the DV_MONITOR role revoked first.)

To change the DBSNMP user password:

1)  Log in to SQL*Plus using an account that has been granted the DV_OWNER role.
2)  Revoke the DV_MONITOR role from the DBSNMP user account.
3)  Connect as a user who has been granted the DV_ACCTMGR role and then change the DBSNMP user account password.
4)  Connect as the DV_OWNER user and then grant the DV_MONITOR role back to the DBSNMP user account.

Alternatively, you can temporarily disable Oracle Database Vault, log on as a user who has been granted the ALTER USER privilege, and then modify the DBSNMP password. Afterwards, re-enable Database Vault.

Thursday, 4 July 2013

12C Auditing Goodies

As we all know that in most cases auditing is required for reporting because of security and compliance regulations and talking about  auditing here once again relative to 12C is not something new, so here comes a new 12C Database goodie known as unified auditing. Good thing is it comes up with no changes required to the database initialization parameters since previous versions. Not only this it enables the security administrators to configure audit policies and enable them in the database with no production database downtime, providing so much ease of administration and flexibility for the database. Thats not the end, as we know that security and compliance regulations require accurate monitoring and reporting of Oracle database activity so in this case we have a new insert-only tablespace which provides guarantee that audit records are not modified or deleted after they have been written to the audit trail. Remember the maintenance of the audit trail is only limited to the database users who have been given this AUDIT_ADMIN role. So the users with the AUDIT_ADMIN role can manage the retention policy of the audit data.

As auditing is suppose to be done because regulations such as the Sarbanes-Oxley Act have common auditing-related requirements like monitoring a suspicious user or notify the auditor the actions of an unauthorised user and other requirements too, so it became more important for oracle to provide more features and options in security as always with a new version . Now in relation to the auditing database we have a new initialization parameter UNIFIED_AUDIT_SGA_QUEUE_SIZE introduced which configures the size of SGA queue for unified auditing. Actually this parameter is very useful for the systems where there is lot of audit records are generated very frequently depending on type of auditing enabled and the amount of auditing options enabled like for standard auditing options for DB there would be huge flushing of audit records to system tablespace in table aud$ in 11G version or when using VAULT the table aud$ would be in system schema with the tablespace being the same. Interestingly in 12C there are 2 modes for this one, the important one is queued-write mode, in this case audit records are first written to the SGA queue and when the SGA queue reaches the threshold, the audit records will be flushed to the disk inside AUDSYS schema table. This queue size can definately be setup and tuned accordingly depending on the amount audit records getting generated. So if there is frequent flushing it makes sense to increase queue size.

In 12C database there are 2 roles which have been introduced which are AUDIT_ADMIN and AUDIT_VIEWER, so they are required to administer unified auditing. So make sure the user who is doing these tasks has these roles.

So now let us take a look at how unified auditing policy is created and how we can manually flush the changes to disk. Dont forget this data is stored in the AUDSYS schema/SYSAUX tablespace.


Audit policy created.

SQL>  audit policy  ORA_KRISHNA ;

Audit succeeded.
##### Remember if the audit trail mode is set to QUEUED, then audit records are not written to disk till the time memory queues become full. The following command explicitly flushes the queues to disk, and then you can see the audit trail records in UNIFIED_AUDIT_TRAIL view.


PL/SQL procedure successfully completed.

SQL> SELECT  SCN, SQL_TEXT FROM UNIFIED_AUDIT_TRAIL where DBUSERNAME='SYS' and sql_text like 'alter system%';

no rows selected

SQL>  alter system set audit_trail=XML scope=spfile;

System altered.


PL/SQL procedure successfully completed.

SQL> SELECT  SCN, SQL_TEXT FROM UNIFIED_AUDIT_TRAIL where DBUSERNAME='SYS' and sql_text like 'alter system%';

---------- --------------------------------------------------------------------------------
   1732822 alter system set audit_trail=XML scope=spfile           
Disabling Unified Auditing

If you must disable unified auditing, then follow this procedure:

    Log into the database instance as user SYS with the SYSOPER privilege.

    sqlplus sys as sysoper
    Enter password: password

    In a multitenant environment, connect to the appropriate PDB.

    For example:

    Enter password: password

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

    Shut down the database.

    For example:


    From the command prompt, run the following commands:

    cd $ORACLE_HOME/rdbms/lib
    make -f uniaud_off ioracle

    In SQL*Plus, restart the database.


Just to add up to the goodies of 12C security there is an increased security in 12C when using SELECT ANY DICTIONARY privilege so SELECT ANY DICTIONARY privilege no longer allows access to security sensitive data dictionary tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS.

With this security is increased of the database as subset of data dictionary tables is not allowed to be seen even though you have SELECT ANY DICTIONARY privilege.

From the Oracle Documentation please see how Mixed Mode Auditing is suppose to behave from the following link

Below is what have been mentioned in oracle doc as it is

About Mixed Mode Auditing

When you create a new database, the database uses mixed mode auditing, which enables both traditional (that is, from releases earlier than Release 12c) and new audit facilities (unified auditing).

As the term refers, mixed mode auditing enables both traditional and unified audit facilities together. Needless to say, the traditional audit facility is driven by the AUDIT_TRAIL initialization parameter. Only for mixed mode auditing, you should set this parameter to the appropriate traditional audit trail. This traditional audit trail will then be populated with audit records, along with the unified audit trail.

When you upgrade your database to the current release, you must manually migrate to unified auditing if you want to use unified auditing. Afterward, traditional auditing is disabled, and the new audit records are written to the unified audit trail. After you complete the migration, the audit records from the previous release are still available in those audit trails. You can then archive and purge these older audit trails by using the DBMS_AUDIT_MGMT PL/SQL procedures, based on your enterprise retention policies.

There is still a lot more in 12C relative to auditing but that shall be next time.