美文网首页
Oracle How to Change SID of data

Oracle How to Change SID of data

作者: ifels | 来源:发表于2016-09-12 14:49 被阅读0次
    Concept 
    clone db to new sid 
    - trace controlfile and edit to new path except database name and add "SET" to database name
        alter database backup controlfile to trace as '/some/arbitrary/path' reuse;
    - alter database backup controlfile to trace;
    - copy all datafile to new path
    - copy init file to new name and edit to new path but 
      db_name and instance_name is original
    - make core dir,bdump and .., to new path
    - export oracle_sid to new new 
    - create orapasswd to new name
    - start up nomount mode and run create new controlfile
    - alter database open resetlogs 
    # if error in  Unable to create logfile file name to set 
    alter system set db_create_online_log_dest_1='/data1/oradata/newdb2/' scope=both; 
    # oracle will not the same sid 
    # difference dbid but same sid is no
    # same sid difference instance(rac)
    # same sid is start in one instance other to exclusive mode 
    Step-by-Step 
    Cloning Procedures 
    1. Make a full backup of the source database 
    # stop the listener 
    $ lsnrctl stop 
    # login the source database as sysdba 
    # shutdown the database for a full backup 
    $ export ORACLE_SID=PROD
    $ sqlplus internal
    SQL> shutdown immediate 
    # backup the config files 
    $ cd /demo/oracle/db/8.1.7/admin 
    $ tar -czvf PROD_config.tar.gz PROD 
    # backup the datafiles 
    $ cd /demo/oracle/db/8.1.7/oradata 
    $ tar -czvf PROD_data.tar.gz PROD 
    2. Create the control files script 
    # startup the source database 
    $ sqlplus internal 
    SQL> startup 
    # create the control files script for the target database 
    # the file can be found at /demo/oracle/db/8.1.7/admin/PROD/udump 
    # for example, ora_17832.trc 
    SQL> alter database backup controlfile to trace resetlogs; 
    # shutdown the database for cloning 
    SQL> shutdown immediate 
    # copy the control file script for later use 
    $ cd /demo/oracle/db/8.1.7/admin/PROD/udump
    $ cp ora_17832.trc /demo/oracle/db/TEST_ctl.sql 
    3. Copy the source database to the target database 
    # copy the config files 
    $ cd /demo/oracle/db/8.1.7/admin
    $ cp -r PROD TEST # create the initTEST.ora 
    $ cd /demo/oracle/db/8.1.7/admin/TEST/pfile 
    $ mv initPROD.ora initTEST.ora 
    # copy the datafiles 
    $ cd /demo/oracle/db/8.1.7/oradata 
    $ cp -r PROD TEST 
    # remove the original control files 
    # we will re-create them later 
    $ cd /demo/oracle/db/8.1.7/oradata/TEST 
    $ rm control01.ctl control02.ctl control03.ctl 
    4. Prepare the initTEST.ora 
    # edit the initTEST.ora 
    # 1. change all PROD to TEST 
    # 2. update the file locations 
    # 3. set remote_login_passwordfile = none 
    $ cd /demo/oracle/db/8.1.7/admin/TEST/pfile 
    $ vi initTEST.ora 
    # create a link for initTEST.ora at /demo/oracle/db/8.1.7/dbs 
    # oracle will use this file to start the instance 
    $ cd /demo/oracle/db/8.1.7/dbs
    $ ln -s /demo/oracle/db/8.1.7/admin/TEST/pfile/initTEST.ora initTEST.ora 
    5. Prepare the control file script 
    # edit the TEST_ctl.sql 
    $ cd /demo/oracle/db
    $ vi TEST_ctl.ora 
    # 1. remove comment lines 
    # 2. change all PROD to TEST 
    # 3. update the file locations 
    # 4. change the following line 
    #    CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG 
    #    to 
    #    CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG 
    # 5. remove the following line 
    #    RECOVER DATABASE USING BACKUP CONTROLFILE 
    
    # after edit, the TEST_ctl.sql should look like this 
      STARTUP NOMOUNT
      CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
      MAXLOGFILES 16
      MAXLOGMEMBERS 2
      MAXDATAFILES 30
      MAXINSTANCES 1
      MAXLOGHISTORY 7260
      LOGFILE
      GROUP 1 '/demo/oracle/db/8.1.7/oradata/TEST/redo01.log' SIZE 500K,
      GROUP 2 '/demo/oracle/db/8.1.7/oradata/TEST/redo02.log' SIZE 500K,
      GROUP 3 '/demo/oracle/db/8.1.7/oradata/TEST/redo03.log' SIZE 500K
      DATAFILE
        '/demo/oracle/db/8.1.7/oradata/TEST/system01.dbf',
        '/demo/oracle/db/8.1.7/oradata/TEST/tools01.dbf',
        '/demo/oracle/db/8.1.7/oradata/TEST/rbs01.dbf',
        '/demo/oracle/db/8.1.7/oradata/TEST/users01.dbf',
        '/demo/oracle/db/8.1.7/oradata/TEST/indx01.dbf',
        '/demo/oracle/db/8.1.7/oradata/TEST/drsys01.dbf',
      CHARACTER SET US7ASCII
      ;
      ALTER DATABASE OPEN RESETLOGS;
      ALTER TABLESPACE TEMP ADD TEMPFILE 
        '/demo/oracle/db/8.1.7/oradata/TEST/temp01.dbf' REUSE; 
    6. Re-create the control files 
    # start the instance for the target database 
    $ export ORACLE_SID=TEST 
    $ sqlplus internal 
    # re-create the control files for the target database 
    SQL> @/demo/oracle/db/TEST_ctl.sql 
    # change the global name of the target database 
    SQL> alter database rename global_name to TEST; 
    7. Edit the network config files 
    # edit the listener.ora 
    # add the following lines in the SID_LIST_LISTENER section 
    (SID_DESC =
        (GLOBAL_DBNAME = TEST)
        (ORACLE_HOME = /demo/oracle/db/8.1.7)
        (SID_NAME = TEST)
      ) 
    # edit the tnsnames.ora 
    # add the following lines at the buttom 
    TEST = (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.1)(PORT=1521))
        (CONNECT_DATA=(SID=TEST))
      ) 
    # start the listener 
    $ lsnrctl start 
    8. Edit the oratab file 
    # add a entry for the new database 
    # dbstart will use this file to bring the database up/down 
    # during system boot time 
    $ cd /etc 
    $ vi oratab 
    # after edit, the oratab file should look like this 
      PROD:/demo/oracle/db/8.1.7:N
      TEST:/demo/oracle/db/8.1.7:N
    
    Credit: Knowledge Management IT for you: Oracle How to Change SID of database and Clone database 
    Under Creative Commons License: Attribution
    

    原文:http://kmit4u.blogspot.hk/2010/07/oracle-how-to-change-sid-of-database.html

    相关文章

      网友评论

          本文标题:Oracle How to Change SID of data

          本文链接:https://www.haomeiwen.com/subject/vpppettx.html