Skip navigation.

Laurent Schneider

Syndicate content
Oracle Certified Master
Updated: 15 hours 51 min ago

duplicate to a future date

Sat, 2016-05-21 07:48

If you work with large databases, you often wait way to long for the clones. Typically you want to duplicate a 10TB database to production timestamp 9am, and you start at 9am and then you wait for hours.

Is it possible to start the clone, let’s say, at midnight, and set until time 9am?

No! You’ll get


RMAN-06617: UNTIL TIME (2016-05-21 09:00:00) is ahead of last NEXT TIME in archived logs (2016-05-20 23:58:52)

But… you could start to restore the datafiles at midnight.


sqlplus sys/***@db02 as sysdba <<EOF
  alter system set db_name='DB01' scope=spfile;
  alter system set db_unique_name='DB02' scope=spfile;
  startup force nomount
EOF

rman target sys/***@db01 auxiliary sys/***@db02 <<EOF
   restore clone primary controlfile;
   alter clone database mount;

run {
   set newname for datafile  1 to
 "/db02/system01.dbf";
   set newname for datafile  2 to
 "/db02/sysaux01.dbf";
   set newname for datafile  3 to
 "/db02/undotbs1_02.dbf";
   set newname for datafile  4 to
 "/db02/users01.dbf";
   restore clone database
   ;
}
EOF

This is exactly when RMAN does when you issue a duplicate. You could use the supported RESTORE command instead of the unsupported RESTORE CLONE command. But then it’ll get a bit more complex as you need to find out the location of your backup and so on.

At 9am, you issue your duplicate, and you’ll see


skipping datafile 1; already restored to file /db02/system01.dbf
skipping datafile 2; already restored to file /db02/sysaux01.dbf
skipping datafile 3; already restored to file /db02/undotbs1_02.dbf
skipping datafile 4; already restored to file /db02/users01.dbf

You just saved nine hours &#x1f642;

column width change in 12c

Thu, 2016-02-18 12:14

In 11g I used to have 30 characters width in my dictionary


SQL> select table_name, column_name from user_tab_columns;

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
BONUS                          COMM
BONUS                          SAL
BONUS                          JOB
BONUS                          ENAME
DEPT                           LOC
DEPT                           DNAME
DEPT                           DEPTNO
EMP                            DEPTNO
EMP                            COMM
EMP                            SAL
EMP                            HIREDATE

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMP                            MGR
EMP                            JOB
EMP                            ENAME
EMP                            EMPNO
SALGRADE                       HISAL
SALGRADE                       LOSAL
SALGRADE                       GRADE

18 rows selected.

Which was pretty nice to read with no setting.

Now in 12c it is ugly enought to make your eyes tired


SQL> select table_name, column_name from user_tab_columns;

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
DEPT
DEPTNO

DEPT
DNAME

DEPT
LOC

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
EMPNO

EMP
ENAME

EMP
JOB

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
MGR

EMP
HIREDATE

EMP
SAL

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
COMM

EMP
DEPTNO

BONUS
ENAME

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
BONUS
JOB

BONUS
SAL

BONUS
COMM

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
SALGRADE
GRADE

SALGRADE
LOSAL

SALGRADE
HISAL

18 rows selected.

SQL>

This is due to a change of the width of the underlying column in the dictionary. Probably a good-news for our data modeling colleagues that it may be 128 in future.

But currently it is only 30 characters in 12c. So why not format it correctly ?

Simply add the format in $ORACLE_HOME/sqlplus/admin/glogin.sql


col TABLE_NAME for a30
col COLUMN_NAME for a30 

DISCLAIMER: it’s fine to add UNIQUE_KEY_LEVEL_NAME or REFERENCED_TRIGGER_NAME, but you may have application tables that have columns called OWNER or USER, it is probably safer to not assume they are all smaller than 30 chars, so don’t add common names.