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/

Find (Search) in CMD (DOS command prompt)

The old way to search files containing a string pattern in DOS command prompt.

find /i "test" *.sql

Spool file from oracle database for microsoft EXCEL

ISSUE:
Business user would like to view reports generated from database through microsoft EXCEL. So tab-separated txt file with xls as file extension name is used to generate reports automatically. However, some cell is displayed in anticipated format like 1.2E+12 (12345678912)or 123456789198000000000 (123456789198,12345567788)

CAUSE:
EXCEL does auto-convert for number from tab-separated text files.

SOLUTION:
I didn't find the way to turn off this auto-convert function to save the number as stored text.

WORKAROUND:
You can add something, which is tolerated by end user, to avoid auto-convert. For example, add a comma ',' at the end of the column from query.

Reference:
http://office.microsoft.com/en-us/excel-help/three-ways-to-convert-numbers-to-text-HA001136619.aspx
http://office.microsoft.com/en-us/excel-help/format-numbers-as-text-HP001216512.aspx

oracle resource manager

1. check current running resource plan
select * from v$rsrc_plan

2. create a simple plan
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'BEN_SIMPLE_PLAN',
   CONSUMER_GROUP1 => 'BEN_GROUP1', GROUP1_PERCENT => 80,
   CONSUMER_GROUP2 => 'BEN_GROUP2', GROUP2_PERCENT => 20);
END;

2. swith plan (INTERNAL_PLAN is equal to no plan
alter system set resource_manager_plan='BEN_SIMPLE_PLAN';
alter system set resource_manager_plan='DEFAULT_MAINTENANCE_PLAN';
alter system set resource_manager_plan='';

3. check available plans
select plan_id, plan from dba_rsrc_plans;

4. check session consumer group
select username, sid, serial#,  resource_consumer_group from v$session;

5. check session state under current plan
select sid, CURRENT_CONSUMER_GROUP_ID, state from V$RSRC_SESSION_INFO;

6. check plan drectives
SELECT group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3, mgmt_p4
 FROM dba_rsrc_plan_directives WHERE plan = 'BEN_SIMPLE_PLAN';

7. change session consumer group
BEGIN
  DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ('100', '64491',
   'SYS_GROUP');
END;
/

8. change all session of a user consumer group
BEGIN
  DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('BEN',
    'BEN_GROUP1');
END;
/

9. resource manager views.
View Description

DBA_RSRC_CONSUMER_GROUP_PRIVS

USER_RSRC_CONSUMER_GROUP_PRIVS


DBA view lists all resource consumer groups and the users and roles to which they have been granted. USER view lists all resource consumer groups granted to the user.

DBA_RSRC_CONSUMER_GROUPS


Lists all resource consumer groups that exist in the database.

DBA_RSRC_MANAGER_SYSTEM_PRIVS

USER_RSRC_MANAGER_SYSTEM_PRIVS


DBA view lists all users and roles that have been granted Resource Manager system privileges. USER view lists all the users that are granted system privileges for the DBMS_RESOURCE_MANAGER package.

DBA_RSRC_PLAN_DIRECTIVES


Lists all resource plan directives that exist in the database.

DBA_RSRC_PLANS


Lists all resource plans that exist in the database.

DBA_RSRC_GROUP_MAPPINGS


Lists all of the various mapping pairs for all of the session attributes.

DBA_RSRC_MAPPING_PRIORITY


Lists the current mapping priority of each attribute.

DBA_HIST_RSRC_PLAN


Displays historical information about resource plan activation. This view contains AWR snapshots of V$RSRC_PLAN_HISTORY.

DBA_HIST_RSRC_CONSUMER_GROUP


Displays historical statistical information about consumer groups. This view contains AWR snapshots of V$RSRC_CONS_GROUP_HISTORY.

DBA_USERS

USERS_USERS


DBA view contains information about all users of the database. It contains the initial resource consumer group for each user. USER view contains information about the current user. It contains the current user's initial resource consumer group.

V$RSRC_CONS_GROUP_HISTORY


For each entry in the view V$RSRC_PLAN_HISTORY, contains an entry for each consumer group in the plan showing the cumulative statistics for the consumer group.

V$RSRC_CONSUMER_GROUP


Displays information about active resource consumer groups. This view can be used for tuning.

V$RSRCMGRMETRIC


Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past minute.

V$RSRCMGRMETRIC_HISTORY


Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past hour on a minute-by-minute basis. If a new resource plan is enabled, the history is cleared.

V$RSRC_PLAN


Displays the names of all currently active resource plans.

V$RSRC_PLAN_HISTORY


Shows when Resource Manager plans were enabled or disabled on the instance. It helps you understand how resources were shared among the consumer groups over time.

V$RSRC_SESSION_INFO


Displays Resource Manager statistics for each session. Shows how the session has been affected by the Resource Manager. Can be used for tuning.

V$SESSION


Lists session information for each current session. Specifically, lists the name of the resource consumer group of each current session.


Reference:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm013.htm#CHDJAJHE
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm003.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14231/dbrm.htm