How to clone a database using a cold backup

There is a very good documentation available on cloning at http://www.samoratech.com/TopicOfInterest/swCloneDB.htm
With due respect to the above documentation, I am trying to be more descriptive in approach. These are purely based on my experience.

1. Decide carefully about the location and server for the new database's datafiles, redo files and control files. Make sure you create all the directories where you want to keep the datafiles, redofiles and control files(like /u02/oradata/$ORACLE_SID), alert log (/u01/app/oracle/admin/$ORACLE_SID/bdump), trace files(/u01/app/oracle/admin/$ORACLE_SID/udump), parameter file(/u01/app/oracle/admin/$ORACLE_SID/pfile)

2. Generate the script for creating the control files for the destination database. To do this, while logged in as SYS to the source database, do ALTER DATABASE BACKUP CONTROLFILE TO TRACE. This will generate a trace file at /u01/app/oracle/admin/source_SID/udump containing the CREATE CONTROLFILE script. Edit the trace file to keep only the relevant script for creating the new control file and save with a suitable name. The Control file script should contain RESETLOGS because we are going to create a brand new cloned database. For example, the first 2 lines of the contro file script should look like:
STARTUP NOMOUNT pfile=/u01/app/oracle/admin/$ORACLE_SID/pfile/$ORACLE_SID.ora
CREATE CONTROLFILE set DATABASE "DD707" resetlogs noarchivelog

3. To know the name and locations of all datafiles to be copied from source to destination

SQL>select file_name from dba_data_files

4. To know the name and locations of all redo files to be copied from source to destination
SQL>select member from v$logfile

5. Shutdwn the source database
SQL>SHUTDOWN IMMEDIATE

6. Copy all the datafiles and redo log files of the source database to the location holding the destination database's datafiles and redo log files. If it is on the same server, use "cp". If the destination is on a different server, use"scp". For example, change your directory to location of source database's datafiles $cd /u02/oradata/$ORACLE_SID and then do
"$ scp -p xyz.dbf oracle@:/u01/app/oracle/product/10.2.0/dbs/xyz.dbf"

7. Copy the CREATE CONTROLFILE script generated in Step#2 to a secure location (like /u01/app/oracle/admin/dest_SID/udump).

8. Copy the source database's init$ORACLE_SID.ora file as destination database's init$ORACLE_SID.ora at /u01/app/oracle/admin/$ORACLE_SID/pfile.

9. In the copied init.ora, make sure you edit all relevant parameters which are specific to the source database (like udump, bdump, DB_NAME, INSTANCE_NAME etc). These should be specific to destination database.

10. Create a soft link at $ORACLE_HOME/dbs for the parameter file. For example, change the directory to $ORACLE_HOME/dbs
$cd $ORACLE_HOME/dbs and then do "$ ln -s /u01/app/oracle/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora init$ORACLE_SID.ora"
where $ORACLE_SID is the destination SID.

Please note that if in Step#8, you copy the pfile directly to $ORACLE_HOME/dbs on the destination, you can skip the Step#10.

11. Now edit tnsnames, listener and oratab files on the destnation server to have entries for the new destination database. All 3 are usually kept at /var/opt/oracle.

12. Now set your environment for the new SID.
$ . oraenv

13. Log in to the idle instance (for det database) as SYS
SQL> sqlplus '/as sysdba'
You should see the message: logged into idle instance

14. Here run the command for creating the CONTROLFILE. Assuming that you copied the script in Step#5 to /u01/app/oracle/admin/$ORACLE_SID/udump and name of the script is ctrlnew.sql, do the foll:
SQL>@/u01/app/oracle/admin/$ORACLE_SID/udump/ctrlnew.sql
Remember that Control file can be created in the NOMOUNT state only. Your CREATE CONTROLFILE command should have the first command as STARTUP NOMOUNT pfile=
If successful, you would get a message "controlfile created"

15. Now open the database with resetlogs
SQL>ALTER DATABASE OPEN RESETLOGS
If this successfuly runs and says "database altered" your cloned destination database is ready to be used.

-Rohit

How to change SPFILE parameters for a RAC database

Well, this is a simple task as it sounds. But believe me when i was supposed to do this for the first time in my career i had a hard time searching for exact solution. Here i am just sharing my experience in doing this task
There are definitely 2 known methods available to perform this task

METHOD 1:
Simply issue the following SQL statement from any of the nodes
ALTER SYSTEM SET = scope=spfile;
There are 3 possible values for the 'scope' clause in this statement:
1. MEMORY: The change is immediate but will not be available after next startup or reboot of the instance.
2. SPFILE: The change will be effective in SPFILE only and will be available after next startup or reboot
3. BOTH: The change is effective for both MEMORY and SPFILE and will be available after next startup also.
Default is BOTH
You could also specify another clause called 'sid' at the end of the above ALTER statement which is specifically meant for a RAC database. This is to specify the instance where you want to make that change. For example,
ALTER SYSTEM SET = scope=spfile sid='*';
means that this particular change in the parameter will happen on all instances after rebooting them (Default is '*')

METHOD 2:
Another method to change a parameter in the spfile is to export it to a pfile, change it and then create a new spfile. Let me detail out the various steps involved:
1. On one instance, create a pfile from the existing spfile: SQL> CREATE PFILE FROM SPFILE. This will create a pfile called initSID.ora at /$ORACLE_HOME/dbs
2. Edit the resulting PFILE initSID.ora in a vi editor (Add/alter the required parameter). You should use *. so that this parameter value is applied to all instances
3. Now shutdown all the instances
4. startup the instance (and hence the database) where you created and altered the pfile using this pfile only
STARTUP PFILE=$ORACLE_HOME/dbs/initSID.ora
Do not start other instances yet
5. Now through this instance only, create a new spfile (which can be at a common location being accesses by all instances)
CREATE SPFILE=‘commom_location/spfile.ora’ FROM PFILE=‘$ORACLE_HOME/dbs/initSID.ora’;
This will overwrite the existing spfile with the spfile which has the new/altered parameter
6. Now shutdown this instance again
7. Now startup normally all the 3 instances without PFILE or SPFILE option:
STARTUP
By default startup will now consider the new spfile
8. To confirm that the new parameters have been set/removed, issue following sql statement from all 3 instances
SHOW PARAMETER

The method would not be much different if this is a single instance database instead of a RAC database (In Step#7 you would be starting only that single instance)

-Rohit