How to work around ORA-01017 in a migrated 12c database

You may see some users in your 12c database that have the password versions set to 10G:

select username, password_versions from dba_users where username='SCOTT';

USERNAME             PASSWORD_VERSIONS
-------------------- -----------------
SCOTT                10G

At the same time, most other users have their password_versions set to the value 10G 11G 12C.

Oracle uses different password versions in all these three versions:

* Oracle 11g it uses SHA1 password based version
* Oracle 10g uses DES based version.
* Oracle 12c uses SHA-2-based SHA-512 password version

Since the Oracle 12c database runs in exclusive mode by default, users with passwords generated in previous versions
will not be able to login (exclusive mode means that the SQLNET.ALLOWED_LOGON_VERSION_SERVER is set either to 12 or 12a).

Workaround is to force a password reset so that the password is generated for the current version.
But before you do that, you need to change the database's minimum allowed authentication protocol.
This is done by editing the file $TNS_ADMIN/sqlnet.ora.
The parameter controlling the sqlnet authentication protocol is SQLNET.ALLOWED_LOGON_VERSION_SERVER.

Here is how I did it:

1. Find the user(s) with password versions of 10G

select username, password_versions 
from dba_users 
where password_versions = '10G';

I have found cases where there is a space after the string '10G' so that you need to actually search for the string '10G '.

2. Edit the $TNS_ADMIN/sqlnet.ora file so that the database doesn't run in exclusive mode. Add

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 11

3. Restart database

4. Expire the user(s) that you want to force a password reset for:

alter user SCOTT password expire;

5. Try to connect as the user:

connect SCOTT
Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for SCOTT
New password:
Retype new password:
Password changed
Connected.

Check that the users now has the correct password version:

select username, password_versions,account_status from dba_users where username='SCOTT';

USERNAME             PASSWORD_VERSIONS ACCOUNT_STATUS
-------------------- ----------------- --------------------------------
SCOTT                10G 11G 12C       OPEN

6. When all the affected users have been changed, set the database to run in exclusive mode.
Change the SQLNET.ALLOWED_LOGON_VERSION_SERVER from 11 to 12 in $TNS_ADMIN/sqlnet.ora, and restart once more.

How to toggle between "Mixed mode" Auditing, Traditional Auditing and Unified Auditing

This article is applicable to Oracle database versions 12.1 and onwards.

For newly created databases, mixed mode auditing is enabled by default through the predefined policy ORA_SECURECONFIG. 

 

Verify that the database is using "Mixed Mode" auditing

select parameter, value from v$option where parameter='Unified Auditing';

PARAMETER	VALUE
Unified Auditing	FALSE

Check for any enabled unified audit policies:

select policy_name, enabled_option
from audit_unified_enabled_policies;

POLICY_NAME	ENABLED_OPTION
ORA_SECURECONFIG	BY USER

If v$option shows FALSE for unified auditing AND the database have at least one enabled unified auditing policy, we are running in "Mixed Mode" auditing. 

In Mixed Mode Auditing, all of the existing auditing startup parameters for the database are still valid: AUDIT_TRAIL, AUDIT_FILE_DEST, AUDIT_SYS_OPERATIONS, and AUDIT_SYSLOG_LEVEL. So if your AUDIT_TRAIL is set to "DB", you can still use all the expected data dictionary views to obtain your audit information. If AUDIT_TRAIL is set to "OS", your auditing information will be sent to the location specified by the parameter AUDIT_FILE_DEST.

To enable "pure" Unified Auditing

1. Shutdown the database:

shutdown immediate

2. Relink the Oracle database binaries:

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

3. Enable at least one unified audit policy. By default, two unified auditing policies are created when you create your a 12.2 database: ORA_SECURECONFIG and ORA_LOGON_FAILURES. The first one is enabled by the default, the last one is not. Let's enable the ORA_LOGIN_FAILURES, too:

audit policy ORA_LOGON_FAILURES;

Verify:

select parameter, value from v$option where parameter='Unified Auditing';

PARAMETER	VALUE
Unified Auditing	TRUE

select *
from audit_unified_enabled_policies;

POLICY_NAME	ENABLED_OPTION	ENTITY_NAME	ENTITY_TYPE	SUCCESS	FAILURE
ORA_LOGON_FAILURES	BY USERS	ALL USERS	USER	YES	YES
ORA_SECURECONFIG	BY USERS	ALL USERS	USER	YES	YES

If v$option shows TRUE for Unified Auditing AND we have at least one enabled unified auditing policy, we are using "Pure" Unified Auditing. 

It doesn't matter what all the Traditional Auditing parameters are set to at this point; they will not have any effect. 

Your audit information will from now on be written to the table AUDSYS.AUD$UNIFIED.

The SYS.AUD$ and SYS.FGA_LOG$ tables will still be accessible, but not used by the Oracle instance. They will only contain auditing records from before unified auditing was enabled. Consequently, your previously used queries based on familiar data dictionary views such as dba_audit_trail will only return information from before Unified Auditing was enabled.

The Oracle documentation provides a table which is very helpfull in determining the pros and cons of migrating to Unified Auditing. 

In my opinion, the most important drawback with Unfied Auditing is that it doesn't allow the auditing data to be written to the operating system.

To enable traditional Auditing

1. First, disable any unified audit policies that are currently enabled. Find the currently enabled policies:

select user_name, policy_name, enabled_opt, enabled_option
from audit_unified_enabled_policies;

USER_NAME	POLICY_NAME	ENABLED_OPT	ENABLED_OPTION
ALL USERS	ORA_LOGON_FAILURES	BY	BY USER
ALL USERS	ORA_SECURECONFIG	BY	BY USER

2. Take them out of audit. This step prevents the database from going into mixed mode auditing after you complete this procedure:

noaudit policy ORA_SECURECONFIG;
noaudit policy ORA_LOGON_FAILURES;

3. Shutdown the database:

shutdown immediate

4. Relink the Oracle database binaries:

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

5. Start the database

sqlplus / as sysdba
startup

The database should now be in Traditional Auditing mode. There will be no more entries logged to the unified_audit_trail. Your audit records will go to the SYS.AUD$ and SYS.FGA_LOG$ tables, or to the operating system, depending on your value for the parameter AUDIT_TRAIL.

More about disabling unified auditing policies can be found here

Another good source for more information about Unified Auditing is this article found at oracle-base.com

How to bulk-grant privileges to a selection of users using PL/SQL

This anonymous PL/SQL script will select all the desired users, and then grant SELECT on a number of performance views to these.

set serveroutput on

DECLARE

 CURSOR c1 IS
  select username
  from dba_users
  where username like 'IT%'
  or username like 'MAITD%';

BEGIN
 FOR x IN c1 LOOP
    dbms_output.put_line('user ' || x.username || ' processed.' );
    execute immediate('GRANT SELECT ON SYS.IND$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.OBJ$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.TAB$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.USER$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$DATABASE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$INSTANCE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$LATCH TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$LIBRARYCACHE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$MYSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$PROCESS TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$ROWCACHE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESSION TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESS_IO TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SGASTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$STATNAME TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SYSSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SYSTEM_EVENT TO ' || x.username);
 END LOOP;
END;
/

How to check for table partitions using PL/SQL

I created the following piece of code a long time ago.
It checks if a table is partitioned or not, and list its partition.
It takes the table name and its owner as parameters, and list it.

If you need a template for a simple script using an anonymous PL/SQL block, I assume it could serve a purpose.

For what it's worth, here it is:

set serveroutput on
DECLARE
  p_table_name  varchar2(100) := '&table_name';
  p_table_owner varchar2(100) := '&owner';
  p_count       number        := 0;
BEGIN
    SELECT count(*) into p_count
    FROM   dba_tab_partitions
    WHERE  table_name   = p_table_name
    AND    table_owner  = p_table_owner;
  IF (p_count = 0) THEN
    dbms_output.put_line('There were no partitions on table ' || p_table_name );
  ELSE
     For l_rec in ( 
     
        select p.owner, p.table_name, t.partition_name, t.TABLESPACE_NAME ,p.partitioning_type
        from  dba_part_tables p, dba_tab_partitions t
        where p.table_name = p_table_name
        and   p.owner = t.TABLE_OWNER
        and   t.TABLE_NAME = p.TABLE_NAME
        )
     LOOP
     dbms_output.put_line(l_rec.partition_name || '    ' || l_rec.tablespace_name || '  ' || l_rec.partitioning_type);
     END LOOP;
  END IF;
END;
/

How to check if Unified Auditing is enabled in your database

Check that Unified Auditing is enabled with the following query:

col parameter format a20
col value format a20
set lines 200
select parameter, value from v$option where parameter='Unified Auditing';
exit

Output shows that it is enabled:

PARAMETER            VALUE
-------------------- --------------------
Unified Auditing     TRUE

What is "Automatic Reoptimization" introduced in Oracle 12cR1?

Some definitions here, mostly taken directly from Oracls documentation, or quoted from Tim Hall's www.oracle-base.com

Automatic Reoptimization

Automatic Reoptimization is the ability of the optimizer to automatically change a plan on subsequent executions of a SQL statement.

Automatic reoptimization takes two forms: Statistics Feedback and Performance Feedback.

Statistics feedback

Statistics Feedback (formerly known as cardinality feedback and first introduced in Oracle 11gR2) is a type of reoptimization that automatically improves plans for repeated queries that have cardinality misestimates.

• At the end of an execution, the optimizer compares its initial cardinality estimates to the actual number of rows returned by each operation in the plan during execution. 
• If estimates differ significantly from actual cardinalities, then the optimizer stores the correct estimates for subsequent use and the statement is marked as "reoptimizable". 
• When the query executes again, the optimizer uses the corrected cardinality estimates instead of its usual estimates, allowing a better plan to be determined
• Statistics Feedback is statement specific and is lost if the instance is restarted or the statement is aged out of the shared pool.

Performance Feedback

This form of automatic reoptimization helps improve the degree of parallelism automatically chosen for repeated SQL statements when PARALLEL_DEGREE_POLICY is set to ADAPTIVE.

• At the end of an initial execution, the optimizer compares the degree of parallelism chosen by the optimizer with the degree of parallelism computed based on the performance statistics (for example, the CPU time) gathered during the actual execution of the statement
• If the two values vary significantly, then the database marks the statement for reparsing, and stores the initial execution  statistics as feedback. This feedback helps better compute the degree of parallelism for subsequent executions.

Note that even if PARALLEL_DEGREE_POLICY is not set to ADAPTIVE, statistics feedback may influence the degree of parallelism chosen for a statement.

In short, to remember it more easily:

• statistics feedback deals with incorrect cardinality
• performance feedback deals with incorrect parallel execution

Sources:

Oracle Documentation

www.oracle-base.com

How to make use of the GATHER_PLAN_STATISTICS hint

Use the gather_plan_statistics hint to view the optimizer's estimation of rows vs. the actual returned number of rows.

Notice the case - gather_plan_statistics - I had trouble getting it to work when stated in capital letters as GATHER_PLAN_STATISTICS. So stick to lower case!

Use the hint in the SELECT part of your query, for example:

select /*+ gather_plan_statistics */
from mytable t1 join mytable t2....

set linesize 200
set pagesize 0
select * from table
(dbms_xplan.display_cursor(NULL,NULL,'TYPICAL ALLSTATS LAST'));
exit

Alternatively, after the query has run to completion, check the cursor cache for the details of your specific SQL ID, and use the display_cursor with the SQL ID explicitly set:

set linesize 200
set pagesize 0
select * from table
(dbms_xplan.display_cursor( 'dnyrjuumj8psq',1,'TYPICAL ALLSTATS LAST'));
exit

Either way will work and give you the following example output:



Plan hash value: 4147659309

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |         |      1 |        |       |  1862 (100)|          |   3012 |00:08:14.63 |     576K|    553K|       |       |          |
|   1 |  SORT ORDER BY                           |         |      1 |      2 |   294 |  1862   (1)| 00:00:01 |   3012 |00:08:14.63 |     576K|    553K|   372K|   372K|  330K (0)|
|*  2 |   FILTER                                 |         |      1 |        |       |            |          |   3012 |00:08:45.50 |     576K|    553K|       |       |          |
|   3 |    NESTED LOOPS                          |         |      1 |      2 |   294 |  1861   (1)| 00:00:01 |   3012 |00:08:45.50 |     576K|    553K|       |       |          |
|   4 |     NESTED LOOPS                         |         |      1 |      2 |   294 |  1861   (1)| 00:00:01 |   3012 |00:08:45.34 |     573K|    553K|       |       |          |
|   5 |      NESTED LOOPS                        |         |      1 |      2 |   270 |  1859   (1)| 00:00:01 |   3012 |00:08:45.31 |     573K|    553K|       |       |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T1      |      1 |      1 |    31 |     3   (0)| 00:00:01 |    439 |00:00:00.27 |      89 |     73 |       |       |          |
|*  7 |        INDEX RANGE SCAN                  | T1_IDX4 |      1 |      4 |       |     2   (0)| 00:00:01 |    440 |00:00:00.01 |       3 |      1 |       |       |          |
|*  8 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2      |    439 |      2 |   208 |  1856   (1)| 00:00:01 |   3012 |00:08:54.15 |     573K|    553K|       |       |          |
|*  9 |        INDEX RANGE SCAN                  | T2_IDX4 |    439 |  23118 |       |    69   (0)| 00:00:01 |     12M|00:00:54.73 |   38436 |  37439 |       |       |          |
|* 10 |      INDEX UNIQUE SCAN                   | T3_IDX1 |   3012 |      1 |       |     0   (0)|          |   3012 |00:00:00.01 |       9 |      2 |       |       |          |
|  11 |     TABLE ACCESS BY INDEX ROWID          | T3      |   3012 |      1 |    12 |     1   (0)| 00:00:01 |   3012 |00:00:00.01 |    3012 |      2 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Notice the column "E-Rows". It deviates substantially from the column A-Rows, which are the actual rows returned.



So the optimizer is clearly not able to make a good estimation about the number of rows.



Let's add a couple of statements which will give the optimizer better statistics. This particular database is not certified with any of the 12.1 adaptive tuning features, so I will have to feed the optimizer using other methods:



alter session set statistics_level='ALL';
alter session set optimizer_dynamic_sampling=11;


After running it a second time, the following plan is generated:



Plan hash value: 1509429641

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |        |      1 |        |       |   735K(100)|          |   3012 |00:09:44.25 |     572K|    551K|       |       |          |
|   1 |  SORT ORDER BY                           |        |      1 |   2986 |   428K|   735K  (1)| 00:00:29 |   3012 |00:09:44.25 |     572K|    551K|   372K|   372K|  330K (0)|
|*  2 |   FILTER                                 |        |      1 |        |       |            |          |   3012 |00:09:44.25 |     572K|    551K|       |       |          |
|*  3 |    HASH JOIN                             |        |      1 |   2986 |   428K|   735K  (1)| 00:00:29 |   3012 |00:09:44.25 |     572K|    551K|  1066K|  1066K|  954K (0)|
|   4 |     NESTED LOOPS                         |        |      1 |   2986 |   393K|   735K  (1)| 00:00:29 |   3012 |00:09:43.01 |     569K|    547K|       |       |          |
|   5 |      NESTED LOOPS                        |        |      1 |     10M|   393K|   735K  (1)| 00:00:29 |     12M|00:00:38.92 |   38488 |  37114 |       |       |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T1     |      1 |    439 | 13609 |    72   (0)| 00:00:01 |    439 |00:00:00.01 |      76 |      0 |       |       |          |
|*  7 |        INDEX RANGE SCAN                  | T1_IDX4|      1 |    440 |       |     2   (0)| 00:00:01 |    440 |00:00:00.01 |       3 |      0 |       |       |          |
|*  8 |       INDEX RANGE SCAN                   | T2_IDX4|    439 |  23118 |       |    69   (0)| 00:00:01 |     12M|00:00:28.47 |   38412 |  37114 |       |       |          |
|*  9 |      TABLE ACCESS BY INDEX ROWID         | T2     |     12M|      7 |   728 |  1856   (1)| 00:00:01 |   3012 |00:08:47.65 |     531K|    510K|       |       |          |
|  10 |     TABLE ACCESS FULL                    | T3     |      1 |  74866 |   877K|   572   (1)| 00:00:01 |  74866 |00:00:01.22 |    3022 |   3017 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Consider the same column - the E-Rows and the A-Rows, and how much more accurate the optimizer is estimating the rows to be returned. 

Notice also that the former of these two plans generates an index lookup on the T3 table, while the ladder generates a full table scan. The FTS proved to be the most efficient, since the runtime dropped from 15 to 9 minutes.




NOTE:
You can only get the results you expect when you actually execute the statement. 
If you try to add the hint along with the EXPLAIN PLAN FOR statement, you will see warnings as these in your output:


Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level



These will of course be shown even if you do set the statistics_level to ALL. 
As Mathew McPeak points out in an aswer on stackoverflow.com

The way you are using it, you are printing the plan of the last statement you explained, not the last statement you executed. And "explain" will not execute the query, so it will not benefit from a gather_plan_statistics hint.