Adding LOB Objects in Repliation

How to Add LOB Objects in Replication site.

If you want add lob objects in replication site. (You must be already done Advanced Replication “Click here “)

That objects must be contains primary key constraints.

In scott@US.edkal

SQL> DESC MASTER

Name Null? Type

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

COMPANY_CODE VARCHAR2(10)

COMPANY_NAME NOT NULL VARCHAR2(100)

COMPANYLOGO BLOB

SHORT_NAME NOT NULL VARCHAR2(10)

PRINT_NAME NOT NULL VARCHAR2(100)

In repadmin@US.edkal

Here you need temporary stop master site activity and add those objects in master site.

SQL> BEGIN

2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (

3 gname => 'us_rep'

4 );

5 END;

6 /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'us_rep',

4 sname => 'SCOTT',

5 oname => 'MASTER',

6 type => 'TABLE',

7 copy_rows => TRUE,

8 use_existing_object => TRUE );

9 END;

10 /

PL/SQL procedure successfully completed.

SQL> ED

Wrote file afiedt.buf

1 BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'us_rep',

4 sname => 'SCOTT',

5 oname => 'MASTER_PK',

6 type => 'INDEX',

7 copy_rows => TRUE,

8 use_existing_object => TRUE );

9* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> CREATE MATERIALIZED VIEW LOG ON SCOTT.MASTER;

Materialized view log created.

SQL> BEGIN

2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

3 sname => 'SCOTT',

4 oname => 'MASTER',

5 type => 'TABLE',

6 min_communication => true,

7 generate_80_compatible => FALSE);

8* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (

3 gname => 'us_rep'

4 );

5 END;

6 /

PL/SQL procedure successfully completed.

Once complete start your master activity.

Create materialized view’s in your replication site.

In scott@mv01.edkal

SQL> CREATE MATERIALIZED VIEW MASTER REFRESH FAST WITH PRIMARY KEY UPDATABLE AS SELECT * FROM SCOTT.MASTER@US.EDKAL;

Materialized view created.

In mviewadmin@mv01.edkal

SQL> BEGIN

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(

gname => '"US_REP"',

sname => '"SCOTT"',

oname => '"MASTER"',

type => 'SNAPSHOT',

min_communication => TRUE);

END;

PL/SQL procedure successfully completed.

SQL> BEGIN

DBMS_REFRESH.ADD(

name => '"MVIEWADMIN"."US_REP"',

list => '"SCOTT"."MASTER"',

lax => TRUE);

END;

PL/SQL procedure successfully completed.

ORA-01788

Oracle Server - Enterprise Edition - Version: 10.2.0.3
This problem can occur on any platform.
After upgrading to Oracle 10g, started getting an ORA-1788 error for a query that includes the LEVEL pseudo column. This query was running fine on 9i.

Example:

On 9i:

SQL> select level from dual;

LEVEL
----------
0

On 10g:

SQL> select level from dual;
select level from dual
*
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block

Solution:

SQL> alter system set "_allow_level_without_connect_by"=true scope=spfile;

or if using a pfile add the line

_allow_level_without_connect_by=true

at the beginning of the pfile.


Both cases will require a restart of the database to take effect.

Replicaton Tuning Tool

The primary tools used to perform the FAST REFRESH analysis were:

1. Oracle provided DBMS_MVIEW.explain_mview procedure
2. MV_CAPABILITIES_TABLE table

The Oracle provided DBMS_MVIEW.explain_mview procedure was used to analyze each of the existing materialized views FAST REFRESH capabilities and write the results to the MV_CAPABILITIES_TABLE:

1. Lists the general capabilities of materialized view or materialized view query
2. Specifies whether each capability is possible (Y or N)
3. If a capability is not possible the reason (why) is written to the msgtxt column of the MV_CAPABILITIES_TABLE

The MV_CAPABILITIES TABLE is created in the owner’s schema by running the following Oracle 9i script:

SQL> @ $ORACLE_HOME/rdbms/admin/utlxmv.sql

Example:

After creating utlxmv.sql script execute the below command.

SQL> exec dbms_mview.explain_mview( 'GENERALISSUEARNO');


SQL> SELECT CAPABILITY_NAME,POSSIBLE,SUBSTR(MSGTXT,1,60) MSGTXT FROM MV_CAPABILITIES_TABLE WHERE CAPABILITY_NAME LIKE '%FAST%';

CAPABILITY_NAME P MSGTXT
------------------------------ - ------------------------------------------------------------
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater
REFRESH_FAST_PCT N the materialized view is upgraded from a previous version th

6 rows selected.

Upgrade Oracle 10.2.0.2 to 10.2.0.3

1. Patch Set Overview

Patch set release 10.2.0.3. Before installing this patch set you must be need 10.2.0.1 version.

2. Requirements

Oracle DB : Oracle 10.2.0.1 (later)
Operating System: Windows 2000 (Service Pack 2 or higher)

3. Pre - Installation Tasks

1. Identify prior installation

Before installing this patch you must install oracle 10.2.0.1 (or later version)

2. Download Patch set

Download and extract the p5337014_10203_WINNT.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.

3. Shutdown oracle database.

C:\>set oracle_sid=DEVDB

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Nov 13 10:49:26 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys@DEVDB as sysdba
Enter password:
Connected.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

4. Stop all services

C:\>set oracle_sid=DEVDB

C:\>net stop OracleDBConsoleDEVDB
The OracleDBConsoleDEVDB service is stopping................
The OracleDBConsoleDEVDB service was stopped successfully.


C:\>net stop OracleServiceDEVDB
The OracleServiceDEVDB service is stopping.
The OracleServiceDEVDB service was stopped successfully.


C:\>net stop OracleOraDB10g_Home1iSQL*Plus..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

C:\>net stop OracleOraDB10g_Home1TNSListener
The OracleOraDb10g_home1TNSListener service is stopping.
The OracleOraDb10g_home1TNSListener service was stopped successfully.

Note: while patch installation time If you face Distributed Transaction Coordinator still running. You should be stop this service

C:\>net stop msdtc
The Distributed Transaction Coordinator service is stopping.
The Distributed Transaction Coordinator service was stopped successfully.

4. Backup your database.

Oracle recommends that you create a backup of the Oracle 10g installation before you install the patch set.

5. Check Tablespace Sizes and Set Parameter Values

Review the following sections before upgrading a database.

7. Check SYSTEM Tablespace Size

If JServer is part of the installation ensure that there is at least 50 MB of free space allocated to the SYSTEM tablespace.

6. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
Users who have JVM (Java enabled) or JVM and XDB installed on their 10.2.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 10.2.0.1 seed database, and will be present unless the user explicitly installed a 10.2.0.1 instance without them.
Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:

C:\>set oracle_sid=DEVDB

C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Nov 13 10:49:26 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys@DEVDB as sysdba
Enter password:
Connected.

SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 437329148 bytes
Fixed Size 453884 bytes
Variable Size 369098752 bytes
Database Buffers 67108864 bytes
Redo Buffers 667648 bytes
Database mounted.

Note:

If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).

SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE;
System altered.

SQL> STARTUP FORCE
ORACLE instance started.
Total System Global Area 437329148 bytes
Fixed Size 453884 bytes
Variable Size 369098752 bytes
Database Buffers 67108864 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

8. Upgrade the Database

Install patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

1. Start all services

2. Connect sys user

C:\> sqlplus /NOLOG

SQL> CONNECT SYS/SYS_password AS SYSDBA

3. Enter the following SQL*Plus commands:

SQL> STARTUP UPGRADE

SQL> SPOOL patch.log

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql

SQL> SPOOL OFF

Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems.

4. Restart the database:
SQL> SHUTDOWN
SQL> STARTUP

5. Compile Invalid Objects

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Please post your comments..

Patch Installation

Upgrade Oracle 9.2.0.2 to 9.2.0.8

1. Patch Set Overview

Patch set release 9.2.0.8 includes all fixes in patch sets 9.2.0.8 and earlier as well as new fixes for patch set 9.2.0.8. This means that unless the patch set documentation indicates otherwise, you can apply this patch set to any earlier release 9.2 installation. You do not have to install intermediate patch sets.

2. Requirements

Oracle DB : Oracle 9.2.0.1 (later)

Operating System: Windows 2000 (Service Pack 2 or higher)

3. Pre – Installation Tasks

1. Identify prior installation

Before installing this patch you must install oracle 9.2.0.1 (or later version)

2. Download Patch set

Download and extract the p4547809_9208_WINNT.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory

3. Shutdown oracle database.

C:\>set oracle_sid=SNGDB

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.2.0 - Production on Tue Nov 13 10:49:26 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys@sngdb as sysdba

Enter password:

Connected.

SQL> SHUTDOWN

Database closed.

Database dismounted.

ORACLE instance shut down.

4. Stop all services

C:\>net start OracleServiceSNGDB

The OracleServicesngdb service is starting.....................

The OracleServicesngdb service was started successfully.

C:\>net stop OracleServiceSNGDB

The OracleServicesngdb service is stopping...

The OracleServicesngdb service was stopped successfully.

C:\>net stop OracleOraHome92Agent

The OracleOraHome92Agent service is stopping..

The OracleOraHome92Agent service was stopped successfully.

C:\>net stop OracleOraHome92TNSListener

The OracleOraHome92TNSListener service is stopping.

The OracleOraHome92TNSListener service was stopped successfully.

C:\>net stop OracleOraHome92HTTPServer

The OracleOraHome92HTTPServer service is stopping....

The OracleOraHome92HTTPServer service was stopped successfully.

C:\>net stop OracleMTSRecoveryService

The OracleMTSRecoveryService service is stopping.

The OracleMTSRecoveryService service was stopped successfully.

Note: while patch installation time If you face Distributed Transaction Coordinator still running. You should be stop this service

C:\>net stop msdtc

The Distributed Transaction Coordinator service is stopping.

The Distributed Transaction Coordinator service was stopped successfully.

4. Backup your database.

Oracle recommends that you create a backup of the Oracle9i installation before you install the patch set.

5. Check Tablespace Sizes and Set Parameter Values

Review the following sections before upgrading a database.

6. Check SYSTEM Tablespace Size

If JServer is part of the installation ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.

7. Check XDB Tablespace Size

For Oracle RAC installations, ensure that there is at least 50 MB of free space allocated to the XDB tablespace.

8. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters

Users who have JVM (Java enabled) or JVM and XDB installed on their 9.2.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 9.2.0.1 seed database, and will be present unless the user explicitly installed a 9.2.0.1 instance without them.

Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:

C:\>set oracle_sid=SNGDB

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.2.0 - Production on Tue Nov 13 10:49:26 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys@sngdb as sysdba

Enter password:

Connected.

Note:

If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).

SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE;

System altered.

SQL> STARTUP FORCE

ORACLE instance started.

Total System Global Area 437329148 bytes

Fixed Size 453884 bytes

Variable Size 369098752 bytes

Database Buffers 67108864 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

SQL> SHUTDOWN

Database closed.

Database dismounted.

ORACLE instance shut down.

9. Upgrade the Database

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

1. Start all services

2.     Connect sys user

   C:\> sqlplus /NOLOG

        SQL> CONNECT SYS/SYS_password AS SYSDBA

3.     Enter the following SQL*Plus commands:

          SQL> STARTUP MIGRATE

          SQL> SPOOL patch.log

         SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catpatch.sql

         SQL> SPOOL OFF

Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.

This list provides the version and status of each SERVER component in the database.

If necessary, rerun the catpatch.sql script after correcting any problems.

4. Restart the database:

SQL> SHUTDOWN

SQL> STARTUP

5. Compile Invalid Objects

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

11. If you are using the Oracle Recovery Manager catalog, enter the following command:

C:\> rman catalog username/password@orcl

RMAN> UPGRADE CATALOG; 

Adding Objects in Replication

Alter Replication:

If you want add objects in replication site. (You must be already done Advanced Replication “Click here “)

That objects must be contains primary key constraints.

In scott@US.edkal

SQL> CREATE TABLE U_MASTER

(UNO INT CONSTRAINT U_MASTER_PK PRIMARY KEY,

USERNAME VARCHAR2 (22),

U_PASSWORD VARCHAR2 (22));

Table created.

In repadmin@US.edkal

Here you need temporary stop master site activity and add those objects in master site.

SQL> BEGIN

2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (

3 gname => 'us_rep'

4 );

5 END;

6 /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'us_rep',

4 sname => 'SCOTT',

5 oname => 'U_MASTER',

6 type => 'TABLE',

7 copy_rows => TRUE,

8 use_existing_object => TRUE );

9 END;

10 /

PL/SQL procedure successfully completed.

SQL> ED

Wrote file afiedt.buf

1 BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'us_rep',

4 sname => 'SCOTT',

5 oname => 'U_MASTER_PK',

6 type => 'INDEX',

7 copy_rows => TRUE,

8 use_existing_object => TRUE );

9* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> CREATE MATERIALIZED VIEW LOG ON SCOTT.U_MASTER;

Materialized view log created.

SQL> BEGIN

2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

3 sname => 'SCOTT',

4 oname => 'U_MASTER',

5 type => 'TABLE',

6 min_communication => true,

7 generate_80_compatible => FALSE);

8* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (

3 gname => 'us_rep'

4 );

5 END;

6 /

PL/SQL procedure successfully completed.

Once complete start your master activity.

Create materialized view’s in your replication site.

In scott@mv01.edkal

SQL> CREATE MATERIALIZED VIEW U_MASTER REFRESH FAST WITH PRIMARY KEY UPDATABLE AS SELECT * FROM SCOTT.U_MASTER@US.EDKAL;

Materialized view created.

In mviewadmin@mv01.edkal

SQL> BEGIN

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(

gname => '"US_REP"',

sname => '"SCOTT"',

oname => '"U_MASTER"',

type => 'SNAPSHOT',

min_communication => TRUE);

END;

PL/SQL procedure successfully completed.

SQL> BEGIN

DBMS_REFRESH.ADD(

name => '"MVIEWADMIN"."US_REP"',

list => '"SCOTT"."U_MASTER"',

lax => TRUE);

END;

PL/SQL procedure successfully completed.