windows 遷移資料庫

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;

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

炊煙起了；夕陽下了；細雨來了

多調試,互動式程式設計體驗

記錄，獨立思考，對比

感謝轉載作者

修車

國産化

read and connect

匍匐前進,

講故事