SQL*Developer now supplied with Oracle 12c

You will never have to search and download SQL*Developer. It's allready supplied in the 12c RDBMS home!

cd $ORACLE_HOME/sqldeveloper
./sqldeveloper.sh

Make sure you run in graphical mode (or use Xming, WinX-32 or something like that).

I found it was very slow on XMing to a remote database on vpn.... so maybe I won't use it much.

[oracle@myhost sqldeveloper]$ pwd
/opt/oracle/product/12.1.0/dbhome_1/sqldeveloper
[oracle@myhost sqldeveloper]$ ls -l
total 136
drwxr-xr-x.  4 oracle oinstall  4096 Sep 10 09:47 dataminer
-rw-r--r--.  1 oracle oinstall  1404 Nov  1  2012 icon.png
drwxr-xr-x.  8 oracle oinstall  4096 Sep 10 09:47 ide
drwxr-xr-x.  3 oracle oinstall  4096 Sep 10 09:47 javavm
drwxr-xr-x.  3 oracle oinstall  4096 Sep 10 09:47 jdbc
drwxr-xr-x.  6 oracle oinstall  4096 Sep 10 09:47 jdev
-rw-r--r--.  1 oracle oinstall    49 Nov 30  2011 jdev.label
drwxr-xr-x.  2 oracle oinstall  4096 Sep 10 09:48 jlib
drwxr-xr-x.  2 oracle oinstall  4096 Sep 10 09:48 jviews
drwxr-xr-x. 12 oracle oinstall  4096 Sep 10 09:48 modules
drwxr-xr-x.  3 oracle oinstall  4096 Sep 10 09:47 rdbms
-rw-r--r--.  1 oracle oinstall 36022 Nov  1  2012 readme.html
drwxr-xr-x.  3 oracle oinstall  4096 Sep 10 09:47 sleepycat
drwxr-xr-x.  8 oracle oinstall  4096 Sep 10 09:47 sqldeveloper
-rwxr-x---.  1 oracle oinstall 32768 Nov  1  2012 sqldeveloper.exe
-rwxr-x---.  1 oracle oinstall    71 Nov  1  2012 sqldeveloper.sh
drwxr-xr-x.  3 oracle oinstall  4096 Sep 10 09:47 sqlj
drwxr-xr-x.  2 oracle oinstall  4096 Sep 10 09:48 timingframework
-rw-r--r--.  1 oracle oinstall  2979 Nov 22  2011 view-source-paths.lis

Cannot add a second redaction policy using EM Cloud Control

Hi,

Using EM Cloud Control 12.1.0.3.0, I added a Redaction Policy (DBMS_REDACT) to a PAUL schema in a pluggable database. This worked fine and my social security numbers could not be read by user BOB.

        ID NAME      SS_NUMBER          INDATE
---------- --------- ------------------ ------------------------------
       100 matt      XXX-XX-1234        10-OCT-13 09.02.43.082299 AM
       110 bob       XXX-XX-3333        10-OCT-13 09.02.43.086830 AM

Now I wanted to add a second policy on another of PAUL's table. But the EM Cloud Control does not show the PAUL schema anymore, I cannot select the schema while for creating a policy.

I think this is a bug, reported it to MOS.

I'm curious if someone can replay this error. You will need 12.1 database, with container and pluggable database, and 12.1.0.3 EM Cloud Control.

Regards,
Paul

Basic steps upgrade to 12c

After reading the Upgrade Guide, you might like to create a short list of steps to take.
You are always responsible to check if the following steps are suitable for your situation. Always check the Upgrade Guide or consult Oracle Support.

This is how I manually upgraded a simple Dev database from 10.2 to 12.1 (no multi-tenant)

• Run the <12c-home>/rdbms/admin/preupgrd.sql on the old database.
• Check the $ORACLE_BASE/cfgtoollogs//preupgrade/preupgrade.log and fix whatever needs to be fixed
• Re-run preupgrd.sql to check after fixing.
• Shutdown old database.
• Move all datafiles, controlfiles, redologfiles etc to new host
• Move the init.ora to new host.
• Make changes in init.ora following preupgrade.log
• Create directories for e.g. audit_dump_dest
• SQL> startup Upgrade
• Go to OS prompt, cd $ORACLE_HOME/rdbms/admin
• $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql  (takes about 30-40mins)
• SQL> startup
• SQL> @?/rdbms/admin/utlrp    (to repair invalid objects)
• SQL> @?/rdbms/admin/utlu121s.sql   (to check status of components, should show VALID only)
• Backup database

Oracle 12c Architecture

I found this great Interactive Quick Reference for 12c.

You can find all the background processes in the following overview:

Bug in 12c found ;-)

In  $ORACLE_HOME/bin/dbshut I noted the typo

# Stop Oracle Net Listener
  if [ -f $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
    echo "$0: Stoping Oracle Net Listener" >> $LOG 2>&1
    $ORACLE_HOME_LISTNER/bin/lsnrctl stop >> $LOG 2>&1 &
  else
    echo "Failed to auto-stop Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"
  fi

It was there in 10.2, 11.1, 11.2 and now in 12.2. Don't they ever check their syntax?

By the way, Stoping is something different: http://en.wikipedia.org/wiki/Stoping

Oracle 12c: Create an empty container database

In this post I will create an empty container database using dbca. The dbca tool has been around since Oracle 9i (before it was dbassist, if you remember ...).
You will find the tool in $ORACLE_HOME/bin, it's graphic based, so you should set the DISPLAY if you work on Linux.

Ok, startup dbca. Follow the screenshots.

Select the Advanced Mode, Next.

 Now just wait until finished. This strongly depends on your speed of hardware.

Click close and your'e done.

Add Oracle 12c database to Enterprise Manager Cloud Control

It's quite easy to add your 12c databases to Cloud Control. You will need to do so, because the EM Express (which replaces DB Control) is very-very limited.

Just follow Setup --> Add Targets --> Add Targets Manually.

Now you can follow the steps, select host etc.

The Container database and the PDB's come up nicely in Cloud Control:

Note that the Container Database is clearly mentioned. If you click the little arrow icon, you get the PDB list:

This opens up the PDB screen:

During Configuration you will need to set the password for monitoring (default the DBSNMP user). This user is defined on containerlevel, so it doesn't really matter if you set (or reset) the password on CDB or PDB. 

Truncate table cascade in Oracle 12c

In Oracle 12c, the TRUNCATE TABLE CASCADE option is new. Here is a demo.

SCOTT@PDB01 > create table PARENT (
  2  ID number generated always as identity PRIMARY KEY,
  3  NAME VARCHAR2(20)
  4  );

Table created.

SCOTT@PDB01 > create table CHILD (
  2   ID number generated always as identity PRIMARY KEY,
  3  NAMECHILD VARCHAR2(20)
  4  );

Table created.

SCOTT@PDB01 > alter table CHILD add constraint FK_PARENT
  2  FOREIGN KEY (ID) references PARENT(ID) ON DELETE CASCADE;

Table altered.

SCOTT@PDB01 > insert into PARENT (NAME) values ('JOHN');

1 row created.

SCOTT@PDB01 > insert into CHILD (NAMECHILD) values ('JULIAN');

1 row created.

SCOTT@PDB01 > commit;

Commit complete.

SCOTT@PDB01 > select * from PARENT;

        ID NAME
---------- --------------------
         1 JOHN

SCOTT@PDB01 > select * from CHILD;

        ID NAMECHILD
---------- --------------------
         1 JULIAN

SCOTT@PDB01 > truncate table PARENT cascade;

Table truncated.

SCOTT@PDB01 > select * from PARENT;

no rows selected

SCOTT@PDB01 > select * from CHILD;

no rows selected