Transportable Tablespace (User Managed)

Performing User-Managed TSPITR with Transportable Tablespaces

Source Database:

1. Connect SQL*Plus to the auxiliary database with administrator privileges. For example:

% sqlplus 'SYS/oracle@aux AS SYSDBA'

2. Make the tablespaces in the recovery set read-only by running the ALTER TABLESPACE ... READ ONLY statement. For example, make users and tools read-only as follows:

ALTER TABLESPACE users READ ONLY;

3. Ensure that the recovery set is self-contained. For example:

EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('users',TRUE);

4. Query the transportable tablespace violations table to manage any dependencies. For example:

SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;

This query should return no rows after all dependencies are managed. Refer to Oracle Database Administrator's Guide for more information about this table.

5. Generate the transportable set by running the Export utility as described in Oracle Database Administrator's Guide. Include all tablespaces in the recovery set, as in the following example:

% exp SYS/oracle TRANSPORT_TABLESPACE=y TABLESPACES=(users) TTS_FULL_CHECK=y file=C:\users.dmp

Destination Database:

In this step, you transport the recovery set tablespaces into the primary database.

1. Connect SQL*Plus to the primary database (not the auxiliary database). For example:

% sqlplus 'SYS/oracle@primary AS SYSDBA'

2. Drop the tablespaces in the recovery set with the DROP TABLESPACE statement. For example:

DROP TABLESPACE users INCLUDING CONTENTS;

3. Restore the recovery set datafiles from the auxiliary database to the recovery set file locations in the primary database. For example:

% copy C:\orcl\data\users01.dbf \

> D:\orcl\data\users01.dbf

4. Move the export file users.dmp to the primary host. For example, enter:

% copy C:\users.dmp \

> C:\users.dmp

5. Plug in the transportable set into the primary database by running Import as described in Oracle Database Administrator's Guide. For example:

% imp SYS/oracle TRANSPORT_TABLESPACE=y FILE=C:\users.dmp

DATAFILES=('D:\orcl\data\users01.dbf')

6. Make the recovered tablespaces read/write by executing the ALTER TABLESPACE READ WRITE statement. For example:

ALTER TABLESPACE users READ WRITE;

Data Guard

How to configure Logical Standby Database:

Database Version: Oracle 10.2.0.3.0

Operating System: Windows 2000

DB Mode: Archive log.

1. Pre-Request:

Before create logical standby database you must ensure primary database is properly configured or not.

A) Determine supported data types and objects

Before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database.

Unsupported data types in logical standby database:

BFILE
ROWID, UROWID
User-defined types
Collections (including VARRAYS and nested tables)
XML type
Encrypted columns
Multimedia data types (including Spatial, Image, and Context)

Unsupported PL/SQL packages.

Oracle supplied pl/sql packaged that modify system metadata typically not supported by SQL apply.

Example:

DBMS_JAVA, DBMS_REGISTRY, DBMS_SPACE, DBMS_REFRESH, DBMS_REDEFINATION, DBMS_SCHEDULER and DBMS_AQ

Unsupported TABLES, VIEWS and SEQUENCES

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED;

Find out the following schema skipping in logical standby database:

SELECT OWNER FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT = 'INTERNAL SCHEMA';

B) Ensure table’s rows in the primary key can be uniquely identified.

select owner,table_name from dba_logstdby_not_unique where (owner,table_name) not in ( select distinct owner,table_name from dba_logstdby_unsupported ) and bad_column='Y';

Create Physical standby database

A. Enable Force logging

In Primary database we want to enable FORCE LOGGING after database creation.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

PS: This statement can take a considerable amount of time to complete because it wall all in unlogged direct write in I/O to finish.

B. Create Password file in Primary database

Create password in your primary database. If already exist no problem if no create password file (using orapwd). Every Data Guard configuration must be need password file.

C. Configure standby redo log

D. Primary database initialization parameters.

In Primary database, we should be defining some initialization

Parameters, this parameters that control redo transport services while the database is primary role.

For Example:

Here I am using the below diagram.

Database	DB_UNIQUE_NAME	Oracle Net Service Name
Primary	WASIM	wasim.samiindia
Physical standby	BABU	babu.samiindia

Primary Database Initialization parameter:

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string wasim

SQL> show parameter db_unique_name

NAME TYPE VALUE

------------------------------------ ----------- -------------db_unique_name string wasim

SQL> show parameter service_names

NAME TYPE VALUE

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

service_names string wasim.samiindia

SQL> alter system set log_archive_config = 'dg_config = (wasim,babu)' scope=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1=

2 'LOCATION=E:\oracle\OraBak\Archive

3 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

4 DB_UNIQUE_NAME=wasim';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2=

2 'SERVICE=babu LGWR ASYNC

3 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

4* DB_UNIQUE_NAME=babu';

System altered.

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system set remote_login_passwordfile = exclusive scope=spfile;

System altered.

SQL>alter system set log_archive_max_processes=30 scope=spfile;

System altered.

Primary Initialization parameters for Standby database:

SQL> alter system set fal_server = babu ;

System altered.

SQL> alter system set fal_client = wasim ;

System altered.

SQL> alter system set db_file_name_convert =

'E:\oracle\product\10.2.0\oradata\wasim', 'E:\oracle\data\babu' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert =

‘E:\oracle\product\10.2.0\oradata\wasim\', ‘E:\oracle\data\babu\' scope=spfile;

System altered.

SQL> alter system set standby_file_management = auto scope=spfile;

System altered.

E. Physical Standby database configuration steps.

1. Take control file, primary database backups (only datafiles)

C:\>set oracle_sid=wasim

C:\>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Mar 11 04:00:44 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

Sys@Wasim.Samiindia> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1289508 bytes

Variable Size 130024156 bytes

Database Buffers 33554432 bytes

Redo Buffers 2904064 bytes

Database mounted.

SQL> alter database create standby controlfile as 'C:\babu.ctl';

Database altered.

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2. Prepare initialization parameters for standby database.

This step we need to create standby initialization parameters using primary database (init.ora).

babu.__db_cache_size=88080384

babu.__java_pool_size=4194304

babu.__large_pool_size=4194304

babu.__shared_pool_size=62914560

babu.__streams_pool_size=0

*.archive_lag_target=1800

*.audit_file_dest='d:\oracle\product\10.2.0\/admin/babu/adump'

*.background_dump_dest='d:\oracle\product\10.2.0\/admin/babu/bdump'

*.compatible='10.2.0.3.0'

*.control_files='e:\oracle\data\babu\control01.ctl'

*.core_dump_dest='d:\oracle\product\10.2.0\/admin/babu/cdump'

*.db_block_size=8192

*.db_domain='samiindia'

*.db_file_multiblock_read_count=16

*.db_file_name_convert='E:\oracle\product\10.2.0\oradata\wasim','E:\oracle\data\babu'

*.db_name='wasim'

*.db_unique_name='babu'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=wasimXDB)'

*.fal_client='babu'

*.fal_server='wasim'

*.global_names=TRUE

*.job_queue_processes=10

*.log_archive_config='dg_config = (wasim,babu)'

*.log_archive_dest=''

*.log_archive_dest_1='LOCATION=E:\oracle\OraBak\Archive

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=babu'

*.log_archive_dest_2='SERVICE=wasim LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=wasim'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=30

*.log_file_name_convert='E:\oracle\product\10.2.0\oradata\wasim','E:\oracle\data\babu'

*.open_cursors=300

*.parallel_max_servers=20

*.parallel_min_servers=0

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.service_names='babu.samiindia'

*.sga_target=167772160

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='d:\oracle\product\10.2.0\/admin/babu/udump'

And need networking configuration both primary and standby database files system.

In TnsNames.ORA (Both Primary and Standby database)

BABU =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY MACHINE)(PORT =

1521)

)

)

(CONNECT_DATA =

(SERVICE_NAME = babu.samiindia)

)

)

WASIM =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY MACHINE)(PORT =

1521)

)

)

(CONNECT_DATA =

(SERVICE_NAME = wasim.samiindia)

)

)

In SQLNET.ORA (Standby database)

Add the below line to broken connection detection on primary file system.

SQLNET.EXPIRE_TIME=2

3. Copy files from primary database to standby database.

In primary database, copy all the datafiles to standby machine. (Primary database already down)

Sys@Wasim.Samiindia> select file_name from dba_data_files;

FILE_NAME

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

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\USERS01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\SYSAUX01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\UNDOTBS01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\SYSTEM01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\EDK01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\EXAMPLE01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\BABU01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\RMAN_TS.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\MIS01.DBF

4. Create standby database environment.

a) Create password file for standby database.

b) Create standby instance using oradim command.

               oradim -NEW -SID babu -INTPWD password -STARTMODE manual

c) I hope already created OFA.

5. Start the physical standby database.

C:\>set oracle_sid=babu

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Mar 11 04:28:02 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

            Connected to an idle instance.

SQL> create pfile from spfile;

File created.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1289484 bytes

Variable Size 75498228 bytes

Database Buffers 83886080 bytes

Redo Buffers 7098368 bytes

Database mounted.

SQL> alter database recover managed standby database disconnect from session;

This statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session.

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

6. Verification primary and standby database.

Logical Standby database Configuration

Before configuring logical standby database we need to configure physical standby database.

1. Pre-Request condition for creating logical standby database.

Before creating logical standby database we must ensure primary database properly configured of not. Determine supported data type, PL/SQL Packages and ensure tables rows in uniquely identified in primary database.

2. Enable Supplemental Logging.

Supplemental logging on the primary database generates additional information in the redo log. That info. Is then used during the redo application process in the standby database to make sure the correct rows are affected by the generated SQL.

We need to enable supplemental logging in primary and physical standby database.

In primary database:

System@Wasim.Samiindia> alter database add supplemental log data

2 (all) columns;

Database altered.

In Standby database:

SQL> alter database add supplemental log data

2 (all) columns;

Database altered.

3. Stop Redo apply in physical standby database.

You can run redo apply in physical standby database for any length of time It to a logical standby database. How ever before converting to logical standby database we need to stop redo apply on the physical standby database.

On Physical standby database.

SQL> alter database recover managed standby database cancel;

Database altered.

4. Prepare primary database to support a logical standby database.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3

'LOCATION=E:\oracle\orabak2\wasim\archive VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=WASIM';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3 = ENABLE;

System altered.

SQL> alter system set undo_retention=3600 scope=spfile;

System altered.

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

PL/SQL procedure successfully completed.

5. Logical standby database configuration

This steps need to configure database using with out NID command. Using

SQL> alter database recover to logical standby babu;

Database altered.

If you using SPIFLE just execute the above command. If you using PFILE edit your parameter file and try to re-open your database.

Alert log:

*** DBNEWID utility started ***

DBID will be changed from 2126122585 to new DBID of 48127288 for database WASIM

DBNAME will be changed from WASIM to new DBNAME of BABU

Starting datafile conversion

Setting recovery target incarnation to 1

Datafile conversion complete

Database name changed to BABU.

Modify parameter file and generate a new password file before restarting.

Database ID for database BABU changed to 48127288.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open with RESETLOGS option.

Succesfully changed database name and ID.

*** DBNEWID utility finished succesfully ***

Completed: alter database recover to logical standby babu

6. New Password file

In logical standby database, we must re-create password file because

Database name changed.

7. Configure initialization parameter in logical standby database.

This steps logical database configuration steps. Before starting this steps need to re-start (in Mount stage only) your logical standy database.

log_archive_dest_1='location=E:\oracle\Orabak\archive valid_for=(All_logfiles,all_roles) db_unique_name=babu'

log_archive_dest_2='SERVICE=WASIM LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=WASIM'

log_archive_dest_3='location=E:\Oracle\Orabak\archive2 valid_for=(standby_logfiles,standby_role) db_unique_name=babu'

log_archive_dest_state_1='ENABLE'

log_archive_dest_state_2='ENABLE'

log_archive_dest_state_3='ENABLE'

8. Open your standby database.

The new database is logically the same as your primary database, but it is transactionally inconsistent with the primary database, and thus incompatible for recovery operations.

To open the new logical standby database, you must open it with the RESETLOGS option by issuing the following statement:

               SQL> ALTER DATABASE OPEN RESETLOGS;

Because this is the first time the database is being opened, the database's global name is adjusted automatically to match the new DB_NAME initialization parameter.

Issue the following statement to begin applying redoes data to the logical standby database. For example:

               SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;