Using RMAN active duplication to move a 12c database to another file system

The purpose of this blog article is to show how you can use RMAN active duplication feature to move a database on another file system on the same machine. RMAN active database duplication allows to create a duplicate database without using additional space on another media like disk or tape: RMAN is using only the network like a backup device.

This database move requires 3 major steps:

  • duplicating the source database to another file system with RMAN active duplication
  • dropping the source database
  • renaming the new database with DBNEWID utility (nid executable) including parameter file changes.
  • Configuration used

    I have used Oracle 12.1.0.1 Entreprise Edition on Linux. The source database is a container database having 2 pluggable databases, it is using file system as storage with a fast recovery area (FRA). The destination database is also using file system as storage with a FRA but with Oracle managed files (OMF).

    All Unix commands are run with Oracle software ower account named ‘oracle’ (unless shell prompt is ‘#’: in this case it means ‘root’ account has been used). All SQL statements are with SYSDBA privileges.

    Prerequisite

    Source database must be running in ARCHIVELOG mode. Check with:

    SYS@cdb12c>select log_mode from v$database;
    
    LOG_MODE
    ------------
    ARCHIVELOG
    

    The source database DB_NAME is cdb12c and the destination database DB_NAME is DBNEW.

    Setup Oracle Net configuration files

    RMAN active duplication is using the network and requires Oracle Net connections: so you must make sure that there is one Oracle Net alias for the source database and the destination database in tnsnames.ora:

    cdb12c =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ol6twsf.localdomain)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = cdb12c)
        )
      )
    
    NEWDB =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ol6twsf.localdomain)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = newdb)
        )
      )
    
    

    Because RMAN needs to connect to the auxiliary database started in NOMOUNT mode you need to define the destination database with a static entry in listener.ora:

    LISTENER=
      (DESCRIPTION=
        (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=tcp)(HOST=ol6twsf)(PORT=1521))))
    #
    SID_LIST_LISTENER=
      (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=NEWDB)
          (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)
          (SID_NAME=NEWDB)))
    

    Create database password files

    Because RMAN active duplication requires to connect to source and destination with an Oracle Net connection (needed in case you use SPFILE feature for destination database), password file must exist to allow remote SYS connections. For the destination database that does not exist you must create it:

    $ orapwd file=$ORACLE_HOME/dbs/orapwNEWDB password=oracle
    

    Prepare file system

    Create the needed directories for the destination database:

    # mkdir /u04/oradata
    # chown oracle:dba /u04/oradata
    $ mkdir /u04/oradata/fra
    

    Create a minimum PFILE for auxiliary instance

    Create the file $ORACLE_HOME/dbs/initNEWDB.ora with following content:

    db_name=NEWDB
    

    Start auxiliary instance in NOMOUNT mode

    $ export ORACLE_SID=NEWDB
    $ sqlplus / as sysdba
    
    SQL*Plus: Release 12.1.0.1.0 Production on Fri May 23 17:05:27 2014
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SYS@NEWDB>startup nomount;
    ORACLE instance started.
    
    Total System Global Area  217157632 bytes
    Fixed Size                  2286656 bytes
    Variable Size             159386560 bytes
    Database Buffers           50331648 bytes
    Redo Buffers                5152768 bytes
    
    >
    

    Write the RMAN script

    In this script I have used hard coded control files names otherwise RMAN fails.

    If CONTROL_FILES parameter is not set RMAN reuses source database control file names and triggers:

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 05/23/2014 17:06:03
    RMAN-05501: aborting duplication of target database
    RMAN-03015: error occurred in stored script Memory Script
    ORA-19849: error while reading backup piece from service cdb12c
    ORA-19504: failed to create file "/u01/oradata/cdb12c/control01.ctl"
    ORA-27086: unable to lock file - already in use
    Linux-x86_64 Error: 11: Resource temporarily unavailable
    Additional information: 8
    Additional information: 2357
    

    If CONTROL_FILES is set and is referencing directories that should be created by RMAN, RMAN also fails (likely because directories are created too late in the duplication process):

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 05/23/2014 16:56:43
    RMAN-05501: aborting duplication of target database
    RMAN-03015: error occurred in stored script Memory Script
    ORA-19849: error while reading backup piece from service cdb12c
    ORA-19504: failed to create file "/u04/oradata/NEWDB/cf1.ctl"
    ORA-27040: file create error, unable to create file
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 1
    

    The script dup.rman I have used is:

    duplicate target database to NEWDB from active database
    password file
    spfile
    set compatible '12.1.0.1'
    set enable_pluggable_database 'true'
    set control_files '/u04/oradata/cf1.ctl','/u04/oradata/fra/cf2.ctl'
    set db_recovery_file_dest_size '10G'
    set db_recovery_file_dest '/u04/oradata/fra'
    set db_create_file_dest '/u04/oradata'
    set db_create_online_log_dest_1 '/u04/oradata'
    set db_create_online_log_dest_2 '/u04/oradata/fra'
    nofilenamecheck;
    

    Start the database duplication

    You only need to run:

    $ rman target sys/oracle@cdb12c auxiliary sys/oracle@NEWDB cmdfile=dup.rman
    

    Wait that RMAN output ends with:

    contents of Memory Script:
    {
       Alter clone database open resetlogs;
    }
    executing Memory Script
    
    database opened
    
    contents of Memory Script:
    {
       sql clone "alter pluggable database all open";
    }
    executing Memory Script
    
    sql statement: alter pluggable database all open
    Finished Duplicate Db at 28-MAY-14
    
    Recovery Manager complete.
    

    At this step the duplicate database is ready to be used.

    Drop source database

    $ . oraenv
    ORACLE_SID = [NEWDB] ? cdb12c
    The Oracle base remains unchanged with value /u01/app/oracle
    $ sqlplus / as sysdba
    
    SQL*Plus: Release 12.1.0.1.0 Production on Wed May 28 09:11:49 2014
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SYS@cdb12c>shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SYS@cdb12c>startup mount exclusive restrict;
    ORACLE instance started.
    
    Total System Global Area 1035534336 bytes
    Fixed Size                  2296184 bytes
    Variable Size             406849160 bytes
    Database Buffers          620756992 bytes
    Redo Buffers                5632000 bytes
    Database mounted.
    SYS@cdb12c>drop database;
    
    Database dropped.
    
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    SYS@cdb12c>
    

    Rename new database

    Because database renaming will change DB_NAME in control files but not in SPFILE you must switch database parameter file from SPFILE to PFILE (to be able to change DB_NAME manually in parameter file later) before running nid (DBNEWID):

    $ export ORACLE_SID=NEWDB
    $ sqlplus / as sysdba
    
    SQL*Plus: Release 12.1.0.1.0 Production on Wed May 28 09:18:31 2014
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SYS@NEWDB>create pfile from spfile;
    
    File created.
    
    SYS@NEWDB>shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SYS@NEWDB>startup mount;
    ORACLE instance started.
    
    Total System Global Area 1035534336 bytes
    Fixed Size                  2296184 bytes
    Variable Size             402654856 bytes
    Database Buffers          624951296 bytes
    Redo Buffers                5632000 bytes
    Database mounted.
    SYS@NEWDB>exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    $ nid target=sys/oracle dbname=cdb12c logfile=/tmp/nid.log
    $ cat /tmp/nid.log
    
    DBNEWID: Release 12.1.0.1.0 - Production on Wed May 28 09:31:22 2014
    
    Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to database NEWDB (DBID=3089151096)
    
    Connected to server version 12.1.0
    
    Control Files in database:
        /u04/oradata/cf1.ctl
        /u04/oradata/fra/cf2.ctl
    
    Changing database ID from 3089151096 to 4047369130
    Changing database name from NEWDB to CDB12C
        Control File /u04/oradata/cf1.ctl - modified
        Control File /u04/oradata/fra/cf2.ctl - modified
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_system_9rc2domb_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_sysaux_9rc2fgnn_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_undotbs1_9rc2g7rx_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_system_9rc2ggpz_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_users_9rc2gxoh_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_sysaux_9rc2gysw_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_system_9rc2hr2k_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_sysaux_9rc2j6x6_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_users_9rc2jzvp_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_system_9rc2k10o_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_sysaux_9rc2kj2w_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_users_9rc2l91b_.db - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_temp_9rc2m4vv_.tm - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_temp_9rc2md3k_.tm - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_temp_9rc2mm1l_.tm - dbid changed, wrote new name
        Datafile /u04/oradata/NEWDB/datafile/o1_mf_temp_9rc2mvv8_.tm - dbid changed, wrote new name
        Control File /u04/oradata/cf1.ctl - dbid changed, wrote new name
        Control File /u04/oradata/fra/cf2.ctl - dbid changed, wrote new name
        Instance shut down
    
    Database name changed to CDB12C.
    Modify parameter file and generate a new password file before restarting.
    Database ID for database CDB12C changed to 4047369130.
    All previous backups and archived redo logs for this database are unusable.
    Database is not aware of previous backups and archived logs in Recovery Area.
    Database has been shutdown, open database with RESETLOGS option.
    Succesfully changed database name and ID.
    

    Note that database has been renamed to CDB12C (uppercase). The last steps to be done:

  • former spfileNEWDB.ora must be removed
  • DB_NAME parameter must be modified in new PFILE initCDB12C.ora
  • ORACLE_SID must updated in /etc/oratab
  • $ rm $ORACLE_HOME/dbs/spfileNEWDB.ora
    $ mv $ORACLE_HOME/dbs/initNEWDB.ora $ORACLE_HOME/dbs/initCDB12C.ora
    $ grep db_name $ORACLE_HOME/dbs/initCDB12C.ora
    *.db_name='CDB12C'
    $ grep CDB12C /etc/oratab
    CDB12C:/u01/app/oracle/product/12.1.0/db_1:N
    

    Now database instance CDB12C can be restarted and must be opened with RESETLOGS (because DBID has also been changed):

    $ . oraenv
    ORACLE_SID = [NEWDB] ? CDB12C
    The Oracle base remains unchanged with value /u01/app/oracle
    -bash-4.1$ sqlplus / as sysdba
    
    SQL*Plus: Release 12.1.0.1.0 Production on Wed May 28 09:46:45 2014
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SYS@CDB12C>startup mount;
    ORACLE instance started.
    
    Total System Global Area 1035534336 bytes
    Fixed Size                  2296184 bytes
    Variable Size             402654856 bytes
    Database Buffers          624951296 bytes
    Redo Buffers                5632000 bytes
    Database mounted.
    SYS@CDB12C>alter database open resetlogs;
    
    Database altered.
    

    Very last step is to create a SPFILE and to restart database instance to make database instance use the new SPFILE:

    SYS@CDB12C>create spfile from pfile;
    
    File created.
    
    SYS@CDB12C>shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SYS@CDB12C>startup;
    ORACLE instance started.
    
    Total System Global Area 1035534336 bytes
    Fixed Size                  2296184 bytes
    Variable Size             402654856 bytes
    Database Buffers          624951296 bytes
    Redo Buffers                5632000 bytes
    Database mounted.
    Database opened.
    SYS@CDB12C>show parameter spfile;
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    spfile                               string      /u01/app/oracle/product/12.1.0
                                                     /db_1/dbs/spfileCDB12C.ora
    

    Note that you should also backup the renamed database because previous backups are not usable after this DBNEWID step.

    Advertisements
    Post a comment or leave a trackback: Trackback URL.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: