Thursday, July 26, 2012

Manually delete flashback logs

Usually when you stop the flashback database feature the flashback logs are automatically increased. In order to manually delete the flashback logs follow this procedure ;

shutdown immediate;
startup mount;
alter database flashback off;
--> Delete using O/S commands
alter database flashback on;
alter database open;

For 10.2.0.3 and Up you can disable flashback database with bouncing the DB.


Configure one way schema replication with Oracle Streams

Oracle Streams can be used to perform replication of databases , schemas , tablespaces and tables between heterogeneous O/S and different DB versions.


In this post i will present the procedure to replicate a schema from a 10gR2 Db on Windows to a 11gR2 DB on RHEL. The instantiation of the schema will take place using Data Pump import (impdp) using the network (via the DB Links that will be created)


A. Configuration Parameters for both Source and Target 



1. COMPATIBLE must be >=10.2.0.0
2. GLOBAL_NAMES = TRUE
3. JOB_QUEUE_PROCESSES >=2
4. OPEN_LINKS >=4
5. UNDO_RETENTION >=3600
6. AQ_TM_PROCESSES
From 10gR2 and up is it advisable to be unset and let system autotune. If it was set to a specific value then the recomended is to set to 1

B.  Create Oracle Streams administrator user on both DBs with the same credentials. Its advisable to create a specific tablespace for this user and give him the necessary privileges.

As SYSDBA user:

create user strmadmin identified by strmadmin  
  default tablespace streams_tbs quota  unlimited on streams_tbs;
 
grant connect, resource, aq_administrator_role,dba to strmadmin;  

begin  
  dbms_streams_auth.grant_admin_privilege(  
grantee => 'strmadmin',  
grant_privileges => true);  
end;  
/  
commit;

C. Create the directory object which will be used to store the script that will enable the Streams on both source and target DB Server

create directory streams_files as '[DIRECTORY_PATH]';

grant read,write on directory streams_files to strmadmin;

The directory path can be different on source and target DB server.

D. Create the appropriate TNS entries and both source and target

Ensure that you will use the global_name of each database to the TNS connect string and that DB Link is working properly from both DBs.

As The Streams Administrator User

create database link [DB_GLOBAL_NAME] connect to strmadmin identified by strmadmin using '[DB_TNS_ENTRY]';

E. Execute on target the following script to replicate schema AGIS which has a table named T for the test purposes

As Streams Admin User on Source DB

begin
  dbms_streams_adm.maintain_schemas(
  schema_names=> 'agis',
  source_directory_object=> null,
  destination_directory_object=> null,
  source_database=> 'ODIN.NET1.CEC.EU.INT',
  destination_database => 'EROSD.NET1.CEC.EU.INT',
  perform_actions => true,
  script_name =>'Schema_maintain_streams.sql',
  script_directory_object=>'streams_files',
  bi_directional=> false,
  include_ddl => true ,
  instantiation=>dbms_streams_adm.instantiation_schema_network);
  commit;
end;
/

If The PL/SQL block terminates without errors then check Streams Functionality.

REMARKS 
"instantiation=>dbms_streams_adm.instantiation_schema_network" on the procedure will instantiate the desired schema using impdp via DBLINK.

F. Test that Streams Replication is working

For example : 

- Insert some records on T@SOURCE and check if the changes are propagated on T@TARGET
- Add a new column on T@SOURCE and check if the changes are propagated on T@TARGET
etc

REFERENCES
Oracle Support Note ID: 878638.1









Thursday, July 12, 2012

Web services callouts from Oracle DB 10g and 11g PL/SQL

In order to perform Web Service calls using PL/SQL use must install the UTL_DBWS PL/SQL package.


First connect as sys and check if has been installed (In 11g its isntaleld by default).


SQL> describe sys.utl_dbws


If this command does not return the package declarations the you must install it manual:


1. Run the following scripts under SYS:


$ORACLE_HOME/sqlj/lib/utl_dbws_decl.sql
$ORACLE_HOME/sqlj/lib/utl_dbws_body.sql

2. The dbwsclientws.jar must be loaded into the database for Web services call-outs and you can verify whether it is already loaded by running the following query in the SYS schema:


select status, object_type
from   all_objects
where  dbms_java.longname(object_name)='oracle/jpub/runtime/dbws/DbwsProxy$1';

The following result indicates that the file is already loaded:



STATUS OBJECT_TYPE
------- -------------------
VALID JAVA CLASS
VALID SYNONYM


If its not loaded use the follwoing commands to load it on the DB:



cd $ORACLE_HOME/sqlj/lib 

loadjava -u username/password -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar

Then grant to above username the following permissions as sys:


execute dbms_java.grant_permission('<SCHEMA>','SYS:java.util.PropertyPermission','http.proxySet','write');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.lang.RuntimePermission','getClassLoader','');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.net.SocketPermission','*','connect,resolve');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.util.PropertyPermission','*','read,write');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.lang.RuntimePermission','setFactory','');



It is recommended that a user created schema be used for this, (CONNECT, RESOURCE and CREATE PUBLIC SYNONYM are the minimum grants that have to be performed on this schema). In the 11g version of the database, it has been deteremined that loading the jars into ths SYS schema will cause conflicts with existing classes already loaded in this schema by default, so it is best to not use SYS for loading the jars. 





REFERENCES:
Oracle Support Note: 1166206.1



Wednesday, July 4, 2012

Oracle Database on Windows Server Memory Fucntionality

A very nice series of articles about Windows Server Memory Architecture. A must read if you are building an Oracle Database Server on Windows(but not only).

http://blogs.technet.com/b/markrussinovich/archive/2008/07/21/3092070.aspx


Monday, July 2, 2012

Handling ORA-0376 (ORA-376) Errors


ORA-376 means at some point in time a datafile made unavailiable on the database. Usually when you come across this error with the database opened, unless SYSTEM tablespace has been unavailiable. Most of the times and depending on the cases you can bypass this error by just recovering the offended datafiles with the database in open or mounted state.

ACTIONS

 1. Check the alert.log of the instance and find the file and the tablespace affected when the error generated. You can also check the corresponding tracefile for further info.

Mon Jul 02 01:01:53 2012
KCF: write/open error block=0x13e5e online=1
     file=3 D:\ORACLE\ORADATA\ZEUS\SYSAUX01.DBF
     error=27070 txt: 'OSD-04016: Message 4016 not found; No message file for product=RDBMS, facility=SOSD
O/S-Error: (OS 1450) Insufficient system resources exist to complete the requested service.'
Mon Jul 02 01:01:53 2012
ARC0: Failed to archive thread 1 sequence 84025 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Jul 02 01:01:53 2012
Automatic datafile offline due to write error on
file 3: D:\ORACLE\ORADATA\ZEUS\SYSAUX01.DBF


2. Find out the status of the offened tablespacetablespaces:

select status,count(*) from dba_tablespaces group by status;

3. Find out the status of the datafiles using the number you found out before.

select file#,name,status,enabled from v$datafile where file#=3;

a. Check that the name of the datafile is matching the one from alert.log and the status column. If status is offline then you can bring the tablespace / datafile online and you have finished.
           
alter tablespace [TABLESPACE_NAME] online;
or
alter database datafile [FULL_PATH_DATAFILE_NAME] online;


b. If the status is recover then you must try media recovery for the datafile and then try to bring it online. If the affected tablespace is SYSTEM, SYSAUX, UNDOTBS then you must cleanly shutdown the database (if its still open) and perform the following steps using sqlplus:
           
shutdown immediate;

startup mount

recover datafile [FULL_PATH_DATAFILE_NAME];
Use AUTO if you prompeted for an Archive Log name to continue recovery using the online redo.logs

alter database datafile [FULL_PATH_DATAFILE_NAME] online;

alter database open;
If database opens without an error then restart the DB.

shutdown immediate;

startup

Its advisable to take a new backup for the affected tablespaces.


1. References

Oracle Support Document ID(s): 183327.1, 1013912.6,