Temporary Undo in 12C

Download PDF of article now http://goo.gl/9FKCE8

Temporary tables are very important when it comes to staging some results and a big advantage is that no redo is generated for this tables, but undo for these tables go to undo tablespace and as we know whatever comes in undo a corresponding change vector goes to redo logs as well, so there is an indirect redo generated for temporary tables not a direct one. So what if the undo for these tables are not logged in redo logs, they just go to temporary tablespace thats it, Welcome to 12C. Because by default undo for temporary tables goes to undo tablespace because of which we have so much redo overhead and archive overhead in flash recovery area, So Oracle Database 12C has resolved this problem. Lets understand in more details again :=

As we know by default undo records for Global temporary tables are stored in the undo tablespace which is set by undo_tablespace paramater and are logged in the redo also which can be very expensive depending on the amount of changes which are done on the global temporary tables. TEMP_UNDO_ENABLED in 12C is a new initialization parameter which enables a session to have a temporary undo log. So if we enable this parameter, undo for global temporary tables will go in TEMPORARY tablespace, in contrast to the default which writes the changes to the UNDO tablespace. This can help improve performance and reduce UNDO and REDO for sure. This parameter can be set at the session level or system level scope. So this way undo changes to temporary objects goes to temporary undo log and changes to persistent objects goes to permanent undo log which is active undo tablespace set by undo_tabelspace.

With this not only the performance is improved but it reduces disk space for UNDO tablespaces, archivelogs, and backups. Moreover DML on global temporary tables in a physical standby database is also possible with this parameter if it is set to true. This parameter is only applicable for the primary database. For a standby database, this parameter is ignored because temporary undo is enabled by default on the standby database with the Oracle Active Data Guard option. However, data definition language (DDL) operations that create temporary tables must be issued on the primary database.

SQL> show parameter temp_undo_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
temp_undo_enabled                    boolean     FALSE


To enable temporary undo for a session, run the following SQL statement:

ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

To disable temporary undo for a session, run the following SQL statement:

ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;

To enable temporary undo for the system, run the following SQL statement:

ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;

To disable temporary undo for the system, run the following SQL statement:

ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;

______________________________________________________

The following are the views which holds information about undo and temporary undo.

V$UNDOSTAT
V$TEMPUNDOSTAT
V$ROLLSTAT
V$TRANSACTION
DBA_UNDO_EXTENTS
DBA_HIST_UNDOSTAT

Download PDF of article now http://goo.gl/9FKCE8

Things to know about 12C Resource Manager

Download PDF of this article http://goo.gl/E5V7uo

As we all know that Resource manager (DBRM), allows us to manage mixed workloads which contend for system and database resources like CPU, IO's, Undo Consumption and so on..In this article we present some advance features of Resource Manager in Oracle Database 12C and we assume you are familiar with the basics of Resource Manager functionality in Oracle Database.

What Answers Does the Resource Manager (DBRM) Provide for a CDB?

1) Improper allocation of resources among PDBs
2) Improper allocation of resources within a single PDB
3) Poor performance of most important PDBs while unimportant PDBs are doing good

Before going to the pluggable databases in 12C lets understand what changes we have in terms of triggering consumer group switching. So coming to the point that we know in 11g we can switch a session from one consumer group to another based on some conditions because triggering consumer group switching becomes very handy when ad hoc queries demand high resources and at that point of time giving priority to some groups/sessions becomes necessary, with 11g we have the parameters switch_io_megabytes, switch_io_reqs switch_for_call, so whats new in 12C ?

The new parameters in 12C which are used to trigger consumer group switching are SWITCH_IO_LOGICAL, SWITCH_IO_ELAPSED. Other parameters new in 12C like shares and parallel_stmt_critical will be discussed below later but lets continue with consumer group switching.

Triggering consumer group switching becomes necessary when it comes to resource sharing and on top of that giving priorities to ones who need it most while restricting the ones who are of medium and low priority. So here in 12C in the context of consumer groups we have a new meta consumer group known as LOG_ONLY, and the use of this group is that in cases where we want to log the runaway queries without changing its consumer group or taking another action like KILL_SESSION or ABORT the call itself with CANCEL_SQL. These queries are actually runaway queries and mostly we monitor it through Real time SQL Monitor which was a new feature in 11g. So again whats new in 12C anyway ? In the context of the relation between SQL MONITOR and the Resource Manager ? Well a special new column RM_LAST_ACTION in V$SQL_MONITOR which tells us the recent action taken on this SQL by resource manager (RM) like for example KILL_SESSION, CANCEL_SQL, LOG_ONLY and so on.. My favourite column RM_LAST_ACTION_REASON which tells us why it happened like for example number of I/O's has reached threshold because the SWITCH_IO_REQS parameter is violated because this limit implicates the number of I/O requests that a session can issue before an action is taken. However Mnnn slave of MMON will always update a row in v$rsrcmgrmetric and v$rsrcmgrmetric_history every 60 seconds regardless of whether there is a resource plan active or not. Moreover in 12C, other new columns in V$SQL_MONITOR are RM_LAST_ACTION_TIME which tells the time of the most recent action that was taken on this SQL operation by Resource Manager and RM_CONSUMER_GROUP which tells the current consumer group for this SQL operation .

Some important views for the SQL MONITOR are V$SQL_MONITOR_SESSTAT, V$SQL_MONITOR_STATNAME

So lets take a switching example, The following PL/SQL code creates a resource plan directive for the OLTP group that temporarily switches any session in that group to the LOW_GROUP consumer group if the session exceeds 800 logical I/O requests. The session is returned to its original group after the offending top call is complete.

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'DAY',
GROUP_OR_SUBPLAN => 'OLTP',
COMMENT => 'Its an OLTP group',
MGMT_P1 => 70,
SWITCH_GROUP => 'LOW_GROUP',
SWITCH_IO_LOGICAL => 800,
SWITCH_FOR_CALL => TRUE);
END;
/


Going further..As we all know that Resource Manager manages several types of resources like CPU, I/O , Parallel Execution Servers, Runaway Queries, Active Session Pool with Queuing,  Undo Pool, Idle Time Limit. However in our discussion related to 12C we will also talk about Parallelism in DBRM in 12C which is a very important resource in Warehouse VLDB's. A new parameter PARALLEL_SERVER_LIMIT in 12C is very interesting, it specifies the maximum percentage of the parallel execution server pool that a particular consumer group can use, for example the total number of parallel execution servers is 72, which is specified by the PARALLEL_SERVERS_TARGET initialization parameter which is a dynamic parameter. So according to the above value if in resource manager the PARALLEL_SERVER_LIMIT  attribute for the consumer group OLTP is set to 50%. This consumer group can use a maximum of 50% of 72, or in other words 36 parallel execution servers.

PARALLEL_STMT_CRITICAL is another interesting parameter it specifies whether parallel statements from the consumer group are critical or not . So when BYPASS_QUEUE is specified, parallel statements from the consumer group are critical. These statements bypass the parallel queue and are executed immediately. However when FALSE or NULL (the default) is specified, parallel statements from the consumer group are not critical. These statements are added to the parallel queue when necessary.

_________________________________________________________________________

A major change in 12C is the Pluggable databases which may be plugged into or unplugged into multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases. In other words the PDBs can be unplugged from the root of one CDB and plugged into the root of another CDB. So let us discuss some points related to pluggable databases when it comes to resource manager the DBRM.

At any point we have 3 Attributes for PDB Directives in a CDB Resource Plan which are SHARES, UTILIZATION_LIMIT, PARALLEL_SERVER_LIMIT.

SHARES attribute defines how much share a specific pluggable database will get, like in the following example there are 3 pluggable databases PDB_A, PDB_B, PDB_C with 2,2,1 of shares respectively, this means that PDB_A and PDB_B is twice more important than PDB_C and the resources are to be shared in the ratio of 2:2:1 respectively for all 3 which means they will be guaranteed minimum of 40%, 40% and 20% CPU respectively. And this share of 2:2:1 is minimum guarantee, it can increase more if there is no resource contention. So this is how allocation of resources among pluggable databases (PDBs) in a multitenant container database (CDB) works. Moreover also it allocates resources among consumer groups in a non-CDB or in a PDB as well. A point here to note is that CPU utilization specified by utilization_limit parameter is 100 which means unlimited for pdb_a and pdb_b and for pdb_c the limit is 30 and parallel_server_limit is 30 for pdb_c which means that the maximum percentage (30%) of the parallel execution server pool pdb_c can use. Another interesting point here is that SHARE means all resources together like CPU and Parallel execution servers, so in this example pdb_a and pdb_b both are twice more important as compared to pdb_c so in this case queued parallel queries from the pdb_a and pdb_b PDBs are selected two times as often  as queued parallel queries from the pdb_c PDB, but the upper limit for pdb_c is 30 as explained already.


BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'plan_one',
pluggable_database => 'pdb_a',
shares => 2,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'plan_one',
pluggable_database => 'pdb_b',
shares => 2,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'plan_one',
pluggable_database => 'pdb_c',
shares => 1,
utilization_limit => 30,
parallel_server_limit => 30);
END;
/

Remember the  PDBs pdb_a and pdb_b are more important than the other PDB which is pdb_c in this CDB. Because they get a higher share (2), unlimited CPU utilization resource, and unlimited parallel execution server resource.

Important Views related to CDB/PDB:

DBA_CDB_RSRC_PLANS := Shows all the CDB resource plan directive
DBA_PDBS := Shows PDBs beloging to a particular CDB. Should be run from ROOT.
DBA_PDB_HISTORY := Gives information of a PDB history belonging to its parent (CDB)
V$PDBS := Shows information about the current instance PDB
V$PDB_INCARNATION := Shows information about all PDB incarnations. Incarnation is set whenever a PDB is opened with a resetlogs option.

Download PDF of this article http://goo.gl/E5V7uo

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.

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  http://jaffardba.blogspot.in/  and http://kyuoracleblog.wordpress.com/

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 11.2.0.3 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 10.2.0.5 Grid Control Agent Home. This patch is available for the 10.2.0.4 and 10.2.0.5 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.
________________________________________________________________________________________________

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.

SQL>    CREATE AUDIT POLICY ORA_KRISHNA
     PRIVILEGES
                ALTER DATABASE, ALTER SYSTEM,
                CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM
    ACTIONS     CREATE DATABASE LINK, ALTER DATABASE LINK, DROP DATABASE LINK,
               LOGON, LOGOFF, CREATE DIRECTORY, DROP DIRECTORY
   /

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.

SQL>  EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

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.

SQL>  EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

PL/SQL procedure successfully completed.


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

       SCN SQL_TEXT
---------- --------------------------------------------------------------------------------
   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:

    CONNECT SYS@appdb AS SYSOPER
    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:

    SHUTDOWN IMMEDIATE

    From the command prompt, run the following commands:

    cd $ORACLE_HOME/rdbms/lib
    make -f ins_rdbms.mk uniaud_off ioracle

    In SQL*Plus, restart the database.

    STARTUP


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

http://docs.oracle.com/cd/E16655_01/network.121/e17607/auditing.htm#CHDBDCHI

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.

Immuning code from SQL INJECTION Attacks (Part 1)

There are various methods of how the plsql coders write their code to avoid sql injection attacks, and here we will see some methods and tips of how we can really protect the code..

Native dynamic SQL code what i feel is actually little easy to read and write than the code that uses the DBMS_SQL package, and runs much faster specially when it can be optimized by the compiler. But thats not the point of our discussion, the point is that SQL injection is a pretty famous attack and we can avoid this with strict adherence to some basic coding practices....

Assuming that you know the concepts of dynamic sql and then bind arguments, and what oracle says you must use dynamic SQL and not only this using bind arguments protects us against SQL injection attacks. Using bind arguments also enables cursor sharing, and thus improves application performance.

If an interface is not available to an attacker, it is clearly not available to be abused. Thus the first, and arguably most important, line of defense is to reduce the exposed interfaces to only those absolutely required.

Be alert of the following rights :=

Use Definer's Right: When you want to provide users unrestricted access to a table or tables via a subprogram, create the subprogram with definer's right.

Use Invoker's Right: When the purpose of the subprogram is to perform a parameterized, but powerful, operation by using the privileges of the user that invokes it, create the subprogram with invoker's right. Using invoker's rights helps to limit the privileges, and thereby, minimize the security exposure. However, it is not sufficient as the sole measure for eliminating SQL injection vulnerabilities.

Invoker's rights helps to minimize the security exposure

For the following procedure if you give execute privilege to HR user lets say, HR user would only be able to change the password of any user including the user SYS only if he has the ALTER USER system privilege.

create or replace procedure change_password (p_username varchar2 default null,p_new_password varchar2 default null)
is
v_sql_stmt varchar2(500);
begin
v_sql_stmt := 'ALTER USER '||p_username||' IDENTIFIED BY '||p_new_password;
execute immediate v_sql_stmt;
end change_password;
/

How bind arguments should be used ? Basically complete protected code from sql injection attack can be achieved only through elimination of input string concatenation in dynamic SQL, so 1 should avoid input string concatenation and use bind arguments be it automatically through static SQL or explicitly through dynamic SQL statements. A basic code of how bind arguments are used is below :=

create or replace procedure sum_it_up
    ( a number, b number, c number)
    is
    begin
    dbms_output.put_line(a+b+c);
    end;
   /


  DECLARE
   x NUMBER := 2;
   y NUMBER := 5;
   plsql_block VARCHAR2(100);
  BEGIN
   plsql_block := 'BEGIN sum_it_up(:g, :g, :h); END;';
   EXECUTE IMMEDIATE plsql_block USING x, y;
 END;
 /

How to reduce the actual attack, below we will see how the code is not immune in the first go and then how the code is immune in the following 2 cases ( PROCEDURE GET_CODE), below is the table of citizens where humas have their social security number which is the most private number and shall be hidden or protected :=

SQL> create table citizens (fname varchar2(20),lname varchar2(20), s_security_num number);

Table created.

SQL> insert into citizens values ('karan','dodwal',10034007);

1 row created.

SQL> insert into citizens values ('ram','singh',10059991);

1 row created.

SQL> commit;

Commit complete.

--FIRST ORDER ATTACK

Now look how it is vulnuerable in the following code

create or replace procedure get_code (p_fname varchar2 default null)
is
type c is ref cursor;
cv c;
vcode citizens.s_security_num%type;
v_stmt varchar2(300);
begin
v_stmt := 'select s_security_num from citizens where fname='''||p_fname||'''';
dbms_output.put_line('sql query : '||v_stmt);
open cv for v_stmt;
loop
fetch cv into vcode;
exit when cv%notfound;
dbms_output.put_line('code is '||vcode);
end loop;
close cv;
exception when others then
dbms_output.put_line(sqlerrm);
dbms_output.put_line('sql query '||v_stmt);
end;
/

Social security number of only ram is printed in the following output

SQL> exec get_code('ram');
sql query : select s_security_num from citizens where fname='ram'
code is 10059991

PL/SQL procedure successfully completed.

But look below how the Social security number of KARAN is also printed without even knowing RAM or KARAN, just by dummy ''X''

SQL>  exec get_code('x'' union select s_security_num from citizens where ''x''=''x');
sql query : select s_security_num from t1 where fname='x' union select
s_security_num from t1 where 'x'='x'
code is 10034007
code is 10059991

ISNT IT REALLY BAD...

--PROTECTION

--Now see how it isnt vulnuerable in the following code because here we are avoiding the use of dynamic SQL with concatenated input values

create or replace procedure get_code (p_fname varchar2)
is
begin
for i in (select s_security_num from citizens where fname=p_fname)
 loop
dbms_output.put_line(i.s_security_num);
end loop;
end;
/

Social security number of only ram is printed in the following output

SQL> exec get_code('ram');
10059991

PL/SQL procedure successfully completed.

But look below how neither of Social security numbers are printed and attacker is defeated clearly

SQL>  exec get_code('x'' union select s_security_num from citizens where ''x''=''x');

PL/SQL procedure successfully completed.

In my subsequent posts i will share with you other protection mechanisms, but for the time being i wish you all happy thanks giving and also do note the following practices to observe when you secure the Oracle database:

1) Encrypt sensitive data so that it cannot be viewed.
 
2) Evaluate all PUBLIC privileges and revoke them where possible.

3) Do not widely grant EXECUTE ANY PROCEDURE.

4) Avoid granting privileges WITH ADMIN option.

5) Ensure that application users are granted minimum privileges by default. Make privileges configurable if necessary.

6) Do not allow wide access to any standard Oracle packages that can operate on the operating system. These packages include:
        UTL_HTTP, UTL_SMTP, UTL_TCP, DBMS_PIPE, UTL_MAIL, and UTL_FTP

7) Certain Oracle packages such as UTL_FILE and DBMS_LOB are governed by the privilege model of the Oracle DIRECTORY object. Protect Oracle DIRECTORY objects.

8) Lock the database default accounts and expire the default passwords.

9) Remove example scripts and programs from the Oracle directory.

10) Run the database listener as a nonprivileged user.

11) Ensure that password management is active.

12) Enforce password management. Apply basic password management rules, such as password length, history, and complexity, to all user passwords. Mandate that all the users change their passwords regularly.

13) Lock and expire the default user accounts and change the default user password.