提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  系统

当前栏目

windows 迁移数据库

Windows数据库迁移
2023-09-11 14:18:40 时间

1) Prerequisites
    ----------------

   - The copy of the datafiles must be done with the database closed or the database must be in ARCHIVELOG  mode.
     To determine the Database is in Archivemode or Noarchivemode use:

       SQL> select name, log_mode from v$database;

 

2) Init<sid>.ora or Spfile<sid>.ora and Controlfile
     ----------------------------------------------------
   - You need to copy the init.ora or spfile file to the target host
     and locate it in ORACLE_HOME\dbs

   - Copy the
        Controlfile(s),
        all the Datafiles  
        all the Archivelogs generated,
     to the target host.

   # To copy the Controlfile,
    - either do a clean shutdown the Database, then take a cold copy of the controlfile
    - or if database is open and Online Backup is taken do:

       SQL> alter database backup controlfile to '/path/cf_name.ctl' ;   -- this takes a binary copy of the controlfile

       -- Hint:
          Do this to get a creation script for the controlfile, in case needed.
          The following statement writes a tracefile to the 'trace' directory containing 'Create Controlfile' Statements
          .
           SQL> alter database backup controlfile to trace ;

 

   # To backup the Database if database is open then, you need to put all the tablespaces in BACKUP MODE,
      before starting the copy of the database/datafiles :

 


     SQL> select tablespace_name from dba_tablespaces;

    SQL> ALTER TABLESPACE <TABLESPACE_NAME> BEGIN BACKUP;
        .
         > copy all the tablespace 'datafiles'
        .
    SQL> ALTER TABLESPACE <TABLESPACE_NAME> END BACKUP;

        # ==> Do this copy for 'ALL THE TABLESPACES/Datafiles'  in the Database !!


    # Comment: Starting with Oracle 10g:
               you can use the BEGIN BACKUP on 'database' level, instead of 'tablespace' level :

            SQL> alter DATABASE begin backup;
                .
                 > copy all the tablespace 'datafiles'
                .
            SQL> alter DATABASE end backup;

 

3) Set the oracle environment
   -------------------------

    C:\> set ORACLE_SID=<SID>
    C:\> sqlplus "sys/password as sysdba"

 

    - Check the init<sid>.ora  parameters that reference 'path/dir' location
    
    control_files                   = <duplicate db control file(s)>
    background_dump_dest  = bdump>
    core_dump_dest            = cdump>
    user_dump_dest            = udump>
    log_archive_dest_1        = <duplicate db arch dump location>

 
4) Set up a password file for the duplicated database

 

    $ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<...>

 

  If Windows Platform, create a new NT service for the 'cloned' duplicated database  using oradim. 

 

   C:\> oradim -new -sid -intpwd -maxusers 10 -startmode manual -spfile  ''

   # or

   C:\> oradim -new -sid -intpwd -maxusers 10 -startmode manual -pfile   ''

 sample:


1) orapwd file=C:\app\oracle\product\12.1.0\dbhome_1\database\orapwtestUAT12C.ora password=oracle123


2)oradim -new -sid testUAT12C -SYSPWD oracle123 -maxusers 10 -startmode manual -pfile  'C:\app\oracle\product\12.1.0\dbhome_1\database\INITtestUAT12C.ORA'

3) copy network from source to destination

6)  Startup the database in mount status

 

    SQL> startup mount

    -- Rename any of the datafiles to the new location, if necessary:

    SQL> ALTER DATABASE RENAME FILE
               '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
               TO
               '/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';


    -- Rename the logfiles to the new location if necessary

    SQL> alter database rename file '<host A location>' to '<host B location>';


      
7)  Check that all the datafiles are in the right location and ONLINE:

 

SQL> SELECT FILE#, STATUS, NAME FROM V$DATAFILE;

 


8) Perform incomplete recovery:     

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

 

    Forward the database applying archived redo log files until you decide
    to stop recovery by typing 'CANCEL' at the prompt
    (assuming that you have  required archived redo log files in the log_archive_dest directory)
    .
    You may archive the source database redo log files and apply them at
    the target database if required.

 

SQL> ALTER DATABASE OPEN RESETLOGS;

 

 

 
9) In Windows platforms, if you want that the database will start automatically then edit the registry:
    

     regedit
       go to
       HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEX
       .
       change the key :  ORA_<SID>_AUTOSTART=TRUE
 
 
sample 2: clone cold backup from prod to uat . 


1.make file and modfiy file:
'C:\app\Administrator\product\11.2.0\dbhome_1\database\initdmprod.ora'

 

2.
orapwd file=C:\app\Administrator\product\11.2.0\dbhome_1\database\PWDDMSPROD.ora password=oracle123
oradim -new -sid dmsprod -SYSPWD oracle123 -maxusers 10 -startmode manual -pfile 'C:\app\Administrator\product\11.2.0\dbhome_1\database\initdmsprod.ora'

 

3.
C:\app\Administrator\admin
set ORACLE_SID=dmprod
sqlplus "sys/oralce as sysdba"

startup mount


##change data file location to 'D:\dmsprod'
rman nocatalog target /
catalog start with 'D:\dmsprod';
switch database to copy;


sqlplus "sys/oralce as sysdba"

select 'alter database rename file '||''''||member||''''||' to '||chr(39)||replace(member,'F:\ORA11GDATA\DMSPROD\','D:\dmsprod\')||''';' from v$logfile;


shutdown immediate
startup

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\DMSPROD\TEMP01.DBF' REUSE;
ALTER TABLESPACE TEMP drop TEMPFILE 'F:\ORA11GDATA\DMSPROD\TEMP01.DBF';

4.
add service_names='DMSUAT,DMSPROD’
select service_id,name from v$services;

相关文章