Wednesday, December 28, 2011

Track history of changes with Oracle Workspace Manager

Oracle Workspace Manager is a feature introduced in Oracle9i that versions the relational data with no modifications to application
DML operation in a transparent and secure fashion.
It permits simultaneous read and write access to the same prod/dev data.
It can be used to
- Improve concurrency for database updates that complete over days, weeks or months by managing them in workspaces
- Track the history of changes to data and view the database as it existed at any point in time
- Create multiple data scenarios in separate workspaces for what-if analysis

This note here goes in brief over how to version a table to track history of changes and
how you can query data at a previous point in time within your workspace.
This test cases are done in 11gR2.

SQL> show user
USER is "SYS"
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create user hb identified by hb;
User created.
SQL> grant connect, resource, create table to hb;
Grant succeeded.
SQL> grant select_catalog_role to hb;
Grant succeeded.
SQL> EXECUTE DBMS_WM.GrantSystemPriv ('CREATE_ANY_WORKSPACE','HB','YES');
PL/SQL procedure successfully completed.
SQL> conn hb/hb
Connected.
SQL> show user
USER is "HB"
SQL> select count(*) from user_objects;
  COUNT(*)
----------
         0
        
----------CASE1
We create a table and attempt to version enable it.
In order to do so, we must have a primary key defined on it, otherwise will fail, as shown below:

SQL> create table table1(t1_id1 number(9), t1_details varchar2(30));
Table created.
SQL> exec dbms_wm.enableversioning('table1','view_wo_overwrite');
BEGIN dbms_wm.enableversioning('table1','view_wo_overwrite'); END;
*
ERROR at line 1:
ORA-20133: table 'HB.TABLE1' needs to have a primary key
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1
SQL> alter table table1 add constraint pk_t1 primary key (t1_id1);
Table altered.
SQL> select object_name, object_type from user_objects;
OBJECT_NAME                                                                                                              OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------
PK_T1                                                                                                                    INDEX
TABLE1                                                                                                                   TABLE
SQL> exec dbms_wm.enableversioning('table1','view_wo_overwrite');
PL/SQL procedure successfully completed.

Now we will check what other objects are created in order to support version enabling of our table.
We see many views are created, our main table is now a view, but must important, 3 triggers are created:

SQL> select object_name, object_type from user_objects;
OBJECT_NAME                                                                                                              OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- --------
PK_T1                                                     INDEX
TABLE1_BASE                                     VIEW
TABLE1_LT                                          TABLE
TABLE1                                                 VIEW
TABLE1_MW                                        VIEW
TABLE1_CONS                                    VIEW
TABLE1_AUX                                      TABLE
AUX_TABLE1                                      INDEX
TABLE1_AP1$                                     INDEX
TABLE1_AP2$                                     INDEX
TABLE1_HIST                                     VIEW
TABLE1_BPKC                                   VIEW
TABLE1_PKC                                      VIEW
TABLE1_CONF                                   VIEW
TABLE1_PKDB                                   VIEW
TABLE1_PKDC                                   VIEW
TABLE1_PKD                                      VIEW
TABLE1_DIFF                                     VIEW
TABLE1_LOCK                                  VIEW
TABLE1_TI$                                       INDEX
TABLE1_PKI$                                    INDEX
OVM_INSERT_2                                TRIGGER
OVM_UPDATE_2                              TRIGGER
OVM_DELETE_2                              TRIGGER
24 rows selected.

We insert a couple of rows:

SQL> insert into table1 values (1,'first value');
1 row created.
SQL> insert into table1 values (2,'second value');
1 row created.
SQL> insert into table1 values (3,'third value');
1 row created.
SQL> commit;
Commit complete.
SQL> select t1_id1,t1_details from table1;
    T1_ID1 T1_DETAILS
---------- ------------------------------
         1 first value
         2 second value
         3 third value
SQL> desc table1_lt;
 Name                                                                                                      Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- --------------------
--------------------------------------------------
 T1_ID1                                                           NUMBER(9)
 T1_DETAILS                                                VARCHAR2(30)
 VERSION                                                      NUMBER(38)
 CREATETIME                                             TIMESTAMP(6) WITH TIME ZONE
 RETIRETIME                                               TIMESTAMP(6) WITH TIME ZONE
 NEXTVER                                                    VARCHAR2(500)
 DELSTATUS                                                NUMBER(38)
 LTLOCK                                                      VARCHAR2(100)

SQL> select T1_ID1,T1_DETAILS,VERSION,to_char(CREATETIME,'dd-mon-yyyy hh24:mm:ss') create_time,to_char(RETIRETIME,'dd-mon-yyyy hh24:mm:ss') retiretime
 from table1_lt;
    T1_ID1 T1_DETAILS                        VERSION CREATE_TIME          RETIRETIME
---------- ------------------------------ ---------- -------------------- --------------------
         1 first value                             0 27-dec-2011 17:12:00
         2 second value                            0 27-dec-2011 17:12:13
         3 third value                             0 27-dec-2011 17:12:24

We do some changes to the data in order to be able to track the history:
        
SQL> update table1 set t1_details='after first update';
3 rows updated.
SQL> commit;
Commit complete.
SQL> select T1_ID1,T1_DETAILS,VERSION,to_char(CREATETIME,'dd-mon-yyyy hh24:mm:ss') create_time,to_char(RETIRETIME,'dd-mon-yyyy hh24:mm:ss') retiretime
 from table1_lt;
    T1_ID1 T1_DETAILS             VERSION CREATE_TIME          RETIRETIME
---------- ------------------------------ ---------- -------------------- --------------------
         1 first value                    0 27-dec-2011 17:12:00 27-dec-2011 17:12:11
         2 second value               0 27-dec-2011 17:12:13 27-dec-2011 17:12:11
         3 third value                  0 27-dec-2011 17:12:24 27-dec-2011 17:12:11
         1 after first update          0 27-dec-2011 17:12:11
         2 after first update          0 27-dec-2011 17:12:11
         3 after first update          0 27-dec-2011 17:12:11
6 rows selected.
SQL> delete from table1 where t1_id1=3;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select T1_ID1,T1_DETAILS,VERSION,to_char(CREATETIME,'dd-mon-yyyy hh24:mm:ss') create_time,to_char(RETIRETIME,'dd-mon-yyyy hh24:mm:ss') retiretime
, delstatus from table1_lt;
    T1_ID1 T1_DETAILS  VERSION CREATE_TIME  RETIRETIME  DELSTATUS
---------- ------------------------------ ---------- -------------------- -------------------- ----------
         1 first value              0 27-dec-2011 17:12:00 27-dec-2011 17:12:11    10
         2 second value         0 27-dec-2011 17:12:13 27-dec-2011 17:12:11    10
         3 third value            0 27-dec-2011 17:12:24 27-dec-2011 17:12:11     10
         1 after first update       0 27-dec-2011 17:12:11                              11
         2 after first update       0 27-dec-2011 17:12:11                              11
         3 after first update    0 27-dec-2011 17:12:11 27-dec-2011 17:12:37     11
         3 after first update       0 27-dec-2011 17:12:37                             -12
7 rows selected.
SQL> select * from table1;
    T1_ID1 T1_DETAILS
---------- ------------------------------
         1 after first update
         2 after first update

----------CASE2
We now see how we can use our workspace to go to previous point in time to query data.

SQL> var date1 varchar2(30);
SQL> exec select to_char(sysdate, 'dd-month-yyyy hh24-mi-ss') into :date1 from dual;
PL/SQL procedure successfully completed.
SQL> insert into table1 values (4,'fourth value');
1 row created.
SQL> insert into table1 values (5,'fifth value');
1 row created.
SQL> insert into table1 values (6,'sixth value');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from table1;
    T1_ID1 T1_DETAILS
---------- ------------------------------
         1 after first update
         2 after first update
         4 fourth value
         5 fifth value
         6 sixth value
SQL> exec dbms_wm.gotoDate(to_date(:date1,'dd-month-yyyy hh24-mi-ss'));
PL/SQL procedure successfully completed.
SQL> select * from table1;
    T1_ID1 T1_DETAILS
---------- ------------------------------
         1 after first update
         2 after first update   

We disable the versioning and we see that all other objects have been dropped.

SQL> exec dbms_wm.disableversioning('TABLE1');
PL/SQL procedure successfully completed.
SQL> select count(*) from user_objects;
  COUNT(*)
----------
         2

----------CASE3
We are now considering the case of versioning some tables which are in a parent-child relation.
For version enabling such tables it must be followed this order: first version enable the child tables and then the parent tables.
For version disabling the reverse order must be followed.

SQL> create table t1 (id1 number primary key, detail1 varchar2(10));
Table created.
SQL> create table t2 (id2 number primary key, detail2 varchar2(10), id1 references t1(id1));
Table created.
SQL> create table t3 (id3 number primary key, detail3 varchar2(10), id2 references t2(id2));
Table created.
SQL> exec dbms_wm.enableversioning('T2');
BEGIN dbms_wm.enableversioning('T2'); END;
*
ERROR at line 1:
ORA-20100: 'HB.T2' is both parent and child tables of referential integrity constraints
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1

SQL> exec dbms_wm.enableversioning('T1');
BEGIN dbms_wm.enableversioning('T1'); END;
*
ERROR at line 1:
ORA-20101: child table must be version enabled
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1

SQL> exec dbms_wm.enableversioning('T3');
PL/SQL procedure successfully completed.
SQL> exec dbms_wm.enableversioning('T2');
PL/SQL procedure successfully completed.
SQL> exec dbms_wm.enableversioning('T1');
PL/SQL procedure successfully completed.
SQL> exec dbms_wm.disableversioning('T3');
BEGIN dbms_wm.disableversioning('T3'); END;
*
ERROR at line 1:
ORA-20104: cannot version disable this table
ORA-06512: at "WMSYS.LT", line 9355
ORA-06512: at line 1

Now we do the cleanup:

SQL> exec dbms_wm.disableversioning('T1');
PL/SQL procedure successfully completed. 
SQL> exec dbms_wm.disableversioning('T2');
PL/SQL procedure successfully completed.
SQL> exec dbms_wm.disableversioning('T3');
PL/SQL procedure successfully completed.
SQL> drop table t3;
 
Table dropped.
SQL> drop table t2;
Table dropped.
SQL> drop table t1;
Table dropped.
        
        
References:
Oracle Workspace Manager - an Example (9i,10g,11g) [ID 181465.1]
Introduction to Workspace Manager [ID 156574.1]
ORA-20100, ORA-20101 Trying to Version Enable a Table [ID 263431.1]
Frequently Asked Questions for Oracle Workspace Manager (OWM) [ID 156963.1]

Monday, November 14, 2011

Sizing UNDO tablespace

When sizing Undo tablespace we shall be aware of the following aspects:
- this shall be large enough to store the case of all the undo generated by concurrent transactions, that will be active undo;
- enough undo to satisfy the longest running query, unexpired undo.

In advanced systems we shall also consider undo space to allow flashback queries. This is not treated in this note.
The job is to calculate the rate at which undo is being generated at the peak workload
and multiply by the length of your longest query.
All the information we need is in V$UNDOSTAT.

For accuracy, the test is done in an isolated environment,
with controlled number of users and active transactions - only the ones which run the below sessions.

SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

We first create two tables that we will use for our demonstration:

SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create table t2 as select * from dba_objects;

We generate some undo records.

Session 1:
SQL> update t1 set owner='TEST1',object_name='OBJECT_T1';
72462 rows updated.
Elapsed: 00:00:06.99
Session 2:
SQL> update t2 set owner='TEST2',object_name='OBJECT_T2';
72463 rows updated.
Elapsed: 00:00:09.43

Check undo in MB generated for the 2 running queries:

SQL> select
  2        -- s.OSUSER,
  3         round(t.used_ublk * bs.block_size / 1024 / 1024, 2) mb_used,
  4         (select sa.SQL_TEXT
  5            from v$sqlarea sa
  6           where sa.SQL_ID = s.SQL_ID
  7             and rownum = 1) as sql_text
  8    from v$session s,
  9         v$transaction t,
 10         (select block_size
 11            from dba_tablespaces
 12           where upper(tablespace_name) like 'UNDO%') bs
 13   where t.addr = s.taddr
 14   order by mb_used desc;
   MB_USED
----------
SQL_TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
     13.11
update t1 set owner='TEST1',object_name='OBJECT_T1'
      13.1
 update t2 set owner='TEST2',object_name='OBJECT_T2'

So we have ~26 MB of active undo data.

We don't commit the work above, so that we use this undo data when running the below long running query.
When considering the longest running query in the system, that might not necesarily look for undo data at all times when is launched,
but we shall be prepared for the worst scenario, when we have an overlap.
In session 3, we run a query that will make use of the undo data, and we intentionally force a cartesian product to make it run for a longer period of time.

Session 3:
SQL> @temp.sql;

With temp.sql having the following content (we call it from script since we want to inhibit the result which will be displayed to SQL*Plus output and 'set termout off' does not supress output when typed interactively):

set termout off
select t1.owner,t2.owner from t1,t2;
exit;

Check the longest running query:
SQL> select * from
  2  (select max(maxquerylen),maxqueryid from v$undostat group by maxqueryid order by max(maxquerylen) desc)
  3  where rownum <=1;
MAX(MAXQUERYLEN) MAXQUERYID
---------------- -------------
            3472 gt9vc8t3aqtm1

Our query is running for almost 1 hour now.

SQL> select sql_text from v$sqlarea where sql_id = 'gt9vc8t3aqtm1';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
select t1.owner,t2.owner from t1,t2

Now we will run a query that computes the recommended UNDO size for the test case we created.
This is basically the sql we are interested in computing the UNDO size:

SQL> select round(
  2          max_undoblks/(round((end_time-begin_time)*1440,2)*60)*        --determine the maximum size of undo blocks generated each second
  3          (select block_size/1024
  4              from dba_tablespaces
  5            where 1=1
  6                and contents='UNDO'
  7                and  tablespace_name = (select value
  8                                        from v$parameter
  9                                        where name='undo_tablespace'))*  --determine the blocksize of the active UNDO tablespace
 10           (select max(maxquerylen) from v$undostat)/                     --longest running query in seconds
 11           1024) as "Recommended size of UNDO in MB"
 12  from (select * from
 13                                   (select /*+ FIRST_ROWS(1)*/
 14                                              max(undoblks) as max_undoblks ,begin_time,end_time
 15                                    from v$undostat
 16                                    group by begin_time,end_time
 17                                    order by max(undoblks) desc)
 18            where rownum=1);
Recommended size of UNDO in MB
------------------------------
                           164
                          
The conclusions here can be long debated:
- yes, we can set undo_retention to 60 minutes or more - but without retention guarantee, this cannot be of too much help;
- if we do guarantee the retention period, without a proper sizing of UNDO, we may face the situation when the transactions will fail in order
for the retention period to be enforced. Users won't be very happy about this.
The test case here is not intended to be generalistic, is made for demonstration purpose only.
The script which computes the recommended UNDO size shall be used wisely, this can be misleading without properly knowing the business side.
(Yes, in some of the tests I made, it indicated to size the UNDO to 9TB in order to cover the worst scenario).
In the end of the day, all you have to do is monitoring, monitoring, monitoring.                  

Tuesday, October 11, 2011

ORA-00030 raised on kill session - troubleshoot with Oracle pstack

It all started with a DDL I had to perform - a Truncate.
This failed as it follows:
truncate table TRANSACTION_FLAGS_IOT;

truncate table TRANSACTION_FLAGS_IOT
Error report:
SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
00054. 00000 -  "resource busy and acquire with NOWAIT specified"
*Cause:    Resource interested is busy.
*Action:   Retry if necessary.

The error tells us that I still have an active DML on the resource, which have found being true, using the below query:

select session_id,owner,name,mode_held,blocking_others;
from dba_dml_locks
where name like 'TRANSACTION_FLAGS_IOT'
151 SMART TRANSACTION_FLAGS_IOT Row-X (SX) Not Blocking

Now, I want to see some more details on this session:

select
sid, serial#,paddr, status,type, last_call_et,blocking_Session,event,wait_class,state, command
from v$session
where sid=151

151 2439 000000015F4DBEA0 ACTIVE USER 537179  db file sequential read User I/O WAITING 0

Since this session is in Waiting state for so much time, I confirmed with users this can be safely killed:

alter system kill session '151,2439';

alter system kill session '151,2439'
Error report:
SQL Error: ORA-00030: User session ID does not exist.
00030. 00000 -  "User session ID does not exist."
*Cause:    The user session ID no longer exists, probably because the
           session was logged out.
*Action:   Use a valid session ID.

This normally happens when you want to kill a session that has a (large) transaction to rollback.
We cannot kill it since it has to rollback, so we expect that the serial# in v$session for the process will keep changing, but in my case, this change of serial# does not happen.
Such cleanup of a rollback is on a low priority, done by SMON background process.
I kept checking and checking, my transaction was not doing a rollback in the shadow, I suspect it was somehow blocked.
Now I don't want to kill the OS process associate with my session, I want to understand where is stuck.
So I identify the OS process as being 1783:

select addr,pid,spid,username,pga_used_mem from v$process where addr = '000000015F4DBEA0'
000000015F4DBEA0 22 1783 oracle 2343004

I verify the existance of the process on the DB server:

[oracle@performance ~]$ ps -flp 1783
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 D oracle    1783     1  0  77   0 - 1108041 sync_p Aug10 ?      00:54:24 oracletoptech (LOCAL=NO)

Now, in order to track the operations done in the background by my process, I take a pstack (process stack - current state of server process 1783):

[oracle@performance ~]$ pstack 1783
#0  0x000000364c60e3f3 in __pread_nocancel () from /lib64/libpthread.so.0
#1  0x00000000085b9ee1 in skgfqio ()
#2  0x000000000849c745 in ksfd_skgfqio ()
#3  0x000000000849ba4b in ksfd_io ()
#4  0x000000000849b104 in ksfdread ()
#5  0x0000000001b28a4d in kcfrbd1 ()
#6  0x0000000000d1d9c8 in kcbzib ()
#7  0x00000000082e5a04 in kcbgcur ()
#8  0x00000000082adb53 in ktbgcur ()
#9  0x00000000015353d5 in kdiulk ()
#10 0x0000000008476bf4 in kcoubk ()
#11 0x0000000008291fce in ktundo ()
#12 0x0000000000c53a5f in kturCurrBackoutOneChg ()
#13 0x0000000000c52037 in ktuabt ()
#14 0x0000000000c4c516 in ktcAbortTxn ()
#15 0x0000000000c46a23 in ktcsod ()
#16 0x000000000822c757 in kssdel ()
#17 0x000000000822da91 in kssdch ()
#18 0x0000000000b5dc5b in ksuxds ()
#19 0x0000000000b5d1b7 in ksudel ()
#20 0x00000000014394e1 in opidcl ()
#21 0x0000000001437bb1 in opidrv ()
#22 0x00000000018aac5b in sou2o ()
#23 0x00000000009d3eb5 in opimai_real ()
#24 0x00000000018afeea in ssthrdmain ()
#25 0x00000000009d3e21 in main ()

This shows that my process is doing some work, and have to identify where is waiting.
The mnemonics shown are described in ORA-600 Lookup Error Categories [ID 175982.1], for example, reading it buttom-up
    - ktc - transaction control operations at the block level
    - ktcAbortTxn - function to abort transaction
    - ktu - internal management of undo and rollback segments
The last (from buttom-up) command is __pread_nocancel () from /lib64/libpthread.so.0
The pread system call does an atomic position-and-read, in our case of /lib64/libpthread.so.0.
So I am having a blocking read, but why on /lib64/libpthread.so.0? Do I have this file, why my Oracle account is not able to read it?

[oracle@performance ~]$ cd /lib64
[oracle@performance lib64]$ ls -lrt libpthread.so.0
lrwxrwxrwx 1 root root 17 Nov 23  2010 libpthread.so.0 -> libpthread-2.5.so

The file is there, is defined as a symbolic link, but am not able to read it.
Now I will look in my environment variable LD_LIBRARY_PATH and will find the answer, the 64 bit library in not defined in my environment:

[oracle@performance ~]$ echo $LD_LIBRARY_PATH
/u01/app/oracle/product/11.1.0/db_1/lib:/lib:/usr/lib

So the /lib64/libpthread.so.0 could have never been able to be read with my current definition of the $LD_LIBRARY_PATH.
I had to do a classic kill -9 1783 in order to kill the process and continue to innitiate the Truncate command.
I then changed the variable definition.

References
ORA-600 Lookup Error Categories [ID 175982.1]
ORA-04031 "KSFQ Buffers" ksmlgpalloc [ID 453521.1]
http://blog.tanelpoder.com/2007/09/06/advanced-oracle-troubleshooting-guide-part-3-more-adventures-in-process-stack/

Monday, October 10, 2011

Read alert log file in Oracle 11g

In Oracle 11g we have the possibility to read the alert log file directly from a X$ view.
This view can be read, only when connected with sysdba role.

Below sql will display the content of the alert log file for the current sysdate , however you can restrict the result set based on your needs:

select to_char(originating_timestamp, 'DD-MON-YYYY HH:MI:SS HH24') "TIME",
       MESSAGE_TEXT
from   X$DBGALERTEXT
where  to_char(originating_timestamp,'DD-MON-YYYY') = TO_CHAR(SYSDATE,'DD-MON-YYYY')
ORDER BY originating_timestamp ASC

Change DB name for a cloned DB

Clone a DB and ORACLE_HOME:

1) perform a cold backup of source DB, and copy the following to the target machine              :
  a) oracle software
  b) oracle home and datafiles (datafiles, controlfiles, redo log files)
  c) configuration files, environment variables, system files
 
2) on the target machine
  a) edit the init ora file and keep the db_name and db_unique_name as in source DB
3) start-up and mount the database with the source db name unchanged
SQL> startup pfile='/u01/DEV3/apps/proddb/11.2.0/dbs/initDEV3.ora nomount;
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size                  2213136 bytes
Variable Size            3489663728 bytes
Database Buffers         1811939328 bytes
Redo Buffers               40914944 bytes
SQL> alter database mount;
Database altered.
SQL> exit

4) backup the control file to trace as 'file_name' in order to edit the path of the datafiles
SQL> alter database backup controlfile to trace as '/u01/DEV3/apps/proddb/11.2.0/dbs/ctl_bck.sql';
Database altered.
SQL> exit

5) edit the control file sql script in order for the datafiles and redo log files to point to the correct location on the target machine

6) bring the database to nomount in order to run the modified control file script
SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/u01/DEV3/apps/proddb/11.2.0/dbs/initDEV3.ora' nomount;
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size                  2213136 bytes
Variable Size            3489663728 bytes
Database Buffers         1811939328 bytes
Redo Buffers               40914944 bytes
SQL>  @ctl_bck.sql
Control file created.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

SQL> exit

The error is raised since the control file script already contains the statement to mount the database.

7) Open the database in order for the file header to synchronize and your new controlfile to become current, then shut it down:
SQL> alter database open;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
8) Startup mount - in order to change the DB name         
SQL> startup pfile='/u01/DEV3/apps/proddb/11.2.0/dbs/initDEV3.ora' nomount;
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size                  2213136 bytes
Variable Size            3489663728 bytes
Database Buffers         1811939328 bytes
Redo Buffers               40914944 bytes
SQL> alter database mount;
Database altered.
             
9) Change the DBID, using 'nid'
applprod@gvaebsdb3:/u01/DEV3/apps/proddb/11.2.0/dbs $ nid target=system/<password> DBNAME=DEV3
DBNEWID: Release 11.2.0.1.0 - Production on Mon Oct 10 12:38:44 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to database PROD (DBID=141797709)
Connected to server version 11.2.0
Control Files in database:
    /u02/DEV3/dbf/DEV3/control01.ctl
    /u01/DEV3/apps/proddb/flash_recovery_area/PROD/control02.ctl
Change database ID and database name PROD to DEV3? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 141797709 to 3627549972
Changing database name from PROD to DEV3
    Control File /u02/DEV3/dbf/DEV3/control01.ctl - modified
    Control File /u01/DEV3/apps/proddb/flash_recovery_area/PROD/control02.ctl - modified
    Datafile /u02/DEV3/dbf/DEV3/system01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/sysaux01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_MEDIA03.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/users01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/example01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/UNDO01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_ARCHIVE01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_NOLOGGING01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_QUEUES01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_SEED01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_SUMMARY01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TOOLS01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/DBA01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/DISCOVERER01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/INTERIM.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_INTERFACE01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_INTERFACE02.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_MEDIA01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_MEDIA02.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX02.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX03.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX04.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX05.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX06.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_IDX07.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA01.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA02.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA03.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA04.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA05.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA06.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TX_DATA07.db - dbid changed, wrote new name
    Datafile /u02/DEV3/dbf/DEV3/APPS_TS_TEM_MV01.db - dbid changed, wrote new name
    Control File /u02/DEV3/dbf/DEV3/control01.ctl - dbid changed, wrote new name
    Control File /u01/DEV3/apps/proddb/flash_recovery_area/PROD/control02.ctl - dbid changed, wrote new name
    Instance shut down
Database name changed to DEV3.
Modify parameter file and generate a new password file before restarting.
Database ID for database DEV3 changed to 3627549972.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

10) Edit the init<SID>.ora file and change the db_name and db_unique_name to the new DB name
11) Start the db using the new init.ora file and check the status and the name of the db:
applprod@gvaebsdb3:/u01/DEV3/apps/proddb/11.2.0/dbs $ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 10 12:41:56 2011
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/u01/DEV3/apps/proddb/11.2.0/dbs/initDEV3.ora'
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size                  2213136 bytes
Variable Size            3489663728 bytes
Database Buffers         1811939328 bytes
Redo Buffers               40914944 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select name,db_unique_name from v$database;
NAME      DB_UNIQUE_NAME
--------- ------------------------------
DEV3      DEV3

12) Create a spfile from pfile in order for the database to start using a spfile on the next startup
SQL> create spfile from pfile;
File created.

References: MOS: [ID 28433.1]