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/