13 October 2013

Cold backup Container database with pluggable databases - open after backup

Hi,
Just a simple script to backup a 12c database, cold, and make sure to open the pluggable databases afterwards.

run {
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
BACKUP AS COMPRESSED BACKUPSET DATABASE INCLUDE CURRENT CONTROLFILE;
}
ALTER DATABASE OPEN;
ALTER PLUGGABLE DATABASE ALL OPEN;
BACKUP VALIDATE DATABASE;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;

This will keep your users happy the next morning :-)

11 October 2013

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

29 September 2013

Switchover Dataguard under Cloud Control 12c fails

During a switchover test in Enterprise Manager Cloud Control 12c, I got the error:

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

Session Information Unavailable

Enterprise Manager is unable to proceed. The most likely cause of this is that database session information could not be retrieved or that EM was unable to process a user-invoked bookmark. Press OK to continue.

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


The switchover did not take place, even though under DGMGRL it works fine, and the Verify Configuration in Cloud Control reveals no errors.

This appears to be a stupid bug in IE9. It runs fine under Chrome! See Doc ID 1512567.1 under Oracle Support.

This really makes me ....






23 September 2013

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:



23 July 2013

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

19 July 2013

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.




18 July 2013

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

17 July 2013

Oracle 12c: create table with sequence as primary key

I've created a small demo to test a few things.

  • connect / as sysdba
  • switch to the pluggable database as sysdba
  • create a table with a sequence as primary key

More demo will follow.

Enjoy.



oracle@srv01:~> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 17 10:03:14 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> @demo
SYS@ORA12CDB AS SYSDBA>
SYS@ORA12CDB AS SYSDBA> conn / as sysdba
Connected.
SYS@ORA12CDB AS SYSDBA> show con_name

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

SYS@ORA12CDB AS SYSDBA> alter session set container=PDB01;

Session altered.

SYS@ORA12CDB AS SYSDBA> show con_name

CON_NAME
------------------------------
PDB01
SYS@ORA12CDB AS SYSDBA> drop user scott cascade;

User dropped.

SYS@ORA12CDB AS SYSDBA> create user scott identified by tiger;

User created.

SYS@ORA12CDB AS SYSDBA> grant create session to scott;

Grant succeeded.

SYS@ORA12CDB AS SYSDBA> grant unlimited tablespace to scott;

Grant succeeded.

SYS@ORA12CDB AS SYSDBA> grant create table to scott;

Grant succeeded.

SYS@ORA12CDB AS SYSDBA> grant create sequence to scott;

Grant succeeded.


SYS@ORA12CDB AS SYSDBA> conn scott/tiger@PDB01
Connected.
SCOTT@PDB01 > create table test (
  2     ID number generated always as identity (start with 100) PRIMARY KEY,
  3     NAME VARCHAR2(20)
  4     );

Table created.

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

1 row created.

SCOTT@PDB01 > insert into test (NAME) values ('PAUL');

1 row created.

SCOTT@PDB01 > insert into test (NAME) values ('GEORGE');

1 row created.

SCOTT@PDB01 > insert into test (NAME) values ('RINGO');

1 row created.

SCOTT@PDB01 > commit;

Commit complete.

SCOTT@PDB01 > select * from test;

        ID NAME
---------- --------------------
       100 JOHN
       101 PAUL
       102 GEORGE
       103 RINGO

SCOTT@PDB01 >

25 June 2013

No graphs in Cloud Control - Top Activity

This morning I had no graphs in my Top Activity on Cloud Control 12.1.0.2. Very strange, but cleaning the history of the browser (Chrome) with Ctrl-Shift-Del worked for me...
    

Hope this fixes your problem if you have it too.