Oracle Knowledge Table

Thursday, September 26, 2013

Report changes with ENABLE_DDL_LOGGING=TRUE

The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of the Database Lifecycle Management Pack when set to TRUE.

When set to TRUE, the database reports schema changes in real time into the DDL alert log.

SQL> show con_id

CON_ID

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

SQL> show parameter enable_ddl_logging

NAME TYPE VALUE

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

enable_ddl_logging boolean FALSE

SQL> alter system set enable_ddl_logging=TRUE scope=memory;

System altered.

SQL> show parameter enable_ddl_logging;

NAME TYPE VALUE

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

enable_ddl_logging boolean TRUE

The alert log show the change as expected:

Thu Sep 26 15:39:47 2013

ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=MEMORY;

SQL> alter session set container=pdb1;

Session altered.

SQL> create user newuser identified by newuser;

User created.

SQL> grant connect, resource, create table, unlimited tablespace to newuser;

Grant succeeded.

SQL> create table newuser.table1 (col1 number(9));

Table created.

SQL> alter table newuser.table1 add col2 varchar2(10);

Table altered.

SQL> drop user newuser cascade;

User dropped.

SQL> create user abc identified by abc;

User created.

SQL> drop user abc cascade;

User dropped.

[oracle@magenta log]$ pwd

/u01/app/oracle/diag/rdbms/orcl12c/orcl12c/log

[oracle@magenta log]$ ll

total 16

drwxr-x---. 2 oracle oinstall 4096 Sep 26 15:46 ddl

-rw-r-----. 1 oracle oinstall 203 Sep 26 15:51 ddl_orcl12c.log

drwxr-x---. 2 oracle oinstall 4096 Jul 29 14:07 debug

drwxr-x---. 2 oracle oinstall 4096 Jul 29 14:07 test

[oracle@magenta log]$ tail -f ddl_orcl12c.log

Thu Sep 26 15:46:13 2013

diag_adl:create table newuser.table1 (col1 number(9))

diag_adl:alter table newuser.table1 add col2 varchar2(10)

diag_adl:drop user newuser cascade

diag_adl:drop user abc cascade

When ENABLE_DDL_LOGGING is set to true, the following DDL statements are written to the DDL alert log:

ALTER/CREATE/DROP/TRUNCATE CLUSTER

ALTER/CREATE/DROP FUNCTION

ALTER/CREATE/DROP INDEX

ALTER/CREATE/DROP OUTLINE

ALTER/CREATE/DROP PACKAGE

ALTER/CREATE/DROP PACKAGE BODY

ALTER/CREATE/DROP PROCEDURE

ALTER/CREATE/DROP PROFILE

ALTER/CREATE/DROP SEQUENCE

CREATE/DROP SYNONYM

ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE

ALTER/CREATE/DROP TRIGGER

ALTER/CREATE/DROP TYPE

ALTER/CREATE/DROP TYPE BODY

DROP USER

ALTER/CREATE/DROP VIEW

Thursday, September 5, 2013

DB12c - when THREADED_EXECUTION=TRUE native authentication is not supported

When threaded execution is enabled (threaded_execution=TRUE) in the initialization parameter file on UNIX platforms, native authentication (that is, connect / as sysdba or connect /) is not supported.

Workaround: Use password-based authentication. Native authentication will not work for connections where the server execution is a thread.

SQL> select banner from v$version where rownum<=1;

BANNER

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

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> select platform_name from v$database;

PLATFORM_NAME

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

Linux x86 64-bit

SQL> show parameter threaded_execution;

NAME TYPE VALUE

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

threaded_execution boolean FALSE

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@xxxxx]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Thu Sep 5 19:14:48 2013

SQL> connect / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> alter system set threaded_execution=TRUE scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-01017: invalid username/password; logon denied

SQL> connect / as sysdba

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> exit

[oracle@xxxxx]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Thu Sep 5 19:17:18 2013

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> show user

USER is "SYS"

SQL> show parameter threaded_execution

NAME TYPE VALUE

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

threaded_execution boolean TRUE

This is logged as Bug 13877504

Monday, September 2, 2013

Gather statistics of SYS objects

There are couple interesting notes on Metalink:
GATHER_FIXED_OBJECTS_STATS Considerations [ID 798257.1]
Also, Metalink note How to Gather Statistics on SYS Objects and ‘Fixed’ Objects? [ID 457926.1]
mentions that “statistics gathering should be done when database has a representative load so that the statistics reflect the normal database activity”

There are some fixed tables that are simply very volatile by nature and it will be extremely hard to get accurate statistics on.
Still, better with than without.

The place I would look is how much time is spent for recursive sql, which basically touches the dictionary tables.

So how often to gather??
It depends. It depends on the load profile, if you regularly add more users (concurrency) in your application,
then gathering system statistics shall be in accordance to that.

RMAN 12c: System trigger to open PDBs causing table recovery to fail with ORA-65019, RMAN-11003

In Oracle Database 12c, while trying to perform a table recovery, I hit the below error:

RMAN-11003: failure during parse/execution of SQL statement: alter pluggable database PDB_RMAN open
ORA-65019: pluggable database PDB_RMAN already open

The command used was:

RECOVER TABLE TEST.TEST OF PLUGGABLE DATABASE PDB_RMAN UNTIL SCN 3106312 AUXILIARY DESTINATION '/u01/app/oracle/test';

I had all the pre-requisites validated:
• The target database was in read-write mode.
• The target database was in ARCHIVELOG mode.
• RMAN full backup taken previously the drop command

For the information, I had created an empty pluggable database, created a table in it, performed full backup at CDB level.
I then dropped the table and tried to recover it.

I have previously created a system trigger to start all my pluggable database when CDB is open.

Full section of the error is the below:

contents of Memory Script:
{
sql clone 'alter pluggable database PDB_RMAN open';
}
executing Memory Script

sql statement: alter pluggable database PDB_RMAN open

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /u01/app/oracle/test/JVGF_PITR_PDB_RMAN_CDB1/datafile/o1_mf_test_916xmk0w_.dbf deleted
auxiliary instance file /u01/app/oracle/test/CDB1/datafile/o1_mf_sysaux_916xkgf6_.dbf deleted
auxiliary instance file /u01/app/oracle/test/CDB1/datafile/o1_mf_system_916xkgh9_.dbf deleted
auxiliary instance file /u01/app/oracle/test/CDB1/datafile/o1_mf_sysaux_916xhq2h_.dbf deleted
auxiliary instance file /u01/app/oracle/test/CDB1/datafile/o1_mf_undotbs1_916xhq31_.dbf deleted
auxiliary instance file /u01/app/oracle/test/CDB1/datafile/o1_mf_system_916xhq2x_.dbf deleted
auxiliary instance file /u01/app/oracle/test/CDB1/controlfile/o1_mf_916xhgl9_.ctl deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/20/2013 15:00:54
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 08/20/2013 15:00:54
RMAN-11003: failure during parse/execution of SQL statement: alter pluggable database PDB_RMAN open
ORA-65019: pluggable database PDB_RMAN already open

In short, what happens during table recovery is the following:
- Oracle creates an automatic (auxiliary) instance
- restores the backup into it and performes a media recovery
- exports from the auxiliary instance using EXPDP the table you want to recover
- imports into your pluggable database using IMPDP

The issue appears when you have a system trigger at CDB level which opens all PDB or at least the pluggable database where you have the issue with
the table you want to recover.

The workaround is to create an auxiliary parameter file /tmp/initaux.ora with the following line in it:
_system_trig_enabled=FALSE

Then, while invoking RMAN, set this auxiliary instance parameter file before recovering the table

RMAN> set auxiliary instance parameter file to '/tmp/initaux.ora';
RMAN> recover table test.test....

The error will no longer appear and you will be able to complete the recovery of the table.

This is documented as the following bug:
Bug 16822622 : PDB TABLESPACE PITR FAILS WITH EXISTING TRIGGER OPENING PDBS

Friday, August 23, 2013

SQL*Plus DEFINE - versatile usage to pass multiple values to an IN clause

SQL*Plus DEFINE specifies a user or predefined variable and assigns a CHAR value to it, or lists the
value and variable type of a single variable or all variables.

Whenever you run a stored query or script, SQL*Plus substitutes the value of variable
for each substitution variable referencing variable (in the form &variable or &&variable).
SQL*Plus will not prompt you for the value of variable in this session until you
UNDEFINE variable.

DEFINE is very versatile when it comes to handle multiple values.
Say you want to define a variable which contains multiple values that you plan to use with an IN operator.

Tested in Oracle Database 12c, but works also in 11g or 10g
SQL> select banner from v$version where rownum =1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

This is as easy as:

SQL> define own = "'SYS', -
> 'SYSTEM', -
> 'NULL'"

This is interpreted as it follows;

SQL> define own
DEFINE OWN = "'SYS', 'SYSTEM', 'NULL'" (CHAR)

And now we will use it in a select:

SQL> select count(*), owner
2 from dba_objects
3 where owner in (&own)
4 group by owner;
old 3: where owner in (&own)
new 3: where owner in ('SYS', 'SYSTEM', 'NULL')

COUNT(*) OWNER
---------- ----------------------------------------
635 SYSTEM
41873 SYS

Sunday, August 18, 2013

EM Express configuration for new pluggable database

We are in 12c; 12.1.0.1.0.
We will show how to create new pluggable database from seed and configure EM Express for it.

Oracle Enterprise Manager Database Express (EM Express) is a web-based database management tool that is built inside the Oracle Database.
From an architectural perspective, EM Express has no mid-tier or middleware components, ensuring that its overhead on the database server is negligible.

C:\Users\hberca>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Aug 18 15:36:09 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

Oracle XML DB is Mandatory in 12c – You Cannot Uninstall it

SQL> column comp_name format a30
SQL> column status format a20
SQL> column version format a20
SQL> select comp_name,status,version
2 from dba_registry
3 where comp_name like '%XML%';

COMP_NAME STATUS VERSION
------------------------------ -------------------- -------------
Oracle XML Database VALID 12.1.0.1.0

The DBMS_XDB_CONFIG package provides an interface for configuring Oracle XML DB and its repository (settings for the http protocol server)
It updates the settings for xdbconfig.xsd.

PL/SQL package DBMS_XDB_CONFIG is the Oracle XML DB resource application program interface (API) for PL/SQL for DBAs to configure their system.
This API provides functions and procedures to access and manage Oracle XML DB Repository resources using PL/SQL.
It includes methods for managing resource security and Oracle XML DB configuration.

We are using the procedure USEDPORT - this obtains the port numbers used by other pluggable databases in the same consolidation database.
We can see we have only configured port 5500, which is the default port for cdb$root which has by default con_id = 1.

SQL> select dbms_xdb_config.usedport from dual;

USEDPORT
--------------------------------------------------------------------------------
<portlist>
<port>
<pdbid>1</pdbid>
<httpport2>5500</httpport2>
</por

We are now creating new pluggable database from seed. We will use the below command:

SQL> create pluggable database pdb123 admin user pdb123 identified by oracle file_name_convert = ('pdbseed', 'pdb123');

Pluggable database created.

After creation, a new pluggable database is in MOUNT mode. We will open it.

SQL> alter pluggable database pdb123 open read write;

Pluggable database altered.

We will now query the view v$pdbs which will show us all pluggable database in this container.
We see PDB123 has con_id=5.

SQL> select con_id, name, open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB1 MOUNTED
4 PDB_SAMPLE MOUNTED
5 PDB123 READ WRITE
6 Z READ WRITE

We are switching container to pdb123 in order to set the https port for EM express:

SQL> alter session set container=pdb123;

Session altered.

SQL> show con_id con_name

CON_ID
------------------------------
5

CON_NAME
------------------------------
PDB123

We will use SETHTTPSPORT procedure, this sets the HTTPS port to a new value, in our case 5502.

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5502);

PL/SQL procedure successfully completed.

We double check the port has been set using function GETHTTPSPORT:

SQL> select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
5502

If we attempt to view the usedports in the container from the pluggable database, an error will be raised:

SQL> select dbms_xdb_config.usedport from dual;
ERROR:
ORA-31120: usedPort cannot be invoked in a Pluggable Database
ORA-06512: at "XDB.DBMS_XDB_CONFIG", line 32

We will switch to CDB$ROOT.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> set long 10000
SQL> select dbms_xdb_config.usedport from dual;

USEDPORT
---------------------------------------------------------
<portlist>
<port>
<pdbid>1</pdbid>
<httpport2>5500</httpport2>
</port>
<port>
<pdbid>5</pdbid>
<httpport2>5502</httpport2>
</port>
</portlist>

We now see that pdbid 5, which is our newly create pluggable database pdb123 is allocated port 5502.

The USEDPORT procedure is also useful to find the used ports in case we are trying to allocate same port to another pluggable database. We will hit the error ORA-44718: Port conflict in XDB Configuration file.

SQL> alter session set container=z;

Session altered.

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5502);
BEGIN DBMS_XDB_CONFIG.SETHTTPSPORT(5502); END;

*
ERROR at line 1:
ORA-44718: Port conflict in XDB Configuration file
ORA-06512: at "XDB.DBMS_XDB", line 528
ORA-06512: at "XDB.DBMS_XDB_CONFIG", line 375
ORA-06512: at "XDB.DBMS_XDB_CONFIG", line 226
ORA-06512: at line 1

Reference:
Oracle® Database PL/SQL Packages and Types Reference
12c Release 1 (12.1)
E17602-14

Oracle® Database 2 Day DBA
12c Release 1 (12.1)
E17643-12

Wednesday, August 7, 2013

Create PDB with Sample schemas in DB12c

In Oracle Database 12c, Sample Schemas are delivered in an xml format as a template and at this point only dbca can use it.

This facilitates plugging and un-plugging into a CDB.

Invoke dbca, choose Manage Pluggable Databases.

Click Next to continue

Then select Create a Pluggable Database

Choose among the container database you have installed.

Now choose the option to Create Pluggable Database using PDB File Set.

Browse on Pluggable Database Metadata File and choose sampleschema.xml

Define the name for your Pluggable Database

Review the summary and click Install.

Installation is in progress

SQL> select banner from v$version;

BANNER

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

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

PL/SQL Release 12.1.0.1.0 - Production

CORE 12.1.0.1.0 Production

TNS for 64-bit Windows: Version 12.1.0.1.0 - Production

NLSRTL Version 12.1.0.1.0 – Production

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> select pdb_id, pdb_name, status from cdb_pdbs;

PDB_ID PDB_NAME STATUS

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

3 PDB1 NORMAL

2 PDB$SEED NORMAL

4 PDB_SAMPLE NORMAL

SQL> alter session set container=pdb_sample;

Session altered.

SQL> select * from (

2 select username, account_status from dba_users order by created desc)

3 where rownum <=7;

USERNAME ACCOUNT_STATUS

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

SCOTT EXPIRED & LOCKED

BI EXPIRED & LOCKED

SH EXPIRED & LOCKED

IX EXPIRED & LOCKED

PM EXPIRED & LOCKED

OE EXPIRED & LOCKED

HR EXPIRED & LOCKED

7 rows selected.