How to delete a specific line from a file, or all characters following a specific character using sed

If you want to search a file for a particular string, and then delete that line, use sed, like this:

sed -i "/_use_column_stats_for_function/d" init.ora

In bash, using the double quotation marks was the only way to make this work when using a variable instead of a fixed string.

To delete a specific line number, in this case line number 7:

 sed -i '7d' myfile.txt
 

To delete lines 7 and 8:

 sed -i '7,8d' insert_data.sql

To delete everything after a specific character, for example #:

sed -i "s/#.*//g" /tmp/autoupgrade.ora

How to find the currently enabled unified auditing policies in an Oracle 12c database

Oracle 12c comes with two auditing policies enabled by default: ORA_SECURECONFIG and ORA_LOGON_FAILURES.
You can find the currently enabled policies in the AUDIT_UNIFIED_ENABLED_POLICIES view.

The query below will reveal what the currently enabled policies will actually audit

select policy_name,audit_option,condition_eval_opt
from audit_unified_policies
where policy_name in (  select policy_name 
                        from audit_unified_enabled_policies);
                        

The result shows that the default Unfied Auditing Setup covers many of the actions you would certainly want to audit, like DROP USER, ALTER SYSTEM etc:

POLICY_NAME	AUDIT_OPTION	CONDITION_EVAL_OPT
ORA_SECURECONFIG	LOGMINING	NONE
ORA_SECURECONFIG	TRANSLATE ANY SQL	NONE
ORA_SECURECONFIG	EXEMPT REDACTION POLICY	NONE
ORA_SECURECONFIG	PURGE DBA_RECYCLEBIN	NONE
ORA_SECURECONFIG	ADMINISTER KEY MANAGEMENT	NONE
ORA_SECURECONFIG	DROP ANY SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	ALTER ANY SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	CREATE ANY SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	CREATE SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	CREATE EXTERNAL JOB	NONE
ORA_SECURECONFIG	CREATE ANY JOB	NONE
ORA_SECURECONFIG	GRANT ANY OBJECT PRIVILEGE	NONE
ORA_SECURECONFIG	EXEMPT ACCESS POLICY	NONE
ORA_SECURECONFIG	CREATE ANY LIBRARY	NONE
ORA_SECURECONFIG	GRANT ANY PRIVILEGE	NONE
ORA_SECURECONFIG	DROP ANY PROCEDURE	NONE
ORA_SECURECONFIG	ALTER ANY PROCEDURE	NONE
ORA_SECURECONFIG	CREATE ANY PROCEDURE	NONE
ORA_SECURECONFIG	ALTER DATABASE	NONE
ORA_SECURECONFIG	GRANT ANY ROLE	NONE
ORA_SECURECONFIG	DROP PUBLIC SYNONYM	NONE
ORA_SECURECONFIG	CREATE PUBLIC SYNONYM	NONE
ORA_SECURECONFIG	DROP ANY TABLE	NONE
ORA_SECURECONFIG	ALTER ANY TABLE	NONE
ORA_SECURECONFIG	CREATE ANY TABLE	NONE
ORA_SECURECONFIG	DROP USER	NONE
ORA_SECURECONFIG	CREATE USER	NONE
ORA_SECURECONFIG	AUDIT SYSTEM	NONE
ORA_SECURECONFIG	ALTER SYSTEM	NONE
ORA_LOGON_FAILURES	LOGON	NONE
ORA_SECURECONFIG	CREATE DATABASE LINK	NONE
ORA_SECURECONFIG	DROP DATABASE LINK	NONE
ORA_SECURECONFIG	ALTER USER	NONE
ORA_SECURECONFIG	CREATE ROLE	NONE
ORA_SECURECONFIG	DROP ROLE	NONE
ORA_SECURECONFIG	SET ROLE	NONE
ORA_SECURECONFIG	CREATE PROFILE	NONE
ORA_SECURECONFIG	DROP PROFILE	NONE
ORA_SECURECONFIG	ALTER PROFILE	NONE
ORA_SECURECONFIG	ALTER ROLE	NONE
ORA_SECURECONFIG	CREATE DIRECTORY	NONE
ORA_SECURECONFIG	DROP DIRECTORY	NONE
ORA_SECURECONFIG	ALTER DATABASE LINK	NONE
ORA_SECURECONFIG	CREATE PLUGGABLE DATABASE	NONE
ORA_SECURECONFIG	ALTER PLUGGABLE DATABASE	NONE
ORA_SECURECONFIG	DROP PLUGGABLE DATABASE	NONE
ORA_SECURECONFIG	EXECUTE	NONE

If the $ORACLE_HOME/rdbms/admin/secconf.sql script was created during database creation, you will have some other policies in your database, too, but they won't be enabled by default. These can be found by executing the following statement:

select policy_name,count(audit_option) "number of audits"
from AUDIT_UNIFIED_POLICIES
where policy_name not in (select unique policy_name from audit_unified_enabled_policies )
group by policy_name
order by 2 desc;

In my database, the following collection shows up as being created, but not yet enabled:

POLICY_NAME	number of audits
ORA_RAS_POLICY_MGMT	33
ORA_CIS_RECOMMENDATIONS	26
ORA_RAS_SESSION_MGMT	14
ORA_ACCOUNT_MGMT	9
ORA_DATABASE_PARAMETER	3

How to use the dbms_explan.display_cursor function to find the execution plan of a query in the Cursor Cache

set linesize 200
set pagesize 0
select * from table
(dbms_xplan.display_cursor( '0syc4upspr638',3,'TYPICAL'));
exit

Where
- the first argument is the SQL id
- the second argument cursor child number
- the third argument is the format string. TYPICAL is the default value.


See the Oracle Documentation for more information on how to use dbms_xplan

How to migrate a non-CDB database to a PDB on the same host

There are many ways to migrate your non-cdb Oracle databases to the new Multitenant Architecture. Here I will show you how to clone a non-cdb database to a PDB running in a container database

Asumptions:
You have two Oracle databases of version 12.1 or higher running on the same server:

1. Your original, non-cdb database called db01
2. Your new container database called cdb01
Both of these databases are running out of Oracle Home installe in /u01/oracle/product/12c

Step 1: For Oracle 12.1, open your non-CDB in read only mode (not needed from version 12.2 and onwards):

shutdown immedate
startup mount
alter database open read only;

Step 2: create an xml file that describes the non-CDB database using the package dbms_pdb:

export ORACLE_SID=db01
sqlplus / as sysdba

Generate the file:

set serveroutput on
begin
  dbms_pdb.describe( pdb_descr_file => '/tmp/ncdb.xml');
end;
/

Step 3: Create the pluggable database

Connect to your CDB, and create the PDB using the script you created in step 2.

export ORACLE_SID=cdb01
sqlplus / as sysdba

create pluggable database pdb01
using '/tmp/ncdb.xml'
copy
file_name_convert = ('/u02/oradata/db01/', '/u02/oradata/cdb01/PDBS/pdb01/');

Note that I am choosing to copy the files from the original location of the non-CDB database, to a brand new one, using the directive file_name_convert. There are other options, too: MOVE and NOCOPY

Step 4: execute $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

sqlplus / as sysdba
alter session set container=PDB01;
@?/rdbms/admin/noncdb_to_pdb.sql

I did receive errors during this phase:

ORA-65172: cannot run noncdb_to_pdb.sql unless pluggable database is an
unconverted non-container database
ORA-06512: at "SYS.DBMS_PDB", line 154
ORA-06512: at line 1

The error message seemed to be harmless:

oracle@myserver.mydomain.com:[cdb1]# oerr ora 65172
65172, 00000, "cannot run noncdb_to_pdb.sql unless pluggable database is an unconverted non-container database"
// *Cause:  An attempt was made to run 'noncdb_to_pdb.sql' on a pluggable
//          database (PDB) that was not an unconverted non-container database.
// *Action: 'noncdb_to_pdb.sql' is not necessary for this PDB.
//

Further research showed 1) the Pdb was indeed created, and 2) that there were errors in PDB_PLUG_IN_VIOLATIONS:

select PDB_ID,PDB_NAME,STATUS,CON_ID from cdb_pdbs

    PDB_ID PDB_NAME                       STATUS                          CON_ID
---------- ------------------------------ --------------------------- ----------
         2 PDB01                          NEW                                  2

alter session set container=cdb$root;


SELECT TO_CHAR(TIME,'dd.mm.yyyy hh24:mi') "time",NAME,STATUS,MESSAGE 
FROM PDB_PLUG_IN_VIOLATIONS;

Result:

time	NAME	STATUS	MESSAGE
20.09.2017 14:17	PDB01	PENDING	Database option CATJAVA mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17	PDB01	PENDING	Database option CONTEXT mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17	PDB01	PENDING	Database option JAVAVM mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17	PDB01	PENDING	Database option ORDIM mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17	PDB01	PENDING	Database option XML mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17	PDB01	PENDING	Sync PDB failed with ORA-65177 during 'alter user sys identified by *'
20.09.2017 14:17	PDB01	PENDING	Sync PDB failed with ORA-65177 during 'alter user system identified by *'

A quick search on Oracles support site revealed that these errors can be ignored. See Doc ID 2020172.1 "OPTION WARNING Database option mismatch: PDB installed version NULL" in PDB_PLUG_IN_VIOLATIONS"

Finally, remember to open your pdb in read write mode. It was in MIGRATE mode after the noncdb_to_pdb.sql script had been run and failed:

 select con_id,name,open_mode from v$containers;

    CON_ID NAME      OPEN_MODE
---------- --------- -----------------------------
         1 CDB$ROOT  READ WRITE
         2 PDB01     MIGRATE

alter session set container=PDB01;

alter pluggable database close;

Pluggable database altered.

alter pluggable database open;

 select con_id,name,open_mode from v$containers;

    CON_ID NAME      OPEN_MODE
---------- --------- -----------------------------
         2 PDB01     READ WRITE

Oracle Multitentant licensing

For Oracle 12c, you don't need to purchase an additional license when running a Multitenant database shop with only 1 - one - PDB. This configuration is also refered to as "single tenant".

Franck Pachot is pointing this out in a community discussion:


You don't need an option when you have only one pluggable database.
You can detect violation with:

select name, aux_count from dba_feature_usage_statistics where name like '%Pluggable%' or name like '%Multitenant%';

If AUX_COUNT is >1 then you need to buy the option, or drop the additional PDBs

In 12.2 you can set MAX_PDBS to 1 to be sure nobody creates more than allowed.
Here are the features you can use in this configuration (know as 'single-tenant'): http://www.slideshare.net/pachot/12cr2-singletenant-multitenant-features-for-all-editions


In Oracle 19c, the MAX_PDBS can be set to 3 without breaking the license.

A workaround for ORA-06553: PLS-213: package STANDARD not accessible when using datapatch in a CDB

Short background:

I was having trouble applying "patch 26550023 - COMBO of OJVM Component 12.1.0.2.170718 DB PSU + DB PSU 12.1.0.2.170814" in my Multitenant environment. The container database only had one PDB at the time, the PDB$SEED.

After having successfully applied opatch apply for both patches, I ran datapatch -verbose to load modified SQL into the database. I had already opened my container database in upgrade mode, and also opened the PDB$SEED in upgrade mode by executing

alter pluggable database all open upgrade;

The state of the PDB$SEED could be confirmed in the alert log, as well as from v$pdb:

SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
--------- --------------
PDB$SEED   MIGRATE

Still, I kept getting weird errors like


Bootstrapping registry and package to current versions...done
Error in bootstrap log /u01/oracle/cfgtoollogs/sqlpatch/sqlpatch_17273_2017_09_13_13_29_29/bootstrap1_CDBVEG_PDBSEED.log:
Error at line 7: ORA-06553: PLS-213: package STANDARD not accessible
Error at line 17: ORA-06553: PLS-213: package STANDARD not accessible
Error at line 25: SP2-0310: unable to open file "/u01/oracle/product/12102/sqlpatch/FALSE.sql"
Prereq check failed, exiting without installing any patches.


There was little information about the problem and potential workarounds to be found on the internet.

After trying different options without success, I could find no other solution than to drop the PDB$SEED container, so that patching could continue.

Here's how:

SQL> 
-- necessarry to avoid "ORA-65017: seed pluggable database may not be dropped or altered"
alter session set "_oracle_script"=TRUE;

Session altered.

SQL> alter pluggable database PDB$SEED close;

Pluggable database altered.

SQL> drop pluggable database pdb$seed including datafiles;

Pluggable database dropped.

SQL> alter session set "_oracle_script"=FALSE;

Session altered.

SQL> select * from cdb_pdbs;

no rows selected

After this point, run datapatch again:

oracle@myserver:[cdbveg]# datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Wed Sep 13 13:34:05 2017
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/oracle/cfgtoollogs/sqlpatch/sqlpatch_18072_2017_09_13_13_34_05/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 26027162 (Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017)):
  Installed in the binary registry only
Bundle series PSU:
  ID 170814 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT
    Nothing to roll back
    The following patches will be applied:
      26027162 (Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017))
      26609783 (DATABASE PATCH SET UPDATE 12.1.0.2.170814)

Installing patches...
Patch installation complete.  Total patches installed: 2

Validating logfiles...
Patch 26027162 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/oracle/cfgtoollogs/sqlpatch/26027162/21319014/26027162_apply_CDBVEG_CDBROOT_2017Sep13_13_34_18.log (no errors)
Patch 26609783 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/oracle/cfgtoollogs/sqlpatch/26609783/21481899/26609783_apply_CDBVEG_CDBROOT_2017Sep13_13_34_18.log (no errors)
SQL Patching tool complete on Wed Sep 13 13:34:52 2017

Verification that the patches are applied:

SQL> select ACTION,DESCRIPTION,STATUS,BUNDLE_SERIES from registry$sqlpatch;

ACTION     DESCRIPTION                                                            STATUS               BUNDLE_SERIES
---------- ---------------------------------------------------------------------- -------------------- --------------------
APPLY      Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017)        SUCCESS
APPLY      DATABASE PATCH SET UPDATE 12.1.0.2.170814                              SUCCESS              PSU

How to attach an ORACLE_HOME to an existing inventory

I wanted to check the patchlevel in one of my Oracle installations, and the following errow was returned:

oracle@tsl0map-dbteam-sandbox-db04:[vegdb01]# opatch lsinventory
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/12102
Central Inventory : /home/oracle/oraInventory
   from           : /u01/oracle/product/12102/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /u01/oracle/product/12102/cfgtoollogs/opatch/opatch2017-09-12_15-30-47PM_1.log

List of Homes on this system:

  Home name= agent13c2, Location= "/u01/oracle/product/agent13c/agent_13.2.0.0.0"
  Home name= 11204, Location= "/u01/oracle/product/11204"
  Home name= OraHome3, Location= "/u01/oracle/product/gg121"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

Turned out my inventory had not been updated with my new Oracle Home. When I looked in the inventory.xml file on my server, there was no entry for the installation there.

To fix this, add the new OH to your inventory. From your OH that is missing, do the following:

cd $ORACLE_HOME/oui/bin
./runInstaller -invPtrLoc /u01/oracle/product/12102/oraInst.loc -attachHome ORACLE_HOME=/u01/oracle/product/12102 ORACLE_HOME_NAME="Ora12cHome"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8191 MB    Passed
The inventory pointer is located at /u01/oracle/product/12102/oraInst.loc
'AttachHome' was successful.

Check the inventory file now, and you'll see a new entry for the OH (in my case the Ora12cHome):

<HOME NAME="agent13c2" LOC="/u01/oracle/product/agent13c/agent_13.2.0.0.0" TYPE="O" IDX="8"/>
<HOME NAME="11204" LOC="/u01/oracle/product/11204" TYPE="O" IDX="1"/>
<HOME NAME="Ora12cHome" LOC="/u01/oracle/product/12102" TYPE="O" IDX="9"/>