Archive

Archive for the ‘Cloning’ Category

Oracle – Datapump example

August 4, 2011 1 comment

To run Oracle Datapump you will need to create a datapump directory where your dump files will be located.

SQL> CREATE DIRECTORY datadir AS '/oaslive/dpdump';

The datapump command for export is as follows:

expdp user/password@orasid tables=tmp_interface_definitions, tmp_interface_registrations directory=datadir dumpfile=OASLIVE_InterfaceExport.dmp logfile=OASLIVE_InterfaceExport.log

The datapump command for import is as follows:

impdp user/password@orasid tables=tmp_interface_definitions, tmp_interface_registrations directory=datadir dumpfile=OASLIVE_InterfaceExport.dmp logfile=OASLIVE_InterfaceImport.log

Further reading

http://download.oracle.com/docs/cd/B12037_01/server.101/b10825/dp_export.htm

http://download.oracle.com/docs/cd/B13789_01/server.101/b10825/dp_import.htm

Oracle – Create Logical Standby Database 10g RAC


Backup primary database OASLIVE

(If using ASM then you must use RMAN to create the backup)

$ export ORACLE_SID=OASLIVE1
$ rman
RMAN> CONNECT TARGET /
connected to target database: OASLIVE (DBID=3404061247)
RMAN> BACKUP DATABASE FORMAT ‘/c04/oracle/oraback/OASLIVE/rman/OASLIVE_%U.rbak’

Copy control file from primary database for standby

RMAN> COPY CURRENT CONTROLFILE FOR STANDBY TO '/home/oracle/kerri/OASREP.stby';

Copy backup file and control file to standby node (Node11)

$ cd /home/oracle/kerri
$ scp OASREP.stby node11:/home/oracle/.
$ cd /c04/oracle/oraback/OASLIVE/rman/
$ scp OASLIVE_*.rbak node11:/c04/oracle/oraback/OASLIVE/rman/.

Whilst waiting for the backup to copy across, create standby redo logs in the primary database OASLIVE

(Amount of standby logs per thread = no. of redo logs +1)

node 1$ sqlplus ‘/as sysdba’
SQL> SELECT *
FROM v$log;
SQL> SELECT group#
,           thread#
,           sequence#
,           status
FROM v$standby_log;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1('+REPDATA') SIZE 256M;
SQL> /
SQL> /
SQL> /
SQL> /
SQL> SELECT group#
,           thread#
,           sequence#
,           status
FROM v$standby_log;

Copy the server details of the standby node to the primary node tnsnames.ora

node 11$ cd $TNS_ADMIN

Copy the following from OASSTBY tnsnames.ora to OASLIVE tnsnames.ora

OASSTBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgoh-data-11-vip)(PORT = 1511))
(CONNECT_DATA =
(UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = OASSTBY.dudleygoh-tr.wmids.nhs.uk)
)
)

Log into ASM and delete all pre-existing OASSTBY files

Check that both OASLIVE nodes have sysdba rman privileges if they don’t then run the following:

node 1$ sqlplus ‘/as sysdba’
SQL> GRANT sysdba TO rman;

Create spfile for physical standby using modified pfile

(see appendix A at bottom of post for pfile details)

node 11$ sqlplus ‘/as sysdba’
SQL> STARTUP NOMOUNT PFILE='/home/oracle/kerri/OASSTBYpfile.ora.bkup';
SQL> CREATE SPFILE FROM PFILE='/home/oracle/kerri/OASSTBYpfile.ora.bkup';
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT

Log into RMAN and recover the standby database

node 11$ rman
RMAN> CONNECT TARGET rman/o45rm4n@OASLIVE
RMAN> CONNECT AUXILIARY rman/o45rm4n@OASSTBY
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY;

Copy the server details of the standby node to the primary node tnsnames.ora

node 11$ cd $TNS_ADMIN

Copy the following from OASREP tnsnames.ora to OASLIVE tnsnames.ora

OASREP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgoh-data-11-vip)(PORT = 1511))
(CONNECT_DATA =
(UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = OASREP.dudleygoh-tr.wmids.nhs.uk)
)
)

Log into ASM and delete all pre-existing OASREP files

Create spfile for logical standby using modified pfile

(see appendix B for pfile details)

node 11$ sqlplus ‘/as sysdba’
SQL> STARTUP NOMOUNT PFILE='/home/oracle/kerri/OASREPpfile.ora.bkup';
SQL> CREATE SPFILE FROM PFILE='/home/oracle/kerri/OASREPpfile.ora.bkup';
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT

Log into RMAN and recover the standby database

node 11$ rman
RMAN> CONNECT TARGET rman/o45rm4n@OASLIVE
RMAN> CONNECT AUXILIARY rman/o45rm4n@OASREP
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY;

Copy across the password file to the standby database

$ scp orapwOASLIVE node11:/u01/oracle/ora102/dbs/orapwOASREP

Recover standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Switch logfile on the Primary database

SQL> ALTER SYSTEM SWITCH LOGFILE;

On the standby verify that redo data has been received and applied

SQL> SELECT thread#
,           sequence#
,           name
,   TO_CHAR(first_time, 'dd-mon-yyyy hh24:mi:ss') First_time
,           applied
,           status
FROM v$archived_log
--WHERE applied='YES'
ORDER BY 3;
SQL> SELECT thread#
,           sequence#
,           applied
,           status
FROM v$archived_log
ORDER BY 1;
SQL> SELECT thread#
,       MAX(sequence#)
FROM v$archived_log
GROUP BY thread#;
SQL> SELECT group#
,           thread#
,           sequence#
,           status
FROM v$standby_log;
SQL> SELECT group#
,           thread#
,           sequence#
,           status
FROM v$log;
SQL> SELECT thread#
,    MAX(al.sequence#) Arch
FROM v$archived_log al
GROUP BY thread#;
SQL> SELECT thread#
,    MAX(lh.sequence#) App
FROM v$log_history lh
GROUP BY thread#;

Determine the latest archive redo log on the Primary

SQL> SELECT thread#
,           sequence#
,           Archived
,           Status
FROM v$log;
SQL> SELECT thread#
,           sequence#
,           Archived
,           Status
FROM v$standby_log;

Determine the most recent archive redo log file on the standby

SQL> SELECT thread#
,       max(sequence#)
FROM v$archived_log
GROUP BY thread#;

Determine the most recent archived logfile at each of the archive destinations on the primary.

SQL> SELECT destination
,           status
,           archived_thread#
,           archived_seq#
FROM v$archive_dest_status
WHERE status <> 'DEFFERED'
AND status <> 'INACTIVE';

Determine the most recent received and applied archived logfile at each of the archive destinations on the standby

SQL> SELECT thread#
,           sequence#
,           first_time
,           next_time
,           applied
FROM v$archived_log
ORDER BY thread#,sequence#;

Verify standby logs are created and running correctly on the standby. Invoke an archive log switch on the primary

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> Select GROUP#
,           THREAD#
,           SEQUENCE#
,           ARCHIVED
,           STATUS
,           BYTES/1048576
FROM v$standby_log;

Stop the apply on the physical standby to make it into a logical standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY CANCEL;

Find unsupported data types and skip them:

SQL> SELECT owner
,           table_name
FROM dba_logstdby_not_unique
WHERE (owner
,      table_name) NOT IN (SELECT DISTINCT owner, table_name FROM dba_logstdby_unsupported)
AND bad_column = 'Y';

EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL',-
schema_name => '<schema_name>', -
object_name => '<object_name>');

EXECUTE DBMS_LOGSTDBY.SKIP('DML','<schema_name>','<object_name>');

Build a dictionary in the redo data on the primary database

SQL> EXECUTE dbms_logstdby.build;

Convert the physical standby to a logical standby

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY <db_name>;

Create new password file

node 11$ orapw file=$ORACLE_HOME/orapw<db_name> force=y entries=10 password=<password>

Open database

SQL> ALTER DATABASE OPEN RESETLOGS;

Open the logical standby database

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Create scripts for reporting database (Optional)

Create a reporting user and their own tablespace on the logical standby

Create a script to refresh the datasets on a nightly basis

#!/bin/bash
export ORACLE_HOME=/u01/oracle/ora102
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=OASREP1
sqlplus '/as sysdba' << EOF
SET SERVEROUPUT ON
SPOOL /home/oracle/kerri/refresh_mviews.lst
SELECT TO_CHAR(SYSDATE, 'Dy DD-Mon-YYYY HH24:MI:SS') FROM DUAL;
EXEC oasis.refresh_mviews_proc;
SELECT TO_CHAR(SYSDATE, 'Dy DD-Mon-YYYY HH24:MI:SS') FROM DUAL;
SPOOL OFF;
EXIT;
EOF

Update the crontab to run at 5am each night

# Refresh Materialized Views on Reporting
00 05 * * * /home/oracle/kerri/refresh_mviews.sh > /home/oracle/kerri/test.txt

Create a trigger for the new user of the logical standby

CREATE OR REPLACE TRIGGER oasdba_LogStbyTrig
AFTER LOGON
ON database
DECLARE
cmmd VARCHAR2(64);
BEGIN
IF user = '<reporting user>' THEN
cmmd:='ALTER session disable guard';
EXECUTE IMMEDIATE cmmd;
INSERT INTO test VALUES(user
,                       SYSDATE
,               TO_CHAR(SYSDATE, 'hh24:mi:ss')
);
END IF;
END;
/

Create a procedure for refreshing materialized views

CREATE OR REPLACE PROCEDURE refresh_mviews_proc IS
CURSOR c1 IS SELECT owner, mview_name FROM dba_mviews;
dbname varchar2(10);
mview varchar2(250);
owner varchar2(250);
snap varchar2(250);
cmmd1 varchar2(64);
cmmd2 varchar2(64);
BEGIN
SELECT name INTO dbname FROM v$database;
cmmd1:='ALTER SESSION DISABLE GUARD';
cmmd2:='ALTER SESSION ENABLE GUARD';
IF dbname='OASREP'
THEN
EXECUTE IMMEDIATE cmmd1;
OPEN c1;
IF c1%ISOPEN THEN
LOOP
FETCH c1 INTO owner, mview;
snap := owner||'.'||mview;
dbms_mview.refresh(''||snap||'','C');
EXIT WHEN c1%NOTFOUND;
END LOOP;
END IF;
EXECUTE IMMEDIATE cmmd2;
END IF;
END;

Appendix A

Physical Standby spfile details for OASSTBY:

OASSTBY1.__db_cache_size=1191182336
OASSTBY.__java_pool_size=16777216
OASSTBY1.__java_pool_size=16777216
OASSTBY.__large_pool_size=16777216
OASSTBY1.__large_pool_size=16777216
OASSTBY.__shared_pool_size=352321536
OASSTBY1.__shared_pool_size=369098752
OASSTBY.__streams_pool_size=0
OASSTBY1.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/OASSTBY/adump'
*.background_dump_dest='/u01/oracle/admin/OASSTBY/bdump'
*.cluster_database=true
*.cluster_database_instances=2
*.compatible='10.2.0.4.0'
*.control_files='+LIVEDATA’
*.core_dump_dest='/u01/oracle/admin/OASSTBY/cdump'
*.db_block_size=8192
*.db_create_file_dest='+LIVEDATA'
*.db_domain='dudleygoh-tr.wmids.nhs.uk'
*.db_file_multiblock_read_count=8
*.db_file_name_convert='OASLIVE','OASSTBY'
*.db_name='OASLIVE'
*.db_unique_name='OASSTBY'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OASSTBYXDB)'
*.fal_client='OASSTBY1'
*.fal_server='OASLIVE1,OASLIVE2'
OASSTBY1.instance_number=1
OASSTBY2.instance_number=2
*.job_queue_processes=1
OASSTBY2.local_listener='LISTENER_OASSTBY2'
OASSTBY1.local_listener='LISTENER_OASSTBY1'
*.log_archive_config='DG_CONFIG=(OASLIVE,OASSTBY,OASREP)'
*.log_archive_dest_1='LOCATION=+LIVEARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=OASSTBY'
*.log_archive_dest_2='SERVICE=OASLIVE VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OASLIVE'
*.log_archive_dest_3='SERVICE=OASREP LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OASREP'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.open_cursors=300
*.pga_aggregate_target=2503999488
*.processes=600
*.remote_listener='LISTENERS_OASSTBY'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
OASSTBY2.thread=2
OASSTBY1.thread=1
*.undo_management='AUTO'
OASSTBY1.undo_tablespace='UNDOTBS1'
OASSTBY2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/u01/oracle/admin/OASSTBY/udump'
*.utl_file_dir='*'

Appendix B

Logical Standby spfile details for OASREP:

OASREP1.__db_cache_size=1073741824
OASREP1.__java_pool_size=33554432
OASREP1.__large_pool_size=16777216
OASREP1.__shared_pool_size=469762048
OASREP1.__streams_pool_size=0
*.archive_lag_target=3600
*.audit_file_dest='/u01/oracle/admin/OASREP/adump'
*.background_dump_dest='/u01/oracle/admin/OASREP/bdump'
*.cluster_database_instances=1
*.cluster_database=FALSE
*.compatible='10.2.0.4.0'
*.control_files='+REPDATA’
*.core_dump_dest='/u01/oracle/admin/OASREP/cdump'
*.db_block_size=8192
*.db_create_file_dest='+REPDATA'
*.db_domain='dudleygoh-tr.wmids.nhs.uk'
*.db_file_multiblock_read_count=8
*.db_file_name_convert='OASLIVE','OASREP'
*.db_name='OASREP'#db_name
*.db_unique_name='OASREP'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OASREPXDB)'
*.fal_client='OASREP1'
*.fal_server='OASLIVE1,OASLIVE2'
OASREP1.instance_number=1
*.job_queue_processes=0
OASREP1.local_listener='LISTENER_OASREP1'
*.log_archive_config='DG_CONFIG=(OASLIVE,OASSTBY,OASREP)'
*.log_archive_dest_1='LOCATION=+LIVEARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=OASREP'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.open_cursors=300
*.pga_aggregate_target=2503999488
*.processes=600
*.remote_listener='LISTENERS_OASREP'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
OASREP1.thread=1
*.undo_management='AUTO'
OASREP1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/OASREP/udump'
*.utl_file_dir='*'

Appendix C

Primary Database pfile details for OASLIVE

OASLIVE1.__db_cache_size=989855744
OASLIVE2.__db_cache_size=1291845632
OASLIVE2.__java_pool_size=16777216
OASLIVE1.__java_pool_size=33554432
OASLIVE2.__large_pool_size=16777216
OASLIVE1.__large_pool_size=16777216
OASLIVE1.__shared_pool_size=553648128
OASLIVE2.__shared_pool_size=268435456
OASLIVE2.__streams_pool_size=0
OASLIVE1.__streams_pool_size=0
*.archive_lag_target=900
*.audit_file_dest='/u01/oracle/admin/OASLIVE/adump'
*.background_dump_dest='/u01/oracle/admin/OASLIVE/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.4.0'
*.control_files='+LIVEDATA/oaslive/controlfile/current.289.724539179','+LIVEARCH/oaslive/controlfile/current.3794.724539179'
*.core_dump_dest='/u01/oracle/admin/OASLIVE/cdump'
*.db_block_size=8192
*.db_create_file_dest='+LIVEDATA'
*.db_domain='dudleygoh-tr.wmids.nhs.uk'
*.db_file_multiblock_read_count=8
*.db_name='OASLIVE'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OASLIVEXDB)'
*.fal_client='OASLIVE1,OASLIVE2'
*.fal_server='OASSTBY'
OASLIVE1.instance_number=1
OASLIVE2.instance_number=2
*.job_queue_processes=1
OASLIVE2.local_listener='LISTENER_OASLIVE2'
OASLIVE1.local_listener='LISTENER_OASLIVE1'
*.log_archive_config='DG_CONFIG=(OASLIVE,OASSTBY,OASREP)'
*.log_archive_dest_1='LOCATION=+LIVEARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=OASLIVE'
*.log_archive_dest_2='SERVICE=OASSTBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OASSTBY'
*.log_archive_dest_3='SERVICE=OASREP LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OASREP'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.open_cursors=300
*.pga_aggregate_target=2503999488
*.processes=600
*.remote_listener='LISTENERS_OASLIVE'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
OASLIVE2.thread=2
OASLIVE1.thread=1
*.undo_management='AUTO'
OASLIVE1.undo_tablespace='UNDOTBS1'
OASLIVE2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/u01/oracle/admin/OASLIVE/udump'
*.utl_file_dir='*'

Oracle – New DBID Post-clone Task


After a clone it is sometimes necessary to change the dbid (particularly when not using RMAN). Therefore you need to perform the following:

$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 29 11:36:36 2011
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  264241152 bytes
Fixed Size                  2072056 bytes
Variable Size              88080904 bytes
Database Buffers          167772160 bytes
Redo Buffers                6316032 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64
With the Partitioning and Data Mining options

$ nid TARGET=/
DBNEWID: Release 10.2.0.3.0 - Production on Wed Jun 29 11:37:11 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to database SAWTST1 (DBID=1067814800)
Connected to server version 10.2.0
Control Files in database:
/sawtst1/ctrl1/sawtst1_01.ctl
/sawtst1/ctrl2/sawtst1_02.ctl
Change database ID of database SAWTST1? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1067814800 to 2196809383
Control File /sawtst1/ctrl1/sawtst1_01.ctl - modified
Control File /sawtst1/ctrl2/sawtst1_02.ctl - modified
Datafile /sawtst1/system/system_01.dbf - dbid changed
Datafile /sawtst1/undo/undotbs_01.dbf - dbid changed
Datafile /sawtst1/system/sysaux_01.dbf - dbid changed
Datafile /sawtst1/oradata/users_01.dbf - dbid changed
Datafile /sawtst1/oradata/data_01.dbf - dbid changed
Datafile /sawtst1/oradata/data_02.dbf - dbid changed
Datafile /sawtst1/oradata/data_03.dbf - dbid changed
Datafile /sawtst1/oradata/indexes_01.dbf - dbid changed
Datafile /sawtst1/oradata/indexes_02.dbf - dbid changed
Datafile /sawtst1/oradata/csmig_01.dbf - dbid changed
Datafile /sawtst1/oradata/cm_rem_ts_01.dbf - dbid changed
Datafile /sawtst1/temp/temp_01.dbf - dbid changed
Control File /sawtst1/ctrl1/sawtst1_01.ctl - dbid changed
Control File /sawtst1/ctrl2/sawtst1_02.ctl - dbid changed
Instance shut down
Database ID for database SAWTST1 changed to 2196809383.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 29 11:37:40 2011
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area  264241152 bytes
Fixed Size                  2072056 bytes
Variable Size              88080904 bytes
Database Buffers          167772160 bytes
Redo Buffers                6316032 bytes
Database mounted.

SQL> alter database open resetlogs;
Database altered.

SQL>

Oracle – Clone Database without RMAN

June 27, 2011 3 comments

If you need to do a clone of a database, say from production to test, then you can do this by backing up the production control file to trace, copying the datafiles and pfile across to the new test environment, then running the script created by the control file backup.

Prereqs:

The copy of the datafiles must be done with the source database shutdown, or in ARCHIVELOG mode.

Copy parameter file:

Copy the init.ora or spfile from the source database $ORACLE_HOME/dbs to the $ORACLE_HOME/dbs directory of the target database.

Vi the parameter file and change any directory names that need amending. I.e. logfile locations, control file locations, etc…

EITHER

A…………….>

A. Source database in archivelog mode

If the source database is open then you need to put the tablespaces into backup mode before starting the copy:

SQL> ALTER TABLESPACE <tablespace_name> BEGIN BACKUP;

Copy all datafiles

SQL> ALTER TABLESPACE <tablespace_name> END BACKUP;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

<……………A

OR

B…………….>

B. Source database not in archivelog mode

On source database:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
SQL> SHUTDOWN IMMEDIATE;

<…………….B

Copy control files, datafiles, all archivelogs to target destination

It may be necessary to create the directory structure for these files if it does not already exist. However, it is best to SCP -rp the entire $ORACLE_HOME/dbs directory to ensure permissions and ownership are correctly assigned and no files are missed.

Set Oracle environment

$ set ORACLE_SID = <target_oracle_sid>
$ set ORACLE_HOME = <target_oracle_home>

Setup password file

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=
$ sqlplus '/as sysdba'

EITHER

A…………….>

A. Clone to same name database

If you are cloning to a database of the same name, then after checking the control file has the correct location for the datafiles, you should be able to startup the database with the startup command:

Check datafile location:

SQL> STARTUP MOUNT
SQL> SELECT file#, name FROM v$datafile;

Rename any incorrect datafile locations:

SQL> ALTER DATABASE RENAME FILE '/old_full_directory_path/datafile_name.dbf' TO '/new_full_directory_path/datafile_name.dbf';

Check logfile location:

SQL> SELECT member FROM v$logfile;

Rename any incorrect logfile locations:

SQL> ALTER DATABASE RENAME FILE '/old_full_directory_path/logfile_name.log' TO '/new_full_directory_path/logfile_name.log';

EITHER
A…………….>

A. Database without archive logs

Open database

SQL> ALTER DATABASE OPEN
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 2072056 bytes
Variable Size 88080904 bytes
Database Buffers 167772160 bytes
Redo Buffers 6316032 bytes
Database mounted.
Database opened.
SQL>

<……………A
OR
B…………….>

B. Database with archive logs

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Wait for all archivelogs to apply then:

SQL> ALTER DATABASE CANCEL;

Open database with resetlogs if necessary

SQL> ALTER DATABASE OPEN RESETLOGS;

<…………….B

<……………A

OR

B…………….>

B. Clone to new database name

  • Rename clone pfile to new db name
  • Change directory names within pfile if necessary
  • Find the trace file containing the backup controlfile script (normally in $ORACLE_ADMIN/udump)
  • Edit the create script from the trace file such as below, remember to change directory names from source to target, save as “create_db.sql”:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "TST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 (
'/tst/redologs1/redo01_1.log',
'/tst/redologs2/redo01_2.log'
) SIZE 25M,
GROUP 2 (
'/tst/redologs1/redo02_1.log',
'/tst/redologs2/redo02_2.2og'
) SIZE 25M,
GROUP 3 (
'/tst/redologs1/redo03_1.log',
'/tst/redologs2/redo03_2.log'
) SIZE 25M
DATAFILE
'/tst/system/system_01.dbf',
'/tst/undo/undotbs_01.dbf',
'/tst/system/sysaux_01.dbf',
'/tst/oradata/users_01.dbf',
'/tst/oradata/data_01.dbf',
'/tst/oradata/data_02.dbf',
'/tst/oradata/data_03.dbf',
'/tst/oradata/indexes_01.dbf',
'/tst/oradata/indexes_02.dbf',
'/tst/oradata/csmig_01.dbf',
'/tst/oradata/cm_rem_ts_01.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER TABLESPACE TEMP ADD TEMPFILE '/tst/temp/temp_01.dbf'
SIZE 1048576000 REUSE AUTOEXTEND OFF;

  • create udump, adump, bdump, cdump directories
  • run create script

$ sqlplus '/as sysdba'
SQL> @create_db.sql

  • check database is open

<…………….B

Change DBID if required using NID

$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 29 11:36:36 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 2072056 bytes
Variable Size 88080904 bytes
Database Buffers 167772160 bytes
Redo Buffers 6316032 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64
With the Partitioning and Data Mining options

$ nid TARGET=/
DBNEWID: Release 10.2.0.3.0 - Production on Wed Jun 29 11:37:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database SAWTST1 (DBID=1067814800)
Connected to server version 10.2.0
Control Files in database:
/sawtst1/ctrl1/sawtst1_01.ctl
/sawtst1/ctrl2/sawtst1_02.ctl
Change database ID of database SAWTST1? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1067814800 to 2196809383
Control File /sawtst1/ctrl1/sawtst1_01.ctl - modified
Control File /sawtst1/ctrl2/sawtst1_02.ctl - modified
Datafile /sawtst1/system/system_01.dbf - dbid changed
Datafile /sawtst1/undo/undotbs_01.dbf - dbid changed
Datafile /sawtst1/system/sysaux_01.dbf - dbid changed
Datafile /sawtst1/oradata/users_01.dbf - dbid changed
Datafile /sawtst1/oradata/data_01.dbf - dbid changed
Datafile /sawtst1/oradata/data_02.dbf - dbid changed
Datafile /sawtst1/oradata/data_03.dbf - dbid changed
Datafile /sawtst1/oradata/indexes_01.dbf - dbid changed
Datafile /sawtst1/oradata/indexes_02.dbf - dbid changed
Datafile /sawtst1/oradata/csmig_01.dbf - dbid changed
Datafile /sawtst1/oradata/cm_rem_ts_01.dbf - dbid changed
Datafile /sawtst1/temp/temp_01.dbf - dbid changed
Control File /sawtst1/ctrl1/sawtst1_01.ctl - dbid changed
Control File /sawtst1/ctrl2/sawtst1_02.ctl - dbid changed
Instance shut down
Database ID for database SAWTST1 changed to 2196809383.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 29 11:37:40 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 2072056 bytes
Variable Size 88080904 bytes
Database Buffers 167772160 bytes
Redo Buffers 6316032 bytes
Database mounted.

SQL> alter database open resetlogs;
Database altered.

SQL>