Refresh an Oracle Database using a hot backup and NDMPCOPY

Refresh an Oracle Database using a hot backup and NDMPCOPY

SOURCE_SID s2idm
TARGET_SID s1idm
SOURCE_SERVER SVLIDMDB01-STG
TARGET_SERVER SVLIDMDBS
SOURCE_VOL svlstgflr11:/vol/s2idm_stg_oradata01svlstgflr12:/vol/s2idm_stg_oradata02
TARGET_VOL svlstgflr506-data1:/vol/s1idm_stg_oradata01/s1idm
SOURCE_SNAPSHOT svlstgflr11:/vol/s2idm_stg_oradata01/.snapshot/s2idm_daily.0svlstgflr12:/vol/s2idm_stg_oradata02/.snapshot/s2idm_daily.0
TARGET FILER PASSWORD $ sudo rsh <filer name> ndmpd password ndmpuser
Script for creating TEMP tablespaceon TARGET_SERVER /u/oracle/refresh_idm/cr_temp.sql
Script for creating new controlfile on TARGET_SERVER /u/oracle/refresh_idm/cr_s1idm.sql

 

  1. 1.      Clean disk spaces on target server. 
 svlidmdbs{oracle}:  rm /oracle/oradata/s1idm/*svlidmdbs{oracle}:  rm /oracle/arch_1/s1idm/*

svlidmdbs{oracle}:  rm /oracle/stdby_1/s1idm/*

svlidmdbs{oracle}:  rm /oracle/redo_1/s1idm/*

svlidmdbs{oracle}:  rm /oracle/arch_2/s1idm/*

svlidmdbs{oracle}:  rm /oracle/stdby_2/s1idm/*

svlidmdbs{oracle}:  rm /oracle/redo_2/s1idm/*

  1. 2.      Make a note of the current archive log change number on the source database.

 

 SQL> column chng format 9999999999999SQL> select max(first_change#) chng from v$archived_log;

CHNG

————–

6604382558837

 

  1. 3.      Run hotbackup script on the source database 
 $ $DBA_UNIX/hotbackup_multi_filers.sh s2idm

 

  1. 4.      Check if backup is done successfully 
 svlidmdb01-stg{oracle}: df -h |grep oradatasvlstgflr11:/vol/s2idm_stg_oradata01

160G  140G   21G  88% /oracle/oradata/s2idm/data1

svlstgflr12:/vol/s2idm_stg_oradata02

160G  142G   19G  89% /oracle/oradata/s2idm/data2

svlidmdb01-stg{oracle}: sudo rsh svlstgflr11 snap list s2idm_stg_oradata01

Volume s2idm_stg_oradata01

working…

%/used       %/total  date          name

———-  ———-  ————  ——–

0% ( 0%)    0% ( 0%)  Feb 12 15:13  s2idm_daily.0

0% ( 0%)    0% ( 0%)  Feb 12 14:49  s2idm_daily.1

4% ( 4%)    3% ( 3%)  Jan 19 16:12  s2idm_daily.2

4% ( 0%)    3% ( 0%)  Jan 19 16:11  s2idm_daily.3

4% ( 0%)    3% ( 0%)  Jan 19 14:33  s2idm_daily.4

5% ( 1%)    3% ( 1%)  Jan 16 11:23  s2idm_daily.5

7% ( 3%)    6% ( 2%)  Dec 17 11:01  s2idm_daily.6

8% ( 0%)    6% ( 0%)  Dec 15 20:38  s1idm_bef_switchover_121508

9% ( 1%)    7% ( 1%)  Dec 08 13:56  s2idm_bef_build_bkup

svlidmdb01-stg{oracle}: sudo rsh svlstgflr12 snap list s2idm_stg_oradata02

Volume s2idm_stg_oradata02

working…

%/used       %/total  date          name

———-  ———-  ————  ——–

0% ( 0%)    0% ( 0%)  Feb 12 15:13  s2idm_daily.0

0% ( 0%)    0% ( 0%)  Feb 12 14:49  s2idm_daily.1

4% ( 4%)    3% ( 3%)  Jan 19 16:12  s2idm_daily.2

4% ( 0%)    3% ( 0%)  Jan 19 16:11  s2idm_daily.3

4% ( 0%)    3% ( 0%)  Jan 19 14:33  s2idm_daily.4

4% ( 1%)    3% ( 1%)  Jan 16 11:23  s2idm_daily.5

7% ( 3%)    5% ( 2%)  Dec 17 11:01  s2idm_daily.6

7% ( 1%)    5% ( 0%)  Dec 15 20:38  s1idm_bef_switchover_121508

8% ( 1%)    7% ( 1%)  Dec 08 13:56  s2idm_bef_build_bkup

 

  1. 5.      Copy the backup oracle files to the target server using NDMCOPY. 
 [syntax]$ sudo rsh <filer name> ndmpd password ndmpuser

Password target-filer-password

$ sudo rsh source-filer ndmpcopy –da ndmpuser:target-filer-password /vol/source_vol/.snapshot/souce-snapshot target-filer:/vol/target_vol

svlidmdbs{oracle}: sudo rsh svlstgflr506-data1 ndmpd password ndmpuser
password XXXXXXXXXXXXX

svlidmdb01-stg{oracle}: sudo rsh svlstgflr11 ndmpcopy -da ndmpuser:XXXXXXXXXXXXX /vol/s2idm_stg_oradata01/.snapshot/s2idm_daily.0/ svlstgflr506-data1:/vol/s1idm_stg_oradata01/s1idm/

Ndmpcopy: Starting copy [ 19 ] …

Ndmpcopy: svlstgflr11: Notify: Connection established

Ndmpcopy: svlstgflr506-data1: Notify: Connection established

Ndmpcopy: svlstgflr11: Connect: Authentication successful

Ndmpcopy: svlstgflr506-data1: Connect: Authentication successful

Ndmpcopy: svlstgflr11: Log: DUMP: Using Full Volume Dump

Ndmpcopy: svlstgflr11: Log: DUMP: Date of this level 0 dump: Thu Feb 12 22:31:01 2009.

Ndmpcopy: svlstgflr11: Log: DUMP: Date of last level 0 dump: the epoch.

Ndmpcopy: svlstgflr11: Log: DUMP: Dumping /vol/s2idm_stg_oradata01/.snapshot/s2idm_daily.0 to NDMP connection

Ndmpcopy: svlstgflr11: Log: DUMP: mapping (Pass I)[regular files]

Ndmpcopy: svlstgflr11: Log: DUMP: mapping (Pass II)[directories]

Ndmpcopy: svlstgflr11: Log: DUMP: estimated 122164843 KB.

Ndmpcopy: svlstgflr11: Log: DUMP: dumping (Pass III) [directories]

Ndmpcopy: svlstgflr11: Log: DUMP: dumping (Pass IV) [regular files]

Ndmpcopy: svlstgflr506-data1: Log: RESTORE: Thu Feb 12 22:33:26 2009: Begin level 0 restore

Ndmpcopy: svlstgflr11: Log: DUMP: Thu Feb 12 23:28:22 2009 : We have written 113465554 KB.

Ndmpcopy: svlstgflr506-data1: Log: RESTORE: Thu Feb 12 23:32:41 2009: Restoring NT ACLs.

. . . . .

Ndmpcopy: svlstgflr11: Log: DUMP: dumping (Pass V) [ACLs]

Ndmpcopy: svlstgflr506-data1: Log: RESTORE: RESTORE IS DONE

Ndmpcopy: svlstgflr506-data1: Log: RESTORE: The destination path is /vol/s1idm_stg_oradata01/s1idm/

Ndmpcopy: svlstgflr506-data1: Notify: restore successful

Ndmpcopy: svlstgflr11: Notify: dump successful

Ndmpcopy: Transfer successful [ 59 minutes 24 seconds ]

Ndmpcopy: Done
svlidmdb01-stg{oracle}: sudo rsh svlstgflr12 ndmpcopy -da ndmpuser:XXXXXXXXXXXXX /vol/s2idm_stg_oradata02/.snapshot/s2idm_daily.0/ svlstgflr506-data1:/vol/s1idm_stg_oradata01/s1idm/ 

 

Delete control file at /oracle/oradata/s1idm on the target server.

 svlidmdbs{oracle}: rm /oracle/oradata/s1idm/control03.ctl

 

  1. Copy archive log files to the target server.It is only necessary to copy archive log files created during the time the source database was in backup mode.

On the source database:

 SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_CHANGE# >= &CHANGE_NO

ORDER BY NAME;

[Enter value for change_no: 6604382558837]

NAME

——————————————————————————–

/oracle/arch_1/s2idm/s2idm_672940023_1_750.arc

/oracle/arch_1/s2idm/s2idm_672940023_1_751.arc

/oracle/arch_1/s2idm/s2idm_672940023_1_752.arc

/oracle/arch_1/s2idm/s2idm_672940023_1_753.arc

/oracle/arch_2/s2idm/s2idm_672940023_1_750.arc

/oracle/arch_2/s2idm/s2idm_672940023_1_751.arc

/oracle/arch_2/s2idm/s2idm_672940023_1_752.arc

/oracle/arch_2/s2idm/s2idm_672940023_1_753.arc

 

Create a shared directory in the source server and copy all the identified archived
log files into it.

 svlidmdb01-stg{oracle}: mkdir –p /u/oracle/refresh_idm/arc
svlidmdb01-stg{oracle}: cp /oracle/arch_1/s2idm/s2idm_672940023_1_75?.arc /u/oracle/refresh_idm/arc

 

  1. 7.      Produce a pfile for the new database on the source database. 
 SQL> CREATE PFILE=’inits1idm.ora’ FROM SPFILE;

7.1 It will create a new pfile in the $ORACLE_HOME/dbs directory on the source database.

7.2 Edit the new pfile to adjust database name and paths according to the target database. And then copy this file to $ORACLE_HOME/dbs on the target system.

7.3 You can also use init<TAGET_SID>.ora at the target database to keep old parameter values instead of using new modified pfile from source database.

  1. 8.      Create and edit the clone controlfile on the source database. 
 SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/oracle/home/cr_s1idm.sql’;

 

8.1 The clone control file (/oracle/home/cr_s1idm.sql) will require editing before
it can be used on the target database:

  • Remove all lines from the top of the file up to but not including the second ‘STARTUP MOUNT’ line.
  • Remove any lines that start with a “—“
  • Remove any lines that start with a “#”
  • Remove any blank lines in the “CREATE CONTROLFILE” section.
  • Remove the line “RECOVER DATABASE USING BACKUP CONTROLFILE”
  • Remove the line “ALTER DATABASE OPEN RESETLOGS;”
  • Move to the “CREATE CONTROLFILE” line. The word ‘REUSE’ needs to be changed to ‘SET’.  RESETLOGS must be specified to set a new database nameCREATE CONTROLFILE SET DATABASE “S1IDM” RESETLOGSARCHIVELOG
    • The database name needs to set to the new database name  : S1IDM
    • If the file paths are being changed, alter the file to reflect the changes.

 

 

8.2 Save “ALTER TABLESPACE TEMP…” lines to /u/oracle/refresh_idm/cr_temp.sql file, and
then remove them from /oracle/home/cr_s1idm.sql. Modify path info of
/u/oracle/refresh_idm/cr_temp.sql:

 ALTER TABLESPACE TEMP ADD TEMPFILE ‘/oracle/oradata/s2idm/data1/temp03.dbf’SIZE 4115M REUSE AUTOEXTEND ON NEXT 104857600  MAXSIZE 32767M;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/oracle/oradata/s2idm/data1/temp02.dbf’

SIZE 4171M REUSE AUTOEXTEND ON NEXT 104857600  MAXSIZE 32767M;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/oracle/oradata/s2idm/data1/temp01.dbf’

SIZE 4171M REUSE AUTOEXTEND ON NEXT 104857600  MAXSIZE 32767M;
→ Modify to :

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/oracle/oradata/s1idm/temp01.dbf’

  SIZE 4171M REUSE AUTOEXTEND ON NEXT 104857600  MAXSIZE 32767M;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/oracle/oradata/s1idm/temp02.dbf’

  SIZE 4171M REUSE AUTOEXTEND ON NEXT 104857600  MAXSIZE 32767M; 

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/oracle/oradata/s1idm/temp03.dbf’

SIZE 4115M REUSE AUTOEXTEND ON NEXT 104857600  MAXSIZE 32767M; 

 

8.3 Modified /oracle/home/cr_s1idm.sql on the source server

 STARTUP NOMOUNTCREATE CONTROLFILE SET DATABASE “S1IDM” RESETLOGS FORCE LOGGING ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 37376

LOGFILE

GROUP 1 (

‘/oracle/redo_1/s1idm/redo01a.log’,

‘/oracle/redo_2/s1idm/redo01b.log’

) SIZE 200M,

GROUP 2 (

‘/oracle/redo_1/s1idm/redo02a.log’,

‘/oracle/redo_2/s1idm/redo02b.log’

) SIZE 200M,

GROUP 3 (

‘/oracle/redo_1/s1idm/redo03a.log’,

‘/oracle/redo_2/s1idm/redo03b.log’

) SIZE 200M,

GROUP 4 (

‘/oracle/redo_1/s1idm/redo04a.log’,

‘/oracle/redo_2/s1idm/redo04b.log’

) SIZE 200M,

GROUP 5 (

‘/oracle/redo_1/s1idm/redo05a.log’,

‘/oracle/redo_2/s1idm/redo05b.log’

) SIZE 200M,

GROUP 6 (

‘/oracle/redo_1/s1idm/redo06a.log’,

‘/oracle/redo_2/s1idm/redo06b.log’

) SIZE 200M,

GROUP 7 (

‘/oracle/redo_1/s1idm/redo07a.log’,

‘/oracle/redo_2/s1idm/redo07b.log’

) SIZE 200M,

GROUP 8 (

‘/oracle/redo_1/s1idm/redo08a.log’,

‘/oracle/redo_2/s1idm/redo08b.log’

) SIZE 200M,

GROUP 9 (

‘/oracle/redo_1/s1idm/redo09a.log’,

‘/oracle/redo_2/s1idm/redo09b.log’

) SIZE 200M

— STANDBY LOGFILE

—   GROUP 10 (

—     ‘/oracle/redo_1/s1idm/sbredo10a.log’,

—     ‘/oracle/redo_2/s1idm/sbredo10b.log’

—   ) SIZE 200M,

—   GROUP 11 (

—     ‘/oracle/redo_1/s1idm/sbredo11a.log’,

—     ‘/oracle/redo_2/s1idm/sbredo11b.log’

—   ) SIZE 200M,

—   GROUP 12 (

—     ‘/oracle/redo_1/s1idm/sbredo12a.log’,

—     ‘/oracle/redo_2/s1idm/sbredo12b.log’

—   ) SIZE 200M

DATAFILE

‘/oracle/oradata/s1idm/system01.dbf’,

‘/oracle/oradata/s1idm/tivoliorts01.dbf’,

‘/oracle/oradata/s1idm/sysaux01.dbf’,

‘/oracle/oradata/s1idm/users01.dbf’,

‘/oracle/oradata/s1idm/XELLERATE_data_01.dbf’,

‘/oracle/oradata/s1idm/XELLERATE_idx_01.dbf’,

‘/oracle/oradata/s1idm/XELLERATE_data_02.dbf’,

‘/oracle/oradata/s1idm/xellerate_data_03.dbf’,

‘/oracle/oradata/s1idm/logmnr01.dbf’,

‘/oracle/oradata/s1idm/xell_sap_tbs1_data_01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs1_data_01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs2_data_01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs3_data_01.dbf’,

‘/oracle/oradata/s1idm/idmoimct_tbs1_data_01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs1_data_02.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs4_data_01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs5_data_01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs5_data_02.dbf’,

‘/oracle/oradata/s1idm/idmoim_lob_tbs1_02.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs5_data_03.dbf’,

‘/oracle/oradata/s1idm/idmoim_lob_tbs1_03.dbf’,

‘/oracle/oradata/s1idm/idmoim_lob_tbs1_04.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs5_indx_02.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs5_data_04.dbf’,

‘/oracle/oradata/s1idm/oim_recon_arch_data_01.dbf’,

‘/oracle/oradata/s1idm/oim_recon_arch_data_02.dbf’,

‘/oracle/oradata/s1idm/idmoim_lob_tbs1_01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs4_indx_01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs5_indx_01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs1_data_03.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs1_data_04.dbf’,

‘/oracle/oradata/s1idm/undotbs02.dbf’,

‘/oracle/oradata/s1idm/tools01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs1_data_05.dbf’,

‘/oracle/oradata/s1idm/undotbs01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs1_indx_01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs2_indx_01.dbf’,

‘/oracle/oradata/s1idm/idmoim_tbs3_indx_01.dbf’

CHARACTER SET UTF8

;

8.4 Copy modified clone controlfile to shared directory.

 svlidmdb01-stg{oracle}: cp /oracle/home/cr_s1idm.sql /u/oracle/refresh_idm

 

  1. Setup Oracle environments on the target system9.1 Modify /etc/oratab
 svlidmdbs{oracle}: vi /etc/oratabs1idm:/oracle/product/102_idm:Y

svlidmdbs{oracle}:

9.2 Modify ~/.profile and then source it

 svlidmdbs{oracle}: vi ~/.profileexport ORACLE_BASE=/oracle

export ORACLE_HOME=$ORACLE_BASE/product/102_idm

export ORACLE_SID=s1idm

export ORACLE_TERM=xterm

export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

svlidmdbs{oracle}: . ~/.profile

 

10.  

Create the new password file on the target database if it is not exist.

 

svlidmdbs{oracle}: cd $ORACLE_HOME/dbs
svlidmdbs{oracle}: orapwd file=orapw${ORACLE_SID} password =xxxx entries=10

 

11.  

Create the new controlfile on the target database using modified clone controlfile from the source database.

 

 svlidmdbs{oracle}: sqlplus ‘/ as sysdba’SQL*Plus: Release 10.2.0.2.0 – Production on Fri Feb 13 11:52:14 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> @/u/oracle/refresh_idm/cr_s1idm.sql

ORACLE instance started.

Total System Global Area 6274678784 bytes

Fixed Size                  2080216 bytes

Variable Size             973079080 bytes

Database Buffers         5167382528 bytes

Redo Buffers              132136960 bytes

Control file created.

SQL>

 

12.  Copy archive log files from /u/oracle/refresh_idm/arc to /oracle/arch_1/s1idm on the target database and replace the part of the file names from s2idm_*.arc to s1idm_*.arc.

 svlidmdbs{oracle}: cd /oracle/arch_1/s1idmsvlidmdbs{oracle}: cp /u/oracle/refresh_idm/arc/* .

svlidmdbs{oracle}: ls –l

total 347232

-rw-r—–  1 oracle dba 142982144 Feb 13 15:53 s2idm_672940023_1_750.arc

-rw-r—–  1 oracle dba  18239488 Feb 13 15:53 s2idm_672940023_1_751.arc

-rw-r—–  1 oracle dba 150296064 Feb 13 15:53 s2idm_672940023_1_752.arc

-rw-r—–  1 oracle dba  43326976 Feb 13 15:53 s2idm_672940023_1_753.arc

svlidmdbs{oracle}: mv s2idm_672940023_1_750.arc s1idm_672940023_1_750.arc

svlidmdbs{oracle}: mv s2idm_672940023_1_751.arc s1idm_672940023_1_751.arc

svlidmdbs{oracle}: mv s2idm_672940023_1_752.arc s1idm_672940023_1_752.arc

svlidmdbs{oracle}: mv s2idm_672940023_1_753.arc s1idm_672940023_1_753.arc

svlidmdbs{oracle}: ls -l

total 347232

-rw-r—–  1 oracle dba 142982144 Feb 13 15:53 s1idm_672940023_1_750.arc

-rw-r—–  1 oracle dba  18239488 Feb 13 15:53 s1idm_672940023_1_751.arc

-rw-r—–  1 oracle dba 150296064 Feb 13 15:53 s1idm_672940023_1_752.arc

-rw-r—–  1 oracle dba  43326976 Feb 13 15:53 s1idm_672940023_1_753.arc

 

13.  Recover and open the new target database.

 svlidmdbs{oracle}: sqlplus ‘/as sysdba’SQL*Plus: Release 10.2.0.2.0 – Production on Fri Feb 13 16:02:42 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

ORA-00279: change 6604382882792 generated at 02/12/2009 22:30:58 needed for

thread 1

ORA-00289: suggestion : /oracle/arch_1/s1idm/s1idm_672940023_1_751.arc

ORA-00280: change 6604382882792 for thread 1 is in sequence #751

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 6604382882830 generated at 02/12/2009 22:31:18 needed for

thread 1

ORA-00289: suggestion : /oracle/arch_1/s1idm/s1idm_672940023_1_752.arc

ORA-00280: change 6604382882830 for thread 1 is in sequence #752

ORA-00278: log file ‘/oracle/arch_1/s1idm/s1idm_672940023_1_751.arc’ no longer

needed for this recovery

ORA-00279: change 6604382926895 generated at 02/13/2009 04:00:43 needed for

thread 1

ORA-00289: suggestion : /oracle/arch_1/s1idm/s1idm_672940023_1_753.arc

ORA-00280: change 6604382926895 for thread 1 is in sequence #753

ORA-00278: log file ‘/oracle/arch_1/s1idm/s1idm_672940023_1_752.arc’ no longer

needed for this recovery

ORA-00279: change 6604382970694 generated at 02/13/2009 10:44:53 needed for

thread 1

ORA-00289: suggestion : /oracle/arch_1/s1idm/s1idm_672940023_1_754.arc

ORA-00280: change 6604382970694 for thread 1 is in sequence #754

ORA-00278: log file ‘/oracle/arch_1/s1idm/s1idm_672940023_1_753.arc’ no longer

needed for this recovery

ORA-00308: cannot open archived log

‘/oracle/arch_1/s1idm/s1idm_672940023_1_754.arc’

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL>

 

ORA-00308 is normal; it simply means that all available logs have been applied.

 SQL> ALTER DATABASE OPEN RESETLOGS;Database altered.

 

14.  

Create TEMP TABLESPACE on the target database.

 

 SQL> @/u/oracle/refresh_idm/cr_temp.sql

 

15.  Perform checks on the new target database.

15.1 “SELECT STATUS FROM V$INSTANCE;” should return ‘OPEN’.

15.2 “SELECT DISTINCT STATUS FROM V$DATAFILE;” should return ‘ONLINE’ and ‘SYSTEM’.

15.3 Verify alert log file if there is any Oracle error.

 SQL> SELECT STATUS FROM V$INSTANCE;STATUS

————

OPEN

SQL> SELECT DISTINCT STATUS FROM V$DATAFILE;

STATUS

——-

ONLINE

SYSTEM

 

16.  Create a spfile on the target database.

 SQL> CREATE SPFILE FROM PFILE;

 

17.  

Change the Database ID on the target database.

 

 SQL> shutdown immediateDatabase closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 6274678784 bytes

Fixed Size                  2080216 bytes

Variable Size             973079080 bytes

Database Buffers         5167382528 bytes

Redo Buffers              132136960 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

svlidmdbs{oracle}: nid target=/

DBNEWID: Release 10.2.0.2.0 – Production on Fri Feb 13 16:30:04 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to database S1IDM (DBID=3644489009)

Connected to server version 10.2.0

Control Files in database:

/oracle/redo_1/s1idm/control01.ctl

/oracle/redo_2/s1idm/control02.ctl

/oracle/oradata/s1idm/control03.ctl

Change database ID of database S1IDM? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 3644489009 to 3204945228

Control File /oracle/redo_1/s1idm/control01.ctl – modified

Control File /oracle/redo_2/s1idm/control02.ctl – modified

Control File /oracle/oradata/s1idm/control03.ctl – modified

Datafile /oracle/oradata/s1idm/system01.dbf – dbid changed

Datafile /oracle/oradata/s1idm/tivoliorts01.dbf – dbid changed

. . . . .

Datafile /oracle/oradata/s1idm/temp03.dbf – dbid changed

Datafile /oracle/oradata/s1idm/temp01.dbf – dbid changed

Datafile /oracle/oradata/s1idm/temp02.dbf – dbid changed

Control File /oracle/redo_1/s1idm/control01.ctl – dbid changed

Control File /oracle/redo_2/s1idm/control02.ctl – dbid changed

Control File /oracle/oradata/s1idm/control03.ctl – dbid changed

Instance shut down

Database ID for database S1IDM changed to 3204945228.

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 ID.

DBNEWID – Completed succesfully.

svlidmdbs{oracle}: sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.2.0 – Production on Fri Feb 13 16:30:43 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 6274678784 bytes

Fixed Size                  2080216 bytes

Variable Size             973079080 bytes

Database Buffers         5167382528 bytes

Redo Buffers              132136960 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL>

 

18.  

Configure Oracle network configuration files and run listener.

 

 svlidmdbs{oracle}: cd $ORACLE_HOME/network/admin
svlidmdbs{oracle}: vi listener.oras1idm_ls =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = svlidmdbs)(PORT = 7300))

)

)

)

SID_LIST_s1idm_ls =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = s1idm_DGMGRL)

(ORACLE_HOME = /oracle/product/102_idm)

(SID_NAME = s1idm)

)

)

## SOX

admin_restrictions_s1idm_ls=on

logging_1sidm_ls=ON

 

 svlidmdbs{oracle}: cd $ORACLE_HOME/network/admin
svlidmdbs{oracle}: vi tnsnames.oras1idm =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = svlidmdbs)(PORT = 7300))(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = sidm)

(INSTANCE_NAME = s1idm)

)

)

 

 svlidmdbs{oracle}: lsnrctl start s1idm_ls
svlidmdbs{oracle}: lsnrctl status s1idm_lsLSNRCTL for Linux: Version 10.2.0.2.0 – Production on 17-FEB-2009 16:54:19Copyright (c) 1991, 2005, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=svlidmdbs)(PORT=7300)))STATUS of the LISTENER

————————

Alias                     s1idm_ls

Version                   TNSLSNR for Linux: Version 10.2.0.2.0 – Production

Start Date                17-FEB-2009 14:03:55

Uptime                    0 days 2 hr. 50 min. 23 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/product/102_idm/network/admin/listener.ora

Listener Log File         /oracle/product/102_idm/network/log/s1idm_ls.log

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=svlidmdbs)(PORT=7300)))

Services Summary…

Service “S1IDM_XPT” has 1 instance(s).

Instance “s1idm”, status READY, has 1 handler(s) for this service…

Service “SIDM” has 1 instance(s).

Instance “s1idm”, status READY, has 1 handler(s) for this service…

Service “s1idm” has 1 instance(s).

Instance “s1idm”, status READY, has 1 handler(s) for this service…

Service “s1idm_DGMGRL” has 1 instance(s).

Instance “s1idm”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

 

19.  Refresh is done.