Just Clone Database - CREATE CONTROLFILE SET DATABASE

Just some idea, But It's great for cloned database. You can read on Super fast Database Copying/Cloning Oracle Tips by Burleson Consulting.
This is idea for using CREATE CONTROLFILE SET DATABASE "NEW_DATABASE_NAME" RESETLOGS
Why I use it? because I use Oracle 9i (Don't ask, why don't I upgrade?) and No Archive Mode.

First step this , create controlfile to trace.

SQL> alter database backup controlfile to trace as '/tmp/ctrl.trc';

Check in '/tmp/ctrl.trc' file.

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 2
MAXDATAFILES 256
MAXINSTANCES 1
MAXLOGHISTORY 1134
LOGFILE
GROUP 1 '/oradata/ORCL/redo11.log' SIZE 20M,
GROUP 2 '/oradata/ORCL/redo21.log' SIZE 20M
DATAFILE
'/oradata/ORCL/system.dbf',
'/oradata/ORCL/undo.dbf',
'/oradata/ORCL/data01.dbf',
'/oradata/ORCL/index01.dbf'
CHARACTER SET US7ASCII
;

Change:

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS

TO

CREATE CONTROLFILE SET DATABASE "NEW_ORCL" RESETLOGS

I don't want to write How to success for cloned database from this idea. I mean... about
- Copy cold backup files to (new server).
- Change database files + folder names blah blah...
- Create, Modify and Check initNEW_ORCL.ora, orapwNEW_ORCL files.
- Check control_files parameter and then move/remove control files before clone database.
- set ORACLE_SID=NEW_ORCL

I just inform by using CREATE CONTROLFILE SET DATABASE "NEW_DATABASE_NAME" RESETLOGS
OK.. then new script:

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "NEW_ORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 2
MAXDATAFILES 256
MAXINSTANCES 1
MAXLOGHISTORY 1134
LOGFILE
GROUP 1 '/oradata/NEW_ORCL/redo11.log' SIZE 20M,
GROUP 2 '/oradata/NEW_ORCL/redo21.log' SIZE 20M
DATAFILE
'/oradata/NEW_ORCL/system.dbf',
'/oradata/NEW_ORCL/undo.dbf',
'/oradata/NEW_ORCL/data01.dbf',
'/oradata/NEW_ORCL/index01.dbf'
CHARACTER SET US7ASCII
;

ALTER DATABASE OPEN RESETLOGS;

Don't forgot to create Temporary Tablespace, After open Database.

Oracle 9.2.0.6... get Wrong Results with using Variable

With Oracle 9.2.0.6 on Sparc...

NAME column is varchar2.

Test query...

SQL> select count(*) from TABLE_NAME where NAME = 'TEST';

  COUNT(*)

----------

         1

Use Variable...

SQL> declare

  2    V varchar2(17) := 'TEST';

  3    a number;

  4  begin

  5  dbms_output.put_line('value='||V );

  6  select count(*) into a  from  TABLE_NAME WHERE NAME = V;

  7  dbms_output.put_line(a);

  8  end;

  9  /

value=TEST

10693

This PL/SQL gets wrong result from table...

So, try to new... PL/SQL... Variable using "execute immediate"  

SQL> declare

  2    V varchar2(17) := 'TEST';

  3    a number;

  4  begin

  5  dbms_output.put_line('value='||V );

  6  execute immediate 'select count(*)  FROM TABLE_NAME WHERE NAME = :p1' into a using V  ;

  7  dbms_output.put_line(a);

  8  end;

  9  /

value=TEST

1

That is Right Result.

Remark:

This case make wrong Data... Because used  "COLUMN NAME" to be "VARIABLE NAME" on PL/SQL and ...,  It make sql statement like: "where column1 = column2" (see on every VERSIONs). So,  That should to avoid  using "COLUMN NAME" to be "VARIABLE NAME" on PL/SQL and ... , Anyway If... need to use!..., using  "EXECUTE IMMEDIATE" can help! 

Thank you Gary MYERS