create directory for mapping drive in oracle database on windows

ISSUE:
data pump can not create files in directory created for mapping drive like S:\test_dir on windows.

CAUSE:
Mapping drive is specific for each account. The mapping drive like S:\test_dir must be recognized by the account running oracle service.

SOLUTION:

1. make sure the mapping drive you specified in " create directory statement" is available as the same mapping drive for the account running the oracle service. You can check services->properties -> logon to find the account name.

2. use direct network drive name like \\192.168.1.3\shearplace\testdir
    SQL: create directory test_dir as '\\192.168.1.3\shearplace\testdir';

reference:
https://forums.oracle.com/message/9981090

statistics manipulation commands

1. create local stats table
exec DBMS_STATS.CREATE_STAT_TABLE (ownname => 'TUSER', stattab => 'TEST_STAT_TABLE');

2. export current table stats to local table
exec DBMS_STATS.EXPORT_TABLE_STATS(ownname => 'TUSER',tabname => 'TTABLE',stattab => 'TEST_STAT_TABLE', statid => 'T1', cascade => true);

3. delete current table stats
exec DBMS_STATS.DELETE_TABLE_STATS(ownname => 'TUSER',tabname => 'TTABLE', cascade_columns => true, cascade_indexes => true);

4. import local stats to table
exec DBMS_STATS.IMPORT_TABLE_STATS(ownname => 'TUSER',tabname => 'TTABLE',stattab => 'TEST_STAT_TABLE', statid => 'T1', cascade => true);

5. check statistics

select statid, count(*) from TEST_STAT_TABLE group by statid;

6. statistics stored in table can be moved to other database and be imported.

7. Columns “C5” and “C1” of the statistics table represent schema owner and table name respectively. if owner and table_name is different when imported, update this two columns.

reference:
http://momendba.blogspot.com/2011/01/import-table-statistics-to-another.html

Swingbench installation

1.down load the file

2. unzip the file to the location you want it to be.

3. include java_home/bin into path variable

4. run oewizard to install soe schema for order entry test

5. check table record change for order entry test
select count(*) from CUSTOMERS ;
select count(*) from LOGON ;
select count(*) from ORDERS ;
select count(*) from ORDER_ITEMS ;

6. below table can be truncate and will be loaded when testing.

truncate table logon;
truncate table order_ITEMS;

reference:
http://www.dominicgiles.com/installation.html

create standby database from standby database

Task: create a new standby database from another standby database for standard edition_one version.

Since dataguard is not available, archived log need be manually applied.

Method: 

1. put standby database to read-only

alter database open (open read only);

2. copy all data file, control file, password file. parameter file  to new location.

You need rename them if location is changed.
alter database rename file 'J:\ORACLE\ORADATA\TEST\TEST_01.DBF' to    'O:\ORACLE\ORADATA\TEST\TEST_01.DBF' ;

3. start up database mount and apply log

sqlplus / as sysdba

startup mount

recover standy database.

Points. 

1. control file from a standby database can be directly copied and used for new standby database. You don't need create a standby control file from primary one.

2. if control file is not copied during read only time period, you can copy it later. However this will cause a problem if new file was added after datafile copy.

3. if latest control file is used and includes new added data files, the new data files can be copied from standby database again after it is put back to read only. Even though the timestamp is not same for the first copied data files and later copied ones, archive log can be applied to both and make them sync. 

4. if you use old control file and new file is added, you will have to add new file manually in 10g version.

alter database create datafile  'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00160' as 'I:\ORACLE\ORADATA\TEST_INDX38.DBF' ;

ORA-16014 and All Archive destinations made inactive due to error 333 on windows

ISSUE:
Database hung and no external connection is allowed.

CAUSE:
The only member of one of inactive online redo log groups is corrupted. Archiver can not archive it to disk completely but stopped partially.

ERROR MESSAGES:


ERROR:
ORA-00257: archiver error. Connect internal only, until freed.


SQL> alter system switch logfile;   # it hung


SQL> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-16020: fewer destinations available than specified by
LOG_ARCHIVE_MIN_SUCCEED_DEST



SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16038: log 1 sequence# 6484 cannot be archived
ORA-00333: redo log read error block  count
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'

ALERT LOG MESSAGE:

ARC2: All Archive destinations made inactive due to error 333
Committing creation of archivelog 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TEST_ARC0000047371_07852571900001.ARC' (error 333)

SOLUTION:
Clear the corrupted log group and make a full backup. All old backup is unusable to bring the database back   beyond the corrupted point.

Identify problem log group:
Found sequence # from alert log and map it with output of " select * from v$log "

Command:
alter database clear unarchived logfile group 5;


RCA:
Need run blow command to see if redo log is corrupted.
alter system dump logfile '<logfileL_filename>'; 

Reference:
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/onlineredo.htm#i1006568
http://www.dba-oracle.com/t_ora_16014_log_sequence_number_not_archived_no_available_destinations.htm
https://forums.oracle.com/forums/thread.jspa?threadID=839923&tstart=0

Bulk insert/update with Cursor in oracle PL/SQL

ISSUE:
table is locked when insert/update millions records and commit at the end. Run time will be very long if commit after every record change.

SOLUTION:
Oracle provide bulk insert/update by fetch multiple records first and perform insert/update on them with commit.

example:

CREATE OR REPLACE PROCEDURE test_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS SELECT * FROM all_objects;

BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO t1 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END test_proc;
/            

reference:
http://www.akadia.com/services/ora_bulk_insert.html

Search text stored in long column through like operation in Oracle PL/SQL

ISSUE:
Text stored in long column can not used directly in like clause in sql statement.

WORKAROUND:
use anonymous PL/SQL with corsor  to do the like search.

Example:
check trigger body with string "TEST"


declare
l_text long;
tname varchar2(100);
cursor c_tri is select trigger_body, trigger_name from all_triggers where owner='TEST';
begin
open c_tri;
loop
 fetch c_tri into l_text, tname;
 exit when c_tri%NOTFOUND;
 if ( Upper(l_text) like '%TEST%' )
 then
    dbms_output.put_line(tname);
end if;
end loop;
close c_tri;
end;
/

reference:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:315118200346261192
http://www.dbanotes.com/database-development/introduction-to-oracle-11g-cursors/