Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: The cloning of the database in noarchivelog mode

  1. #1

    Red face The cloning of the database in noarchivelog mode

    Why cloning is needed?
    1. Oracle database has to be relocated to another machine.
    2. Oracle database has to be mounted to new Storage media.
    3. Renaming Oracle database

    [oracle@localhost ~]$ export ORACLE_SID=orcl
    [oracle@localhost ~]$ sqlplus "/ as sysdba"p

    SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 12 15:01:03 2011
    Copyright (c) 1982, 2005, Oracle. All rights reserved.
    Connected to an idle instance.

    SQL> startup
    ORACLE instance started.
    Total System Global Area 285212672 bytes
    Fixed Size 1218992 bytes
    Variable Size 96470608 bytes
    Database Buffers 184549376 bytes
    Redo Buffers 2973696 bytes
    Database mounted.
    Database opened.

    SQL> select name from v$database;
    NAME
    ---------
    ORCL

    SQL> conn / as sysdba
    Connected.
    SQL> select username from v$session;

    USERNAME
    ------------------------------
    space
    space
    space
    SYS
    Space

    22 rows selected.
    You would see several blank rows being returned with no username they are actually the internal oracle logins and one SYS login will be shown thats the only sys who is running the commands.
    SQL> alter system enable restricted session;
    System altered.

    SQL> alter database backup controlfile to trace;
    Database altered.

    SQL> show parameter user_dump_dest

    NAME TYPE VALUE
    ------------------------ --------- --------------------------------------------------
    user_dump_dest string /u01/app/oracle/admin/orcl/udump

    SQL> archive log list;
    Database log mode No Archive Mode
    Automatic archival Disabled
    Archive destination USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence 3
    Current log sequence 5

    SQL> shut immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    CREATE “CREATE_DB.SQL” IN VIM OR NOTEPAD AND MAKE THE FOLLOWING ENTRIES

    STARTUP NOMOUNT
    CREATE CONTROLFILE set DATABASE "ORCL3" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
    LOGFILE
    GROUP 1 '/u01/app/oracle/oradata/orcl3/redo01.log' SIZE 50M,
    GROUP 2 '/u01/app/oracle/oradata/orcl3/redo02.log' SIZE 50M,
    GROUP 3 '/u01/app/oracle/oradata/orcl3/redo03.log' SIZE 50M
    -- STANDBY LOGFILE
    DATAFILE
    '/u01/app/oracle/oradata/orcl3/system01.dbf',
    '/u01/app/oracle/oradata/orcl3/undotbs01.dbf',
    '/u01/app/oracle/oradata/orcl3/sysaux01.dbf',
    '/u01/app/oracle/oradata/orcl3/users01.dbf',
    '/u01/app/oracle/oradata/orcl3/example01.dbf'
    CHARACTER SET WE8ISO8859P1;
    [oracle@localhost ~]$ export ORACLE_SID=orcl3
    [oracle@localhost ~]$ sqlplus "/ as sysdba"

    SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 12 15:19:18 2011
    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Connected to an idle instance.

    Remember to create the init<sid>.ora file at dbs folder example: ‘/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl3.ora'

    SQL> @/u01/app/oracle/create_db.sql
    ORACLE instance started.

    Total System Global Area 285212672 bytes
    Fixed Size 1218992 bytes
    Variable Size 92276304 bytes
    Database Buffers 188743680 bytes
    Redo Buffers 2973696 bytes

    Control file created.

    SQL> alter database open resetlogs;
    Database altered.

    SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl3/temp03.dbf' size 25m;
    Tablespace altered.

    SQL> alter database rename global_name to orcl3;
    Database altered.

    SQL> select name from v$database;

    NAME
    ---------
    ORCL3

    SQL> archive log list;
    Database log mode No Archive Mode
    Automatic archival Disabled
    Archive destination USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence 0
    Current log sequence 1
    SQL>

  2. #2
    Administrator
    Join Date
    Oct 2011
    Posts
    13
    1. Identify the list of data files, control files, redo logs of the db to be cloned
    2. Create backup control file from trace
    3. Create pfile from spfile (if it’s not already available)
    4. Copy it as init.ora and edit the below
    a. Path of ‘control files’ to reflect the new directory path
    b. dump directories etc
    c. db_name is set to “clonedb”
    5. alter system switch logfile
    6. shutdown immediate
    7. cp –rp datafiles and redologs to the new clonedb path
    8. edit the original control file from the trace from step 2 above
    similar to below
    CREATE CONTROLFILE SET DATABASE "" RESETLOGS NOARCHIVELOG
    ......
    .......
    LOGFILE
    GROUP 1 '/u01/oradata//clonedb/redo01.log' SIZE 50M,
    ........
    DATAFILE
    /u01/oradata/clonedb/system01.dbf,
    ......
    .......
    CHARACTER SET WE8ISO8859P1;
    RECOVER DATABASE USING BACKUP CONTROLFILE;
    ALTER DATABASE OPEN RESETLOGS;
    ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/clonedb/temp01.dbf,
    SIZE ......;


    9. export ORACLE_SID=clonedb
    10. sqlplus /nolog
    startup nomount pfile=’path to edited init.ora’ from step 4 above
    11. Run the create-controlfile script prepared at step 8
    12. Create spfile from pfile
    13. Shutdown immediate
    14. Create password file
    15. Startup
    16. Show parameter db_name


    The above process doesn’t change the ‘dbid’ and if that too needs to be changed, we need to use dbnewid (nid) utility.


    The only reason you need to copy the redologs too is if the 'recover using backup contolfile' does ask for a change in the redolog, you need to manually provide the path of the redolog.
    If you switch the logfile prior to shutdown, it won't be required but it's better to copy them anyway


    Pradip K. | Sr. Oracle Apps DBA
    www.dbalounge.com
    Contact me: er.pradeepkr erpradeepkr
    A creative man is motivated by the desire to achieve, not by the desire to beat others.
    ---------------------------------------------------------------------------------------
    TAGS: DBA Lounge Provides Oracle DBA Training Services. This includes Online Training, Classroom based Training at Noida (Delhi NCR).
    Students come form Delhi,Gurgaon,Noida,Ghaziabad,Faridabad,Greater Noida.
    Trainings : Oracle Database,10G,11G,RAC,Apps,11i,R12,Fusion Middleware,WebLogic,OID,OBIEE,WebCenter.
    We plan to commence Oracle DBA Training at Chandigarh,Mohali,Panchkula,Bangalore,Jaipur,Pune, Mumbai,Jammu and Hyderabad soon.

+ Reply to Thread

Similar Threads

  1. Archivelog Mode On RAC
    By devesh_ocp in forum RAC Installation, ASM Install , ASM Administration
    Replies: 0
    Last Post: 04-25-2012, 03:28 PM
  2. What happens when we put either database or tablespace in begin backup mode
    By nemat in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 01-13-2012, 02:16 PM
  3. Changing the Archivelog Mode on 9i and earlier
    By Sandeep Sharma in forum Core Database Administration and Monitoring
    Replies: 3
    Last Post: 12-22-2011, 09:29 PM
  4. Changing Oracle Database from Noarchivelog to Archivelog mode
    By Pradeep in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 12-14-2011, 05:10 AM
  5. Maintenance Mode
    By Hemant in forum AD Utilities , Autoconfig , FND Utilities
    Replies: 0
    Last Post: 10-30-2011, 12:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics