Move That Datafile!

Moving datafiles has always been a pain.  There are several steps, it’s fairly easy to make a mistake and it requires the datafile to be offline.  There are also different steps depending on whether the database is in ARCHIVELOG mode or not.  In ARCHIVELOG mode, the steps are…

1)      Take the tablespace containing the datafile offline

2)      Copy/rename the datafile at the OS layer

3)      Use ALTER TABLESPACE…RENAME DATAFILE to rename the datafile so that the controlfile will be aware of it

4)      Backup the database for recovery purposes (recommended)

If the database is in NOARCHIVELOG mode, you have to shutdown the DB, put it in the MOUNT state, etc, etc.  That’s certainly not that hard to do, but you get the feeling that there should be a better way.  Now in Oracle 12c, there is – using the ALTER DATABASE MOVE DATAFILE command.  With this command, you can move a datafile, while it’s online, in one simple step.  Let’s set this up.

SQL> create tablespace test datafile '/oracle/base/oradata/TEST1/datafile/test01.dbf' size 10m;

Tablespace created.

SQL> create table altdotoracle.tab1 (col1 number) tablespace test;

Table created.

SQL> insert into altdotoracle.tab1 values (1);

1 row created.

SQL> commit;

Commit complete.

Let’s go the extra mile and lock the table in that datafile in another session.

SQL> lock table altdotoracle.tab1 in exclusive mode;

Table(s) Locked.

Now let’s use the command.

SQL> alter database move datafile '/oracle/base/oradata/TEST1/datafile/test01.dbf'

  2   to '/oracle/base/oradata/TEST1/datafile/newtest01.dbf';

Database altered.

That’s all there is to it.  Datafile moved/renamed in one step while a table it contained was locked.

SQL> select file_name from dba_data_files where file_name like '%newtest%';

FILE_NAME

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

/oracle/base/oradata/TEST1/datafile/newtest01.dbf

Oracle 12c - New SYS-level Administration Privileges

For a while now, Oracle has been moving its security model toward a more differentiated set of privileges than just SYSDBA and SYSOPER.  We saw this in 11g with the SYSASM privilege.  This is in response to the growing number of DBA shops that delineate permissions at a more granular level, even among DBAs.  Rather than saying, “Here’s my DBA team, everyone has SYSDBA,” more and more IT shops are defining DBA job roles at a lower level.  If an application DBA never has the job responsibility to shutdown or backup a database, then maybe they don’t need SYSDBA.  However, the lines aren’t always that clear, so Oracle has been adding new levels of admin privileges.  In 12c, they’ve added several.

The SYSBACKUP privilege allows a user to connect to the target database and run RMAN commands, without requiring SYSDBA.  Here’s what it looks like.

/home/oracle:test1:rman

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Aug 13 14:57:41 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target "altdotoracle@test1 as sysbackup"

target database Password:

connected to target database: TEST1 (DBID=1787845942)

RMAN> select user from dual;

using target database control file instead of recovery catalog

USER

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

SYSBACKUP

RMAN> backup tablespace users;

Starting backup at 13-AUG-13

allocated channel: ORA_DISK_1

…

Finished backup at 13-AUG-13

RMAN>

In truth, SYSBACKUP has a lot more rights than just those needed to do something like a hot backup, including startup and shutdown, creating tables and tablespaces and executing a number of supplied packages.  So from that perspective, I’m not exactly sure they hit the mark on this one.  Nevertheless, it does differentiate SYSBACKUP from the god-like SYSDBA admin privilege to some degree.  There are also the new admin privileges SYSDG, for administering Data Guard, and SYSKM to do Transparent Data Encryption (TDE) administration.  Consequently, there are new columns in v$pwfile_users to reflect these new privs.

SQL> desc v$pwfile_users

 Name                        Null?        Type

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

 USERNAME                    VARCHAR2(30)

 SYSDBA                      VARCHAR2(5)

 SYSOPER                     VARCHAR2(5)

 SYSASM                      VARCHAR2(5)

 SYSBACKUP                   VARCHAR2(5)

 SYSDG                       VARCHAR2(5)

 SYSKM                       VARCHAR2(5)

 CON_ID                      NUMBER

If we examine the view itself, we see this in action.

SQL> select * from v$pwfile_users;

USERNAME          SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

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

SYS               TRUE  TRUE  FALSE FALSE FALSE FALSE          0

SYSDG             FALSE FALSE FALSE FALSE TRUE  FALSE          0

SYSBACKUP         FALSE FALSE FALSE TRUE  FALSE FALSE          0

SYSKM             FALSE FALSE FALSE FALSE FALSE TRUE           0

ALTDOTORACLE      FALSE FALSE FALSE TRUE  FALSE FALSE          0

Tightening Security with SELECT ANY DICTIONARY in Oracle 12c

Developers and users sometimes request the SELECT ANY DICTIONARY system privilege to enable them to view various data dictionary tables.  This may be fine for querying DBA_TABLES, etc, but the Oracle data dictionary contains a LOT of information.  Some of the views/tables are compromising from a security standpoint.  One of Oracle’s legendary gaffs in version 9i displayed cleartext passwords for database links in SYS.LINK$.  Yikes.  In version 12c, Oracle has locked down this type of access even further.  Here’s an example from version 11g.

SQL> create user altdotoracle identified by altdotoracle;

User created.

SQL> grant create session, select any dictionary to altdotoracle;

Grant succeeded.

SQL> conn altdotoracle/altdotoracle

SQL> select name, password from sys.user$ where password is not null;

NAME                           PASSWORD

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

SYS                            AAJ125C9483Q017

SYSTEM                         W45825DFFFD37SE

OUTLN                          WW24Z1N6A8ED2E1

ALTDOTORACLE                   73NH15SG3Q2364W

Armed with these password hashes, password cracking tools can be used to compare these values to common dictionary passwords.  When a match is found on a non-complex password, your password is cracked.  (Don’t wear yourself out trying to crack the hashes above, I obfuscated them)  SELECT ANY DICTIONARY no longer allows access to dictionary objects like USER$, ENC$ and DEFAULT_PWD$. Let’s try it.

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> create user altdotoracle identified by altdotoracle;

User created.

SQL> grant create session, select any dictionary to altdotoracle;

Grant succeeded.

SQL> conn altdotoracle/altdotoracle

Connected.

SQL> select name, password from sys.user$ where password is not null;

select name, password from sys.user$ where password is not null

                               *

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> select * from sys.enc$;

select * from sys.enc$

                  *

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> select * from sys.default_pwd$;

select * from sys.default_pwd$

                  *

ERROR at line 1:

ORA-01031: insufficient privileges

Data Pump 12c – Pumping Data with the LOGTIME Parameter

Since its release, Oracle Data Pump has been a worthy successor to the traditional exp/imp tools.  However, one area lacking with Data Pump has been something as simple as the ability to identify how long each step of a Data Pump job actually takes.  The log will show start time at the top of the log and end time at the bottom, but the time of execution for each step is a mystery.  Oracle 12c solves this problem with the LOGTIME parameter, which adds a timestamp to the execution of each step of the Data Pump job.  Here’s what it looks like without the parameter.

/home/oracle:test1:expdp altdotoracle/altdotoracle \

> directory=data_pump_dir dumpfile=expdp.dmp \

> tables=employee

Export: Release 12.1.0.1.0 - Production on Tue Aug 13 09:32:38 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  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

Starting "ALTDOTORACLE"."SYS_EXPORT_TABLE_01":  altdotoracle/******** directory=data_pump_dir dumpfile=expdp.dmp tables=employee

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

. . exported "ALTDOTORACLE"."EMPLOYEE"                   10.93 KB      16 rows

Master table "ALTDOTORACLE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for ALTDOTORACLE.SYS_EXPORT_TABLE_01 is:

  /oracle/base/admin/test1/dpdump/expdp.dmp

Job "ALTDOTORACLE"."SYS_EXPORT_TABLE_01" successfully completed at Tue Aug 13 09:32:51 2013 elapsed 0 00:00:11

With the LOGTIME parameter, each step is prefixed with a timestamp, indicating the start time for each event that is processed.

/home/oracle:test1:expdp altdotoracle/altdotoracle \

> directory=data_pump_dir dumpfile=expdp.dmp \

> tables=employee LOGTIME=ALL

Export: Release 12.1.0.1.0 - Production on Tue Aug 13 09:34:54 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  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

13-AUG-13 09:34:56.757: Starting "ALTDOTORACLE"."SYS_EXPORT_TABLE_01":  altdotoracle/******** directory=data_pump_dir dumpfile=expdp.dmp tables=employee LOGTIME=ALL

13-AUG-13 09:34:57.019: Estimate in progress using BLOCKS method...

13-AUG-13 09:34:57.364: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

13-AUG-13 09:34:57.396: Total estimation using BLOCKS method: 64 KB

13-AUG-13 09:34:57.742: Processing object type TABLE_EXPORT/TABLE/TABLE

13-AUG-13 09:34:57.894: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

13-AUG-13 09:34:57.964: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

13-AUG-13 09:35:04.853: . . exported "ALTDOTORACLE"."EMPLOYEE"   10.93 KB      16 rows

13-AUG-13 09:35:05.123: Master table "ALTDOTORACLE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

13-AUG-13 09:35:05.127: ******************************************************************************

13-AUG-13 09:35:05.128: Dump file set for ALTDOTORACLE.SYS_EXPORT_TABLE_01 is:

13-AUG-13 09:35:05.131:   /oracle/base/admin/test1/dpdump/expdp.dmp

13-AUG-13 09:35:05.134: Job "ALTDOTORACLE"."SYS_EXPORT_TABLE_01" successfully completed at Tue Aug 13 09:35:05 2013 elapsed 0 00:00:09

The parameter works similarly with Data Pump Import.  Note that, although it is documented, the LOGTIME parameter is not described when you do a expdp help=y or impdp help=y command.

RMAN 12c – More SQL without ‘sql’

Up until version 12c, the RMAN command line parser was capable of parsing certain DML and DDL statements.  To do this, we use the “sql” command from the RMAN command line, like this.

11gHost> rman

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Aug 7 15:29:19 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target

connected to target database: TEST2 (DBID=1111111111)

RMAN> sql 'create table altdotoracle (col1 number)';

sql statement: create table altdotoracle (col1 number)

RMAN> sql 'insert into altdotoracle values (1)';

sql statement: insert into altdotoracle values (1)

However, one operation we could not do was SELECT.  The RMAN parser lacked the facility to return arrays of data to the screen.  This shouldn’t be surprising, considering that RMAN is intended to be used to run backup and restore operations.

RMAN> sql 'select * from altdotoracle';

sql statement: select * from altdotoracle

The statement does not error, but no data is returned.  Now, in version 12c, we get two new benefits in this area.  First, we no longer need to use the ‘sql’ command.  For example…

12cHost> rman

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Aug 7 15:35:27 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target

connected to target database: TEST1 (DBID=1212121212)

RMAN> create table altdotoracle (col1 number);

Statement processed

RMAN> insert into altdotoracle values (1);

Statement processed

Additionally, the RMAN parser in 12c allows us to run SELECT commands, as well as DESCRIBE commands.

RMAN> select * from altdotoracle;

      COL1

----------

         1

RMAN> describe altdotoracle

 Name                                      Null?    Type

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

 COL1                                               NUMBER

The ability to use the ‘sql’ RMAN command is still supported for backward compatibility.  A simple thing, but useful!

Oracle 12c - Out With the Grid, In With the Cloud

So Oracle database 12c is out now.  The cloud (and hence the “c”) is hanging over us.  At least we don’t have to hear about “the grid” anymore.  New versions of Oracle are funny things – as DBAs, we’re kinda excited about the possibilities, but then reality sets in and we realize that our company isn’t going to upgrade for a long time.  That’s not necessarily a bad thing – some of us have experienced the pain of the “release 1’s” that have come out in recent years.  There are generally early adopters and then everyone else.  Over the course of my time as a DBA, I’ve swung from wanting to be on “the bleeding edge” to saying “we’re not upgrading until the terminal release!”  Reality is somewhere in the middle – every release probably has something useful to us.  Inevitably, what I see is that new versions come out and DBAs don’t get familiar with the new features.  Let’s be honest – so many features seem oriented towards selling something that we miss the good ones.  But understanding a new feature set can be extremely useful.  I’m not promising anything, but 12c could have that one new thing that could make your life so much easier that you eventually can’t live without it.  Who could really deny the importance of stuff like ASM and the AWR that came with version 10g?  Of course, reading through the actual new features documentation can be about as pleasant as giving yourself a root canal.  So, since I’ve combed through some of the docs, I wanted to spare you the pain and give you some of the good stuff.  Over the next few posts, we’ll take a look at some new features in 12c – I think they could be useful, so maybe you will too.  All of the meat and none of the fat!

New book

Just a quick announcement that my second book is available from Packt Publishing.  OCA Oracle Database 11g: Database Administration I: A Real-World Certification Guide (again with the long title) is designed to be a different kind of certification guide.  Generally, it seems to me that publishers of Oracle certification guides assume that the only people who want to become certified are those with a certain level of experience, like a working DBA with several years on the job.  So, these guides make a lot of assumptions about the reader.  They end up being more about a lot of facts for the test rather than a cohesive learning experience.  My book attempts to target to a different kind of reader.  I've observed in the last several years that many people from non-database backgrounds are setting out to get their OCA or OCP certifications.  These folks don't necessarily bring a lot of knowledge or experience to this attempt, just a strong drive to learn.  My two books are designed to start from the beginning, then take the reader through all of the subjects needed for the certification test.  They're designed to be read straight through, completing the examples along the way.  In a sense, I'm attempting to recreate the experience of one of my Oracle classes in book form. 

You'll find the book at these fine sellers of books.

Packt Publishing
Amazon
Barnes and Noble