Oracle 11g R1 transportable database

I had read a lot about transportable database but up until today didn't get a chance to try it out. Well today in the morning I was having coffee with my colleagues when suddenly topic of transportable database came up and I thought of giving it a go. So here's what I had and what I did.

Resource:

Well I had a PC and a laptop both running Windows and the username in both was Koenig. I had oracle 11g R1 installed on both of the system.

Target:

To transport the orcl database from the PC to my laptop and rename it to MYDB

Process:
Step 1: Check if the operating system to which you want to migrate is compatible with the source system operating system..

SQL> select platform_name from v$db_transportable_platform;

PLATFORM_NAME

-------------------------------------------------------------------------------

Microsoft Windows IA (32-bit)

Linux IA (32-bit)

HP Tru64 UNIX

Linux IA (64-bit)

HP Open VMS

Microsoft Windows IA (64-bit)

Linux 64-bit for AMD

Microsoft Windows 64-bit for AMD

Solaris Operating System (x86)

HP IA Open VMS

Solaris Operating System (AMD64)

Since both my source and target are of Microsoft Windows IA (32-bit) hence I had little to worry about here.

Next I needed to identify the external tables, directories and BFILES that are part of the source database cause they need to be transported separately at a later time.
For that execute the following command:

SQL> set serveroutput on

SQL> declare x boolean;

  2  begin x := dbms_tdb.check_external; end;

  3  /

The following external tables exist in the database:

SH.SALES_TRANSACTIONS_EXT

The following directories exist in the database:

SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.MEDIA_DIR, SYS.IDR_DIR, SYS.SUBDIR,

SYS.XMLDIR, SYS.AUDIT_DIR, SYS.DATA_PUMP_DIR, SYS.ORACLE_OCM_CONFIG_DIR

The following BFILEs exist in the database:

PM.PRINT_MEDIA

PL/SQL procedure successfully completed.

Identify the directory objects in the source system (PC) and created them in the target system(laptop).
For identifying the directory objects on the source system execute the command:

SQL> select directory_path from dba_directories;

DIRECTORY_PATH

-------------------------------------------------------------------------------

c:\app\koenig\diag\rdbms\orcl\orcl\ir

C:\app\Koenig\product\11.1.0\db_1\demo\schema\order_entry\/2002/Sep

C:\app\Koenig\product\11.1.0\db_1\demo\schema\order_entry\

C:\app\Koenig\product\11.1.0\db_1\demo\schema\log\

C:\app\Koenig\product\11.1.0\db_1\demo\schema\sales_history\

C:\app\Koenig\product\11.1.0\db_1\demo\schema\product_media\

/tmp/

C:\app\Koenig\admin\orcl\dpdump\

C:\app\Koenig\product\11.1.0\db_1\ccr\state

9 rows selected.

Then I ensured that the folders existed in the target system (laptop). To my surprise most of the folders were already there. I just had to create the following folders:

C:\app\Koenig\product\11.1.0\db_1\demo\schema\product_media\ /tmp/

C:\app\Koenig\admin\orcl\dpdump\

C:\app\Koenig\product\11.1.0\db_1\ccr\state

 In order to identify the external tables execute the following sql query on the source (PC) system:

SQL> select directory_path || '\' || location External_file_path from dba_directories a, dba_external_locations b where a.directory_name=b.directory_name;

EXTERNAL_FILE_PATH

--------------------------------------------------------------------------------

C:\app\Koenig\product\11.1.0\db_1\demo\schema\sales_history\\sale1v3.dat

It is recommended to copy the files to the same location on the target system but since the file already existed on the target (laptop) system hence this was not needed.

Next step involved identifying which BFILE I need to migrate seperately. For this I used a script that I obtained from the net: and got the following output:

SQL> @"C:\Documents and Settings\Koenig\My Documents\tdb_get_bfile_dirs.sql"

The following directories contain external files for BFILE columns

Copy the files within these directories to the same path on the target system

C:\app\Koenig\product\11.1.0\db_1\demo\schema\product_media\

There are 1 directories, 4 total BFILEs

Since I couldn't locate the folder in the source system hence I didn't bother about copying them or transferring them to the laptop. (Will do R&D on product_media folder later).
P.S. I will post the tdb_get_bfile_dirs.sql script in another post but frankly it was of no use in this practice.

Before moving to the next step open the source database in read only mode as follows:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1334380 bytes

Variable Size             243270548 bytes

Database Buffers          285212672 bytes

Redo Buffers                5844992 bytes

Database mounted.

SQL> alter database open read only;

Next I needed to convert the database from source format to target format. Since the source and target were on the same operating system the only thing I needed to worry about was the database name. For this I executed the following RMAN operation on the source system:

C:\Documents and Settings\Koenig>rman

Recovery Manager: Release 11.1.0.6.0 - Production on Wed Nov 10 23:39:26 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN> connect target /

connected to target database: ORCL (DBID=1262344548)

RMAN> convert database

2> transport script 'c:\temp\transport_mydb.sql'

3> new database 'mydb'

4> to platform 'Microsoft Windows IA (32-bit)'

5> parallelism 4

6> format 'c:\temp\mydb'

7> db_file_name_convert 'C:\app\Koenig\oradata\orcl\','c:\stage\';

Starting conversion at source at 10-NOV-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=170 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=143 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=151 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=155 device type=DISK

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.IDR_DIR found in the database

Directory SYS.SUBDIR found in the database

Directory SYS.XMLDIR found in the database

Directory SYS.MEDIA_DIR found in the database

Directory SYS.LOG_FILE_DIR found in the database

Directory SYS.DATA_FILE_DIR found in the database

Directory SYS.AUDIT_DIR found in the database

Directory SYS.DATA_PUMP_DIR found in the database

Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00003 name=C:\APP\KOENIG\ORADATA\ORCL\UNDOTBS01.DBF

channel ORA_DISK_2: starting datafile conversion

input datafile file number=00001 name=C:\APP\KOENIG\ORADATA\ORCL\SYSTEM01.DBF

channel ORA_DISK_3: starting datafile conversion

input datafile file number=00002 name=C:\APP\KOENIG\ORADATA\ORCL\SYSAUX01.DBF

channel ORA_DISK_4: starting datafile conversion

input datafile file number=00005 name=C:\APP\KOENIG\ORADATA\ORCL\EXAMPLE01.DBF

converted datafile=C:\STAGE\UNDOTBS01.DBF

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:13

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00004 name=C:\APP\KOENIG\ORADATA\ORCL\USERS01.DBF

converted datafile=C:\STAGE\USERS01.DBF

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15

converted datafile=C:\STAGE\EXAMPLE01.DBF

channel ORA_DISK_4: datafile conversion complete, elapsed time: 00:00:34

converted datafile=C:\STAGE\SYSTEM01.DBF

channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:01:23

converted datafile=C:\STAGE\SYSAUX01.DBF

channel ORA_DISK_3: datafile conversion complete, elapsed time: 00:01:20

Edit init.ora file C:\TEMP\INIT_MYDB.ORA. This PFILE will be used to create the

database on the target platform

Run SQL script C:\TEMP\TRANSPORT_MYDB.SQL on the target platform to create datab

ase

To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target plat

form

To change the internal database identifier, use DBNEWID Utility

Finished conversion at source at 10-NOV-10.

Since we are doing this on a windows to windows transport hence in the target system create an instance:

c:\> oradim --new -sid mydb

Then I copied the generated pfile from Temp folder of source to c:\app\Koenig\admin\MYDB\pfile' folder of target.

Next I made the following changes to the INIT_MYDB.ora:

control_files="c:\app\Koenig\oradata\MYDB\control01.ctl"

db_recovery_file_dest="c:\app\Koenig\flash_recovery_area"

audit_file_dest="c:\app\Koenig\admin\MYDB\adump"

Then I copied the TRANSPORT_MYDB.SQL file to a desired location and made the following changes:
In the Logfile section:
LOGFILE

GROUP 1 'C:\app\Koenig\oradata\log1' size 50M,

GROUP 2 'C:\app\Koenig\oradata\log2' size 50M,

GROUP 3 'C:\app\Koenig\oradata\log3' size 50M,

Finally on the target system I connected as the sysdba to the mydb instance:

c:\> set ORACLE_SID=mydb

c:\> sqlplus / as sysdba

and executed the copied TRANSPORT_MYDB.SQL script

SQL> @"C:\TRANSPORT_MYDB.SQL"

My database was created and ready.

Cheers!!!

For Reference:

http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-platformmigrationtdb-131164.pdf

Types of Replication Methids

There are two methods by which we categorize replication:

1. Commit Synchronization Replication
2. Directional Replication

Commit Synchronization Replication:

 This determines the way in which the changes are implemented on the Source and Target site. It can be either:

1. Synchronous Replication flow
2. Asynchronous Replication flow

In the case of Synchronous Replication Flow the changes  are implemented on the source side only after they have been implemented on all the other sites. In case the changes fail to be applied in any of the targets then the changes are rolled back on all the nodes including the source.





Asynchronous Replication means that the changes are applied on the source irrespective of whether they are successfully implemented on the target side on or not. If the changes are not successful in any of the target sites then deferred commit is implemented i.e. the logs for applying the changes to the failed site are generated and maintained at the source site for applying them when the failed site becomes available.

Note: Synchronous and Asynchronous replication are mutually exclusive.

Directional Flow:

It specifies the direction in which the changes are applied. There are basically two types:

1. Unidirectional flow
2. Bidirectional flow

The Unidirectional flow generally takes place between a Master Node and a Client Node. Hence generally changes are not allowed in the target side. If changes are allowed in the target side then there might be inconsistency and might need conflict resolution implemented to allow future replication.

The Birectional Flow generally takes place between Master-Master nodes and if the transmission is of type asynchronous then it might cause data inconsistency.




Note: Unidirectional and Bidirectional flow are mutually exclusive.

Oracle 10g Clusterware Daemons

First and foremost Oracle Clusterware is one of the several clusterwares that are available in the market.
In 10.1 it was known as Cluster Ready Service or CRS.
In 10.2 it was renamed Clusterware.


Oracle Clusterware consist of three primary daemons. They are:
1. Cluster Ready Service Daemon
2. Cluster Synchronization Daemon
3. EVent Monitor Daemon


Before understanding the function of these daemons we need to know the two main components of 10g clusterware. These are:
1. Oracle Cluster Register Disk
2. Voting Disk.


The OCR disk stores the OCR file, which contains configuration information about the clusterware.In simple words all the information related to the status of the various applications running on the cluster gets stored in the OCR. These applications can be listeners, Instances, Databases, Services etc. 
Similarly node membership information is stored in a quorom file in the voting disk.
Both the OCR and Voting Disk needs to be stored in the shared storage.


Now that we know what is the purpose of OCR let us learn about the EVMD and the CRSD. The EVMD regularly monitors the status of the applications at the instance level and checks against their corresponding last known status in the OCR in case there is a change in the status of the application in the instance as compared to the status recoded in the OCR then the EVMD will inform the CRSD process about the same and the CRSD will make the corresponding modification in the OCR. The CRSD process of the remote instance will pick up this change and will take the appropriate corrective action. This is known as FAN or fast application notification.

The CSSD on the other hand is concerned with the node membership notification. Let us assume that we have a two node cluster. The CSSD of each node at regular interval will inform each other of their existence and also the Voting Disk with the same information.

Now assume that one of the nodes (Node 2) goes down. Hence the CSSD of Node 2 won't be sending any update to the CSSD of Node 1 or the voting disk. When the CSSD of Node 1 realizes that it is not receiving any information from Node 2 then it will check the Voting Disk to figure out when the last update was made by Node 2. When it identifies that the update was before a certain threshold time interval it will consider that the Node 2 is no longer active and will evict the node from the cluster and make the corresponding update in the voting disk.

Creating a Physical standby database using 11g R1 in Windows

In this example I am trying to create a 11g dataguard standby database (pc01sby)  from my primary database (pc01prmy).

I have installed Oracle 11g R1 on both the nodes.

1. On the primary database pc01prmy enable force logging:
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> SELECT force_logging FROM v$database;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> SELECT force_logging FROM v$database;

2. Enable Archiving in the primary database:
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST;

3. Create a static entry for the primary database (pc01prmy) in the listener.ora:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pc01prmy)
      (ORACLE_HOME = C:\app\Administrator\product\11.1.0\db_1)
      (SID_NAME = pc01prmy)
    )
)
4. Create a service for the standby database (pc01sby):
C:\> oradim -new -sid pc01sby

5. Create a static entry for the standby database in listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pc01sby)
      (ORACLE_HOME = C:\app\Administrator\product\11.1.0\db_1)
      (SID_NAME = pc01sby)
    )
  )
If you are creating the standby database in a remote system ensure not to make any static entry for the primary database. Just add the static entry show above in step 5.
 
6. Create a service named after your standby database (pc01sby) using the netmanager or adding the following entry in tnsnames.ora of the primary node:

PC01SBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lab40)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pc01sby)
    )
  )
 
7. Add a few standby logfiles for your primary database:
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'C:\app\Administrator\oradata\pc01prmy\srl01.log' size 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'C:\app\Administrator\oradata\pc02prmy\srl01.log' size 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'C:\app\Administrator\oradata\pc03prmy\srl01.log' size 52428800;

8. Set proper value for log_archive_config and log_archive_dest_1
C:\> SET ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER SYSTEM SET log_archive_config = 'dg_config=(pc01prmy, pc01sby)';
SQL> ALTER SYSTEM SET log_archive_dest_2 = 'service=pc01sby async valid_for=(online_logfile, primary_role) db_unique_name = pc01sby';

9. Still connected to your primary database increase the size of FRA:
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=5G;

10. Copy the password file from your primary database node (eg: PWDpc01prmy.ora) in 'c:\app\Administrator\product\11.1.0\db_1\database' to the same location in your standby database node and rename it for your standby database (eg: PWDpc01sby.ora)

11. Create a pfile in the standby node for the standby database in 'c:\app\Administrator\product\11.1.0\db_1\database' and name it initpc01stby.ora




12. Edit the initpc01sby.ora and make the following entry:
DB_NAME=pc01sby
If you have an option of creating the dataguard configuration between two separate nodes then remember to keep the same db_name in the initialization parameter file of both the  nodes but different names of the db_unique_name. Just a convention but not mandatory.
13. Create the following folders for the standby database in the standby node:
C:\> md C:\app\Administrator\admin\pc01sby
C:\> md C:\app\Administrator\admin\pc01sby\adump
C:\> md C:\app\Administrator\oradata\pc01sby

14. Startup the database in nomount state using the pfile that you created in step 12:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> startup nomount pfile = C:\app\Administrator\product\11.1.0\db_1\database\initpc01sby.ora


15. Restart the listener on both the nodes:
C:\> lsnrctl
LSNRCTL> stop
LSNRCTL> start
LSNRCTL> status

16. In the primary database node use rman to connect to the primary database and standby database:
C:\> rman
RMAN> CONNECT TARGET sys/oracle@pc01prmy
RMAN> CONNECT AUXILIARY sys/oracle@pc01sby

Note: If you didn't copy the exact passwordfile from the primary to the standby it will give you the following error:

RMAN> connect auxiliary sys/qfund@pc01sby
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password;

17. Still connected to the RMAN execute the following script:
RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'pc01prmy','pc01sby'
set db_unique_name='pc01sby'
set db_file_name_convert='\pc01prmy\','\pc01sby\'
set log_file_name_convert='\pc01prmy\','\pc01sby\'
set control_files='C:\app\Administrator\oradata\pc01sby\pc01sby.ctl'
set log_archive_max_processes='5'
set fal_client='pc01sby'
set fal_server='pc01prmy'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(pc01prmy,pc01sby)'
set log_archive_dest_2 = 'service=pc01prmy ASYNC
valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=pc01prmy'
;
}

18. Exit from RMAN and connect to you primary database and perform a logswitch:
RMAN> exit
C:\> set ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER DATABASE SWITCH LOGFILE;

19. Open a separate command prompt and connect to your standby database and test if you can recover the standby using the newly created logfile:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

20. On the standby verify that archive logs were received by executing a few log switch on the primary and verifying them on standby:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> SELECT sequence#, first_time,next_time FROM v$archived_log ORDER BY sequence#;
SQL> exit
C:\> set ORACLE_SID=pc01prmy
C:\> sqlplus / as sysdba
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> exit
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> SELECT sequence#, first_time,next_time from v$archived_log order by sequence#;
SQL> exit


21. You can verify if a log was applied or not by executing:
C:\> set ORACLE_SID=pc01sby
C:\> sqlplus / as sysdba
SQL> SELECT sequence#, applied FROM v$archived_log ORDER BY sequence#;
SQL> exit

For more details visit:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/create_ps.htm