Thursday, June 19, 2014

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

Wednesday, June 4, 2014

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