1. Introduction

I have been looking lately for the best solution in Oracle 12c to relocate database from one server to another with minimum downtime and hassle.

I had a few requirements:

  • The method should provide minimum downtime for the service affected.
  • A MUST HAVE: leaving the original files intact, a quick fallback to the previous environment if an immediate issue arises.
  • I should be able to prepare destination server in advance and just the step of the move itself can cause any related service to be unavailable.
  • DB_NAME, DB_UNIQUE_NAME should be the same, as I want this database to look exactly as it is now.
  • I want the old backup to be valid and accessible to the database in new place – I don’t want to create new incarnation of the database, clone it or anything similiar.
  • Don’t want to build Data Guard for this purpose – too much hassle if you have a lot of databases to move .
  • It would be best to avoid a requirement for a staging area on NFS or anywhere.

I have had a similiar procedure in 11.2 using BACKUP FOR RECOVER option but it is not straightforward especially when you have ASM and it requires a staging area. With just the filesystem under the hood it would work smoothly. I could backup database as copy to the nfs share which would be the final directory for the files on the destination server and then recover the files until the time of the offline phase. With ASM the procedure of the BACKUP FOR RECOVER is not capable to backup as copy and recover files to and in the remote ASM, it implies additional staging area and downtime is needed to copy the files to the final destination and to complete all the steps.

Looking for an idea and reading a little about the complexity of mixing Multitenancy with Data Guard in 12c I have found quite a few interesting notes, which led to me to the final solution :

What I found the most interesting is the feature of BACKUP AS COPY, you can make a backup to an auxiliary database instance (yes yes, to the remote ASM), this is exactly what I need here. I googled and it turned out to be available since 11.1 – wow, I was not aware of it :).
https://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta007.htm

The command is dedicated for the Data Guard environments which I don’t want to use, but maybe some tricks would do the job – more about it later.

The following connects to the target and auxliary instance and copies the file from the target to auxiliary’s +DATA diskgroup on its associated ASM

RMAN>connect target sys@noncdb
RMAN> connect auxiliary sys@cont3
RMAN> backup as copy database auxiliary format '+data';

If you want to rely on the DB_CREATE_FILE_DEST initialization parameter of the auxiliary instance just run

RMAN> connect target sys@noncdb
RMAN> connect auxiliary sys@cont3
RMAN> backup as copy database auxiliary format NEW;

The second feature that has been implemented in 12.1 is the extension to RMAN allowing to RESTORE and RECOVER database files from a remote database using TNS service.
https://docs.oracle.com/database/121/RCMRF/rcmsynta2008.htm#RCMRF149
https://docs.oracle.com/database/121/RCMRF/rcmsynta2001.htm#RCMRF140

Some examples:

Restore database from service:

RMAN> restore database from service noncdb;

Recover database from service (apply incremental changes to the files)

RMAN> recover database noredo from service noncdb;

Restore controlfile from service

RMAN> restore controlfile from service cont4;

This features seem to be invented for a Data Guard, but I am gonna try to use it my way 😉

2. Test environment

RelocationFromServiceDiagram
Click the grapth to enlarge it

As you can see I have two servers:

  • serverA – the source server with CDB container database named CDB
  • serverB – the destination server where I am going to create new instance to relocate there CDB from serverA

3. General assumptions

  • My Oracle version is 12.1.0.2 with Oct2015 PSU
  • Relocation takes place between machines of the same endianess, architecture and operating system.
  • ASM is in use on both servers
  • OMF is in use on both servers
  • There is a direct network connection between the servers on 1521 ports

4. The Plan

Good graph is worth more than a thousand words 😉

RelocationPlan

Click the graph to enlarge

 

5. Prepare the environments

5.1 Add TNS entries

On the source – serverA add tns entry pointing to the CDB database instance on serverB (dest).

CDB_DEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = serverB)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB) (UR = A)
    )
  )

“(UR = A)” is important as we are going to connect to the not mounted instance on serverB.

On the source and destination – serverA and serverB add a tns entries pointing to the CDB database instance on serverA (source).

CDB_SOURCE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = serverA)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB) 
    )
  )
5.2 Prepare spfile for the destination database

Log in to the source serverA as oracle and use the spfile’s contents of the source database.

[oracle@serverA ~]$ . .CDB
[oracle@serverA ~]$ sqlp
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 4 09:10:47 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management and Unified Auditing options

SQL> create pfile='/tmp/initCDB.ora' from spfile;
File created.

Copy the file to the destination server and modify it.

*._ash_size=266338304
*.archive_lag_target=3600
*.audit_file_dest='/u01/app/oracle/admin/CDB/adump'
*.audit_trail='DB'
*.cluster_database=FALSE
*.compatible='12.1.0.2.0'
*.control_files='+DATA','+FRA'
*.db_block_checking='MEDIUM'
*.db_block_checksum='FULL'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='CDB'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers=''
*.enable_pluggable_database=true
*.fast_start_mttr_target=180
*.global_names=FALSE
*.inmemory_force='OFF'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=800
*.os_authent_prefix=''
*.pga_aggregate_target=1048576000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=4294967296
*.sga_target=4294967296
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.use_large_pages='ONLY'

Modify it to suit your needs, but notice the control_files parameter has been modified.

Create spfile on +DATA using this pfile. Start CDB instance using pfile on serverB in nomount mode in order to create the spfile in the correct directory.

[oracle@serverB ~]$ . .CDB
[oracle@serverB ~]$ sqlplus / as sysdba
Connected to an idle instance.

SQL> startup nomount pfile='initCDB.ora';
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size                  5291928 bytes
Variable Size            1006633064 bytes
Database Buffers         3271557120 bytes
Redo Buffers               11485184 bytes

SQL> create spfile='+DATA' from pfile='/home/oracle/initCDB.ora';
File created.
SQL> shutdown abort;
ORACLE instance shut down.

Now find the newly create spfile in the ASM structure for CDB database and note its path – we will need it later.

[myuser@serverB ~]$ sudo -u grid –i
[grid@serverB ~]$ asmcmd
ASMCMD> ls -l +data/CDB/PARAMETERFILE
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   APR 4 09:00:00  Y    spfile.1014.910776467
5.3 Copy the password file from the source database

In order to be able to use RESTORE FROM SERVICE and RECOVER FROM SERVICE features source password file needs to be copied to the destination serverB

[myuser@serverA ~]$ sudo -u grid –i
[grid@serverA ~]$ srvctl config database -d CDB
Database unique name: CDB
Database name: CDB
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1/
Oracle user: oracle
....
Password file: +DATA/CDB/PASSWORD/pwdCDB.743.898173519
....
[grid@serverA ~]$ asmcmd
ASMCMD> pwcopy DATA/CDB/PASSWORD/pwdCDB.743.898173519 /tmp/orapwCDB
copying +DATA/CDB/PASSWORD/pwdCDB.743.898173519 -> /tmp/orapwCDB

Now copy the extracted password file to serverB using any consistent and safe method

You can read more about handling password files on ASM here
Data Guard Physical Standby – Managing password files in a RAC Physical Standby (Doc ID 1984091.1)

5.4 Add new database to the CRS
[oracle@serverB ~]$ srvctl add database -d CDB -diskgroup "DATA,FRA" -spfile \
+data/CDB/PARAMETERFILE/spfile.1014.910776467 \
-oraclehome /u01/app/oracle/product/12.1.0/dbhome_1
5.5 Copy password file to ASM
[grid@serverB ~]$ asmcmd
ASMCMD> pwcopy --dbuniquename CDB /home/grid/orapwCDB +DATA/CDB/PASSWORD/
copying /home/grid/orapwCDB -> +DATA/CDB/PASSWORD/orapwCDB
5.6 Assign password file to the database resource in CRS
[oracle@serverB ~]$ srvctl modify database -d CDB -pwfile '+DATA/CDB/PASSWORD/orapwCDB'
[oracle@serverB ~]$ srvctl config database -d CDB
Database unique name: CDB
Spfile: +data/CDB/PARAMETERFILE/spfile.1014.910776467
Password file: +DATA/CDB/PASSWORD/orapwCDB

Now we have properly added new database to the configuration.

6. Initial copy

6.1 Start destination CDB instance in nomount
[oracle@serverB ~]$ srvctl start database -d CDB -o nomount
[oracle@serverB ~]$ srvctl status database -d CDB
Database is running.
6.2 Backup as copy from source to destination using AUXILIARY FORMAT clause of BACKUP AS COPY
[oracle@serverA ~]$ rman target sys@CDB_SOURCE auxiliary sys@CDB_DEST

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 4 13:06:10 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: CDB (DBID=363749286)
auxiliary database Password:
connected to auxiliary database: CDB (not mounted)

RMAN> backup as copy database auxiliary format '+data';

Starting backup at 04-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1397 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_data.725.897898611
output file name=+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_data.1017.910789855 tag=TAG20160502T131054
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/this_data.717.897898437
output file name=+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/this_data.1018.910789921 tag=TAG20160502T131054
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/weekend.719.897898439output file name=+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/weekend.1019.910789977 tag=TAG20160502T131054
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/this_index.714.897898433
output file name=+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/this_index.1024.910790075 tag=TAG20160502T131054
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017

 …. Text removed for clarity …..

Finished backup at 04-APR-16

Magic isn’t it!? :). All the files have landed on the auxiliary instance in the directory specified by the DB_CREATE_FILE_DEST (“+DATA”) and DB_UNIQUE_NAME (“+CDB”) parameters.

6.3 Restore controlfile from remote service

Login to the serverB and connect RMAN to the destination CDB instance

[oracle@serverB ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 4 13:37:50 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CDB (not mounted)

RMAN> restore controlfile from service CDB_SOURCE;

Starting restore at 04-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=243 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service CDB_SOURCE
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/CDB/CONTROLFILE/current.1040.910791755
output file name=+FRA/CDB/CONTROLFILE/current.1309.910791755

Finished restore at 04-APR-16
6.4 Catalog previously copied datafiles

ServerB: Mount the CDB instance

RMAN> alter database mount;
Statement processed

Catalog copied datafiles and switch to copy appropriately

RMAN> catalog start with '+DATA/CDB/ ';
searching for all files that match the pattern +DATA/CDB/
List of Files Unknown to the Database
=====================================
File Name: +DATA/CDB/spfileCDB.ora
File Name: +DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/system.1032.910790171
File Name: +DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/sysaux.1033.910790185
File Name: +DATA/CDB/DATAFILE/sysaux.1020.910790021
File Name: +DATA/CDB/DATAFILE/undotbs1.1022.910790051
File Name: +DATA/CDB/DATAFILE/system.1026.910790089
File Name: +DATA/CDB/DATAFILE/audsys.1031.910790131
File Name: +DATA/CDB/DATAFILE/users.1039.910790207
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_data.1017.910789855
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/this_data.1018.910789921
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/weekend.1019.910789977
…. Text removed for clarity…
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/audsys.1038.910790205
File Name: +DATA/CDB/PASSWORD/orapwCDB

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/system.1032.910790171
File Name: +DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/sysaux.1033.910790185
File Name: +DATA/CDB/DATAFILE/sysaux.1020.910790021
File Name: +DATA/CDB/DATAFILE/undotbs1.1022.910790051
File Name: +DATA/CDB/DATAFILE/system.1026.910790089
File Name: +DATA/CDB/DATAFILE/audsys.1031.910790131
File Name: +DATA/CDB/DATAFILE/users.1039.910790207
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_data.1017.910789855
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/this_data.1018.910789921
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/weekend.1019.910789977
…. Text removed for clarity…
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/audsys.1038.910790205

List of Files Which Were Not Cataloged
=======================================
File Name: +DATA/CDB/spfileCDB.ora
  RMAN-07518: Reason: Foreign database file DBID: 0  Database Name:
File Name: +DATA/CDB/PASSWORD/orapwCDB
  RMAN-07518: Reason: Foreign database file DBID: 0  Database Name:

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/CDB/DATAFILE/system.1026.910790089"
datafile 2 switched to datafile copy "+DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/system.1032.910790171"
datafile 3 switched to datafile copy "+DATA/CDB/DATAFILE/sysaux.1020.910790021"
datafile 4 switched to datafile copy "+DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/sysaux.1033.910790185"
datafile 5 switched to datafile copy "+DATA/CDB/DATAFILE/undotbs1.1022.910790051"
datafile 6 switched to datafile copy "+DATA/CDB/DATAFILE/users.1039.910790207"
datafile 7 switched to datafile copy "+DATA/CDB/DATAFILE/audsys.1031.910790131"
datafile 8 switched to datafile copy "+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/system.1030.910790117"
datafile 9 switched to datafile copy "+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/sysaux.1028.910790103"
datafile 10 switched to datafile copy "+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_data.1017.910789855"
datafile 11 switched to datafile copy "+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_index.1027.910790095"
datafile 12 switched to datafile copy "+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/weekend.1019.910789977"
…. Text removed for clarity…

What we have now is a controlfile from the source CDB and an image copy of all the database files that have been registered in that controlfile. We are ready to pull the incremental changes from the source database in online mode.

7. Incremental updates

This step can be run as many times and as frequently as needed. It pulls just the changes from the source CDB database that haven’t been yet applied to the destination CDB database. In order to minimize downtime in the next step of relocation, you need to be as close with your destination DB to the source DB as possible. It’s good to run the last incremental backup just minutes before the planned downtime. Take into the considaration the change ratio on your database.

RMAN> recover database noredo from service CDB_SOURCE;

Starting recover at 04-APR-16
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_DISK_1
skipping datafile 2; already restored to SCN 8358119647615
skipping datafile 4; already restored to SCN 8358119647615
skipping datafile 6; already restored to SCN 8364362408993
skipping datafile 13; already restored to SCN 8364362408439
skipping datafile 19; already restored to SCN 8364362408420
skipping datafile 20; already restored to SCN 8364362408563
skipping datafile 21; already restored to SCN 8364362408706
skipping datafile 22; already restored to SCN 8364362408921
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CDB_SOURCE
destination for restore of datafile 00001: +DATA/CDB/DATAFILE/system.1026.910790089
channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_1: using network backup set from service CDB_SOURCE
destination for restore of datafile 00003: +DATA/CDB/DATAFILE/sysaux.1020.910790021
channel ORA_SBT_TAPE_2: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_2: using network backup set from service CDB_SOURCE
destination for restore of datafile 00005: +DATA/CDB/DATAFILE/undotbs1.1022.910790051
channel ORA_SBT_TAPE_3: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_3: using network backup set from service CDB_SOURCE
destination for restore of datafile 00007: +DATA/CDB/DATAFILE/audsys.1031.910790131
channel ORA_SBT_TAPE_4: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_4: using network backup set from service CDB_SOURCE
destination for restore of datafile 00008: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/system.1030.910790117
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CDB_SOURCE
destination for restore of datafile 00009: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/sysaux.1028.910790103
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:02
channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_1: using network backup set from service CDB_SOURCE
destination for restore of datafile 00010: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_data.1017.910789855
channel ORA_SBT_TAPE_3: restore complete, elapsed time: 00:00:02
channel ORA_SBT_TAPE_3: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_3: using network backup set from service CDB_SOURCE
destination for restore of datafile 00011: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_index.1027.910790095
channel ORA_SBT_TAPE_4: restore complete, elapsed time: 00:00:01
channel ORA_SBT_TAPE_4: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_4: using network backup set from service CDB_SOURCE
destination for restore of datafile 00012: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/weekend.1019.910789977
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CDB_SOURCE
destination for restore of datafile 00014: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/this_data.1018.910789921
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:02
channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_1: using network backup set from service CDB_SOURCE
destination for restore of datafile 00015: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/this_index.1024.910790075
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:00:01
channel ORA_SBT_TAPE_3: restore complete, elapsed time: 00:00:02
channel ORA_SBT_TAPE_4: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:04
Finished recover at 04-APR-16

8. Final phase

8.1 On ServerA shutdown the source db cleanly and start in mount.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  5291928 bytes
Variable Size            1191182440 bytes
Database Buffers         3087007744 bytes
Redo Buffers               11485184 bytes

Database mounted.

8.2 Recover database on serverB from the consistent mounted source
RMAN> recover database noredo from service CDB_SOURCE;

Starting recover at 04-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 7.1.0.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=337 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Data Protection for Oracle: version 7.1.0.0
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=386 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Data Protection for Oracle: version 7.1.0.0
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=434 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Data Protection for Oracle: version 7.1.0.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=482 device type=DISK
skipping datafile 2; already restored to SCN 8358119647615
skipping datafile 4; already restored to SCN 8358119647615
channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_1: using network backup set from service CDB_SOURCE
destination for restore of datafile 00001: +DATA/CDB/DATAFILE/system.1026.910790089
channel ORA_SBT_TAPE_2: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_2: using network backup set from service CDB_SOURCE
destination for restore of datafile 00003: +DATA/CDB/DATAFILE/sysaux.1020.910790021

... Text removed for simplicity....

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:02
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:00:04
channel ORA_SBT_TAPE_3: restore complete, elapsed time: 00:00:01
Finished recover at 04-APR-16

And one last time to be sure all is in sync now

RMAN> recover database noredo from service CDB_SOURCE;

Starting recover at 04-APR-16
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_DISK_1
skipping datafile 1; already restored to SCN 8364384345644
skipping datafile 2; already restored to SCN 8358119647615
skipping datafile 3; already restored to SCN 8364384345644
skipping datafile 4; already restored to SCN 8358119647615
skipping datafile 5; already restored to SCN 8364384345644
skipping datafile 6; already restored to SCN 8364384345644

... Text removed for simplicity....

skipping datafile 19; already restored to SCN 8364384345616
skipping datafile 20; already restored to SCN 8364384345616
skipping datafile 21; already restored to SCN 8364384345616
skipping datafile 22; already restored to SCN 8364384345616
skipping datafile 23; already restored to SCN 8364384345616
Finished recover at 04-APR-16

As you can see all the files have been skipped during the last call of the recovery.
They are all in sync with the source database.

8.3 Restart destination database in nomount – modify control_files parameter
SQL> alter system set control_files='+DATA','+FRA' scope=spfile;
System altered.

SQL> shutdown immediate;
[oracle@serverB ~]$ srvctl start database -d CDB -o nomount
8.4 Restore controlfile one last time from the source DB

The source database has been cleanly shutted down – the datafiles and controlfiles are consistent and in sync. The source datafiles’ state has been reflected in the destination database with incremental FROM SERVICE backups, but the controlfile is still out of sync. In this step we need to restore the controlfile from the source service. After this operation, destination database (controlfiles and datafiles) will be the exact copy of the source.

[oracle@serverB ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Wed Apr 4 14:40:54 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CDB (not mounted)

RMAN>  restore controlfile from service CDB_SOURCE;

Starting restore at 04-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=337 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service CDB_SOURCE
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/CDB/CONTROLFILE/current.1041.910968075
output file name=+FRA/CDB/CONTROLFILE/current.3379.910968077

Finished restore at 04-APR-16

RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1

8.5 Switch controlfile to copy of source on the destination server

The controlfile is in the correct place and so are the datafiles, but the controlfile is not aware of the datafiles because of the different file names comparing with the source. We need to help RMAN to find the datafiles and register them as the databases files.

RMAN> catalog start with '+DATA/CDB/';

Starting implicit crosscheck backup at 04-APR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=290 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 04-APR-16
Starting implicit crosscheck copy at 04-APR-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 04-APR-16
searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +FRA/CDB/CONTROLFILE/current.1309.910791755

searching for all files that match the pattern +DATA/CDB/
List of Files Unknown to the Database
=====================================
File Name: +DATA/CDB/spfileCDB.ora
File Name: +DATA/CDB/CONTROLFILE/current.1040.910791755
File Name: +DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/system.1032.910790171
File Name: +DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/sysaux.1033.910790185
File Name: +DATA/CDB/DATAFILE/sysaux.1020.910790021
File Name: +DATA/CDB/DATAFILE/undotbs1.1022.910790051
File Name: +DATA/CDB/DATAFILE/system.1026.910790089
File Name: +DATA/CDB/DATAFILE/audsys.1031.910790131
File Name: +DATA/CDB/DATAFILE/users.1039.910790207
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_data.1017.910789855
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/this_data.1018.910789921
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/weekend.1019.910789977

..... Text removed for better clarity...

File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/audsys.1038.910790205
File Name: +DATA/CDB/PASSWORD/orapwCDB

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +DATA/CDB/CONTROLFILE/current.1040.910791755
File Name: +DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/system.1032.910790171
File Name: +DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/sysaux.1033.910790185
File Name: +DATA/CDB/DATAFILE/sysaux.1020.910790021
File Name: +DATA/CDB/DATAFILE/undotbs1.1022.910790051
File Name: +DATA/CDB/DATAFILE/system.1026.910790089
File Name: +DATA/CDB/DATAFILE/audsys.1031.910790131
File Name: +DATA/CDB/DATAFILE/users.1039.910790207
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_data.1017.910789855
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/this_data.1018.910789921

..... Text removed for better clarity...

File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/weekend.1019.910789977
File Name: +DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/audsys.1038.910790205

List of Files Which Were Not Cataloged
=======================================
File Name: +DATA/CDB/spfileCDB.ora
RMAN-07518: Reason: Foreign database file DBID: 0  Database Name:
File Name: +DATA/CDB/PASSWORD/orapwCDB


RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/CDB/DATAFILE/system.1026.910790089"
datafile 2 switched to datafile copy "+DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/system.1032.910790171"
datafile 3 switched to datafile copy "+DATA/CDB/DATAFILE/sysaux.1020.910790021"
datafile 4 switched to datafile copy "+DATA/CDB/1C19D992B0EB1AA8E053233EEB0ADB2F/DATAFILE/sysaux.1033.910790185"
datafile 5 switched to datafile copy "+DATA/CDB/DATAFILE/undotbs1.1022.910790051"
datafile 6 switched to datafile copy "+DATA/CDB/DATAFILE/users.1039.910790207"
datafile 7 switched to datafile copy "+DATA/CDB/DATAFILE/audsys.1031.910790131"
datafile 8 switched to datafile copy "+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/system.1030.910790117"
datafile 9 switched to datafile copy "+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/sysaux.1028.910790103"
datafile 10 switched to datafile copy "+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_data.1017.910789855"
datafile 11 switched to datafile copy "+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/new_index.1027.910790095"
datafile 12 switched to datafile copy "+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/weekend.1019.910789977"

..... Text removed for better clarity...

datafile 22 switched to datafile copy "+DATA/CDB/1C1B7C0B27843F65E053233EEB0A11A1/DATAFILE/audsys.1038.910790205"

8.6 Recreate logfiles on the destination CDB

To avoid error messages in the alert.log during the ALTER DATABASE OPEN RESETLOGS operation it is a good habit to recreate the redolog files in advance.

RMAN> select group#,thread# from v$log;

 GROUP#    THREAD#  
---------------   -----------
         1           	1      
         4          	1          
         3          	1          
         2          	1 
      
RMAN> alter database clear logfile group 1;
Statement processed

RMAN> alter database clear logfile group 2;
Statement processed

RMAN> alter database clear logfile group 3;
Statement processed

RMAN> alter database clear logfile group 4;
Statement processed

8.7 Open the database

First I tried what would happen without the RESETLOGS clause

RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 05/04/2016 14:47:56
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file: '+DATA/CDB/CHANGETRACKING/ctf.741.897900847'
ORA-17502: ksfdcre:4 Failed to create file +DATA/CDB/CHANGETRACKING/ctf.741.897900847
ORA-15046: ASM file name '+DATA/CDB/CHANGETRACKING/ctf.741.897900847' is not in single-file creation form
ORA-17503: ksfdopn:2 Failed to open file +DATA/CDB/CHANGETRACKING/ctf.741.897900847
ORA-15012: ASM file '+DATA/CDB/CHANGETRACKING/ctf.741.897900847' does not exist

Wow, the famous BCTF bug that has been known since years and is to be fixed in 12.2.
Let’s disable the BCTF and try to open again

RMAN> alter database disable block change tracking;
Statement processed

RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 05/04/2016 14:48:13
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Ok, that was expected. Try RESETLOGS then

RMAN> alter database open resetlogs;
Statement processed

It works, database is open. Turn the BCT on at the end.

SQL> alter database enable block change tracking;
Database altered.

8.8 Check state of the PDBs
SQL> show pdbs

CON_ID CON_NAME                    OPEN MODE  RESTRICTED
---------- ---------------------- -------- --------------
         2   PDB$SEED             READ ONLY     NO
         3   PDB1                 MOUNTED

PDB1 is in a MOUNTED state, let’s open all the pdbs and save the state for future.

SQL> alter pluggable database all open instances=all;
Pluggable database altered.

SQL>alter pluggable database all save state instances=all;
Pluggable database altered.
This is it. The database has been successfully relocated to the new server.
If you have better or different ideas how to do it, please comment or send an email

About the author

 
maciej tokar
Maciej Tokar

An Oracle technology geek and crazy long distance runner, DBA24 Owner
Senior Oracle DBA / Consultant / [OCP10g, OCP12c, OCE RAC 10g] / [experience: 9y+]
Currently working for Bluegarden (Oslo Norway) by Miratech Group
Past: Mastercard / Trevica by Britenet, Citi International PLC, PZU

 
View Maciej Tokar's profile on LinkedIn         logoDB4
LinkedIn Auto Publish Powered By : XYZScripts.com