Step by Step – Oracle database physical migrate

中文版:http://www.ioracle.org/archives/241

由于特殊原因,顺便写了此英文版,较中文版更简略。

On source database:
1.    Use the following scripts to trace control file.

SELECT    d.VALUE
 || '/'
 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
 || '_ora_'
 || p.spid
 || '.trc' trace_file_name
 FROM (SELECT p.spid
 FROM v$mystat m, v$session s, v$process p
 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
 (SELECT t.INSTANCE
 FROM v$thread t, v$parameter v
 WHERE v.NAME = 'thread'
 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
 (SELECT VALUE
 FROM v$parameter
 WHERE NAME = 'user_dump_dest') d
/

2.    Collect source database information
—Character Set

col parameter for a50
col value for a50
select * from nls_database_parameters;

—Block

show parameter block

—Export Control File

Alter database backup controlfile to trace;

—Find The Trace File

@gettrcname

—Datafile,Tablespace Infomation

Select name from v$datafile;
Select tablespace_name from dba_tablespaces;
Select member from v$logfile;

3.    Shutdown database

shutdown immediate

On destination database:
1.    Delete the following directories or files:

/u02/flash_recovery_area
 /u01/app/oracle/diag/rdbms/xe5/XE5/trace/
 /u02/oradata/xe5/control01.ctl
 /u02/oradata/xe5/TEMP01.DBF

2.    Copy data file and redo file to new database, unrar them:

./rar/unrar x REDO110408.rar /u02/oradata/xe5/ &
 ./rar/unrar x DBF110408.rar /u02/oradata/xe5/ &

3.    Configure trace file

cp marshall_ora_4484.trc createctl.sql
 vi createctl.sql
STARTUP NOMOUNT
 CREATE CONTROLFILE REUSE DATABASE "XE5" NORESETLOGS  ARCHIVELOG
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 8
 MAXLOGHISTORY 584
 LOGFILE
 GROUP 1 '/u02/oradata/xe5/REDO01.LOG'  SIZE 50M,
 GROUP 2 '/u02/oradata/xe5/REDO02.LOG'  SIZE 50M,
 GROUP 3 '/u02/oradata/xe5/REDO03.LOG'  SIZE 50M
 DATAFILE
 '/u02/oradata/xe5/SYSTEM01.DBF',
 '/u02/oradata/xe5/SYSAUX01.DBF',
 '/u02/oradata/xe5/UNDOTBS01.DBF',
 '/u02/oradata/xe5/USERS01.DBF',
 '/u02/oradata/xe5/FLOW_1.DBF',
 '/u02/oradata/xe5/FLOW_2.DBF',
 '/u02/oradata/xe5/FLOW_3.DBF'
 CHARACTER SET AL32UTF8
 ;

4.    Check environment variably

echo $ORACLE_SID

5.    Create pfile
Create pfile under $ORACLE_HOME/db. Name it as init<ORACLE_SID>.ora
db_name= “MARSHALL”
audit_file_dest=’/u01/app/oracle/admin/orcl/adump’
db_recovery_file_dest=’/u02/app/oracle/flash_recovery_area’
db_recovery_file_dest_size=2147483648
diagnostic_dest=’/u01/app/oracle’
control_files=’/u02/oradata/marshall/control01.ctl’,’/u02/flash_recovery_area/marshall/control02.ctl’
6.    Rebuild control file
Run the following script.
@createctl.sql
7.    Check archive mode
archive log list
Change archivelog mode to noarchivelog mode when the database is mounted.

alter database noarchivelog;
 archive log list

8.    Restart database

shutdown immediate
 startup upgrade
 archive log list

9.    Add temp file

alter tablespace temp add tempfile '/u02/oradata/xe5/TEMP01.DBF' size 10M autoextend on maxsize unlimited;

10.    Check shared pool size

show parameter shared_pool

11.    Run the following scripts one by one

nohup sqlplus “/ as sysdba” @?/rdbms/admin/utlu112i.sql &
 mv nohup.out utlu112i.log
 
nohup sqlplus “/ as sysdba” @?/rdbms/admin/utlirp.sql &
 mv nohup.out utlirp.log

 

// the catupgrd.sql will run about 2 hours

nohup sqlplus “/ as sysdba” @?/rdbms/admin/catupgrd.sql &
 mv nohup.out catupgrd.log

//to check INVALID obejects

select count(*) from dba_objects where status =’INVALID’;

//recompile INVALID objects

nohup sqlplus “/ as sysdba” @?/rdbms/admin/utlrp.sql
 mv nohup.out utlirp2.log

//to check INVALID obejects

Select count(*) from dba_objects where status=’INVALID’;
 
set pages 1000 lines 180
 col owner for a20
 col object_id for a80
 col object_type for a30
 select owner,object_id,object_type from dba_objects where status=’INVALID’;

 

//change arvhivelog mode

Alter database archivelog;

12.    Add backup strategy

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress