Oracle Database Duplication

I’ve noticed the conversion rate on my muskie hookups has not been good this year. I have hooked at least four muskies this year and have only landed one of them. The other three were hooked for a couple seconds at most, and all three threw the lure while jumping out of the water. Hooking up with a muskie is an exciting moment, and it can be tough to remember everything you need to do when it happens. In my case, I think I haven’t really been giving a good hookset. For some reason when using a long, stiff muskie rod my first instinct is not to give a big, sweeping hookset – but that is exactly what is needed. For the rest of this year’s fishing season, I’m going to really focus on giving fierce hooksets whenever I feel a bite. The important thing to remember is that practice makes perfect.

Me on the lake
I’m mainly a SQL Server DBA. I learned to be a DBA on SQL Server, so all their standards and methods make perfect sense to me. Recently, I had to roll out a test version of our Oracle server to a new server. I’ve had some Oracle training, and I also get to do Oracle tasks about every three months at work, but I don’t work with it frequently enough to really learn what I am doing. In this instance, I was able to figure out the correct steps, but if I don’t practice it, it won’t stick. If I have to try again in a year without any practice in between, it’ll go smoother than this time (my first time), but I’ll still have to hobble my way through it.
Before I go through the steps, I want to point out two areas of frustration on this process. The first is how unbelievably complicated this process is compared to how simple it is when using SQL Server. Restoring individual databases between instances in SQL Server couldn’t be any simpler – it’s basically just plug and play. Restore a backup or attach a copy of the data files and you’re good to go. In the case of trying to make a copy of an entire instance, you’d also need to restore the master, msdb, and possibly the model system databases, which is a bit more complicated, but nothing like trying to get this to run with an Oracle database. Secondly, all of Oracle’s documentation is centered around non-Windows operating systems (particluarly Linux). I realize that these operating systems are used much more commonly with the Oracle database, but when the Oracle support community’s usually response to help requests is a link to a big, long page of documentation, that documentation had better give me the info I need no matter what OS I’m using. For example, Oracle’s documentation on database duplication neglects to list an important step for Windows users – creating the auxiliary database’s service! This may be common knowledge to many full time Oracle DBAs, but for us part timers it isn’t. It’s hard to want to search through the documentation for answers instead of using Google when the documentation is missing key steps.

Following are the steps I took to duplicate the database to the new server. Make sure you are trying to duplicate to the same version! I first tried a 11.2.0.3 to 11.2.0.1 duplication and it failed causing me to have to start all over.

Take a full backup of the database you wish to copy.  I did my backup to a shared folder that was accessible to both servers.  Make sure to include a backup of your control file.

All the rest of the steps are done on the server with the auxiliary database.

Create a password file on the auxiliary server.  Put the password file in the ORACLE_HOME/dbs folder.

C:\orapwd file=D:\app\oracle\product\11.2.0\db_1\dbs\pwdtestsid.ora password=syspass entries=10

Make sure to make the password the same as the sys password on your target database.

I first attempted to do a duplication by connecting to the target database, but was not able to make that work so I used the database backup instead.  Since I was trying to connect directly to the target from the auxiliary, had to create an entry in TNSNames.ora:

# Added to the tnsnames.ora
TARGETDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hos.domain.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DB11G)
    )
  )

Create a pfile for the auxiliary database. This will usually only need to have the name of the auxiliary database. I also needed to include some parameters for changing the file locations since the auxiliary server had different drive letters than the target server.

db_name='testsid'

# Convert file names to allow for different directory structure if necessary.
DB_FILE_NAME_CONVERT='C:\APP\ORACLE\ORADATA\TARGETDB\','F:\Oracle_DB_Files\testsid'
LOG_FILE_NAME_CONVERT='D:\APP\ORACLE\ORADATA\TARGETDB\','F:\Oracle_Log_Files\testsid'

Now here’s the part that Oracle’s documentation leaves out. Create a service for the auxiliary database by using oradim:

oradim –NEW –SID testsid -INTPWD syspass

Again, use your target’s sys password. If it already isn’t started, start the server’s listener, then add the auxiliary instance to the listener.ora file. Reload the listener so it can point connections to your auxiliary database.

lsnrctl
start
reload

Here’s what the listener.ora file should look like:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = testsid)
      (ORACLE_HOME = E:\oracle\product\11.2.0\dbhome_1)
	  (GLOBAL_DBNAME = testsid)
  )
 )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hos.domain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Connect to the idle, empty auxiliary database with SQLPlus. You may have to start or restart the service through the windows administration control to be able to connect. Once connected, shutdown immediate and then startup in nomount mode.

SQLPlus /nolog
Connect sys/syspass@testsid as sysdba;
Shutdown Immediate;
Startup Nomount;

The idea is to get the auxiliary database started in nomount mode. Next go into rman to run the actual duplication.

rman
connect auxiliary sys/syspass@testsid
RUN
{
SET NEWNAME FOR DATAFILE 1 TO 'F:\Oracle_DB_Files\testsid\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'F:\Oracle_DB_Files\testsid\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'F:\Oracle_DB_Files\testsid\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'F:\Oracle_DB_Files\testsid\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'F:\Oracle_DB_Files\testsid\AppFile.DBF';
DUPLICATE TARGET DATABASE TO testsid
BACKUP LOCATION '\\NAS\OracleSQLBackups\TargetDB\TestLoad'
SPFILE PARAMETER_VALUE_CONVERT 'c:\app', 'e:'
SET DB_RECOVERY_FILE_DEST='E:\Oracle\fast_recovery_area\testsid'
SET CONTROL_FILES='E:\Oracle\oradata\testsid\control01.ctl','F:\Oracle_DB_Files\control02.ctl'
LOGFILE
GROUP 1 ('E:\RedoLogs\Redo01.log') SIZE 1 G REUSE,
GROUP 2 ('E:\RedoLogs\Redo02.log') SIZE 1 G REUSE,
GROUP 3 ('E:\RedoLogs\Redo03.log') SIZE 1 G REUSE;
}

For me, it took well over an hour to finish duplicating the 150 GB database to the second server. Once finished, I had a complete duplicate of my target database into the auxiliary.

Advertisements

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