DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

Playing with Oracle 12c Multitenant Users and Roles

Posted on November 10, 2013 by Ludovico

I’ve realized these days that the great list of articles by Oracle Alchemist does not contain any articles describing a little more in depth common roles and Users.

I’ve found these ones by Pete Finnigan and Bobby Curtis:

http://www.petefinnigan.com/weblog/archives/00001366.htm

http://dbasolved.com/2013/06/29/common-user-vs-local-user-12c-edition/

http://www.petefinnigan.com/weblog/archives/00001370.htm

But I would like to investigate a little more.

My test environment

Just to give you an idea, I have two PDBs (HR and HR2), each containing an HR schema.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select NAME, CON_ID from v$containers;

NAME                               CON_ID
------------------------------ ----------
CDB$ROOT                                1
PDB$SEED                                2
HR                                      3
HR2                                     4

4 rows selected.

SQL> r
  1* select username, con_id from cdb_users where username ='HR'

USERNAME                           CON_ID
------------------------------ ----------
HR                                      4
HR                                      3

2 rows selected.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select NAME, CON_ID from v$containers;

NAME CON_ID

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

CDB$ROOT 1

PDB$SEED 2

HR 3

HR2 4

4 rows selected.

SQL> r

1* select username, con_id from cdb_users where username ='HR'

USERNAME CON_ID

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

HR 4

HR 3

2 rows selected.

Creating the common user

As already pointed by the existing articles, I can’t create a common user into the root container without the c## prefix, unless I’m altering the hidden parameter _common_user_prefix.

SQL> create user goofy identified by pippo;
create user goofy identified by pippo
*
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> create user goofy identified by pippo;

create user goofy identified by pippo

ERROR at line 1:

ORA-65096: invalid common user or role name

so I specify the correct prefix, and it works:

SQL> create user C##GOOFY identified by pippo;

User created.

SQL> grant create session, alter session to c##goofy;

Grant succeeded.

SQL> create user C##GOOFY identified by pippo;

User created.

SQL> grant create session, alter session to c##goofy;

Grant succeeded.

The user is common, so it appears in all the containers, I can check it by querying CDB_USERS from the root container.

SQL> select username, con_id from cdb_users where username like '%GOOFY%';

USERNAME                                               CON_ID
-------------------------------------------------- ----------
C##GOOFY                                                    1
C##GOOFY                                                    3
C##GOOFY                                                    4

SQL> select username, con_id from cdb_users where username like '%GOOFY%';

USERNAME CON_ID

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

C##GOOFY 1

C##GOOFY 3

C##GOOFY 4

Creating the local user

Then I create also a local user into the HR PDB.

SQL> alter session set container=HR;

Session altered.

SQL> create user goofy identified by pippo;

User created.

SQL> grant create session, alter session to goofy;

Grant succeeded.

SQL> alter session set container=HR;

Session altered.

SQL> create user goofy identified by pippo;

User created.

SQL> grant create session, alter session to goofy;

Grant succeeded.

CDB_USERS scope

From the PDB I see only the users in the PDB scope:

SQL> select username, con_id from cdb_users where username like '%GOOFY%';

USERNAME                                               CON_ID
-------------------------------------------------- ----------
C##GOOFY                                                    3
GOOFY                                                       3

SQL> select username, con_id from cdb_users where username like '%GOOFY%';

USERNAME CON_ID

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

C##GOOFY 3

GOOFY 3

If I change to the root, I see the users valid into all the containers:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select username, con_id from cdb_users where username like '%GOOFY%';

USERNAME                                               CON_ID
-------------------------------------------------- ----------
C##GOOFY                                                    1
C##GOOFY                                                    3
C##GOOFY                                                    4
GOOFY                                                       3

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select username, con_id from cdb_users where username like '%GOOFY%';

USERNAME CON_ID

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

C##GOOFY 1

C##GOOFY 3

C##GOOFY 4

GOOFY 3

Creating a common role

Do the roles obey to the same rules valid for the users?

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> create role country_reader;
create role country_reader
*
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> create role country_reader;

create role country_reader

ERROR at line 1:

ORA-65096: invalid common user or role name

Yes, they do! So, let’s create a common role with the C## prefix:

SQL> create role C##COUNTRY_ROLE container=ALL;

Role created.

SQL> create role C##COUNTRY_ROLE container=ALL;

Role created.

It works, but if I try to create a common role into the root container only, I get an error:

SQL> create role C##REGIONS_ROLE container=current;
create role C##REGIONS_ROLE container=current
*
ERROR at line 1:
ORA-65094: invalid local user or role name

SQL> create role C##REGIONS_ROLE container=current;

create role C##REGIONS_ROLE container=current

ERROR at line 1:

ORA-65094: invalid local user or role name

And also if I try to create a local role into the root, I can’t:

SQL> create role REGIONS_ROLE container=current;
create role REGIONS_ROLE container=current
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

SQL> create role REGIONS_ROLE container=current;

create role REGIONS_ROLE container=current

ERROR at line 1:

ORA-65049: creation of local user or role is not allowed in CDB$ROOT

Note that the error ORA-65049 is different from the ORA-65096 that I’ve got before.

My conclusion is that the clause container of the create role and create user statements doesn’t make sense as you can ONLY create common users and roles into the root container and only local ones into the PDBs.

Creating a local role

Just as experiment, I’ve tried to see if I can create a local role with container=ALL. It doesn’t work:

SQL> alter session set container=hr;

Session altered.

SQL> create role REGION_ROLE container=ALL;
create role REGION_ROLE container=ALL
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT

SQL> alter session set container=hr;

Session altered.

SQL> create role REGION_ROLE container=ALL;

create role REGION_ROLE container=ALL

ERROR at line 1:

ORA-65050: Common DDLs only allowed in CDB$ROOT

So I create the local role with container=current:

SQL> create role REGION_ROLE container=CURRENT;

Role created.

SQL> create role REGION_ROLE container=CURRENT;

Role created.

Now, from the PDB I can see the two roles I can access, whereas from the root I can see all the roles I’ve defined so far: the common role is available from all the PDBs, the local role only from the db where it has been defined, just like the users.

SQL>  select role, con_id from cdb_roles where   role like '%COUNTRY%' or role like '%REGION%';

ROLE                                                CON_ID
----------------------------------------------- ----------
REGION_ROLE                                              3
C##COUNTRY_ROLE                                          3

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL>  select role, con_id from cdb_roles where  oracle_maintained='N';

ROLE                                                                                                                           CON_ID
----------------------------------------- ----------
C##COUNTRY_ROLE                                    1
C##COUNTRY_ROLE                                    4
C##COUNTRY_ROLE                                    3
REGION_ROLE                                        3

4 rows selected.

SQL> select role, con_id from cdb_roles where role like '%COUNTRY%' or role like '%REGION%';

ROLE CON_ID

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

REGION_ROLE 3

C##COUNTRY_ROLE 3

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select role, con_id from cdb_roles where oracle_maintained='N';

ROLE CON_ID

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

C##COUNTRY_ROLE 1

C##COUNTRY_ROLE 4

C##COUNTRY_ROLE 3

REGION_ROLE 3

4 rows selected.

Granting object privileges to the roles

From the root I can’t give grants on objects that reside in a PDB since I cannot see them: I need to connect to the PDB and give the grants from there:

SQL> grant select on hr.countries to C##country_role;
grant select on hr.countries to C##country_role
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> alter session set container=hr;

Session altered.

SQL> grant select on hr.countries to C##COUNTRY_ROLE;

Grant succeeded.

SQL> grant select on hr.regions to REGION_ROLE;

Grant succeeded.

SQL> grant select on hr.countries to C##country_role;

grant select on hr.countries to C##country_role

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> alter session set container=hr;

Session altered.

SQL> grant select on hr.countries to C##COUNTRY_ROLE;

Grant succeeded.

SQL> grant select on hr.regions to REGION_ROLE;

Grant succeeded.

Now, if I query CDB_TAB_PRIVS from the root, I see that the grants are given at a local level (con_id=3 and common=N):

SQL> alter session set container=cdb$root;

Session altered.

SQL> select GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, COMMON, CON_ID from  cdb_tab_privs where owner='HR';

GRANTEE              OWNER    TABLE_NAME       PRIVILEGE     COM     CON_ID
-------------------- -------- ---------------- ------------- --- ----------
REGION_ROLE          HR       REGIONS          SELECT        NO           3
C##COUNTRY_ROLE      HR       COUNTRIES        SELECT        NO           3

2 rows selected.

SQL> alter session set container=cdb$root;

Session altered.

SQL> select GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, COMMON, CON_ID from cdb_tab_privs where owner='HR';

GRANTEE OWNER TABLE_NAME PRIVILEGE COM CON_ID

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

REGION_ROLE HR REGIONS SELECT NO 3

C##COUNTRY_ROLE HR COUNTRIES SELECT NO 3

2 rows selected.

Granting common and local roles to commond and local users

From a PDB, I can grant local roles to local users or common users:

SQL> grant REGION_ROLE to goofy;

Grant succeeded.

SQL> grant REGION_ROLE to c##goofy;

Grant succeeded.

SQL> grant REGION_ROLE to goofy;

Grant succeeded.

SQL> grant REGION_ROLE to c##goofy;

Grant succeeded.

But I can’t grant a common role to a common user with container=all if I’m in a PDB:

SQL> grant C##COUNTRY_ROLE to c##goofy container=all;
grant C##COUNTRY_ROLE to c##goofy container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT

SQL> grant C##COUNTRY_ROLE to c##goofy container=all;

grant C##COUNTRY_ROLE to c##goofy container=all

ERROR at line 1:

ORA-65050: Common DDLs only allowed in CDB$ROOT

To grant the a common role to a common user I can decide either to:

give the grant locally while connected to the PDB:

SQL> grant C##COUNTRY_ROLE to c##goofy container=current;

Grant succeeded.

SQL>

SQL> grant C##COUNTRY_ROLE to c##goofy container=current;

Grant succeeded.

SQL>

give the grant commonly while connected to the root:

SQL> alter session set container=cdb$root;

Session altered.

SQL> grant C##COUNTRY_ROLE to c##goofy container=all;

Grant succeeded.

SQL> alter session set container=cdb$root;

Session altered.

SQL> grant C##COUNTRY_ROLE to c##goofy container=all;

Grant succeeded.

I can also grant locally both roles and system privileges to common users while connected to the root container: in this case the privileges are applied to the root container only. Finally having the clause container finally starts to make sense:

SQL> grant C##COUNTRY_ROLE to c##goofy container=current;

Grant succeeded.

SQL> grant select any table to c##goofy container=current;

Grant succeeded.

SQL> grant C##COUNTRY_ROLE to c##goofy container=current;

Grant succeeded.

SQL> grant select any table to c##goofy container=current;

Grant succeeded.

Verifying the grants

Ok, I’ve given the grants and I’ve never verified if they work, so far.

Let’s try with the select any table privilege I’ve given in the last snippet. I expect C##GOOFY to select any table from the root container and only HR.COUNTRIES and HR.REGIONS on the HR PDB bacause they have been granted through the two roles.

SQL> connect c##goofy/pippo;
Connected.
SQL> select count(*) from APEX_040200.WWV_FLOW_TABS;

COUNT(*)
----------
609

1 row selected.

SQL> connect c##goofy/pippo@node4:1521/hr
ERROR:
ORA-01045: user C##GOOFY lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect c##goofy/pippo;

Connected.

SQL> select count(*) from APEX_040200.WWV_FLOW_TABS;

COUNT(*)

----------

609

1 row selected.

SQL> connect c##goofy/pippo@node4:1521/hr

ERROR:

ORA-01045: user C##GOOFY lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

What’s the mess? When I’ve created the user c##goofy, I’ve granted create and alter session without the container=all:

SQL&gt; grant create session, alter session to c##goofy;

1	SQL> grant create session, alter session to c##goofy;

According to the documentation, the grant command uses container=current by default (common=N):

SQL> select * from dba_sys_privs where grantee='C##GOOFY';

GRANTEE                        PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
C##GOOFY                       SELECT ANY TABLE                         NO  NO
C##GOOFY                       ALTER SESSION                            NO  NO
C##GOOFY                       CREATE SESSION                           NO  NO

3 rows selected.

SQL> select * from dba_sys_privs where grantee='C##GOOFY';

GRANTEE PRIVILEGE ADM COM

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

C##GOOFY SELECT ANY TABLE NO NO

C##GOOFY ALTER SESSION NO NO

C##GOOFY CREATE SESSION NO NO

3 rows selected.

So, I need to give the grants commonly to let c##goofy connect to all the PDBs:

SQL> grant create session, alter session to c##goofy container=all;

Grant succeeded.

SQL>  select * from dba_sys_privs where grantee='C##GOOFY';

GRANTEE                        PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
C##GOOFY                       SELECT ANY TABLE                         NO  NO
C##GOOFY                       ALTER SESSION                            NO  NO
C##GOOFY                       CREATE SESSION                           NO  NO
C##GOOFY                       ALTER SESSION                            NO  YES
C##GOOFY                       CREATE SESSION                           NO  YES

5 rows selected.

SQL> grant create session, alter session to c##goofy container=all;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee='C##GOOFY';

GRANTEE PRIVILEGE ADM COM

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

C##GOOFY SELECT ANY TABLE NO NO

C##GOOFY ALTER SESSION NO NO

C##GOOFY CREATE SESSION NO NO

C##GOOFY ALTER SESSION NO YES

C##GOOFY CREATE SESSION NO YES

5 rows selected.

Now I see that the grants give two distinct permissions : one local and the other common.

If I revoke the grants without container clause, actually only the local one is revoked and the user can continue to login. To revoke the grants I would need to check and revoke both local and common privileges.

SQL> revoke create session, alter session from c##goofy;

Revoke succeeded.

SQL>  select * from dba_sys_privs where grantee='C##GOOFY';

GRANTEE                        PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
C##GOOFY                       SELECT ANY TABLE                         NO  NO
C##GOOFY                       ALTER SESSION                            NO  YES
C##GOOFY                       CREATE SESSION                           NO  YES

3 rows selected.

SQL> connect C##GOOFY/pippo;
Connected.

SQL> revoke create session, alter session from c##goofy;

Revoke succeeded.

SQL> select * from dba_sys_privs where grantee='C##GOOFY';

GRANTEE PRIVILEGE ADM COM

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

C##GOOFY SELECT ANY TABLE NO NO

C##GOOFY ALTER SESSION NO YES

C##GOOFY CREATE SESSION NO YES

3 rows selected.

SQL> connect C##GOOFY/pippo;

Connected.

After the first revoke statement, I can still connect to HR and verify that my select any table privilege doesn’t apply to the PDB as it’s local to the root container:

SQL> connect C##GOOFY/pippo@node4:1521/hr
Connected.
SQL> select count(*) from hr.DEPARTMENTS;
select count(*) from hr.DEPARTMENTS
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> connect C##GOOFY/pippo@node4:1521/hr

Connected.

SQL> select count(*) from hr.DEPARTMENTS;

select count(*) from hr.DEPARTMENTS

ERROR at line 1:

ORA-00942: table or view does not exist

After that, I want to check the privileges given through the local and common roles.

I expect both users to select from hr.countries and hr.regions since they have been granted indirectly by the roles.

Let’s try the local user first:

SQL> connect goofy/pippo@node4:1521/hr
Connected.
SQL> select count(*) from hr.regions;

  COUNT(*)
----------
         4

1 row selected.

SQL> select count(*) from hr.countries;

  COUNT(*)
----------
        25

1 row selected.

SQL> connect goofy/pippo@node4:1521/hr

Connected.

SQL> select count(*) from hr.regions;

COUNT(*)

----------

1 row selected.

SQL> select count(*) from hr.countries;

COUNT(*)

----------

1 row selected.

Yeah, it works as expected.

Now let’s try the common user:

SQL&gt; connect c##goofy/pippo@node4:1521/hr
Connected.
SQL&gt; select count(*) from hr.regions;

  COUNT(*)
----------
         4

1 row selected.

SQL&gt; select count(*) from hr.countries;

  COUNT(*)
----------
        25

1 row selected.

SQL> connect c##goofy/pippo@node4:1521/hr

Connected.

SQL> select count(*) from hr.regions;

COUNT(*)

----------

1 row selected.

SQL> select count(*) from hr.countries;

COUNT(*)

----------

1 row selected.

It also work, so everything is ok.

Common and local grants, why you must pay attention

During the example, I’ve granted the C##COUNTRY_ROLE many times: locally to PDB, locally to the ROOT, commonly. The result is that I’ve flooded the grant table with many entries:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select grantee, GRANTED_ROLE, COMMON, CON_ID from cdb_role_privs where grantee like '%GOOFY' order by GRANTEE, GRANTED_ROLE;

GRANTEE                        GRANTED_ROLE                   COM     CON_ID
------------------------------ ------------------------------ --- ----------
C##GOOFY                       C##COUNTRY_ROLE                YES          4
C##GOOFY                       C##COUNTRY_ROLE                YES          3
C##GOOFY                       C##COUNTRY_ROLE                YES          1
C##GOOFY                       C##COUNTRY_ROLE                NO           1
C##GOOFY                       C##COUNTRY_ROLE                NO           3
C##GOOFY                       REGION_ROLE                    NO           3
GOOFY                          C##COUNTRY_ROLE                NO           3
GOOFY                          REGION_ROLE                    NO           3

8 rows selected.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select grantee, GRANTED_ROLE, COMMON, CON_ID from cdb_role_privs where grantee like '%GOOFY' order by GRANTEE, GRANTED_ROLE;

GRANTEE GRANTED_ROLE COM CON_ID

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

C##GOOFY C##COUNTRY_ROLE YES 4

C##GOOFY C##COUNTRY_ROLE YES 3

C##GOOFY C##COUNTRY_ROLE YES 1

C##GOOFY C##COUNTRY_ROLE NO 1

C##GOOFY C##COUNTRY_ROLE NO 3

C##GOOFY REGION_ROLE NO 3

GOOFY C##COUNTRY_ROLE NO 3

GOOFY REGION_ROLE NO 3

8 rows selected.

Let’s try to clean things: for sure I don’t need the grant local to the root:

SQL> revoke C##COUNTRY_ROLE from C##GOOFY container=current;

Revoke succeeded.

SQL> revoke C##COUNTRY_ROLE from C##GOOFY container=current;

Revoke succeeded.

Then I can choose between revoking the common privilege or the local one. Let’s try to remove the local one:

SQL> alter session set container=hr;

Session altered.

SQL>  revoke C##COUNTRY_ROLE from C##GOOFY container=current;

Revoke succeeded.

SQL> alter session set container=hr;

Session altered.

SQL> revoke C##COUNTRY_ROLE from C##GOOFY container=current;

Revoke succeeded.

I’ve removed the local one, but I have still the common one (I’m connected to the PDB so the entries from the other containers are not displayed):

SQL> select grantee, GRANTED_ROLE, COMMON, CON_ID from cdb_role_privs where grantee like '%GOOFY' order by GRANTEE, GRANTED_ROLE;

GRANTEE                        GRANTED_ROLE                   COM     CON_ID
------------------------------ ------------------------------ --- ----------
C##GOOFY                       C##COUNTRY_ROLE                YES          3
C##GOOFY                       REGION_ROLE                    NO           3
GOOFY                          C##COUNTRY_ROLE                NO           3
GOOFY                          REGION_ROLE                    NO           3

4 rows selected.

SQL> select grantee, GRANTED_ROLE, COMMON, CON_ID from cdb_role_privs where grantee like '%GOOFY' order by GRANTEE, GRANTED_ROLE;

GRANTEE GRANTED_ROLE COM CON_ID

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

C##GOOFY C##COUNTRY_ROLE YES 3

C##GOOFY REGION_ROLE NO 3

GOOFY C##COUNTRY_ROLE NO 3

GOOFY REGION_ROLE NO 3

4 rows selected.

I still have access to the tables as expected:

SQL> connect C##GOOFY/pippo@node4:1521/hr
Connected.
SQL>  select count(*) from hr.countries;

  COUNT(*)
----------
        25

1 row selected.

SQL>  select count(*) from hr.regions;

  COUNT(*)
----------
         4

1 row selected.

SQL> connect C##GOOFY/pippo@node4:1521/hr

Connected.

SQL> select count(*) from hr.countries;

COUNT(*)

----------

1 row selected.

SQL> select count(*) from hr.regions;

COUNT(*)

----------

1 row selected.

So, you must pay attention to a couple of things:

When granting privileges from the root container, keep in mind that container=current is the default even when the grantee or the role granted are common.
When revoking the grants with a Multitenant architecture, keep in mind that there is a scope and you may need more than one statement to actually remove the grant from all the scopes.

As always, I look for opinions and suggestions, feel free to comment!

🙂

—
Ludovico

How many Oracle instances can be consolidated on a single server?

Posted on November 6, 2013 by Ludovico

According to Exadata consolidation guide, this is what you can consolidate on Oracle specialized Hardware:

NOTE: The maximum number of database instances per cluster is 512 for Oracle 11g Release 1 and higher. An upper limit of 128 database instances per X2-2 or X3-2 database node and 256 database instances per X2-8 or X3-8 database node is recommended. The actual number of database instances per database node or cluster depends on application workload and their corresponding system resource consumption.

But how many instances are actually beeing consolidated by DBAs from all around the world?

I’ve asked it to the Twitter community

I’ve sent this tweet a couple of weeks ago and I would like to consolidate some replies into a single blog post.

who has done more than this on a single server? $ ps -eaf | grep ora_pmon | wc -l 77 #oracle #consolidation

— Ludovico Caldara (@ludovicocaldara) October 25, 2013

My customer environment however, was NOT a production one. On the production they have 45.

Some replies…

@CacheFlush @kevinclosson @ludovicocaldara I know one customer with 58

— Bjoern Rost (@brost) October 25, 2013

@ludovicocaldara I have more 🙂 — Wissem El Khlifi (@orawiss) October 25, 2013

@ludovicocaldara ay cant believe it, think they removed some DBs, i have now 73. Ok u win 🙁 — Wissem El Khlifi (@orawiss) October 25, 2013

Wissem cores 73 on a production system, 1TB memory!

@ludovicocaldara about 150 TB of storage, 1TB memory, 40 Cores …

— Wissem El Khlifi (@orawiss) October 25, 2013

@ludovicocaldara we have at least 4 servers like this , big ones! 2 for prods …

— Wissem El Khlifi (@orawiss) October 25, 2013

Chris correctly suggests to give a try to the new 12c consolidation features:

.@ludovicocaldara a good playground for either threaded_execution=true or multitenant 😉

— Christian Antognini (@ChrisAntognini) October 25, 2013

Kevin, as a great expert, already experimented one hundred instances environment:

.@ludovicocaldara up to 100 instances/host when researching for this paper: http://t.co/sIAuvnDJ2D Proof point stayed much lower though

— Kevin (@kevinclosson) October 25, 2013

But Bertrand impresses with his numbers!

@ludovicocaldara Will give you the right number on Monday but I would say around 120 🙂

— Bertrand Drouvot (@BertrandDrouvot) October 25, 2013

@ludovicocaldara Here is the exact number dude: ps -ef | grep -c ora_smon 131

— Bertrand Drouvot (@BertrandDrouvot) October 28, 2013

@BertrandDrouvot @ludovicocaldara damn your the winner, i can’t higher then 118 but this customer is still busy migrating more.

— Klaas-Jan Jongsma (@futureveterans) October 28, 2013

@ludovicocaldara capacity planning is a big challenge here with currently around 1800 databases and this is constantly increasing…

— Bertrand Drouvot (@BertrandDrouvot) October 25, 2013

@ludovicocaldara @orawiss 1TB is our default as of a few months 🙂

— Bertrand Drouvot (@BertrandDrouvot) October 25, 2013

Intel platform with 1TB of RAM = Xeon E7, suggests Kevin:

. @BertrandDrouvot @ludovicocaldara @orawiss so you standardize on Xeon E7 ?

— Kevin (@kevinclosson) October 25, 2013

@kevinclosson how did you guess ? 🙂 @ludovicocaldara @orawiss

— Bertrand Drouvot (@BertrandDrouvot) October 25, 2013

. @BertrandDrouvot @ludovicocaldara @orawiss because there are no 1TB EP offerings… at least not until after Haswell. Ivy EX does 12TB 🙂

— Kevin (@kevinclosson) October 25, 2013

Flashdba has seen 87 instances on a single host, but on a Multi-node RAC: but still huge and complex!

@ludovicocaldara @BertrandDrouvot @orawiss When I worked for Oracle I saw 87 instances on single a node – and it was six node RAC

— flashdba (@flashdba) October 25, 2013

@ludovicocaldara @BertrandDrouvot @orawiss It was a production system too – and yes, it was a nightmare!

— flashdba (@flashdba) October 25, 2013

@kevinclosson @ludovicocaldara @BertrandDrouvot @orawiss The cluster had around 120 DBs, not every database had an instance on every node

— flashdba (@flashdba) October 25, 2013

@kevinclosson @ludovicocaldara @BertrandDrouvot @orawiss The result was between 50 and 87 instances running per node #complicated

— flashdba (@flashdba) October 25, 2013

Conclusion

Does this thread of tweets reply to the question? Are you planning to consolidate your Oracle environment? If you have questions about how to plan your consolidation, don’t hesitate to get in touch! 🙂

—

Ludo

Exciting News from Oracle Open World 2013

Posted on October 4, 2013 by Ludovico

I’m back at work now, safely, after the week in San Francisco.

It’s time to sit down, and try to pull out some thought about what I’ve experienced and done.

I’ll start from the new announcements, what is most important for most people, and leave my personal experience for my next post.

In-memory Database Option

Oracle has announced the In-Memory option for the Oracle Database. This feature will store the data simultaneously in traditional row-based and into a new in-memory columnar format, to serve optimally both analytics and OLTP workloads AT THE SAME TIME. Because column-based storage is redundant, it will work without logging mechanism, so the overhead will be minimal. The marketing message claims “ungodly speed”: 100x faster queries for analytics and 2x faster queries in OLTP environments.

By separating Analytics and OLTP with different storage formats, the indexes on the row-based version of the table can be reduced to make the transactions faster, getting the rid of the analytical indexes thank to the columnar format that is already optimized for that kind of workload. The activation of the option will be transparent to the applications.

How it will be activated?

inmemory_size = XXX GB

alter table foo ... inmemory;

inmemory_size = XXX GB

alter table foo ... inmemory;

Now my considerations:

[evil] Will this option make your database faster than putting it on an actual Exadata?
It will be an option, so it will cost extra-money on top of the Enterprise Edition
[I guess] it will be released with 12cR2 because a such big change cannot be introduced simply with a patch set. So I think we’ll not see it before the end of 2014
And, uh, Maria Colgan has given up the Product Management of the Cost Based Optimizer to become the Product Manager of the In-Memory option. Tom Kyte will take the ownership of the CBO.

M6-32 Big Memory Machine

I’ve paid much less attention for this new announcement. The new big super hyper machine engineered by Oracle will have:

1024 DIMMS
32TB of DRAM
12 cores per processors
96 threads per processor

This huge memory machine can be connected through InfiniBand to an Exadata to rely on its storage cells.

But it will cost 3M$, so it’s not really intended for SMBs or for the average DBA, that’s why I don’t care too much about it…

Oracle Database Backup, Logging, Recovery Appliance

Only 8 minutes in the keynote to introduce this appliance that is really hot, IMHO. This… oh my… let’s call it ODBLRA, is a backup appliance (based on the same HW of Exadata) capable of receiving the stream of redo logging over SQL*Net, the same way as it’s done with DataGuard, except that instead of having a standby database, you’ll have an appliance capable of storing all the redo stream of your entire DB farm and have a real-time backup of your transactions. That’s it: no transactions lost between two backup archives and no need to have hundreds of DataGuard setups or network filesystems as secondary destinations in order to make your redo stream safer.

I guess that it will host an engine RMAN-aware that can create incremental-updated backups, so that you can almost forget about full backups. You can leverage an existent tape infrastructure to offload the appliance if it starts getting full.

Your ODBLRA can also replicate your backups to an another appliance hosted on the Oracle Cloud: ODBLRAaaS! 🙂

To conclude, Oracle is pushing for bigger, dedicated, specialized SPARC machines instead of relying on commodity hardware…

Oracle Multi-tenant Self-Service Provisioning

There’s a new APEX application, now in BETA, that can be downloaded from the Oracle Multitenant Page that provides self-service provisioning of databases in a Multitenant architecture. It’s worth a try… if you plan to introduce the Multitenant option in your environment!

All products in the Cloud

Oracle now offers (as a preview) its Database, Middleware and Applications as a Service, in its public cloud. For a DBA can be of interest:

The Storage aaS, use Java & REST API (Openstack SWIFT) for block level access to the storage.

The Computing aaS allows you to scale the computing power to follow your computing needs.

The Database aaS is the standard, full-featured Oracle Database (in the cloud!) 11gR2 or 12c in all editions (SE, SE1, EE). You can choose five different sizes, up to 17cores and 256Gb of RAM, and choose 3 different formulas:

Single Schema (3 sizes: 5, 20 or 50Gb, with prices from 175$/month to 2000$/month)
Basic Database (user-managed, single-instance preconfigured databases only with a local EM)
Managed Database (single-instance with managed backups & PITR, managed quarterly apply of critical parches)
Premium Managed Database (fully managed RAC, with optional DG or Active DG, PDB and upgrades)

My considerations:

Oracle releases this cloud offering with significant delay comparing to his competitors
It’s still in preview and there’s no information about the billing schema. Depending on that, it can be more or less attractive.
As for other cloud services, the performance will be acceptable only when putting all the stack into the same cloud (WebLogic, DB, etc.)

Oracle on Azure

Microsoft starts offering preconfigured Oracle platforms, Database and WebLogic, on Azure on both Linux and Windows systems. I haven’t seen the price list yet, but IMHO Azure has been around since longtime now, and it appears as a reliable and settled alternative comparing to Oracle Cloud. Nice move Microsoft, I think it deserves special attention.

Keynotes recordings

You can see the full keynote recordings here:

Oracle OpenWorld Keynote Highlights

Larry Ellison — Oracle OpenWorld Keynote 9-22-2013

Oracle OpenWorld General Session 2013: Database

Kurian and Fowler — Oracle OpenWorld Keynote 9-24-2013

Will these announcements change your life? Let me know…

…and stay tuned, I’ll come soon with a new post about the my “real” week at the Open World and why I’ve loved it.

Ludovico

Oracle Database 12c: MySQL C API implementation: a double-edge sword?

Posted on October 1, 2013 by Ludovico

One of the new features of Oracle 12c is the new is the MySQL C API implementation for Oracle, so that all applications and tools built on this API can use transparently a MySQL or an Oracle database as backend.

API Reference for Oracle MySQL Client Library Driver

Oracle says that this will facilitate the migration from MySQL to Oracle, but I ask myself: Won’t be attractive for many developers to start developing applications with the MySQL API rather than with the Oracle libraries? This can potentially permit new applications to be migrated quickly in the opposite direction… (Oracle -> MySQL).

Time will tell.

Ludo

My Agenda at Oracle Open World 2013

Posted on August 30, 2013 by Ludovico

For truth’s sake, I wasn’t planning to head at Oracle Open World this year. I’ve never had this opportunity, and the same days my company is planning it’s great internal conference (Trivadis Tech Event) that I always enjoy and I hope will be made public soon.

But this summer I’ve started contributing heavily to the rewriting of the RAC Attack project, now focusing on Oracle RAC 12c.

So I’ve seized the opportunity and asked my managers to send me at OOW (with partial success). The final result is that I’m attending Oracle Open World and I’ll be glad to meet everyone of you! 🙂 I’ll also mentor the RAC Attack (Operation Ninja) event, so make sure you come at the OTN Lounge, lobby of Moscone South, Tuesday and Wednesday between 10:00AM and 2:00PM and meet the whole team of RAC experts.

My Agenda

I’m very struggled with timing conflicts between the many sessions I would like to attend. It’s a unique opportunity to meet and listen to all my favorite bloggers, technologists and tweeps, and will be a pity to miss many of their sessions.

However, I’ve ended up with this Agenda, it’s a semi-definitive one, but I reserve the right to change things the last minute, so follow me on twitter during these days.

You may notice the two huge slots I’ve reserved for the RAC Attack, and many sessions I’ll follow at the Oak Table World 2013. Most of my favorite technologists will head there.

I’ll also attend to two fitness events on Sunday and Monday, if you’re brave enough you can join us! 🙂

And as Yury says…

“If YOU or any of YOUR team‘s members participate in Oracle OpenWorld 2013 conference please please reach me. I would love to MEET UP.”

—

Ludovico

Oracle Database 12c: Multitenant, Services and Standard Edition RAC

Posted on July 3, 2013 by Ludovico

The installation process of a typical Standard Edition RAC does not differ from the Enterprise Edition. To achieve a successful installation refer to the nice quick guide made by Yury Velikanov and change accordingly the Edition when installing the DB software.

Standard Edition and Feature availability

The first thing that impressed me, is that you’re still able to choose to enable pluggable databases in DBCA even if Multitenant option is not available for the SE.

So I decided to create a container database CDB01 using template files, so all options of EE are normally cabled into the new DB. The Pluggable Database name is PDB01.

[oracle@se12c01 ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 14:21:47 2013

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

[oracle@se12c01 ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 14:21:47 2013

Enter user-name: / as sysdba

Connected to:

Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

As you can see, the initial banner contains “Real Application Clusters and Automatic Storage Management options“.

Multitenant option is not avilable. How SE reacts to its usage?

First, on the ROOT db, dba_feature_usage_statistics is empty.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select * from dba_feature_usage_statistics;

no rows selected

SQL>

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select * from dba_feature_usage_statistics;

no rows selected

SQL>

This is interesting, because all features are in (remember it’s created from the generic template) , so the feature check is moved from the ROOT to the pluggable databases.

On the local PDB I have:

SQL> alter session set container=PDB01;

Session altered.

SQL> select * from dba_feature_usage_statistics where lower(name) like '%multitenant%';

NAME               VERSION    DETECTED_USAGES   TOTAL_SAMPLES   CURRE
----------------------------- ----------------- --------------- -----
Oracle Multitenant 12.1.0.1.0                 0               0 FALSE

SQL> alter session set container=PDB01;

Session altered.

SQL> select * from dba_feature_usage_statistics where lower(name) like '%multitenant%';

NAME VERSION DETECTED_USAGES TOTAL_SAMPLES CURRE

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

Oracle Multitenant 12.1.0.1.0 0 0 FALSE

Having ONE PDB is not triggering the usage of Multitenant (as I was expecting).

How if I try to create a new pluggable database?

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> create pluggable database PDB02 admin user pdb02admin identified by pdb02admin;
create pluggable database PDB02 admin user pdb02admin identified by pdb02admin
*
ERROR at line 1:
ORA-65010: maximum number of pluggable databases created

SQL>

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> create pluggable database PDB02 admin user pdb02admin identified by pdb02admin;

create pluggable database PDB02 admin user pdb02admin identified by pdb02admin

ERROR at line 1:

ORA-65010: maximum number of pluggable databases created

SQL>

A-AH!! Correctly, I can have a maximum of ONE pluggable database in my container.

This allows however:

Smooth migration from SE to a Multitenant Architecture
Quick upgrade from one release to another

To be sure that I can plug/unplug, I’ve tried it:

SQL> alter pluggable database pdb01 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb01 unplug into '/u01/app/oracle/unplugged/pdb01/pdb01.xml';

Pluggable database altered.

SQL> drop pluggable database pdb01 keep datafiles;

Pluggable database dropped.

SQL> create pluggable database PDB02 admin user pdb02admin identified by pdb02admin;

Pluggable database created.

SQL> alter pluggable database pdb02 open;

Pluggable database altered.

SQL> select * from dba_feature_usage_statistics where name like '%Multite%';

NAME               VERSION    DETECTED_USAGES   TOTAL_SAMPLES   CURRE
----------------------------- ----------------- --------------- -----
Oracle Multitenant 12.1.0.1.0                 0               0 FALSE

SQL> alter pluggable database pdb01 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb01 unplug into '/u01/app/oracle/unplugged/pdb01/pdb01.xml';

Pluggable database altered.

SQL> drop pluggable database pdb01 keep datafiles;

Pluggable database dropped.

SQL> create pluggable database PDB02 admin user pdb02admin identified by pdb02admin;

Pluggable database created.

SQL> alter pluggable database pdb02 open;

Pluggable database altered.

SQL> select * from dba_feature_usage_statistics where name like '%Multite%';

NAME VERSION DETECTED_USAGES TOTAL_SAMPLES CURRE

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

Oracle Multitenant 12.1.0.1.0 0 0 FALSE

Other features of Enterprise off course don’t work

SQL> alter index SYS_C009851 rebuild online tablespace users;
alter index SYS_C009851 rebuild online tablespace users
*
ERROR at line 1:
ORA-00439: feature not enabled: Online Index Build

SQL> alter database move datafile 'DATA/CDB01/E09CA0E26A726D60E043A138A8C0E475/DATAFILE/users.284.819821651';
alter database move datafile 'DATA/CDB01/E09CA0E26A726D60E043A138A8C0E475/DATAFILE/users.284.819821651'
*
ERROR at line 1:
ORA-00439: feature not enabled: online move datafile

SQL> alter index SYS_C009851 rebuild online tablespace users;

alter index SYS_C009851 rebuild online tablespace users

ERROR at line 1:

ORA-00439: feature not enabled: Online Index Build

SQL> alter database move datafile 'DATA/CDB01/E09CA0E26A726D60E043A138A8C0E475/DATAFILE/users.284.819821651';

alter database move datafile 'DATA/CDB01/E09CA0E26A726D60E043A138A8C0E475/DATAFILE/users.284.819821651'

ERROR at line 1:

ORA-00439: feature not enabled: online move datafile

Create a Service on the RAC Standard Edition (just to check if it works)

I’ve just followed the steps to do it on an EE. Keep in mind that I’m using admin managed DB (something will come about policy managed DBs, stay tuned).

As you can see it works pretty well. Comparing to 11g you have to specify the -pdb parameter:

[oracle@se12c01 admin]$ srvctl add service -db CDB01 -service testpdb02 -preferred CDB012 -pdb PDB02
[oracle@se12c01 admin]$ srvctl start service -db cdb01 -s testpdb02

[oracle@se12c01 admin]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       se12c01                  STABLE
               ONLINE  ONLINE       se12c02                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       se12c01                  STABLE
               ONLINE  ONLINE       se12c02                  STABLE
ora.asm
               ONLINE  ONLINE       se12c01                  Started,STABLE
               ONLINE  ONLINE       se12c02                  Started,STABLE
ora.net1.network
               ONLINE  ONLINE       se12c01                  STABLE
               ONLINE  ONLINE       se12c02                  STABLE
ora.ons
               ONLINE  ONLINE       se12c01                  STABLE
               ONLINE  ONLINE       se12c02                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       se12c01                  STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       se12c02                  STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       se12c02                  STABLE
ora.cdb01.db
      1        ONLINE  ONLINE       se12c01                  Open,STABLE
      2        ONLINE  ONLINE       se12c02                  Open,STABLE
ora.cdb01.testpdb02.svc  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
      1        ONLINE  ONLINE       se12c02                  STABLE
ora.cvu
      1        ONLINE  ONLINE       se12c02                  STABLE
ora.oc4j
      1        OFFLINE OFFLINE                               STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       se12c01                  STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       se12c02                  STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       se12c02                  STABLE
ora.se12c01.vip
      1        ONLINE  ONLINE       se12c01                  STABLE
ora.se12c02.vip
      1        ONLINE  ONLINE       se12c02                  STABLE
--------------------------------------------------------------------------------

[oracle@se12c01 admin]$ srvctl add service -db CDB01 -service testpdb02 -preferred CDB012 -pdb PDB02

[oracle@se12c01 admin]$ srvctl start service -db cdb01 -s testpdb02

[oracle@se12c01 admin]$ crsctl stat res -t

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

Name Target State Server State details

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

Local Resources

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

ora.DATA.dg

ONLINE ONLINE se12c01 STABLE

ONLINE ONLINE se12c02 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE se12c01 STABLE

ONLINE ONLINE se12c02 STABLE

ora.asm

ONLINE ONLINE se12c01 Started,STABLE

ONLINE ONLINE se12c02 Started,STABLE

ora.net1.network

ONLINE ONLINE se12c01 STABLE

ONLINE ONLINE se12c02 STABLE

ora.ons

ONLINE ONLINE se12c01 STABLE

ONLINE ONLINE se12c02 STABLE

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE se12c01 STABLE

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE se12c02 STABLE

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE se12c02 STABLE

ora.cdb01.db

1 ONLINE ONLINE se12c01 Open,STABLE

2 ONLINE ONLINE se12c02 Open,STABLE

ora.cdb01.testpdb02.svc <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

1 ONLINE ONLINE se12c02 STABLE

ora.cvu

1 ONLINE ONLINE se12c02 STABLE

ora.oc4j

1 OFFLINE OFFLINE STABLE

ora.scan1.vip

1 ONLINE ONLINE se12c01 STABLE

ora.scan2.vip

1 ONLINE ONLINE se12c02 STABLE

ora.scan3.vip

1 ONLINE ONLINE se12c02 STABLE

ora.se12c01.vip

1 ONLINE ONLINE se12c01 STABLE

ora.se12c02.vip

1 ONLINE ONLINE se12c02 STABLE

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

Then I can access my DB (and preferred instance) using the service_name I specified.

[oracle@se12c02 admin]$ $ORACLE_HOME/bin/sqlplus pdb02admin/pdb02admin@testpdb02

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 16:46:06 2013

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

Last Successful login time: Wed Jul 03 2013 16:46:01 +02:00

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> show con_name

CON_NAME
------------------------------
PDB02
SQL>

[oracle@se12c02 admin]$ $ORACLE_HOME/bin/sqlplus pdb02admin/pdb02admin@testpdb02

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 16:46:06 2013

Last Successful login time: Wed Jul 03 2013 16:46:01 +02:00

Connected to:

Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL> show con_name

CON_NAME

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

PDB02

SQL>

Let me know what do you think about SE RAC on 12c. It is valuable for you?

I’m also on twitter: @ludovicocaldara

Cheers

—

Ludo

Oracle Database 12c: Enterprise Manager Database Express

Posted on June 27, 2013 by Ludovico

Oracle Database 12c says goodbye to a tool being around after the 10gR1: the Database Console.

OC4J for the 10g and weblogic for the 11g, both have had a non-negligible overhead on the systems, especially with many configured instances.

In many cases I’ve decided to switch to Grid/Cloud Control for the main reason to avoid too many db consoles, in other cases I’ve just disabled at all web management.

The new 12c brings a new tool called Database Express (indeed, very similar to its predecessors).

Where’s my emca/emctl?

The DB Express runs entirely with pl/sql code within the XDB schema. It’s XDB that leverages its features to enable a web-based console, and it’s embedded by default in the database.

To enable it, it’s necessary to check that the parameter dispatchers is enabled for XDB:

SQL> show parameter dispatchers

NAME        TYPE   VALUE
----------- ------ -----------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=CLASSICXDB)

SQL> show parameter dispatchers

NAME TYPE VALUE

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

dispatchers string (PROTOCOL=TCP) (SERVICE=CLASSICXDB)

and then set an https port unique on the server:

SQL> exec dbms_xdb_config.sethttpsport (5502);

PL/SQL procedure successfully completed.

SQL>

SQL> exec dbms_xdb_config.sethttpsport (5502);

PL/SQL procedure successfully completed.

SQL>

If you’ve already done it but you don’t remember the port number you can get it with this query:

SQL> select dbms_xdb_config.gethttpsport () from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
5502

SQL> select dbms_xdb_config.gethttpsport () from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()

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

5502

You can now access the web interface by using the address:

https://yourserver:5502/em

1	https://yourserver:5502/em

db_express

Lower footprint, less features

From one side DB Express is thin (but not always fast on my laptop…), from the other it has clearly fewer features comparing to the DB Console.

It’s not clear to me if Oracle wants to make it more powerful in future releases or if it’s a move to force everybody to use something else (SQLDeveloper or EM12c Cloud Control). However the DBA management plugin of the current release of SQL Developer is fully compatible with the 12c, including the ability to manage pluggable databases:

So is the EM 12c Cloud Control, so you have plenty of choice to manage your 12c databases from graphical interfaces.

Stay tuned!

Ludovico

Oracle Database 12c: RMAN recover at table level

Posted on June 27, 2013 by Ludovico

Oracle Database 12c comes with a new feature named “RMAN table level recovery”.

After a quick try it’s easy to understand that we are talking about Tablespace Point-in-Time Recovery (TSPITR) with some automation to have it near-transparent.

How to launch it

The syntax is quite trivial. Suppose you’ve dropped a table ludovico.reco and then purged it (damn!) then you can’t flashback it to before drop and don’t want to flashback the entire database.

SQL> create table reco (field1 varchar2(50) primary key);

Table created.

SQL> insert into reco values ('test');

1 row created.

SQL> insert into reco values ('foo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from reco;

FIELD1
--------------------------------------------------
foo 
test

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
803916

SQL> drop table reco;

Table dropped.

SQL> purge table reco;

Table purged.

SQL> flashback table reco to before drop;
flashback table reco to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

SQL> create table reco (field1 varchar2(50) primary key);

Table created.

SQL> insert into reco values ('test');

1 row created.

SQL> insert into reco values ('foo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from reco;

FIELD1

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

foo

test

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

803916

SQL> drop table reco;

Table dropped.

SQL> purge table reco;

Table purged.

SQL> flashback table reco to before drop;

flashback table reco to before drop

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

You can recover the table with:

RMAN> recover table ludovico.reco until scn 803916 auxiliary destination '/tmp/recover';

1	RMAN> recover table ludovico.reco until scn 803916 auxiliary destination '/tmp/recover';

You identify the schema.table:partition to restore, optionally you can pass the pluggable database containing the table to recover, the time definition as usual (scn, seq# or timestamp) and an auxiliary destination.

This Auxiliary destination is well-known to be mandatory for TSPITR. You can pass other options like table renaming or tablespace remapping.

Off course, the database must be open in read-write, in archivelog mode and at least one successful backup must be taken.

How it works

Oracle prepare an auxiliary instance by restoring the SYSTEM, UNDO and SYSAUX tablespaces.

Starting recover at 10-JUN-13
using channel ORA_DISK_1 
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM 
Tablespace UNDOTBS1

Creating automatic instance, with SID='kCoq'

initialization parameters used for automatic instance:
db_name=CLASSIC 
db_unique_name=kCoq_pitr_CLASSIC 
compatible=12.0.0.0.0 
db_block_size=8192 
db_files=200 
sga_target=1G 
processes=80 
diagnostic_dest=/u01/app/oracle 
db_create_file_dest=/tmp/recover 
log_archive_dest_1='location=/tmp/recover' 
#No auxiliary parameter file used

starting up automatic instance CLASSIC

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes
Variable Size 281020272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5480448 bytes
Automatic instance created

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# restore the controlfile 
restore clone controlfile; 
# mount the controlfile 
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current'; 
} 
executing Memory Script

executing command: SET until clause

Starting restore at 10-JUN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 
output file name=/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl 
Finished restore at 10-JUN-13

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new; 
set newname for clone datafile 3 to new; 
set newname for clone datafile 2 to new; 
set newname for clone tempfile 1 to new; 
# switch all tempfiles 
switch clone tempfile all; 
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2; 
switch clone datafile all; 
} 
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/recover/CLASSIC/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 10-JUN-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/recover/CLASSIC/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_%u_.dbf 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:56 
Finished restore at 10-JUN-13

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf
datafile 3 switched to datafile copy 
input datafile copy RECID=5 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf
datafile 2 switched to datafile copy 
input datafile copy RECID=6 STAMP=817751487 file name=/tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

Starting recover at 10-JUN-13

using channel ORA_DISK_1

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='kCoq'

initialization parameters used for automatic instance:

db_name=CLASSIC

db_unique_name=kCoq_pitr_CLASSIC

compatible=12.0.0.0.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

diagnostic_dest=/u01/app/oracle

db_create_file_dest=/tmp/recover

log_archive_dest_1='location=/tmp/recover'

#No auxiliary parameter file used

starting up automatic instance CLASSIC

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes

Variable Size 281020272 bytes

Database Buffers 780140544 bytes

Redo Buffers 5480448 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 10-JUN-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463 tag=TAG20130610T165249

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07

output file name=/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl

Finished restore at 10-JUN-13

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 2 to new;

set newname for clone tempfile 1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 3, 2;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/recover/CLASSIC/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 10-JUN-13

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/recover/CLASSIC/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:56

Finished restore at 10-JUN-13

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=817751487 file name=/tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf

Then it opens in READ-ONLY mode the partial database.

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
# recover and open database read only 
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only'; 
} 
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

Starting recover at 10-JUN-13
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=54 
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=55 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 
archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54 
archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55 
media recovery complete, elapsed time: 00:00:10 
Finished recover at 10-JUN-13

sql statement: alter database open read only

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 3 online";

sql clone "alter database datafile 2 online";

# recover and open database read only

recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

Starting recover at 10-JUN-13

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=54

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=55

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54

archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55

media recovery complete, elapsed time: 00:00:10

Finished recover at 10-JUN-13

sql statement: alter database open read only

It uses then the read-only dictionary to take the tablespace that was containing the table before the data loss. This tablespace (users in my example) is restored and recovered, and the database is opened.

contents of Memory Script:

{ 
sql clone "create spfile from memory";
shutdown clone immediate; 
startup clone nomount; 
sql clone "alter system set control_files = 
''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment=
''RMAN set'' scope=spfile"; 
shutdown clone immediate; 
startup clone nomount; 
# mount database 
sql clone 'alter database mount clone database'; 
} 
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes
Variable Size 285214576 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes

sql statement: alter system set control_files = ''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes
Variable Size 285214576 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 4 to new; 
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 4; 
switch clone datafile all; 
} 
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 10-JUN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 
Finished restore at 10-JUN-13

datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=817751583 file name=/tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# online the datafiles restored or switched
sql clone "alter database datafile 4 online";
# recover and open resetlogs 
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs; 
} 
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 4 online

Starting recover at 10-JUN-13
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=54 
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=55 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 
archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54 
channel clone_default: deleting archived log(s) 
archived log file name=/tmp/recover/1_54_814717091.dbf RECID=4 STAMP=817751585 
archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55 
channel clone_default: deleting archived log(s) 
archived log file name=/tmp/recover/1_55_814717091.dbf RECID=5 STAMP=817751587 
media recovery complete, elapsed time: 00:00:01 
Finished recover at 10-JUN-13

database opened

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

contents of Memory Script:

{

sql clone "create spfile from memory";

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set control_files =

''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment=

''RMAN set'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes

Variable Size 285214576 bytes

Database Buffers 775946240 bytes

Redo Buffers 5480448 bytes

sql statement: alter system set control_files = ''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes

Variable Size 285214576 bytes

Database Buffers 775946240 bytes

Redo Buffers 5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile 4 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 4;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 10-JUN-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=26 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

Finished restore at 10-JUN-13

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=817751583 file name=/tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# online the datafiles restored or switched

sql clone "alter database datafile 4 online";

# recover and open resetlogs

recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 4 online

Starting recover at 10-JUN-13

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=54

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=55

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54

channel clone_default: deleting archived log(s)

archived log file name=/tmp/recover/1_54_814717091.dbf RECID=4 STAMP=817751585

archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55

channel clone_default: deleting archived log(s)

archived log file name=/tmp/recover/1_55_814717091.dbf RECID=5 STAMP=817751587

media recovery complete, elapsed time: 00:00:01

Finished recover at 10-JUN-13

database opened

At this point, RMAN starts an export/import with datapump to move the table from the auxiliary database back to the target database:

contents of Memory Script:
{ 
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/recover''"; 
# create directory for datapump export 
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/recover''"; 
} 
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover''

Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_kCoq_fqic": 
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB 
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE 
EXPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER 
EXPDP> . . exported "LUDOVICO"."RECO" 5.054 KB 2 rows
EXPDP> Master table "SYS"."TSPITR_EXP_kCoq_fqic" successfully loaded/unloaded 
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_kCoq_fqic is: 
EXPDP> /tmp/recover/tspitr_kCoq_82218.dmp 
EXPDP> Job "SYS"."TSPITR_EXP_kCoq_fqic" successfully completed at Mon Jun 10 17:14:44 2013 elapsed 0 00:00:41
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_kCoq_Fbti" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_kCoq_Fbti":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "LUDOVICO"."RECO" 5.054 KB 2 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_kCoq_Fbti" successfully completed at Mon Jun 10 17:15:12 2013 elapsed 0 00:00:07
Import completed

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/tmp/recover''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/tmp/recover''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover''

Performing export of tables...

EXPDP> Starting "SYS"."TSPITR_EXP_kCoq_fqic":

EXPDP> Estimate in progress using BLOCKS method...

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

EXPDP> Total estimation using BLOCKS method: 64 KB

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

EXPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN

EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

EXPDP> . . exported "LUDOVICO"."RECO" 5.054 KB 2 rows

EXPDP> Master table "SYS"."TSPITR_EXP_kCoq_fqic" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_kCoq_fqic is:

EXPDP> /tmp/recover/tspitr_kCoq_82218.dmp

EXPDP> Job "SYS"."TSPITR_EXP_kCoq_fqic" successfully completed at Mon Jun 10 17:14:44 2013 elapsed 0 00:00:41

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

Oracle instance shut down

Performing import of tables...

IMPDP> Master table "SYS"."TSPITR_IMP_kCoq_Fbti" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_kCoq_Fbti":

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP> . . imported "LUDOVICO"."RECO" 5.054 KB 2 rows

IMPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN

IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

IMPDP> Job "SYS"."TSPITR_IMP_kCoq_Fbti" successfully completed at Mon Jun 10 17:15:12 2013 elapsed 0 00:00:07

Import completed

Finally, the auxiliary instance is cleaned:

Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_temp_8vcv5mrt_.tmp deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_3_8vcv8nfh_.log deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_2_8vcv8fqb_.log deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_1_8vcv86fv_.log deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl deleted
auxiliary instance file tspitr_kCoq_82218.dmp deleted
Finished recover at 10-JUN-13

Removing automatic instance

Automatic instance removed

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_temp_8vcv5mrt_.tmp deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_3_8vcv8nfh_.log deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_2_8vcv8fqb_.log deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_1_8vcv86fv_.log deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl deleted

auxiliary instance file tspitr_kCoq_82218.dmp deleted

Finished recover at 10-JUN-13

We can check if our table is ok:

RMAN> select * from ludovico.reco;

FIELD1
--------------------------------------------------
foo
test

RMAN>

RMAN> select * from ludovico.reco;

FIELD1

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

foo

test

RMAN>

Oh, and yes, now we can select directly from RMAN! 🙂

My opinion

It still needs the amount of space needed to recover the auxiliary instance (system, sysaux, temp and the user tablespace containing the missing data), so it has all the defeats of the typical TSPITR, but it’s automatic so is an improvement for the real life.
Restoring the user tablespace separately from the system tablespaces can be an issue if you’re saving backupsets over tape: you can end up by reading twice the same backupset that could be read once instead.

Cheers

Ludovico

Oracle Database 12c: sequence.nextval as default and identity columns

Posted on June 27, 2013 by Ludovico

Finally! I can count how many times I’ve seen questions like this one.

The new Oracle 12c now allows to define a table with the sequence.nextval directly in the in-line column definition:

SQL> create sequence foo_seq;

Sequence created.

SQL> create table foo (
2 id number default foo_seq.nextval,
3 bar varchar2(50) not null,
4 constraint foo_pk primary key (id)
5 );

Table created.

SQL> insert into foo (bar) values ('baz');

1 row created.

SQL> insert into foo (bar) values ('test');

1 row created.

SQL> insert into foo (bar) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from foo;

ID BAR
---------- --------------------------------------------------
1 baz
2 test
3 whoo

SQL> create sequence foo_seq;

Sequence created.

SQL> create table foo (

2 id number default foo_seq.nextval,

3 bar varchar2(50) not null,

4 constraint foo_pk primary key (id)

5 );

Table created.

SQL> insert into foo (bar) values ('baz');

1 row created.

SQL> insert into foo (bar) values ('test');

1 row created.

SQL> insert into foo (bar) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from foo;

ID BAR

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

1 baz

2 test

3 whoo

But Oracle has fixed this twice, in the new release it’s possible to use identity columns as well, avoiding the necessity to create explicitly a new sequence:

SQL> create table bar (
2 id number generated as identity,
3 foo varchar2(50) not null,
4 constraint bar_pk primary key (id)
5 );

Table created.

SQL> insert into bar (foo) values ('baz');

1 row created.

SQL> insert into bar (foo) values ('test');

1 row created.

SQL> insert into bar (foo) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bar;

ID FOO
---------- --------------------------------------------------
1 baz
2 test
3 whoo

SQL> create table bar (

2 id number generated as identity,

3 foo varchar2(50) not null,

4 constraint bar_pk primary key (id)

5 );

Table created.

SQL> insert into bar (foo) values ('baz');

1 row created.

SQL> insert into bar (foo) values ('test');

1 row created.

SQL> insert into bar (foo) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bar;

ID FOO

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

1 baz

2 test

3 whoo

I’ve said “explicitly” because actually a sequence is created with a system-generated name, so you’ll still need to deal with sequences.

TABLE_NAME TABLE_TYPE
------------------------------- -----------
ISEQ$$_23657 SEQUENCE

TABLE_NAME TABLE_TYPE

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

ISEQ$$_23657 SEQUENCE

cheers

Ludo

Oracle Database 12c: move datafile online

Posted on June 26, 2013 by Ludovico

The new Oracle Database 12c allows to move datafiles ONLINE while they’re been used. This allows great availability when dealing with database moving, compared to the previous approach.

Pre 12c:

copy datafile with RMAN
offline datafile
switch datafile to copy
recover datafile
alter datafile online

From 12c:

move the datafile! 🙂

The actual command for moving the datafile is:

SQL> alter database move datafile 1 to '/data/CLASSIC/system01.dbf';

1	SQL> alter database move datafile 1 to '/data/CLASSIC/system01.dbf';

where the source can be specified using the file#, or the actual path.

The destination must be specified only if moving to a non-OMF file, otherwise it takes the db_create_file_dest parameter:

SQL> alter database move datafile 1;

1	SQL> alter database move datafile 1;

in the latter it will move the system datafile back to my +DATA diskgroup.

So, it’s a great enhancement to move database:

from a storage to another without using ASM
migrating online from an ASM DG to another
moving from FS to ASM and viceversa
(not tested) move datafiles on Windows from a logical disk to another!
etc.

Full example (including some information on the proper MV enqueue):

SQL> select file#, name from v$datafile;

FILE# NAME
---------- ------------------------------------------------------------------------------------------
1 +DATA/CLASSIC/DATAFILE/system.267.814717093
2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123
3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147
4 +DATA/CLASSIC/DATAFILE/users.258.814717205
5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305

SQL> select * from x$ksqst where indx=63;

ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN
---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------
KSQSTEXPL KSQSTEVIDX 
------------------------------------------------------------------------------------------------------------------------ ---------- 
000000008EE528A8 63 1 0 MV 0 0 0 0 0 datafile move 
Held during online datafile move operation or cleanup 157

SQL> alter database move datafile 1 to '/data/CLASSIC/system01.dbf';

Database altered.

SQL>

SQL> select file#, name from v$datafile;

FILE# NAME
---------- ------------------------------------------------------------------------------------------
1 /data/CLASSIC/system01.dbf
2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123
3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147
4 +DATA/CLASSIC/DATAFILE/users.258.814717205
5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305

SQL> select * from x$ksqst where indx=63;

ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN
---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------
KSQSTEXPL KSQSTEVIDX
------------------------------------------------------------------------------------------------------------------------ ----------
000000008EE528A8 63 1 0 MV 56 0 3 53 0 datafile move
Held during online datafile move operation or cleanup 157

SQL> select file#, name from v$datafile;

FILE# NAME

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

1 +DATA/CLASSIC/DATAFILE/system.267.814717093

2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123

3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147

4 +DATA/CLASSIC/DATAFILE/users.258.814717205

5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305

SQL> select * from x$ksqst where indx=63;

ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN

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

KSQSTEXPL KSQSTEVIDX

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

000000008EE528A8 63 1 0 MV 0 0 0 0 0 datafile move

Held during online datafile move operation or cleanup 157

SQL> alter database move datafile 1 to '/data/CLASSIC/system01.dbf';

Database altered.

SQL>

SQL> select file#, name from v$datafile;

FILE# NAME

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

1 /data/CLASSIC/system01.dbf

2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123

3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147

4 +DATA/CLASSIC/DATAFILE/users.258.814717205

5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305

SQL> select * from x$ksqst where indx=63;

ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN

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

KSQSTEXPL KSQSTEVIDX

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

000000008EE528A8 63 1 0 MV 56 0 3 53 0 datafile move

Held during online datafile move operation or cleanup 157

Controlfiles cannot be moved online yet. The other kind of files (temp and redo logs) off course can be moved easily by creating the new ones and deleting the old ones, as it was on pre-12c releases.

Cheers

Ludo