Oracle Log

How to Trace an Oracle Error

2011-09-29T04:16:00.000-07:00

In order to trace an oracle error ORA-XXXX, first login database using a privileged account and enable trace:

alter system set events 'XXXX trace name errorstack level 1';

(XXXX is the oracle error number not beginning with zero. You may set trace level up to 3 for more detailed trace)

After trace enabled if ORA-XXXX occurs, you can find a trace file generated in trace (dump) directory.

In order to disable trace:

alter system set events 'XXXX trace name errorstack off';

Generated trace file is more like a memory dump then an error stack. So you may not find what you are looking for...easily...

How to configure 10g database with 11g RAC SCAN

2011-07-07T04:08:00.000-07:00

If you have a 10g database in a 11.2g cluster and want to connect 10g database using SCAN then you should update local/remote listener parameters of 10g database manually. Here is an example for 2-node RAC:

mycluster-crs-scan.mydomain.com : My scan name (DNS)
mynode1-vip.mydomain.com: vip of node 1
MYSID1: Instance Name running on node 1
mynode2-vip.mydomain.com: vip of node 2
MYSID2: Instance Name running on node 2

alter system set remote_listener='mycluster-crs-scan.mydomain.com:1521' scope=both SID='*';
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mynode1-vip.mydomain.com)(PORT=1521))))' scope=both SID='MYSID1';
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mynode2-vip.mydomain.com)(PORT=1521))))' scope=both SID='MYSID2';

How to find CPU-core information on Sun Solaris

2011-03-03T23:56:00.000-08:00

Here is 4 CPU, 16 cores...

$ prtdiag -v
System Configuration: Sun Microsystems sun4u SPARC Enterprise M9000 Server
System clock frequency: 960 MHz
Memory size: 20480 Megabytes
==================================== CPUs ====================================
      CPU                 CPU                         Run    L2$    CPU   CPU
LSB   Chip                 ID                         MHz     MB    Impl. Mask
---   ---- ---------------------------------------- ----   ---    ----- ----
00     2     16, 17, 18, 19                       2280   5.0        6 146
02     1     72, 73, 74, 75                       2280   5.0        6 146
03     2    112, 113, 114, 115                       2280   5.0        6 146
04     3    152, 153, 154, 155                       2280   5.0        6 146

Incremental statistics

2011-02-01T03:41:00.000-08:00

In order to gether incremental statistics on a table :
1-) Set incremental statistic pref for the table
exec dbms_stats.set_table_prefs('MYSCHEMA','MYTABLE','INCREMENTAL' ,'TRUE') ;
2-) Chek if it is set (should return TRUE)
select dbms_stats.get_prefs('INCREMENTAL','MYSCHEMA','MYTABLE') from dual;
3-) Gather statistics on a partition using GRANULARITY set to AUTO

For official documentation:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/stats.htm (13.3.1.3 Statistics on Partitioned Objects)

Setting Global/Database prefs:
1-) In order to set database stat gathering preferences excluding oracle system tables:
exec DBMS_STATS.SET_DATABASE_PREFS('METHOD_OPT','FOR ALL COLUMNS SIZE 254');
2-) In order to set database stat gathering preferences globally:
exec DBMS_STATS.SET_GLOBAL_PREFS('METHOD_OPT','FOR ALL COLUMNS SIZE 254');
to see preference value:
select dbms_stats.get_prefs('METHOD_OPT') from dual;

Preferences:
AUTOSTATS_TARGET
CASCADE
DEGREE
ESTIMATE_PERCENT
METHOD_OPT
NO_INVALIDATE
GRANULARITY
PUBLISH
INCREMENTAL
STALE_PERCENT

CPU count

2011-02-01T00:33:00.000-08:00

AIX Lpar CPU count:

lparstat -i|grep "Entitled Capacity"|grep -v Pool

Disabling Parallel Load Balancing

2011-01-06T04:59:00.000-08:00

Normally, parallel load balancing is a good feature which distributes parallel sessions among rac nodes depending on node workloads. However there is a little problem with this algorithm. Node load statistics are calculated on very little time intervals (i don't remember now, let say in a few milliseconds). What happens if a big batch allocates large number of parallel sessions within this time period? All parallel sessions are allocated depending on this constant statistic which may lead one node to be overloaded.

Let me explain with an example. Let say there is 3-node rac cluster and we will run a big batch which will open 128 parallel sessions. Let assume before running batch 2 nodes are 50% loaded, 1 is idle (<3%). When we run batch, oracle starts distributing parallel sessions to nodes depending on their workload and he may decided to open all parallel sessions on idle node. This is because all parallel sessions are opened in a very short time interval in which node load statistics are not updated so oracle decides to open every connection in idle node which seems best suitable. The result 2 nodes with 50% load and 1 with 100%.

In order to eliminate this, you may set following variable on batch session:

alter session set "_parallel_load_balancing"=FALSE;

which disables load balancing and distributes all sessions one by one to all nodes ignoring workload.

However, you should be very careful while using this parameter. Because if one node is already 100% loaded and if you set this parameter, oracle will try to open 1/3 of the 128 parallel session on this already 100% loaded node which may kill node. However load balancing would prevent opening new sessions on loaded node.

PRKP-1001 and CRS-0215 while starting 10g RAC database on 11gr2 cluster using srvctl

2010-12-13T04:31:00.000-08:00

Recently we created 10g RAC database on 11gR2 cluster but could not able to start database with srvctl altough we can using sqlplus. Solution is to copy tnsnames.ora and listener.ora files from TNS_ADMIN location to ORACLE_HOME/network/admin:

problem:
srvctl start instance -d MYDB -i MYINST
PRKP-1001 : Error starting instance MYINST on node mynode
CRS-0215: Could not start resource 'ora.MYDB.MYINST.inst'.

Solution:
cp $TNS_ADMIN/listener.ora $ORACLE_HOME/network/admin/listener.ora
cp $TNS_ADMIN/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.ora

Using fked to restore ASM Disk Header (test)

2010-12-06T04:55:00.000-08:00

Recently i tested kfed for restoring ASM disk header corruption. Here are the steps:

1-) Create a test Diskgroup, test tablespace and test table
CREATE DISKGROUP TEST EXTERNAL REDUNDANCY
DISK
'/dev/rhdisk6',
'/dev/rhdisk7'
ATTRIBUTE
'compatible.asm' = '11.2',
'compatible.rdbms' = '11.2';

CREATE TABLESPACE TEST_TS DATAFILE '+TEST' SIZE 100M AUTOEXTEND ON NEXT 125M MAXSIZE 10240M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

create table SCOTT.TEST_TABLE (a int, b varchar2(1000)) tablespace TEST_TS;
insert into SCOTT.TEST_TABLE values (1, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE values (2, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE values (3,'sdfghfasdfgywbefg');

2-) Check ASM
SQL> select GROUP_NUMBER, NAME, ALLOCATION_UNIT_SIZE, TYPE, TOTAL_MB-FREE_MB USED_MB, TOTAL_MB, COMPATIBILITY, DATABASE_COMPATIBILITY from v$asm_diskgroup;
GROUP_NUMBER NAME                           ALLOCATION_UNIT_SIZE TYPE      USED_MB   TOTAL_MB COMPATIBIL DATABASE_C
------------ ------------------------------ -------------------- ------ ---------- ---------- ---------- ----------
           1 DATACRS                                     1048576 EXTERN       2123      10240 11.2.0.0.0 10.1.0.0.0
           2 TEST                                        1048576 EXTERN        163      10240 11.2.0.0.0 11.2.0.0.0

SQL> select GROUP_NUMBER, HEADER_STATUS, PATH,NAME, LIBRARY, FAILGROUP_TYPE, trunc(OS_MB/1024*1024*1024/1024/1024) SUM_OS from V$ASM_DISK;
GROUP_NUMBER HEADER_STATU PATH                           NAME                           LIBRARY              FAILGRO     SUM_OS
------------ ------------ ------------------------------ ------------------------------ -------------------- ------- ----------
           0 CANDIDATE    /dev/rhdisk9                                                  System               REGULAR          5
           0 CANDIDATE    /dev/rhdisk8                                                  System               REGULAR          5
           1 MEMBER       /dev/rhdisk2                   DATACRS_0000                   System               REGULAR          5
           1 MEMBER       /dev/rhdisk3                   DATACRS_0001                   System               REGULAR          5
           2 MEMBER       /dev/rhdisk6                   TEST_0000                      System               REGULAR          5
           2 MEMBER       /dev/rhdisk7                   TEST_0001                      System               REGULAR          5

2-) Corrupt disk hdisk6 (with root):

node1
lquerypv -h /dev/rhdisk6
00000000   00820101 00000000 80000000 B8CC7719 |..............w.|
00000010   00000000 00000000 00000000 00000000 |................|
00000020   4F52434C 4449534B 00000000 00000000 |ORCLDISK........|
00000030   00000000 00000000 00000000 00000000 |................|
00000040   0B200000 00000103 54455354 5F303030 |. ......TEST_000|
00000050   30000000 00000000 00000000 00000000 |0...............|
00000060   00000000 00000000 54455354 00000000 |........TEST....|
00000070   00000000 00000000 00000000 00000000 |................|
00000080   00000000 00000000 54455354 5F303030 |........TEST_000|
00000090   30000000 00000000 00000000 00000000 |0...............|
000000A0   00000000 00000000 00000000 00000000 |................|
000000B0   00000000 00000000 00000000 00000000 |................|
000000C0   00000000 00000000 01F6B0CB 6422B400 |............d"..|
000000D0   01F6B0CB 64C47000 02001000 00100000 |....d.p.........|
000000E0   0001BC80 00001400 00000002 00000001 |................|
000000F0   00000002 00000002 00000000 00000000 |................|
#stop crs before. Other case ASM will protect disk from corruption
./crsctl stop crs
chdev -l hdisk6 -a pv=yes
lquerypv -h /dev/rhdisk6 #corrupted!!!
00000000   C9C2D4C1 00000000 00000000 00000000 |................|
00000010   00000000 00000000 00000000 00000000 |................|
00000020   00000000 00000000 00000000 00000000 |................|
00000030   00000000 00000000 00000000 00000000 |................|
00000040   00000000 00000000 00000000 00000000 |................|
00000050   00000000 00000000 00000000 00000000 |................|
00000060   00000000 00000000 00000000 00000000 |................|
00000070   00000000 00000000 00000000 00000000 |................|
00000080   00C2E250 BB2CE1C7 00000000 00000000 |...P.,..........|
00000090   00000000 00000000 00000000 00000000 |................|
000000A0   00000000 00000000 00000000 00000000 |................|
000000B0   00000000 00000000 00000000 00000000 |................|
000000C0   00000000 00000000 00000000 00000000 |................|
000000D0   00000000 00000000 00000000 00000000 |................|
000000E0   00000000 00000000 00000000 00000000 |................|
000000F0   00000000 00000000 00000000 00000000 |................|

3-) TEST DG seem to be unmounted. Try to mount it:

ASMCMD> mount TEST
ORA-15032: not all alterations performed
ORA-15017: diskgroup "TEST" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "TEST" (DBD ERROR: OCIStmtExecute)

SQL> select * from SCOTT.TEST_TABLE ;
select * from SCOTT.TEST_TABLE
                    *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '+TEST/mydb/datafile/test_ts.256.737033885'

4-) Restore disk header using kfed (with root)
kfed REPAIR /dev/rhdisk6
#now it seems to be ok!
lquerypv -h /dev/rhdisk6
00000000   00820101 00000000 80000000 B8CC7719 |..............w.|
00000010   00000000 00000000 00000000 00000000 |................|
00000020   4F52434C 4449534B 00000000 00000000 |ORCLDISK........|
00000030   00000000 00000000 00000000 00000000 |................|
00000040   0B200000 00000103 54455354 5F303030 |. ......TEST_000|
00000050   30000000 00000000 00000000 00000000 |0...............|
00000060   00000000 00000000 54455354 00000000 |........TEST....|
00000070   00000000 00000000 00000000 00000000 |................|
00000080   00000000 00000000 54455354 5F303030 |........TEST_000|
00000090   30000000 00000000 00000000 00000000 |0...............|
000000A0   00000000 00000000 00000000 00000000 |................|
000000B0   00000000 00000000 00000000 00000000 |................|
000000C0   00000000 00000000 01F6B0CB 6422B400 |............d"..|
000000D0   01F6B0CB 64C47000 02001000 00100000 |....d.p.........|
000000E0   0001BC80 00001400 00000002 00000001 |................|
000000F0   00000002 00000002 00000000 00000000 |................|

5-) Try to mount again:

ASMCMD> mount TEST #ok!!!

6-) Now do some tests (everything ok)
CREATE TABLESPACE TEST_TS2 DATAFILE '+TEST' SIZE 100M AUTOEXTEND ON NEXT 125M MAXSIZE 10240M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table SCOTT.TEST_TABLE2 (a int, b varchar2(1000)) tablespace TEST_TS;

insert into SCOTT.TEST_TABLE2 values (4, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE2 values (5, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE2 values (6,'sdfghfasdfgywbefg');
drop table SCOTT.TEST_TABLE;
drop tablespace TEST_TS;
alter diskgroup TEST add disk '/dev/rhdisk8','/dev/rhdisk9' drop disk 'TEST_0000','TEST_0001';

SQL> select GROUP_NUMBER, HEADER_STATUS, PATH,NAME, LIBRARY, FAILGROUP_TYPE, trunc(OS_MB/1024*1024*1024/1024/1024) SUM_OS from V$ASM_DISK;
GROUP_NUMBER HEADER_STATU PATH                           NAME                           LIBRARY              FAILGRO     SUM_OS
------------ ------------ ------------------------------ ------------------------------ -------------------- ------- ----------
           0 FORMER       /dev/rhdisk6                                                  System               REGULAR          5
           0 FORMER       /dev/rhdisk7                                                  System               REGULAR          5
           1 MEMBER       /dev/rhdisk2                   DATACRS_0000                   System               REGULAR          5
           1 MEMBER       /dev/rhdisk3                   DATACRS_0001                   System               REGULAR          5
           2 MEMBER       /dev/rhdisk8                   TEST_0002                      System               REGULAR          5
           2 MEMBER       /dev/rhdisk9                   TEST_0003                      System               REGULAR          5

CRS-0222 and CRS-2640 while removing RAC datagourp

2010-12-06T01:20:00.000-08:00

Recently I tried to drop a datagroup from RAC cluster. It was previously registered with cluster while creating a RAC database:

HA Resource Target State
ora.FRA.dg ONLINE OFFLINE

I moved all database files from this datagroup to an other. Then when i tried to drop it, following error occured:

srvctl remove diskgroup -g FRA
PRCA-1002 : Failed to remove CRS resource ora.FRA.dg for ASM Disk Group FRA
PRCR-1028 : Failed to remove resource ora.FRA.dg
PRCR-1072 : Failed to unregister resource ora.FRA.dg
CRS-0222: Resource 'ora.FRA.dg' has dependency error.

The right thing was to remove datagroup as told in here.
1-) srvctl modify database -d MYDB -a “DATA,OTHER_DG,...”
2-) srvctl disable diskgroup -g FRA
3-) srvctl remove diskgroup -g FRA –f

However, I removed datagroup first:

srvctl remove diskgroup -g FRA -f

Then, when i tried to open database, It gave me fallowing error:

srvctl start database -d mydb
PRCR-1079 : Failed to start resource ora.mydb.db
CRS-2640: Required resource 'ora.FRA.dg' is missing.

That was because altough i removed datagroup with force option, dependency was remained. You can see it with similar command:

./crsctl status resource ora.mydb.db -f|grep FRA
START_DEPENDENCIES=hard(ora.DATA.dg,ora.FRA.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,uniform:ora.eons) pullup(ora.DATA.dg)
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg,shutdown:ora.FRA.dg)

I run fallowing command to remove FRA dependency:

srvctl modify database -d MYDB -a “DATA” (step 1)
When i check dependency again, i saw that FRA was gone:

./crsctl status resource ora.mydb.db -f|grep FRA
(nothing)

./crsctl status resource ora.mydb.db -f|grep DATA
START_DEPENDENCIES=hard(ora.DATA.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,uniform:ora.eons) pullup(ora.DATA.dg)
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg)

After that, i could able to open my database

srvctl start database -d mydb

How to Copy Files in ASM

2010-12-03T03:54:00.000-08:00

cp command in ASMCMD actually not copies system generated the file but creates an alias for real file. Here is a procedure to copy system generated file (redo log) from one asm datagroup to an other:

1-) Login a database which has access to asm disk groups

2-) Create following directories

CREATE OR REPLACE DIRECTORY SOURCE_DIR AS '+DG1/DBNAME/ONLINELOG';
CREATE OR REPLACE DIRECTORY DEST_DIR AS '/..filesystem_path../ONLINELOG';

3-) Copy asm file to filesystem

BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
'SOURCE_DIR', source_file_name => 'filename',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'filename');
END;
/

4-) Copy file on filesystem back to asm

CREATE OR REPLACE DIRECTORY SOURCE_DIR AS '/..filesystem_path../ONLINELOG';
CREATE OR REPLACE DIRECTORY DEST_DIR AS '+DG2/DBNAME/ONLINELOG';
BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
'SOURCE_DIR', source_file_name => 'filename',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'new_filename');
END;
/

AIX: ASM Metadata & DISKS

2010-10-18T04:21:00.000-07:00

lspv: command used to get disk list (disk, assigned PVID, vg, status)

$lspv
hdisk0 00c2e2503cddd971 rootvg active
hdisk9 00c2e2508b71c3a9 sw_vg active
hdisk1 none None
hdisk2 none None
hdisk3 none None
...

ASM disks should not have PVID assigned to them (Configuring Storage for Grid Infrastructure, 3.3.3 Configuring Disk Devices for Oracle ASM, step 7). They should be removed with fallowing command before assigned to ASM:

$/usr/sbin/chdev -l hdiskn -a pv=clear

Note: If you already assigned disks without clearing PVID, don't clear it using above command. It corrupts asm disks while clearing header. Because it is the same header where asm keeps its internal info. Actually ASM clears header and PVID and writes its own header when a disk assigned to a datagroup. However because OS odm (aix's internal database) is not updated, you can see PVIDs using lspv although it is not there.

By using following command you can query disk header in order to see ASM internal information written on disk (requires root permission)

$lquerypv -h /dev/rhdisk5
00000000 00820101 00000000 80000000 81AA935B |...............[|
00000010 00000000 00000000 00000000 00000000 |................|
00000020 4F52434C 4449534B 00000000 00000000 |ORCLDISK........|
00000030 00000000 00000000 00000000 00000000 |................|
00000040 0A100000 00000103 54455354 5F303030 |........TEST_000|
00000050 30000000 00000000 00000000 00000000 |0...............|
00000060 00000000 00000000 54455354 00000000 |........TEST....|
00000070 00000000 00000000 00000000 00000000 |................|
00000080 00000000 00000000 54455354 5F303030 |........TEST_000|

In 11g you can backup and recover corrupted disk headers using md_backup and md_restore. The corrupted diskgroup must be specified with the ‘-g’ flag for the restore to complete
successfully:

ASMCMD> md_restore -b /oracle/backup/asm_metadata020409.bkp -g 'TEST'

Ref:
11gRAC_ASM_1.pdf (doc link)

How to Drop ASM Datagroup while not mounted and disks corrupted

2010-10-18T02:23:00.000-07:00

In a test environment we accidentally corrupt asm disks and reset their headers. When I query V$ASM_DISK view, I saw that disks were discovered as "CANDIDATE". It was like newly added disks available for ASM. The problem was, my datagroup which was using these disks, was exists but i could not drop it. That was because a diskgroup has to be mounted in order to be dropped, but its disks were corrupted so i could not mount it:

SQL> drop diskgroup DATA;
drop diskgroup DATA
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "DATA" does not exist or is not mounted

SQL> ALTER DISKGROUP DATA mount;
ALTER DISKGROUP DATA mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"

How can i drop/delete datagroup which i can not mount?

Solution is to create a new datagroup, add all disks to new datagroup. At the end, old datagroup disappears... Then you may drop newly added diskgroup.

create diskgroup DATA_DROP external redundancy disk '/dev/rhdisk#' force;
alter diskgroup DATA_DROP add disk '/dev/rhdisk#';
...
DATA disappears when i add all its disks to DATA_DROP...
...
drop diskgroup DATA_DROP including contents;

now, DATA diskgroup has gone and all its disks are in "FORMER" status.

How to get process path on linux

2010-09-19T23:52:00.000-07:00

$ps -eaf | grep pmon

oracle 24771 1 0 Aug14 ? 00:02:59 ora_pmon_INST1
oracle 28486 1 0 Sep05 ? 00:04:00 ora_pmon_INST2

$readlink -f /proc/24771/exe
/.../bin/oracle
$readlink -f /proc/28486/exe
/.../bin/oracle

SCAN IP Configuration

2010-08-25T00:48:00.000-07:00

In order to display scan configuration of RAC cluster:

$srvctl config scan

SCAN name: my-scan.mydomain.com.tr, Network: X/XXX.XXX.XXX.XXX/XXX.XXX.XXX.XXX/en0
SCAN VIP name: scan1, IP: /XXX.XXX.XXX.XXX/XXX.XXX.XXX.XXX
SCAN VIP name: scan2, IP: /XXX.XXX.XXX.XXX/XXX.XXX.XXX.XXX
SCAN VIP name: scan3, IP: /XXX.XXX.XXX.XXX/XXX.XXX.XXX.XXX

AIX: list top 10 processes consume memory

2010-08-16T04:02:00.000-07:00

ps aux | head -1; ps aux | sort -rn +5 | head -10

AIX mount-point hdisk information

2010-08-13T03:57:00.000-07:00

In order to find relations between mount point and disks follow this procedure:
1-) Find logical volume groups:
lsvg
2-) Find which mount points are belong to which logical volume:
lsvg -l {volume group}
3-) Find which hdisks are belong to logical volume
lslv -l {logical volume}
4-) Finally find storage serial and other information
lscfg -vl {hdisk}

11GR2: Creating Physical Standby on ASM usign Active Database Duplicate

2010-07-16T00:28:00.000-07:00

You can follow official documents:
e10642.pdf: Backup and Recovery User's Guide page:24-1 "Duplicating a Database"
e10700.pdf: Data Guard Concepts and Administration page: F-4 "Using the DUPLICATE Command to Create a Standby Database"
e10643.pdf: "Backup and Recovery Reference "

Prepare Auxiliary database

#Create Password File for the Auxiliary Instance
$orapwd file=$ORACLE_HOME/dbs/orapwMYSID password=xxxx entries=5

#Create a static listener (single-instance)

LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = myserverip)(PORT = 1521)(IP = FIRST))
))
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle_home_path)
(SID_NAME = MYSID)
))

#Create necessary folders and prepare paths for new database:
ORACLE_BASE
ORACLE_BASE\DBNAME
ORACLE_BASE\DBNAME\adump
ORACLE_BASE\DBNAME\dpdump
ORACLE_BASE\DBNAME\hdump
ORACLE_BASE\DBNAME\pfile
ORACLE_BASE\DBNAME\scripts

Normally documents say that you need to create a pfile including only db_name parameter at Auxiliary site. Then you can start instance with nomount mode and Auxiliary configuration is done. However, at later steps you may have troupble with rman dublicate command including SPFILE configuration parameters (like me):

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
SPFILE
....
SET "db_unique_name"="..."
SET DB_CREATE_FILE_DEST "+ASMDISK";

An other choice, you can manually create Auxiliary spfile and set all spfile parameters here instead of rman duplicate command. Later we will use only command to create standby....
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
Here is my previously created pfile at Auxiliary database:

MYDBNAME.__db_cache_size=XXXXXXXX
MYDBNAME.__java_pool_size=XXXXXXXX
MYDBNAME.__large_pool_size=XXXXXXXX
MYDBNAME.__oracle_base='/oracle_base_path'#ORACLE_BASE set from environment
MYDBNAME.__pga_aggregate_target=XXXXXXXX
MYDBNAME.__sga_target=XXXXXXXX
MYDBNAME.__shared_io_pool_size=0
MYDBNAME.__shared_pool_size=XXXXXXXX
MYDBNAME.__streams_pool_size=XXXXXXXX
*.audit_file_dest='/adump_path'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/db_ctrl_path/ctrl1.ctl','/db_ctrl_path/ctrl2.ctl'
*.db_block_size=XXXXXXXX
*.db_create_file_dest='+ASMDISK'
*.db_domain=''
*.db_file_multiblock_read_count=XXXXXXXX
*.db_name='MYDBNAME'
*.db_recovery_file_dest='+ASMRECODISK'
*.db_recovery_file_dest_size=XXXXXXXX
*.db_unique_name='MYDBNAME'
*.diagnostic_dest='/oracle_base_path'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBNAME2XDB)'
*.memory_max_target=XXXXXXXX
*.memory_target=XXXXXXXX
*.open_cursors=XXXXXXXX
*.processes=XXXXXXXX
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=XXXXXXXX
*.service_names='MYDBNAME',...
*.sessions=XXXXXXXX
*.sort_area_size=XXXXXXXX
*.undo_retention=XXXXXXXX
*.undo_tablespace='MYUNDOTS'

Note: I dont suggest using "db_file_name_convert" or "log_file_name_convert" parameters. If you use them both with db_create_file_dest parameter, then datafiles and logfiles are created at paths "{db_create_file_dest}/{db_unique_name}/DATAFILE", "{db_create_file_dest}/{db_unique_name}/ONLINELOG" and aliases are created at paths "{db_file_name_convert}", "{log_file_name_convert}".
Note: Because db_unique_name is used as a template parameter for asm paths, chose it wisely. Then It will be difficult to move them and rename in controlfile. I suggest use DBNAME as unique name. However both primary and standby can not have same db_unique_name so decide what to set which. If you decide to change db_unique_name of the primary, it resuires restart...

When your pfile is ready, first startup instance with nomount option using this pfile and then create spfile. Then shutdown, startup again with nomount option, this time using spfile.

Prepare Source database and run RMAN DUPLICATE

Before beginning duplication, force database logging:

ALTER DATABASE FORCE LOGGING;

You need to configure sql net connections for both source and auxiliary databases. Here is the tnsnames.ora:

MYDBNAMEPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodhost)(PORT = 1521))
(CONNECT_DATA = (SID = MYSID))
))

MYDBNAMESTBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stbyhost)(PORT = 1521))
(CONNECT_DATA =(SERVER = DEDICATED)(SID = MYSID))
)

You should be able to connect both instance with sys as sysdba through sql.net:

sqlplus /nolog
SQL> connect SYS@MYDBNAMEPROD as sysdba
SQL> connect SYS@MYDBNAMESTBY as sysdba

If you could not connect, you should correct this before proceeding.

Now connect rman and start duplicate:

$rman
RMAN> CONNECT TARGET SYS@MYDBNAMEPROD
target database Password:
connected to target database: MYDBNAME (DBID=XXXXXX)

RMAN> CONNECT AUXILIARY SYS@MYDBNAMESTBY
auxiliary database Password:
connected to auxiliary database: MYDBNAME (not mounted)

RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> FROM ACTIVE DATABASE
4> ;
....

Note: If datafiles are created with aliases and you decide to remove aliases, dont use "rm" command of asmcmd. It will remove both alias and file itself. Instead, use "rmalias". I removed a file accidently by removing alias with rm command and could not find a way to get that single datafile from source directly into auxillary asm. If you have a filesystem drive at auxillary big enough to hold that datafile, then there seems a way...

Prepare Dataguard Configuration

Put standby control files into ASM:

First of all, prepare paths for controlfiles. If necessary, create asm directories.

SQL> shutdown immediate

ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount

ORACLE instance started.
Total System Global Area 2.6724E+10 bytes
Fixed Size 2219408 bytes
Variable Size 1.2885E+10 bytes
Database Buffers 1.3824E+10 bytes
Redo Buffers 12148736 bytes
SQL> exit

$rman target /

RMAN> restore controlfile to '+ASMDISK' from '/db_ctrl_path/ctrl1.ctl';
RMAN> restore controlfile to '+ASMRECODISK' from '/db_ctrl_path/ctrl2.ctl';

Check that new control files are created at paths:
+ASMDISK/MYDBNAME/CONTROLFILE
+ASMRECODISK/MYDBNAME/CONTROLFILE

Go to asm and find out newly created control files:
+ASMDISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX

+ASMRECODISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX

Then update control_files and mount database:

sqlplus '/ AS SYSDBA'
SQL> alter system set control_files='+ASMDISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX',
'+ASMRECODISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX' scope=spfile;
SQL> startup mount

Follow this procedure to setup dataguard:
http://erkanorclog.blogspot.com/2008/03/setting-up-dataguard-configuration-for.html

Configure dynamic (local) listener:

You may want to register standby with local listener :

ALTER SYSTEM SET LOCAL_LISTENER='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mystbyhost)(PORT=1521))))' scope=both;

Check and see if local listener registration is ok. Change "MYDBNAMESTBY" tns at primary side to use this new listener configuration. If it is ok and you see dataguard is working fine then you can unset static listener configuration created previously.

Using Sql loader (sqlldr.exe)

2010-07-15T23:21:00.000-07:00

Here is an example of using sql loader to load data from flat file to oracle table. In order to solve input-client time format conflicts, time format for timestamp columns specified manually in parameter file.

C:\>set NLS_LANG=TURKISH_TURKEY.TR8MSWIN1254
C:\>sqlldr myuser@mydb control=loader.ctl

loader.ctl:
load data

infile 'myinputfile'
into table MYSCHEMA.MYTABLE
fields terminated by "\t" OPTIONALLY ENCLOSED BY '"'
(COLUMN1, TIMESTAMP_COLUMN TIMESTAMP "DDMONYYYY:HH24:MI:SS", COLUMN2, COLUMN3...)

How to change DBNAME & SID without changing DBID

2010-07-15T02:21:00.000-07:00

This procedure does not require resetlogs so previously taken backups will be valid after this operation. However database restart is needed at some points so maintenance with downtime should be scheduled for this operation.

You may follow “Changing Only the Database Name” topic at official document:

http://download.oracle.com/docs/cd/B14117_01/server.101/b10825/dbnewid.htm#i1004734

$cd $ORACLE_HOME/dbs
#backup old initora file
$mv initMYOLDSID.ora initMYOLDSID.ora.org

#create pfile from spfile
$sqlplus '/ AS SYSDBA'
SQL> create pfile from spfile;
SQL> exit

#replace all old dbname parameters with new dbname in pfile. Becareful, if you change some path variables, make sure that new path (including new dbname) also exists on system. If not, create manually.
$vi initMYOLDSID.ora
vi=> :%s/MYOLDDBNAME/MYNEWDBNAME/g

vi=> :wq

#now database goes offline...
$sqlplus '/ AS SYSDBA'
SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT
SQL> exit

#change dbname using nid. Output is similar to fallowing...
$nid TARGET=/ DBNAME=MYNEWDBNAME SETNAME=YES
DBNEWID: Release 11.2.0.1.0 - Production on ....

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to database MYOLDDBNAME (DBID=XXXXXXXXX)

Connected to server version 11.2.0

Control Files in database:
/db_ctrl_path/ctrl1.ctl
/db_ctrl_path/ctrl2.ctl

Change database name of database MYOLDDBNAME to MYNEWDBNAME? (Y/[N]) => Y

Proceeding with operation
Changing database name from MYOLDDBNAME to MYNEWDBNAME
Control File /db_ctrl_path/ctrl1.ctl - modified
Control File /db_ctrl_path/ctrl2.ctl - modified
Datafile /db_data_path/file1.db - wrote new name
Datafile /db_data_path/file2.db - wrote new name
Datafile /db_data_path/file3.db - wrote new name
...
Control File /db_ctrl_path/ctrl1.ctl - wrote new name
Control File /db_ctrl_path/ctrl2.ctl - wrote new name
Instance shut down

Database name changed to MYNEWDBNAME.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

#Change ORACLE_SID environment variable
$vi $HOME/.profile
vi=> change ORACLE_SID from MYOLDSID to MYNEWSID
$. $HOME/.profile

$env|grep SID
ORACLE_SID=MYNEWSID

#Create new password file and rename pfile..
$orapwd file=$ORACLE_HOME/dbs/orapwMYNEWSID password=xxxx entries=5

$mv initMYOLDSID.ora initMYNEWSID.ora

#Start database with new pfile and create spfile.
$sqlplus '/ AS SYSDBA'
SQL> startup pfile=oracle_home_path/dbs/initMYNEWSID.ora
ORACLE instance started.
…
SQL> select name from v$database;
NAME
---------
MYNEWDBNAME

SQL> create spfile from pfile;

#Note that database is opened using pfile so no spfile operation will be allowed. You should restart database again without pfile parameter to open it with spfile.

ODI: connection to repository failed

2010-07-12T00:57:00.000-07:00

If connection string for the repository database has been changed, you may face following error while connecting to repository altough you have changed url parameter of client tool:

java.sql.SQLException: Io Exception: The Network Adapter could not establish the connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:274)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:328)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:361)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:151)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:595)
at com.sunopsis.sql.SnpsConnection.u(SnpsConnection.java)
at com.sunopsis.sql.SnpsConnection.c(SnpsConnection.java)
at com.sunopsis.sql.h.run(h.java)

This is because, altough tool connects to repository successfully, it retrieves repository connection string from repository database and reconnects using it (or tries to open a new connection with it and fails). As a result, you can not connect and correct repository connection parameter.

I solved this problem by manually logging into repository database and updating repository connection parameter:

select TXT from snp_mtxt_part where i_txt=( select I_TXT_JAVA_URL from SNP_CONNECT where CON_NAME='MY_PROD_WORK_REP_NAME')

update snp_mtxt_part set TXT='{correct repository connection string}' where i_txt=....;

Creating New 11gr2 database on existing 11gr2 RAC

2010-07-05T06:08:00.000-07:00

It is assumed that 11gr2 cluster has been installed. ASM is configured and disks are prepared for new database.

1-) Create new OS user for new database. New user should be in the same group with grid's OS user (Using different os users for databases is optional, not a must)

2-) Set ssh trust between the nodes for the newly created os user:

From each node, logged in as new user:

mkdir ~/.ssh
chmod 755 ~/.ssh
/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa

Example:
$ mkdir ~/.ssh
$ chmod 755 ~/.ssh
$ /usr/bin/ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (.../.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:

Your identification has been saved in .../.ssh/id_rsa.
Your public key has been saved in .../.ssh/id_rsa.pub.
The key fingerprint is:
XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX os_user@my.domain.com

$ /usr/bin/ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (.../.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:

Your identification has been saved in .../.ssh/id_dsa.
Your public key has been saved in .../.ssh/id_dsa.pub.
The key fingerprint is:
XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX os_user@my.domain.com

Append all public keys to authorized_keys of all nodes:

FIRST NODE

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
chmod 644 ~/.ssh/authorized_keys

SECOND NODE

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node1 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node1 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
chmod 644 ~/.ssh/authorized_keys

ssh command run at previous step will register each node's ssh identity to the known_hosts file at other node (confirmation appeared when you run ssh first). However, every node should also has a know_hosts entry for itself. In order to do that, run following command at each node and answer yes at confirmation.

ssh os_user@nodeitself ls

Finally, check that each node does a ssh ls to orher node without requiring any password or confirmation.

ssh os_user@othernode ls
ssh os_user@nodeitself ls

3-) edit user profile and set following variables:

ORACLE_HOME

RDBMS_HOME
ASM_HOME
CRS_HOME
ORACLE_BASE
PATH
ORACLE_SID
LD_LIBRARY_PATH
4-) Check permisions of ORACLE HOME and BASE so new user has write permission on these (on both nodes)
5-) Set new environments paramaters on both nodes.
6-) Start Installer and install software only... Create database? maybe next time...

System Wide Stats Gathering

2010-07-01T07:44:00.000-07:00

It is recommended to run gather statistics reqularly, especially after upgrades or running catalog scripts:

exec DBMS_STATS.GATHER_DICTIONARY_STATS();

exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();

Also it is recommended to gather hardware based statistics after database first created or a hardware maintenance has been done

exec DBMS_STATS.gather_system_stats('start');
exec DBMS_STATS.gather_system_stats('stop');

You may query aux_stats$ system view to see system stat results:

select * from aux_stats$

Oracle 11GR2 SCAN, ORA-12502: TNS:listener received no CONNECT_DATA from client

2010-07-01T03:39:00.000-07:00

While local_listener parameter of RAC database was something like this:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node-vip)(PORT=1521))))

connecting to database using SCAN listener gave following error (after a short period of hang time)

ORA-12502: TNS:listener received no CONNECT_DATA from client

After setting local_listener as:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node-vip.mycompany.com)(PORT=1521))))

problem resolved...

Oracle 11gR2 RAC and crs_stat

2010-07-01T00:49:00.000-07:00

In Oracle 11gR2 RAC cluster, crs_stat does not show database instance (node,online,offline) information any more. In order to get this information, you should use srvctl commands...

example: mydb2 is offline at mynode2

$crs_stat -t (no information at output)
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE mynode1
ora.DATA10G.dg ora....up.type ONLINE ONLINE mynode1
ora....ER.lsnr ora....er.type ONLINE ONLINE mynode1
ora....N1.lsnr ora....er.type ONLINE ONLINE mynode2
ora....N2.lsnr ora....er.type ONLINE ONLINE mynode1
ora....N3.lsnr ora....er.type ONLINE ONLINE mynode1
ora....TING.dg ora....up.type ONLINE ONLINE mynode1
ora.RECO.dg ora....up.type ONLINE ONLINE mynode1
ora.asm ora.asm.type ONLINE ONLINE mynode1
ora.eons ora.eons.type ONLINE ONLINE mynode1
ora....SM1.asm application ONLINE ONLINE mynode1
ora....A1.lsnr application ONLINE ONLINE mynode1
ora....ca1.gsd application OFFLINE OFFLINE
ora....ca1.ons application ONLINE ONLINE mynode1
ora....ca1.vip ora....t1.type ONLINE ONLINE mynode1
ora....SM2.asm application ONLINE ONLINE mynode2
ora....A2.lsnr application ONLINE ONLINE mynode2
ora....ca2.gsd application OFFLINE OFFLINE
ora....ca2.ons application ONLINE ONLINE mynode2
ora....ca2.vip ora....t1.type ONLINE ONLINE mynode2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE mynode1
ora.oc4j ora.oc4j.type OFFLINE OFFLINE
ora.ons ora.ons.type ONLINE ONLINE mynode1
ora.mydb.db ora....se.type ONLINE ONLINE mynode1
ora.scan1.vip ora....ip.type ONLINE ONLINE mynode2
ora.scan2.vip ora....ip.type ONLINE ONLINE mynode1
ora.scan3.vip ora....ip.type ONLINE ONLINE mynode1

$srvctl status database -d mydb
Instance mydb1 is running on node mynode1
Instance mydb2 is not running on node mynode2

DUMP_CVS: Procedure to dump table data into csv

2010-04-29T01:53:00.000-07:00

Here is a procedure which can be used to dump table data into csv format.

CREATE OR REPLACE PROCEDURE MYSCHEMA.dump_csv( p_query in varchar2, p_separator in varchar2 default ',', p_dir in varchar2 , p_filename in varchar2 )
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );

dbms_sql.parse( l_theCursor, p_query,
dbms_sql.native );

for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;

dbms_sql.define_column( l_theCursor, 1,
l_columnValue, 2000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
utl_file.put( l_output,
l_separator || l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
dbms_output.put_line( to_char(l_cnt) ||' rows exported into '||p_filename||' file...' );
end dump_csv;
/

ORA-39126 and ORA-01017 while executing dbms_datapump import over database link

2010-04-28T23:57:00.000-07:00

I hit this problem when executing DBMS_DATAPUMP IMPORT operation over database link. Here is the full error message:

Starting "SYS"."import_MYTASK":
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.POST_MT_INIT [SELECT SYS.KUPM$MCP.GET_ENDIANNESS@MYDBLINK FROM SYS.DUAL]
ORA-04052: error occurred when looking up remote object SYS.KUPM$MCP@MYDBLINK
ORA-00604: error occurred at recursive SQL level 3
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from MYDBLINK
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164
----- PL/SQL Call Stack -----
object line object
handle number name
70000058f38e5b8 19028 package body SYS.KUPW$WORKER
70000058f38e5b8 8191 package body SYS.KUPW$WORKER
70000058f38e5b8 2072 package body SYS.KUPW$WORKER
70000058f38e5b8 1438 package body SYS.KUPW$WORKER
70000058e31ea78 2 anonymous block
Job "SYS"."import_MYTASK" stopped due to fatal error at 09:33:35

Database link was working fine when i query manually. Restarting database solved the problem however i dont know what was the cause...

How to generate move/rebuild script to migrate partitioned tables from one tablespace to other

2010-04-24T11:02:00.000-07:00

declare
cflag number;
oldDataTablespaceName varchar2(100);
newDataTablespaceName varchar2(100);
oldIndexTablespaceName varchar2(100);
newIndexTablespaceName varchar2(100);
parallel_level varchar2(2);
begin
DBMS_OUTPUT.ENABLE(1000000);
-------------------- SET VARIABLES ----------------------------
oldDataTablespaceName:=OLD_TABLESPACE';
newDataTablespaceName:='NEW_TABLESPACE';
newIndexTablespaceName:='NEW_INDEX_TABLESPACE';
parallel_level:='8';
---------------------------------------------------------------
dbms_output.put_line('alter session enable parallel ddl;');
for l in ( select table_owner, table_name, partition_name, partition_position from dba_tab_partitions where tablespace_name=oldDataTablespaceName order by table_owner, table_name, partition_position )
loop
cflag := 0;
dbms_output.put_line('alter table '||l.table_owner||'.'||l.table_name||' move partition "'||l.partition_name||'" tablespace '||newDataTablespaceName||' parallel '||parallel_level||';');
for k in (select dip.index_owner, dip.index_name, dip.partition_name from dba_ind_partitions dip, dba_indexes di where di.table_owner=l.table_owner and di.table_name=l.table_name and di.owner=dip.index_owner and di.index_name=dip.index_name and dip.partition_position=l.partition_position)
loop
dbms_output.put_line('alter index '||k.index_owner||'.'||k.index_name||' rebuild partition "'||k.partition_name||'" tablespace '||newIndexTablespaceName||' parallel '||parallel_level||';');
end loop;
end loop;
END;
/

Recover Database with allow corruption

2010-04-22T23:11:00.000-07:00

Here is an example of block corruption faced during database recovery:

recover database;

RA-00600: internal error code, arguments: [3020], [48], [41103361], [41103361], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 48, block# 41103361)
ORA-10564: tablespace MYTS
ORA-01110: data file 48: '+MYDISC/mydb/datafile/myts.14714.699899641'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 323837
Errors in file /mytracepath/mydb_pr06_4728.trc:

You can skip recovery of this corrupted block by running following command:

recover database datafile '+MYDISC/mydb/datafile/myts.14714.699899641' allow 1 corruption;

If there are more then 1 corruption, you may repeat this step or run command with allow many corruption. However, dont forget to note corrupted file and block numbers. After opening database, we will use following query to find out to which segment corrupted block belogns to:

SELECT tablespace_name, segment_type, owner, segment_name

FROM dba_extents WHERE file_id = 48 and 41103361 between block_id AND block_id + blocks - 1;

Log Miner

2009-10-07T23:22:00.001-07:00

These are steps to use log miner:

1-) Execute following PL/SQL block as sys user (note that first dbms_logmnr.add_logfile procedure is called with dbms_logmnr.new argument while others are called with dbms_logmnr.addfile argument):

begin
sys.dbms_logmnr.add_logfile (logfilename => '{oracle log file name}',options=>sys.dbms_logmnr.new);
sys.dbms_logmnr.add_logfile (logfilename => '{oracle log file name}',options=>sys.dbms_logmnr.addfile);
...
sys.DBMS_LOGMNR.START_LOGMNR(options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
end;

2-) You can select log content using following query:

select * from V$LOGMNR_CONTENTS;

3-) Because content is read from file sequencely, it is very slow. If you are planning to query it multiple times, i strongly suggest you to insert log miner data to a local table and create an index on timestamp column. Requested storage for local table and its index is usually at around 4*(log size)*(number of log files).

create table LOGMNR TABLESPACE TOOLS as select * from V$LOGMNR_CONTENTS;
CREATE INDEX LOGMNR_IX1 ON LOGMNR ("TIMESTAMP") TABLESPACE TOOLS ;

4-) After you finish with log miner, execute following query:

begin
sys.dbms_logmnr.end_logmnr;
end;

How to find remote session executing over a database link

2009-10-01T01:23:00.000-07:00

Follow this procedure to find remote session created by local session executing query over database link:

1-) Find local session id from v$session

2-) Execute following script on both local and remote databases:

Select /*+ ORDERED */
s.sid,substr(s.username,1,15),substr(s.ksusemnm,1,10)'-' substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)'.' substr(s.ksuseser,1,5) "LSESSION" ,
substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED'),1,1) "S",
substr(w.event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w ,v$session s
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx
and w.sid=s.sid
/

3-) From the script output on local database find GTXID value for local session
4-) From the script output on remote database, search for a GTXID value matches the one we find on step 3. When you find it, sid column of the same row is the session we are looking for on remote database.

Another method is using lsof (assuming it is a unix server)

1-) Find process id of the local session

select spid from v$session s ,v$process p where s.paddr=p.addr and s.sid={local_sid}

2-) On the server (running local database) find out TCP connections of the session

lsof -p {process id}

3-) Search for a line containing remote server name (or IP) in output of step 2. When you find it, take destination port number
4-) On remote server run following command to find oracle process listening on this port

lsof -i tcp:{port_number}

5-) Now you can find remote session by running following script on remote database:

select s.sid from v$session s ,v$process p where s.paddr=p.addr and spid={oracle_process_id}

How to determine database segments which are participated in global cache wait events.

2009-07-13T06:43:00.000-07:00

Here is a sql script to determine database segments which are participated in global cache wait events.

select /*+ rule */
ash.sql_id, de.owner, de.segment_name, de.segment_type,
ash.event,
sum(ash.time_waited) total_time,
count(*) count,
trunc(sum(ash.time_waited)/count(*)) Avg_wait
from
gV$ACTIVE_SESSION_HISTORY ash,
dba_extents de
where
ash.event like 'gc%' and
ash.P1TEXT='file#' and
ash.P2TEXT='block#' and
ash.p1=de.file_id and
ash.time_waited > 0 and
ash.p2 between de.block_id AND (de.block_id+de.blocks-1)
group by ash.sql_id,de.owner, de.segment_name, de.segment_type, ash.event
order by 6 desc;

How to move/rename a datafile while database is online but tablespace is required to be taken offline

2009-04-09T01:41:00.000-07:00

1-) First take tablespace offline to which datafile belongs:

ALTER TABLESPACE MYTABLESPACE OFFLINE NORMAL;

2-) Move datafile to new location

mv /datadir/datafile.dbf /newdatadir/datafile.dbf

3-) Rename datafile name in database

ALTER TABLESPACE MYTABLESPACE RENAME DATAFILE '/datadir/datafile.dbf' TO '/newdatadir/datafile.dbf';

4-) Bring tablespace online:

ALTER TABLESPACE MYTABLESPACE ONLINE;

Searching dba_views giving text condition

2009-04-01T05:52:00.001-07:00

If you try to run a query like this:

SQL>select * from dba_views where text like '%some text here%';

following error occurs:

select * from dba_views where text like '%some text here%'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

You can use following function to search dba_views giving text condition:

create or replace
FUNCTION SEARCH_VIEW_TEXT( in_owner varchar,in_view_name varchar, in_search_str
varchar)
RETURN INTEGER IS
w_crs integer;
r_val integer;
num_bytes INTEGER := 32000;
offset INTEGER := 0;
out_val VARCHAR2(32000);
out_length INTEGER;
begin
w_crs := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(w_crs, 'select text from dba_views where owner=:OWNR and view_name=:VNAME ',dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(w_crs, ':OWNR', in_owner);
DBMS_SQL.BIND_VARIABLE(w_crs, ':VNAME', in_view_name);
DBMS_SQL.DEFINE_COLUMN_LONG(w_crs,1);
r_val := DBMS_SQL.EXECUTE(w_crs);
r_val := DBMS_SQL.FETCH_ROWS(w_crs);
LOOP
DBMS_SQL.COLUMN_VALUE_LONG(w_crs, 1, num_bytes, offset, out_val, out_length);
IF (INSTR(UPPER(out_val),upper(in_search_str))>0) THEN
DBMS_SQL.CLOSE_CURSOR(w_crs);
RETURN 1;
END IF;
offset := offset+num_bytes;
IF out_length < num_bytes THEN
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(w_crs);
RETURN 0;

EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(w_crs) then
DBMS_SQL.CLOSE_CURSOR(w_crs);
END IF;
RAISE;
END;

SQL> select * from dba_views where owner not in ('SYS','SYSTEM') and SEARCH_VIEW_TEXT(owner, view_name, 'some text here') = 1

ORA-02449: unique/primary keys in table referenced by foreign keys

2009-03-31T01:03:00.000-07:00

When you hit this error, it means that you can not drop(modify) current table (or index) because there are some foreign keys defined on some other tables referencing unique/primary keys of the current table. You should drop these foreign keys in order to drop/modify current table/index.

SQL script to find out these foreign keys:

select * from dba_constraints where R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from dba_constraints where owner='MYOWNER' and table_name='MYTABLE') and CONSTRAINT_TYPE='R';

How to Create Function Based Index

2009-03-11T01:37:00.000-07:00

Here is an example of creating function based index setting null values to default values:

CREATE INDEX ERKAN.MYTABLE_FUNC_IX on ERKAN.MYTABLE (COL1, COL2,nvl(COL3,COL1), nvl(COL4,to_date('01/01/2009 00:00:00','DD/MM/YYYY hh24:mi:ss')), nvl(COL5,0))
tablespace MYTABLESPACE;

How to trace session(s)

2009-03-05T23:59:00.000-08:00

Using DBMS_SUPPORT package

Starting Trace:
Alter system set max_dump_file_size=unlimited;
exec sys.dbms_support.START_TRACE_IN_SESSION(sid,serial#,waits=>TRUE, binds=>TRUE ) ;

Stopping Trace:
Exec sys.DBMS_SUPPORT.STOP_TRACE_IN_SESSION( sid , null )
Alter system set max_dump_file_size=102400;

Generating Start-Stop Trace script for sessions (trace.sql):

ACCEPT USERNAME char prompt 'Enter the User Name > '
variable b0 varchar2(50)
exec :b0 := upper('&USERNAME');

select '------------ START TRACE ------------' TRACE_SCRIPT from dual
union all
select 'Alter system set max_dump_file_size=unlimited;' TRACE_SCRIPT from dual
union all
select 'exec sys.dbms_support.START_TRACE_IN_SESSION('||sid||','||serial#||',waits=>TRUE,binds=>TRUE ) ;' TRACE_SCRIPT from v$session where username=:b0
union all
select '------------ STOP TRACE ------------' TRACE_SCRIPT from dual
union all
select 'Exec sys.DBMS_SUPPORT.STOP_TRACE_IN_SESSION('||sid||',null);' TRACE_SCRIPT from v$session where username=:b0
union all
select 'Alter system set max_dump_file_size=102400;' TRACE_SCRIPT from dual
/

Using Oradebug

Find server process id of the session:

select spid from v$session s ,v$process p where s.paddr=p.addr and s.sid=&sid

Starting Trace:
sqlplus '/ AS SYSDBA'
SQL> oradebug setospid {spid}
Oracle pid: {pid}, Unix process pid: {spid}, image: oracle@... (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.

Stopping Trace:
SQL> oradebug event 10046 trace name context off
Statement processed.

How to create new service name

2009-03-05T22:56:00.000-08:00

In order to define a new service name:

1-) Modify service_names parameter and add new service name:
if spfile is used then
alter system set service_names='..., {new_service_name}' scope=both;
if init ora file is used then

alter system set service_names='..., {new_service_name}';
modify init ora file and add {new_service_name} to service_names parameter

2-) Add following SID description to listener configuration (listener.ora)
SID_LIST_... =
( SID_LIST=
(SID_DESC =
(ORACLE_HOME =...)
(SID_NAME = ...))
....
(SID_DESC =
(GLOBAL_DBNAME={new_service_name})
(SID_NAME = {oracle_sid})
(ORACLE_HOME ={oracle_home}))
)

3-) Restart listener.

Now you may connect to database using following tnsnames.ora configuration:

{tnsname}.WORLD = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(Host = {host_name})(Port = {port}))(CONNECT_DATA =(SERVICE_NAME = {new_service_name})))

After login if you query v$session system view, you will see new service name in SERVICE_NAME column for your session.

Commit from a Procedure/Function Executed Through a Database Link, ORA-02064: distributed operation not supported

2008-10-30T03:04:00.000-07:00

It is not supported to issue a commit from a procedure with OUT parameters or a function with return value, when the procedure/function is executed through a database link.

Here is a test case, working fine:

1-) At remote database create following procedure:

create or replace procedure erkan_trunc

begin

insert into XXX values(1,1,'erkan saka'); -- Create a transaction at remote db

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX'; -- This is a DDL! which also autocommits

end;

2-) At source database create following procedure

create or replace procedure erkan_dene

begin

insert into erkan.A values(1); -- Create a transaction at local (source) db

erkan_trunc@{db_link}; -- remote procedure call

rollback; -- End local transaction

end;

3-) Working fine:

SQL>exec erkan_dene

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Here is an other test case with ORA-02064: distributed operation not supported:

1-) At remote database create following procedure:

create or replace function erkan_trunc2

RETURN NUMBER -- This is the reason causing ORA-02064

begin

insert into XXX values(1,1,'erkan saka');

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX';

RETURN (0);

end;

2-) At source run following PL/SQL

SQL>declare

2 de_return number;

3 begin

4 de_return := erkan_trunc2@{db_link};

5 dbms_output.put_line('de_return='||de_return);

6 end;

7 /

declare

ERROR at line 1:

ORA-02064: distributed operation not supported

ORA-06512: at "ERKAN.ERKAN_TRUNC2", line 6

ORA-06512: at line 4

Elapsed: 00:00:00.01

ORA-02064: distributed operation not supported

2008-10-07T07:09:00.000-07:00

If a commit/rollback or autocommit(DDL) operation is executed within a procedure or function which is called remotely via database link then ORA-02064 may occur. Exact explanation is:

It is not supported to issue a commit from a procedure with OUT parameters or a function with return value, when the procedure/function is executed through a database link.

I tested following two senarios. First works fine while second fails with ORA-02064:

Senario 1-)

* Create following procedure at remote database:

create or replace procedure erkan_trunc

begin

insert into XXX values(1,1,'erkan'); -- in order to create a local transaction at remote database

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX'; -- autocommits

end;

* Create following procedure at source database:

create or replace procedure erkan_dene

begin

insert into erkan.A values(1); --in order to create a local transaction in source database

BEGIN

erkan_trunc@remote_tns;

EXCEPTION

WHEN OTHERS

THEN

dbms_output.put_line('Error while truncate: ' || SUBSTR (SQLERRM, 1, 100););

END;

rollback;

end;

* Works fine:

SQL>exec erkan_dene

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

if i hold lock on table XXX at remote database...

SQL>exec erkan_dene

Error while truncate: ORA-00054: resource busy and acquire with NOWAIT specified

ORA-06512: at "ERKAN.ERKAN_TRUNC", line 5

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Senario 2-)

* Create following function at remote database:

create or replace function erkan_trunc2

RETURN NUMBER

begin

insert into XXX values(1,1,'erkan');

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX';

RETURN (0);

end;

* Run following pl/sql script at source database:

SQL>declare

2 de_return number;

3 begin

4 de_return := erkan_trunc2@remote_tns;

5 dbms_output.put_line('de_return='||de_return);

6 end;

7 /

declare

ERROR at line 1:

ORA-02064: distributed operation not supported

ORA-06512: at "ERKAN.ERKAN_TRUNC2", line 6

ORA-06512: at line 4

Elapsed: 00:00:00.01

ORA-38029: object statistics are locked

2008-09-16T23:52:00.000-07:00

If this error occurs while trying to analyze a table then run following to unlock the statistics:

exec DBMS_STATS.UNLOCK_TABLE_STATS('{owner}','{table name}');

You can see list of all locked tables by running following query:

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

or

select 'exec DBMS_STATS.UNLOCK_TABLE_STATS('''||owner||''','''||table_name||''');' from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM')

For further detail, refer to metalink doc id 433240.1

LOCALLY MANAGED TABLESPACE with SPACE MANAGEMENT AUTO

2008-09-10T04:44:00.000-07:00

CREATE TABLESPACE DATA_1M DATAFILE
'/data01/dbfs/data_1m01.dbf' SIZE 2048M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
/

alter tablespace DATA_1M add datafile '/data01/dbfs/data_1m02.dbf' SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 2048M;

alter database datafile '/data01/dbfs/data_1m02.dbf' AUTOEXTEND ON NEXT 1024M MAXSIZE 3072M;

ARCHIVED_SEQ# column of v$ARCHIVE_DEST_STATUS is not being updated

2008-08-29T03:54:00.000-07:00

Check and correct fal_client and fal_server system parameters at standby database:

alter system set fal_client= scope=both;
alter system set fal_server= scope=both;

Dataguard Swithover

2008-08-28T23:55:00.000-07:00

switchover commands:

Prod Site:
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

Standby Site:
alter database commit to switchover to primary;
alter database open ;

Old Prod Site:
SHUTDOWN;
STARUP MOUNT;
alter database recover managed standby database using current logfile disconnect from session;

Identity (Autoincrement) Column on Oracle

2008-08-18T06:51:00.000-07:00

You can do this by using a sequence and before insert trigger on oracle:

create table ABC (id int, data varchar2(200));

create sequence INCABC
start with 1
increment by 1
maxvalue 999999999999
minvalue 1
nocyclecache 10
order;

create or replace trigger ABC_IDENTITY
before insert on ABC
FOR EACH ROW
begin
select INCABC.nextval into :new.id from dual;
end;
/

However in order to insert table, all column names except "id" should be provided in insert statement like:

insert into ABC (data) values ('abc');

or a dummy value should be provided for "id" column:

insert into abc values('abc',5);

other case following error occurs:

SQL> insert into abc values('abc');
insert into abc values('abc')
ERROR at line 1:
ORA-00947: not enough values

sqlplus / as sysdba, ORA-01031: insufficient privileges

2008-08-14T05:09:00.000-07:00

Follow following steps to correct problem:
1-) go to $ORACLE_HOME/rdbms/lib
2-) correct dba OS user group name in config.s file:
a) type "id" command in shell in order to get group name of your OS user
b)correct group name in config.s file. It is written just after the ".string"
.csect H.12.NO_SYMBOL{RO}, 3
.string "dbat"
# End csect H.12.NO_SYMBOL{RO}
.csect H.14.NO_SYMBOL{RO}, 3
.string "dbat"
# End csect H.14.NO_SYMBOL{RO}
c) run following commands to backup & compile:
mv config.o config.o.org
make -f ins_rdbms.mk ioracle

Note: sometimes dba OS dba group is correct in config.s file so you dont need to change anything and compile (next step) solves the problem.

Error: OiifbStackLayout: can't understand "FİLL LEFT TOP"

2008-08-14T01:51:00.000-07:00

This error may occur while running oracle installer if LANG environment variable is set to tr_TR. When this problem occurs, you see curruptions on GUI screen. Running following command before running installer corrects the problem:

export LANG=en_EN

How to correct UNNAMED{nnnnn} datafile problem

2008-04-28T23:02:00.000-07:00

When standby_file_management is auto and file path which is added to the primary database does not exist on standby site, then standby database creates a dummy datafile such as

{path}/UNNAMEDnnnnn

To correct this problem, you should set standby_file_management to manual and create standby datafile :

alter system set standby_file_management=manual;
alter database create datafile '{path}/UNNAMEDnnnnn' as '{standby file path}/{data file name}.dbf' size xxxM;

How to change oracle dba group in unix

2008-04-17T05:08:00.000-07:00

cd $ORACLE_HOME/rdbms/lib
cat config.[cs]
The line '#define SS_DBA_GRP "group"' should name the chosen DBA group. If you wish to change the DBA group change the group name shown in this file. Eg: Change:
#define SS_DBA_GRP "dba" to:
#define SS_DBA_GRP "mygroup"
To effect any changes to the DBA group and to be sure you are using the group defined in this file relink the Oracle executable as below. Be sure to shutdown all databases before relinking:
rm config.o
make -f ins_rdbms.mk config.o ioracle

Getting sql text from hash value

2008-04-16T04:16:00.000-07:00

Here is a function and sql script which uses this function to display sql text from its hash value:

create or replace function user.hashsql(hnum number) return user.sqltable PIPELINED is
sqlline varchar2(64);
sqlt user.sqlline:=user.sqlline('');
tmptxt varchar(2000);
ln int;
ind int;
cursor cor_sql is
select SQL_TEXT from v$sqltext_with_newlines where HASH_VALUE=hnum order by PIECE;
begin
open cor_sql;
fetch cor_sql into sqlline;
while cor_sql%FOUND loop
-- SELECT REPLACE(sqlline,chr(32),'') into sqlline from dual;
ln := length(sqlline);
ind:=1;
while (ind <>
if substr(sqlline,ind,1) not in (chr(10),chr(13)) then
ind := ind+1;
else
tmptxt := tmptxt||substr(sqlline,1,ind);
sqlt.line:=(tmptxt);
PIPE ROW(sqlt);
sqlline:=substr(sqlline,1,ind+1);
tmptxt:='';
ln := length(sqlline);
ind := 1;
end if;
end loop;
tmptxt:=tmptxt||sqlline;
if length(tmptxt) > 100 then
sqlt.line:=(substr(tmptxt,1,100));
PIPE ROW(sqlt);
tmptxt:=substr(tmptxt,101,length(tmptxt)+1);
end if;
fetch cor_sql into sqlline;
end loop;
close cor_sql;
sqlt.line:=tmptxt;
PIPE ROW(sqlt);
RETURN;
end;
/

set linesize 2000
select * from table(user.hashsql(&hash_value))

PL/SQL script to generate rcp and rename datafile commands for physical standby

2008-04-16T04:01:00.000-07:00

This is a PL/SQL script used to generate rcp and rename datafile commands in order to create standby database. If
* there are more then 1k datafiles
* source and destination paths are different
* source and destination disk sizes are different
(as in my case) it might be useful. However you should modify code to make it suitable for your case:

create table user.cmds (RCP varchar2(1000), MV varchar2(1000));

declare
cursor c_sql is
select name, bytes/(1024*1024) bsize from v$datafile order by name;
sqlline c_sql%ROWTYPE;
tsize number;
cnt int;
begin
open c_sql;
fetch c_sql into sqlline;
tsize:=0;
cnt:=1;
execute immediate 'truncate table user.cmds';
while c_sql%FOUND loop
tsize:=tsize+sqlline.bsize;
if cnt < 10 then
insert into user.cmds values('rcp '||sqlline.name||' userp@server:/userdata0'||cnt||'/userp/','alter database rename file '''||sqlline.name||''' to ''/userdata0'||cnt||'/userp/'||substr(sqlline.name,19,255)||''';');
--dbms_output.put_line('alter database rename file '''||sqlline.name||''' to ''/userdata0'||cnt||'/userp/'||substr(sqlline.name,19,255)||''';');
else
insert into user.cmds values('rcp '||sqlline.name||' userp@server:/userdata'||cnt||'/userp/','alter database rename file '''||sqlline.name||''' to ''/userdata'||cnt||'/userp/'||substr(sqlline.name,19,255)||''';');
end if;
if tsize > 95000 then
tsize:=0;
cnt:=cnt+1;
end if;
fetch c_sql into sqlline;
end loop;
commit;
close c_sql;
end;
/

Windows (VBS) and UNIX (Shell) scripts to delete applied archive logs

2008-04-15T04:12:00.000-07:00

In a dataguard configuration, archive logs should stay on production archive log directory until they are copied and applied to the standby database. Then they should be backed up and removed in order to prevent archive log directories from getting full. Following scripts are written by me to automate this procedure:

Unix:
There should be a file "$HOME/stby/archive_last_deleted " which is used to keep sequence number of the last deleted archive log file. At first you can create it with vi and write 1 in it.

Following scheduled script "$HOME/stby/archclean.sh" runs on standby site to delete applied archived logs:

#/bin/ksh

if [ "$hosttype" == "SunOS" ]
then
userid=`/usr/xpg4/bin/id -u -n`
else
userid=`/usr/bin/id -u -n`
fi
. ~$userid/.profile

sqlplus '/ AS SYSDBA' > $HOME/stby/out/archclean.out1 <
set lines 2000
select 'MAKEGREP' GREP,count(1) from V\$MANAGED_STANDBY where PROCESS='MRP0' and SEQUENCE# <> 0;
exit;
EOF
IS_APPLY_RUNNING=`cat $HOME/stby/out/archclean.out1|grep MAKEGREP|awk '{print $2}'`
date >> $HOME/stby/out/archclean.log
if [[ $IS_APPLY_RUNNING -eq 1 ]];then
echo "Apply process (MRP0) is running..." >> $HOME/stby/out/archclean.log
sqlplus '/ AS SYSDBA' > $HOME/stby/out/archclean.out1 <
set lines 2000
SELECT 'MAKEGREP' GREP,SEQUENCE#-1 FROM V\$MANAGED_STANDBY where PROCESS in ('MRP0') and SEQUENCE# <> 0;
EOF
MAX_SEC_TO_DELETE=`cat $HOME/stby/out/archclean.out1|grep MAKEGREP|awk '{print $2}'`
LAST_DELETED=`cat $HOME/stby/archive_last_deleted`
NEXTTODELETE=$((LAST_DELETED+1))
echo "Archive Sequence Range to Clean: $NEXTTODELETE - $MAX_SEC_TO_DELETE" >> $HOME/stby/out/archclean.log
ARCHIVE_FORMAT=`cat $HOME/mntdir/config/dba.ini|grep -v "##"|grep ARCHIVE_FORMAT|cut -d= -f2`
ARCHIVE_DIR=`cat $HOME/mntdir/config/dba.ini|grep -v "##"|grep ARCHIVE_DIR|cut -d= -f2`
while [[ $MAX_SEC_TO_DELETE -gt $NEXTTODELETE ]];do
NEXTARCHIVE=`awk 'BEGIN {str="'$ARCHIVE_FORMAT'" ; sub("SEQUENCE","'$NEXTTODELETE'",str) ; print str}'`
FILECOUNT=`ls -l $ARCHIVE_DIR|grep $NEXTARCHIVE|grep ".arc"|wc -l 2>/dev/null`
if [[ $FILECOUNT -eq 1 ]];then
echo "removing $ARCHIVE_DIR/$NEXTARCHIVE" >> $HOME/stby/out/archclean.log
/usr/bin/rm -f $ARCHIVE_DIR/$NEXTARCHIVE
fi
echo $NEXTTODELETE>$HOME/stby/archive_last_deleted
NEXTTODELETE=$(($NEXTTODELETE+1))
done
else
echo "Apply process (MRP0) is not running..." >> $HOME/stby/out/archclean.log
fi

At production site my backup procedure updates "$HOME/stby/backupseq" with the last backed up archive sequence number. There should be a file "$HOME/stby/archive_last_deleted " which is used to keep sequence number of the last deleted archive log file. At first you can create it with vi and write 1 in it. My following scheduled script "$HOME/stby/archcleanprod.sh" deletes all archived which are backed up and applied to standby database:

#/bin/ksh

if [ "$hosttype" == "SunOS" ]
then
userid=`/usr/xpg4/bin/id -u -n`
else
userid=`/usr/bin/id -u -n`
fi
. ~$userid/.profile

sqlplus / > $HOME/stby/out/archcleanprod.out1 <
set lines 2000
select 'MAKEGREP' GREP,APPLIED_SEQ#-10 from V\$ARCHIVE_DEST_STATUS where DEST_ID=2;
exit;
EOF
MAX_SEC_APPLIED=`cat $HOME/stby/out/archcleanprod.out1|grep MAKEGREP|awk '{print $2}'`
MAX_SEQ_NMBCOPY_2=`cat $HOME/stby/backupseq`
if [[ $MAX_SEQ_NMBCOPY_2 -gt $MAX_SEC_APPLIED ]];then
MAX_SEC_TO_DELETE=$MAX_SEC_APPLIED
else
MAX_SEC_TO_DELETE=$MAX_SEQ_NMBCOPY_2
fi
LAST_DELETED=`cat $HOME/stby/archive_last_deleted`
NEXTTODELETE=$((LAST_DELETED+1))
date >> $HOME/stby/archcleanprod.log
echo "Archive Sequence Range to Clean: $NEXTTODELETE - $MAX_SEC_TO_DELETE" >> $HOME/stby/archcleanprod.log
ARCHIVE_FORMAT=`cat $HOME/mntdir/config/dba.ini|grep -v "##"|grep ARCHIVE_FORMAT|cut -d= -f2`
ARCHIVE_DIR=`cat $HOME/mntdir/config/dba.ini|grep -v "##"|grep ARCHIVE_DIR|cut -d= -f2`
while [[ $MAX_SEC_TO_DELETE -gt $NEXTTODELETE ]];do
NEXTARCHIVE=`awk 'BEGIN {str="'$ARCHIVE_FORMAT'" ; sub("SEQUENCE","'$NEXTTODELETE'",str) ; print str}'`
FILECOUNT=`ls -l $ARCHIVE_DIR|grep $NEXTARCHIVE|grep ".arc"|wc -l 2>/dev/null`
if [[ $FILECOUNT -eq 1 ]];then
echo "removing $ARCHIVE_DIR/$NEXTARCHIVE" >> $HOME/stby/archcleanprod.log
/usr/bin/rm -f $ARCHIVE_DIR/$NEXTARCHIVE
fi
echo $NEXTTODELETE>$HOME/stby/archive_last_deleted
NEXTTODELETE=$(($NEXTTODELETE+1))
done

windows:
There is a configuration file "E:\dba\orahome\mntdir\config\dba.xml" (you should replace { and } with the ones used in standard xml. I can not use them in this post, corrupts page)

{?xml version="1.0" encoding="utf-8" ?}
{config}
{general}
{archive_format}ARC_0628272535_001_SEQUENCE.ARC{/archive_format}
{archive_dir}E:\oracle\oradata\archive{/archive_dir}
{/general}
{stby}
{prodUser}dbuser{/prodUser}
{prodPass}password{/prodPass}
{prodTNS}PROD{/prodTNS}
{/stby}
{/config}

There is a file "E:\dba\orahome\stby\archive_last_deleted.txt" in which last deleted archived log's sequence number is kept. At first it can be set as 1.
My scheduled vbs script "E:\dba\orahome\stby\stby_archclean.vbs" uses configuration file to connect primary site, query dataguard status and delete applied archived logs at standby server:

'********************* CODE TO PARSE DBA.XML FILE ****************************
Set oXMLDoc = CreateObject("MSXML2.DOMDocument.3.0")
oXMLDoc.async = False
If Not oXMLDoc.load("mntdir\config\dba.xml") Then
Set oErr = oXMLDoc.parseError
sErrMsg = "XML Parsing Error. File: " & oErr.url & " Reason : " & oErr.reason & " Line: " & oErr.line & ", Character: " & oErr.linepos & ",Text: " & oErr.srcText
Err.Raise 999, , sErrMsg
End If
'******************************************************************************

Set WshShell = CreateObject("WScript.Shell")
pUSER = oXMLDoc.selectSingleNode("/config/stby/prodUser").text
pPASS = oXMLDoc.selectSingleNode("/config/stby/prodPass").text
ARCHIVE_FORMAT = oXMLDoc.selectSingleNode("/config/general/archive_format").text
ARCHIVE_DIR = oXMLDoc.selectSingleNode("/config/general/archive_dir").text
pTNS = oXMLDoc.selectSingleNode("/config/stby/prodTNS").text

Set objFSO = CreateObject("Scripting.FileSystemObject")
set archive_last_deleted = objFSO.OpenTextFile("stby\archive_last_deleted.txt", 1) 'for reading
DIM LAST_DELETED
if not archive_last_deleted.AtEndOfStream then
LAST_DELETED = cInt(archive_last_deleted.ReadLine)
else
WScript.Echo "Error on stby\archive_last_deleted file"
WScript.exit
end if
archive_last_deleted.close
NEXTTODELETE=LAST_DELETED+1

'ADO Connection object
Set con = CreateObject("ADODB.Connection")
con.CursorLocation = 3
If con.State = 1 Then con.close
con.Open "PROVIDER=MSDAORA;DATA SOURCE="&pTNS&";USER ID="&pUSER&";PASSWORD="&pPASS&";"

'ADO Command object
Set cmd = CreateObject("ADODB.Command")
cmd.CommandText="select APPLIED_SEQ#-3 from v$ARCHIVE_DEST_STATUS where DEST_ID=2 "
cmd.CommandType=1
cmd.ActiveConnection=con

'ADO Recordset object
set rs=CreateObject("ADODB.Recordset")
Set rs=cmd.Execute

if rs.EOF then
WScript.Echo "Error: No APPLIED_SEQ# record for DEST_ID 2"
rs.Close
con.Close
WScript.exit
else
DIM MAX_SEC_APPLIED
rs.MoveFirst
MAX_SEC_APPLIED = cInt(rs.Fields(0))
rs.Close
con.Close
end if

WScript.Echo "Archive Sequence Range to Clean:" & cStr(NEXTTODELETE) & " - " & cStr(MAX_SEC_APPLIED)
do while MAX_SEC_APPLIED > NEXTTODELETE
WScript.Echo "removing... " & ARCHIVE_DIR & "\" & Replace(ARCHIVE_FORMAT,"SEQUENCE",cStr(NEXTTODELETE))
'WshShell.run("del /Q " & ARCHIVE_DIR & "\" & Replace(ARCHIVE_FORMAT,"SEQUENCE",cStr(NEXTTODELETE)))
objFSO.DeleteFile ARCHIVE_DIR & "\" & Replace(ARCHIVE_FORMAT,"SEQUENCE",cStr(NEXTTODELETE))
set archive_last_deleted = objFSO.OpenTextFile("stby\archive_last_deleted.txt", 2) 'for writing
archive_last_deleted.Write cStr(NEXTTODELETE)
archive_last_deleted.close
NEXTTODELETE = NEXTTODELETE+1
loop

I ran exactly the same script at prod side, however i added my backup procedure just before the delete.

How to find active sqls

2008-04-09T06:41:00.000-07:00

Following script can be used to determine long running active sqls with elapsed time and wait event:

set lines 233
select /*+ ordered */ to_char(s.sid,'99999'),
substr(username,1,8),
substr(t.sql_text,1,60),
nvl(last_call_et,0) TIME_sec,
t.hash_value,
substr(w.event,1,30) WAIT_EV,
substr(machine,1,10) MACHINE
from v$session s,v$sqltext t,v$session_wait w
where s.sql_address=t.address
and s.sql_HASH_VALUE=t.HASH_VALUE
and w.sid=s.sid
and username between 'AAAAAAAAA' and 'ZZZZZZZZZ'
and s.status='ACTIVE'
and t.piece=0
--and upper(sql_text) not like 'BEGIN%'
order by TIME_SEC
/

In order to find sqls with high executions and little elapsed times

1-) Take a snap of v$sqlarea:

create table tmp as select sql_id, sql_text, executions, ELAPSED_TIME, CPU_TIME, DISK_READS, DIRECT_WRITES, BUFFER_GETS from v$sqlarea;

2-) Track sqls depending on executions:

select a.sql_id,substr(b.sql_text,1,50),
b.executions-a.executions,
b.ELAPSED_TIME - a.ELAPSED_TIME,
b.CPU_TIME - a.CPU_TIME,
b.DISK_READS - a.DISK_READS,
b.DIRECT_WRITES - a.DIRECT_WRITES,
b.BUFFER_GETS - a.BUFFER_GETS
from tmp a, v$sqlarea b
where a.sql_id=b.sql_id and b.executions-a.executions > 100 order by b.executions-a.executions
/
CREATING A JOB FOR ACTIVE SQL EXECUTIONS HISTORYOne day i need to monitor sql executions over time in order to determine batch-OLTP transactions over time. You may wonder why i did not use AWR report, EM or something else.. because usually i spent much more time on those tools to find what i need.

One important note, because my focus was sql execution counts, i put "a.sql_id=b.sql_id and b.executions-a.executions > 10000" condition in my sql withing procedure sp_sqlarea_snap_history in order to eliminate low execution sqls to be ignored. If your focus on something else (for example buffer gets or CPU) then i suggest change this condition which suits your case. Sometimes a sql executed a few times may consume much more resource then an other sql executes thousand times....

create table sqlarea_snap as select sysdate snap_time, sql_id, sql_text, executions, ELAPSED_TIME, CPU_TIME, DISK_READS, DIRECT_WRITES, BUFFER_GETS from v$sqlarea where 1=2;

create table sqlarea_snap_history
(SNAP_BEGIN_TIME DATE,
SNAP_END_TIME DATE,
SQL_ID VARCHAR2(13),
SQL_TEXT VARCHAR2(1000),
EXECUTIONS_DELTA NUMBER,
TPS NUMBER,
ELAPSED_TIME_DELTA NUMBER,
CPU_TIME_DELTA NUMBER,
DISK_READS_DELTA NUMBER,
DIRECT_WRITES_DELTA NUMBER,
BUFFER_GETS_DELTA NUMBER);

create or replace procedure sp_sqlarea_snap_history
as
begin
EXECUTE IMMEDIATE 'insert into sqlarea_snap_history select a.SNAP_TIME, sysdate, a.sql_id, b.sql_text, b.executions-a.executions, '
'trunc((b.executions-a.executions)/((sysdate-snap_time)*24*60*60)), b.ELAPSED_TIME - a.ELAPSED_TIME, b.CPU_TIME - a.CPU_TIME, '
'b.DISK_READS - a.DISK_READS, b.DIRECT_WRITES - a.DIRECT_WRITES, b.BUFFER_GETS - a.BUFFER_GETS from sqlarea_snap a, v$sqlarea b '
'where a.sql_id=b.sql_id and b.executions-a.executions > 10000 order by b.executions-a.executions';
EXECUTE IMMEDIATE 'drop table sqlarea_snap';
EXECUTE IMMEDIATE 'create table sqlarea_snap as select sysdate snap_time, sql_id, sql_text, executions, ELAPSED_TIME, CPU_TIME, '
'DISK_READS, DIRECT_WRITES, BUFFER_GETS from v$sqlarea ';
end;
/

exec dbms_job.isubmit(20,'sp_sqlarea_snap_history;',sysdate,'sysdate+1/144',false);

Finally, here is the query

select SNAP_BEGIN_TIME, EXECUTIONS_DELTA, TPS, SQL_TEXT from sqlarea_snap_history where SNAP_BEGIN_TIME > trunc(sysdate) order by SNAP_BEGIN_TIME desc, EXECUTIONS_DELTA desc;

How to create a logon trigger for session tracing

2008-03-24T00:09:00.000-07:00

Here is a function setting session trace on:
CREATE OR REPLACE
FUNCTION logon_actions
RETURN boolean IS
CURSOR user_prog IS
SELECT machine,program,osuser,module,sid, serial# FROM v$session where audsid=sys_context('USERENV','SESSIONID');
user_rec user_prog%ROWTYPE;
fname utl_file.file_type;
fnamedtl utl_file.file_type;
begin
OPEN user_prog;
FETCH user_prog INTO user_rec;
if upper(user_rec.machine) like '%anyhostname%' then
-- insert into sessiontrace values(user_rec.machine,user_rec.program,user_rec.osuser,user_rec.module,user_rec.sid,user_rec.serial#);
--commit;
execute immediate 'alter session set sql_trace=true';
end if;
CLOSE user_prog;
RETURN true;
EXCEPTION
WHEN OTHERS THEN
RETURN false;
END;
/

And the trigger:
CREATE OR REPLACE TRIGGER logon_actions_trg
AFTER
LOGON
ON DATABASE
declare
begin
if ( not LOGON_ACTIONS ) then
RAISE LOGIN_DENIED;
end if;
end;
/

An Example of Transportable Tablespaces

2008-03-14T04:59:00.000-07:00

(set oracle environment for source DB)
At source DB:
connect / as sysdba
create tablespace data4 datafile
'/path1/data401.dbf' size 10m autoextend on next 10m,
'/path1/data402.dbf' size 10m autoextend on next 10m;
create tablespace indx4 datafile
'/path1/indx401.dbf' size 10m autoextend on next 10m;
create user tc identified by tc default tablespace data4
temporary tablespace temp quota unlimited on data4 quota unlimited on indx4;
grant create session, create table to tc;
create table tc.testtab (nr number, txt varchar2(10)) tablespace data4;
insert into tc.testtab values (1, 'line 1');
commit;
create index tc.i_testtab on tc.testtab(nr) tablespace indx4;
select owner'.'segment_name "OWNER.SEGMENT_NAME", segment_type, tablespace_name from dba_segments where tablespace_name in ('DATA4','INDX4');
alter tablespace data4 read only;
alter tablespace indx4 read only;
execute dbms_tts.transport_set_check('data4,indx4', TRUE);
select * from transport_set_violations;
exit;

At Shell:
exp "'/ as sysdba'" file=exp_tts.dmp log=exp_tts.log transport_tablespace=y tablespaces=data4,indx4
cp /path1/data401.dbf /path2/data401.dbf
cp /path1/data402.dbf /path2/data402.dbf
cp /path1/indx401.dbf /path2/indx401.dbf
cd /path2
chmod g+w *

(set oracle environment for destination DB)
At destination DB:
connect / as sysdba
create user tcnew identified by tcnew default tablespace tools temporary tablespace temp;
grant create session, create table, resource to tcnew;

At Shell:
imp "'/ as sysdba'" file=exp_tts.dmp log=imp_tts.log fromuser=tc touser=tcnew transport_tablespace=y datafiles=/path2/data401.dbf,/path2/data402.dbf,/path2/indx401.dbf

At destination DB:
connect / as sysdba
alter tablespace data4 read write;
alter tablespace indx4 read write;
alter user tcnew quota unlimited on data4;
alter user tcnew quota unlimited on indx4;
revoke resource from tcnew;
select owner'.'segment_name "OWNER.SEGMENT_NAME", segment_type, tablespace_name from dba_segments where tablespace_name in ('DATA4','INDX4');

How to determine full scan sqls?

2008-03-14T02:26:00.000-07:00

Here is a sql script to determine full scan sqls:

SELECT /*+ ordered */
DISTINCT s.hash_value, s.buffer_gets, s.disk_reads, s.executions,
s.sql_text,
'SELECT * FROM V$SQL_PLAN WHERE ADDRESS='''
|| s.address
|| ''' AND HASH_VALUE='
|| s.hash_value
FROM v$sql_plan p, v$sqlarea s
WHERE p.options LIKE '%FULL%'
AND s.address = p.address
AND s.hash_value = p.hash_value
AND object_name <> 'DUAL'
-- AND EXECUTIONS > 10
ORDER BY s.buffer_gets DESC

Sql Script to Generate AWR Report

2008-03-04T04:12:00.000-08:00

Here is a sql script to generate awr report using sqlplus:

select DBID,INSTANCE_NUMBER,SNAP_ID,END_INTERVAL_TIME, BEGIN_INTERVAL_TIME from dba_hist_snapshot order by BEGIN_INTERVAL_TIME;
undefine DBID
undefine INSID
undefine STARTSNAPID
undefine ENDSNAPID
column report_name new_value report_name;
select 'awr_'||(select NAME from v$database where DBID = &&DBID)||'_'||&&INSID||'_'||
(select to_char(END_INTERVAL_TIME,'yyyymmddhh24mi') from dba_hist_snapshot where SNAP_ID=&&STARTSNAPID)||'_'||
(select to_char(END_INTERVAL_TIME,'yyyymmddhh24mi') from dba_hist_snapshot where SNAP_ID=&&ENDSNAPID)
||'.html'
report_name
from dual;
set termout off
spool &&report_name
select output from table(sys.dbms_workload_repository.awr_report_html(&DBID,&INSID,&STARTSNAPID,&ENDSNAPID));
spool off
set termout on

Setting up Dataguard Configuration for Oracle 10G

2008-03-04T03:24:00.000-08:00

I assume that physical standby database is previously created, configured and up-to-date.

Prod configuration
If you use spfile, set scope=both at the end of all alter commands. Other case, dont forget to alter parameters in init.ora file
1-)Be sure logging is enabled at database level:ALTER DATABASE FORCE LOGGING;
2-)Change db_unique_name parameter (in init.ora or spfile) if necessary. Default is ORACLE_SID. You may leave it as it is. Change requires restart to apply.
* alter system set STANDBY_FILE_MANAGEMENT=AUTO;
* alter system set log_archive_dest_state_1=ENABLE;
* alter system set log_archive_dest_state_2=DEFER;
* alter system set log_archive_dest_1='LOCATION={archive_path_prod} MANDATORY REOPEN=60 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME={db_unique_name_prod}';
TNS_stby is the TNS name configured for standby database. I strongly suggest configuring a new TNS listener on a different port reserverd for dataguard configuration. We will set it later at standby database.
* alter system set log_archive_dest_2='SERVICE={TNS_stby} LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME={db_unique_name_stby}';
* alter system set FAL_SERVER={TNS_stby};
* alter system set FAL_CLIENT={TNS_prod}; -- TNS_prod: tns name for primary database.
3-)Be sure that remote_login_passwordfile is set to EXCLUSIVE (show parameter remote_login_passwordfile)
4-)On platforms other than Windows, create a password file, and set the password for the SYS user to the same password used by the SYS user on the primary database. The password for the SYS user on every database in a Data Guard configuration must be identical for redo transmission to succeed. See Oracle Database Administrator's Guide.
6-)Add number of redologgroups+1 standby logfiles to the prod database. These redo logs wont be used unless a swithover is performed.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 20 ('{redo_path}/stbyredo01.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 ('{redo_path}/stbyredo02.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 22 ('{redo_path}/stbyredo03.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 23 ('{redo_path}/stbyredo04.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 24 ('{redo_path}/stbyredo05.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 25 ('{redo_path}/stbyredo06.log') SIZE 52428800;

if there is RAC configuration then add logfiles for thread 2:

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 26 ('{redo_path}/stbyredo07.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 27 ('{redo_path}/stbyredo08.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 28 ('{redo_path}/stbyredo09.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 29 ('{redo_path}/stbyredo10.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 30 ('{redo_path}/stbyredo11.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 31 ('{redo_path}/stbyredo12.log') SIZE 52428800;

Standby configuration:
If you use spfile, set scope=both at the end of all alter commands. Other case, dont forget to alter parameters in init.ora file
1-) change db_unique_name parameter (in init.ora or spfile) if necessary. It can not be the same with production. I assume it is {db_unique_name_stby}
* alter system set STANDBY_FILE_MANAGEMENT=AUTO;
* alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
* alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
* alter system set LOG_ARCHIVE_DEST_1='LOCATION={archive_path_stby} VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME={db_unique_name_stby}';
TNS_prod is the TNS name configured for prod database. I strongly suggest configuring a new TNS listener on a different port reserverd for dataguard configuration.
* alter system set LOG_ARCHIVE_DEST_2='SERVICE={TNS_prod} LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME={db_unique_name_prod}';
* alter system set FAL_SERVER={TNS_prod};
* alter system set FAL_CLIENT={TNS_stby};
2-)Be sure that remote_login_passwordfile is set to EXCLUSIVE (show parameter remote_login_passwordfile)
3-)Configure listener at prod site so be sure that "tnsping TNS_prod" is working.
4-)Configure listener at standby site so be sure that "tnsping TNS_stby" is working.
5-)On platforms other than Windows, create a password file, and set the password for the SYS user to the same password used by the SYS user on the primary database. The password for the SYS user on every database in a Data Guard configuration must be identical for redo transmission to succeed. See Oracle Database Administrator's Guide.
6-)Add number of redologgroups+1 standby logfiles to the standby database.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 20 ('{redo_path}/stbyredo01.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 ('{redo_path}/stbyredo02.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 22 ('{redo_path}/stbyredo03.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 23 ('{redo_path}/stbyredo04.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 24 ('{redo_path}/stbyredo05.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 25 ('{redo_path}/stbyredo06.log') SIZE 52428800;

if there is RAC configuration then add logfiles for thread 2:

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 20 ('{redo_path}/stbyredo07.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 21 ('{redo_path}/stbyredo08.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 22 ('{redo_path}/stbyredo09.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 23 ('{redo_path}/stbyredo10.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 24 ('{redo_path}/stbyredo11.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 25 ('{redo_path}/stbyredo12.log') SIZE 52428800;

Finally, activating dataguard configuration (prod):
alter systen set log_archive_dest_state_2=ENABLE; -- If you use spfile, set scope=both, Other case, dont forget to update init.ora file

Tuning Network Configuration:
Network Tuning - Oracle Net Session Data Unit (SDU) Size
Prod (tnsnames.ora):
...
( DESCRIPTION =
(SDU=32767)
( ADDRESS = (PROTOCOL = TCP) (Host = ...) (Port = ...) )
( CONNECT_DATA = (SID = ...) )
)

Standby (listener.ora):
SID_LIST_LISTENER_... =
( SID_LIST=
(SID_DESC =
(SDU=32767)
(ORACLE_HOME = ...)
(SID_NAME =...)
)
)

Network Tuning: Setting TCP Socket Buffer Sizes:
BDP= {available bandwidth}*{Network Round Trip Time-RTT}
Socket Buffer Size = 3*BDP

Example for 34Mbit bandwith with 2ms RTT:
= 3* ((34000000*0.02)/8)
=255000

Prod(tnsnames.ora):
... =
( DESCRIPTION =
(SDU=32767)
(SEND_BUF_SIZE=255000)
(RECV_BUF_SIZE=255000)
( ADDRESS = (PROTOCOL = TCP) (Host = ...) (Port = ...) )
( CONNECT_DATA = (SID = ...) )
)

standby(listener.ora):
LISTENER_...=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= ...))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =...)(PORT = 1521) (SEND_BUF_SIZE=255000) (RECV_BUF_SIZE=255000))
)
)
)

Starting and stopping Managed Recovery (Standby):
start
STARTUP MOUNT;
alter database recover managed standby database using current logfile disconnect from session; -- Applies not only completed redo logs, but also redo log currently being copied
or
alter database recover managed standby database disconnect from session; -- Applies completed redo logs

stop
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SHUTDOWN IMMEDIATE;

Monitoring:
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

A Script to Add Supplemental Logging at Table Level

2008-03-04T01:29:00.000-08:00

Here is a script which may be used to generate script which enables supplemental logging at table level for all tables.

declare
out varchar2(2000);
cflag number;
begin
-- You may change this line, i am choosing tables with only 50 to 250 MBs and owner OWNR
for l in ( select segment_name from dba_segments where bytes/(1024*1024) between 50 and 250 and owner in ('OWNR') and SEGMENT_TYPE = 'TABLE' and segment_name not like 'BIN%' order by segment_name)
loop
cflag := 0;
out :='alter table OWNR.'||l.segment_name||' ADD SUPPLEMENTAL LOG GROUP "'||substr(l.segment_name,1,25)||'_SLOG" (';

-- If there is a PK then add its columns
for k in (select COLUMN_NAME from DBA_IND_COLUMNS where INDEX_NAME in (select index_name from dba_constraints where CONSTRAINT_TYPE = 'P' and table_name=l.segment_name) order by COLUMN_POSITION)

loop
if (cflag = 0) then out:=out||k.column_name;
else out:=out||','||k.column_name;
end if;
cflag := 1;
end loop;

-- If there is no PK but there is a UI, then add its columns.
-- Normally there should not be more then one UI on any table. If there is, then is a problem. You may check it with this sql:
-- select TABLE_NAME,count(*) from dba_indexes where INDEX_TYPE='NORMAL' and UNIQUENESS='UNIQUE' and owner='OWNR' and
-- TABLE_NAME not in (select TABLE_NAME from dba_constraints where CONSTRAINT_TYPE = 'P') group by TABLE_NAME having count(*) > 1;

if (cflag = 0) then
for m in (select COLUMN_NAME from DBA_IND_COLUMNS where INDEX_NAME in (select index_name from dba_indexes where INDEX_TYPE='NORMAL' and UNIQUENESS='UNIQUE' and owner='OWNR' and table_name=l.segment_name) order by COLUMN_POSITION)

loop
if (cflag = 0) then out:=out||m.column_name;
else out:=out||','||m.column_name;
end if;
cflag := 1;
end loop;
end if;
-- If there is no PK and UI, then add all columns to supplemental logging
if (cflag = 0) then
for n in (select COLUMN_NAME from dba_tab_columns where table_name=l.segment_name order by COLUMN_ID)
loop
if (cflag = 0) then out:=out||n.column_name;
else out:=out||','||n.column_name;
end if;
cflag := 1;
end loop;
end if;

out := out||') ALWAYS;';
dbms_output.put_line(out);
end loop;
END;
/

STANDBY_FILE_MANAGEMENT MANUAL, ORA-01110

2008-01-08T01:26:00.000-08:00

If STANDBY_FILE_MANAGEMENT system parameter is MANUAL and a datafile is added to primary database, the same datafile should be added manually to the standby database after ORA-01110 error. However, by setting STANDBY_FILE_MANAGEMENT=AUTO, you can allow standby database to create its own datafile automatically whenever a datafile is added to the primary.

SQL> alter system set STANDBY_FILE_MANAGEMENT=MANUAL;
SQL> alter database create datafile '...DBF' as '...DBF' size ...M;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile; or change/add initora parameter.

How to Resize Redo Logs

2007-09-07T05:56:00.000-07:00

Here is a newly created database redo log information:

SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 17 52428800 1 NO INACTIVE 996238 06/09/2007 22:01:59
2 1 18 52428800 1 NO INACTIVE 1006432 06/09/2007 22:13:32
3 1 19 52428800 1 NO CURRENT 1036439 07/09/2007 09:56:44

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -------------------------------------------------
3 ONLINE /database/data/redo03.log
2 ONLINE /database/data/redo02.log
1 ONLINE /database/data/redo01.log

Here is how i changed this to five 200M redo logs:

SQL> alter database add logfile group 4 ('/database/data/redo04.log') size 200M;
SQL> alter database add logfile group 5 ('/database/data/redo05.log') size 200M;

while running following sql commands, if you hit an error like this:

ORA-01623: log 3 is current log for instance RPTDB (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/database/data/redo03.log'

you should run " alter system switch logfile;" until current log is 4 or 5. Then execute "alter system checkpoint;"

SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

then move (or maybe drop) old redo logs

mv /database/data/redo01.log /database/data/redo01_old.log
mv /database/data/redo02.log /database/data/redo02_old.log
mv /database/data/redo03.log /database/data/redo03_old.log

finally

SQL> alter database add logfile group 1 ('/database/data/redo01.log') size 200M;
SQL> alter database add logfile group 2 ('/database/data/redo02.log') size 200M;
SQL> alter database add logfile group 3 ('/database/data/redo03.log') size 200M;

How to determine tables with highest data change rate at Oracle 10G

2007-09-06T04:05:00.000-07:00

DBA_HIST_SEG_STAT system view can be used to see physical read-write statistics on segments. Statistics are taken by regular snaps. Following sql script returns tables with higher block change rate, bigger then 20000.

select SNAP_ID,OWNER ,OBJECT_NAME, OBJECT_TYPE, DB_BLOCK_CHANGES_DELTA
from dba_objects a,
(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b
where a.object_id=b.obj# and object_type='TABLE'
order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc

select c.SNAP_ID,c.END_INTERVAL_TIME SNAP_TIME,a.OWNER ,a.OBJECT_NAME, a.OBJECT_TYPE, b.DB_BLOCK_CHANGES_DELTA
from dba_objects a,
(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,
DBA_HIST_SNAPSHOT c
where a.object_id=b.obj# and object_type='TABLE' and b.SNAP_ID=c.SNAP_ID
order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc

ANALYZE & DBMS_STATS

2007-08-25T11:51:00.000-07:00

ANALYZE:
analyze table owner.table_name compute statistics for table for all indexes for all indexed columns size 254;

analyze table owner.table_name partition(partition_name) compute statistics for table for all LOCAL indexes for all indexed columns size 254;

DBMS_STATS:
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'owner', TABNAME => 'table_name', PARTNAME => null, ESTIMATE_PERCENT => 100, BLOCK_SAMPLE => null, METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 254', DEGREE => null, GRANULARITY => 'ALL', CASCADE => TRUE, STATTAB => null, STATID => null, STATOWN => null);

ownname Schema of table to analyze.
tabname Name of table.
partname Name of partition.
estimate_percent Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the best sample size for good statistics.
block_sample Whether or not to use random block sampling instead ofrandom row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.
method_opt Accepts:

FOR ALL [INDEXED HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] columnattribute[size_clause] [,columnattribute [size_clause]...], where size_clause is defined as: ize_clause := SIZE {integer REPEAT AUTO SKEWONLY}
integer—Number of histogram buckets. Must be in the range [1,254].
REPEAT—Collects histograms only on the columns that already have histograms.
AUTO—Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
SKEWONLY—Oracle determines the columns to collect histograms based on the data distribution of the columns.

degree Degree of parallelism. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters.
granularity Granularity of statistics to collect (only pertinent if the table is partitioned).

DEFAULT: Gather global- and partition-level statistics.
SUBPARTITION: Gather subpartition-level statistics.
PARTITION: Gather partition-level statistics.
GLOBAL: Gather global statistics.
ALL: Gather all (subpartition, partition, and global) statistics.

cascade Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS procedure on each of the table’s indexes.
stattab User stat table identifier describing where to save the current statistics.
statid Identifier (optional) to associate with these statistics within stattab
statown Schema containing stattab (if different than ownname).
no_invalidate Dependent cursors are not invalidated if this parameter is set to TRUE. When the ’cascade’ argument is specified, this parameter is not relevant with certain types of indexes.

PROCEDURE TO RECREATE PARTITIONED TABLE STATS

exec DBMS_SESSION.SET_NLS('NLS_SORT','BINARY'); -- necessary for a AIX Bug

-- Backup statistics (my backup table is BAKIM.STATTAB)
truncate table bakim.stattab;
exec DBMS_STATS.EXPORT_TABLE_STATS({owner},{table_name},null,'STATTAB','AAA',TRUE,'BAKIM');

declare

cursor cr_x is
select TABLE_owner,table_name,partition_name from dba_tab_partitions where
table_name in ('{table_name}') --and last_analyzed <>trunc(sysdate)
;
begin
for l in cr_x loop
DBMS_STATS.EXPORT_TABLE_STATS(l.TABLE_owner,l.table_name,l.partition_name,'STATTAB','AAA',TRUE,'BAKIM');
end loop;
end;

-- Delete general table statistics. We want partition statistics to be escalated to table statistics
exec DBMS_STATS.DELETE_TABLE_STATS ({owner},{table_name},null,null,null,TRUE,TRUE,TRUE,null);

-- Gather partition statitstics
declare

cursor cr_x is
select TABLE_owner,table_name,partition_name from dba_tab_partitions where
table_name in ('{table_name}') --and last_analyzed <>trunc(sysdate)
;
Begin
for l in cr_x loop
DBMS_STATS.GATHER_TABLE_STATS ( l.TABLE_owner,l.table_name,l.partition_name,null,null,'FOR ALL INDEXED COLUMNS SIZE 254', NULL, 'PARTITION',TRUE, null, null, null);
end loop;
end;

Oracle 10G enterprise manager (emca)

2007-08-25T10:56:00.000-07:00

start stop:

emctl start dbconsole
emctl stop dbconsole

To creare repository if not installed yet:

emca -config dbcontrol db -repos create -host {hostname} -SID {Oracle SID} -PORT 1521

To uninstall previously created repository (metalink DocID: 278100.1 )

1- Logon SQLPLUS as user SYS or SYSTEM
2- EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
3- EXEC sysman.setEMUserContext('',5);
4- REVOKE dba FROM sysman;
5-
DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM 'r1.name;
ELSE EXECUTE IMMEDIATE 'DROP SYNONYM 'r1.owner'.'r1.name;
END IF;
END LOOP;
END;
6- DROP USER mgmt_view CASCADE;
7- DROP ROLE mgmt_user;
8- DROP USER sysman CASCADE;
9- Then login shell and execute following:
emca -deconfig dbcontrol db -repos drop

Shell commands to create and drop em repository

$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager -connect (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP) (HOST={hostname}) (PORT=1521))) (CONNECT_DATA=(SERVICE_NAME={ORACLE SID}))) -repos_user SYSMAN -action drop -verbose -output_file $ORACLE_HOME/cfgtoollogs/emca/{ORACLE_SID}/emca_repos_drop_2006-01-18_03-02-57-PM.log
$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager -connect (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP) (HOST={hostname}) (PORT=1521))) (CONNECT_DATA=(SERVICE_NAME={ORACLE SID}))) -repos_user SYSMAN -action create -verbose -output_file $ORACLE_HOME/cfgtoollogs/emca/{ORACLE_SID}/emca_repos_drop_2006-01-18_03-02-57-PM.log

Batch scripts to set up asyncronous standby log shipping for oracle in windows

2007-08-20T03:40:00.000-07:00

This setup consists of setting up manual compressed log shipping for oracle using windows batch scripts.

Prod site:
* Create following directories in log_archive_dest: OLD, TMP
* Guessing archives are created with ARC extension. Other case scripts should be modified
* Reserve a domain user account and grant him access to both prod and standby servers. For easy, i granted him local administrator account in both servers but u may try to restrict access. If there is no domain available, then create local user account with the same user name & password on both prod and standby servers.
* You should download gnu awk and arj32 utility from net and place then on somewhere at %PATH%
* Share log_archive_dest folder of the standby and schedule script on prod with the user account you reserved
* You should modify {...} parameters for your environment

logmove.bat
set ORACLE_SID={ORACLE_SID}
set LOG_DEST={log_archive_dest}
set SCRIPT_DEST={where to place this script}
set LOGFILE=%SCRIPT_DEST%\logmove.log
set TEMP_FILE=%SCRIPT_DEST%\logmove.tmp
dir /OD %LOG_DEST%\*.ARC|findstr .ARC|awk "{print $4}">%TEMP_FILE%
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @arj32 a -e %LOG_DEST%\TMP\%%i.ARJ %LOG_DEST%\%%i >> %LOGFILE%
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @move /Y %LOG_DEST%\%%i %LOG_DEST%\OLD\ >> %LOGFILE%
dir /OD %LOG_DEST%\TMP\*.ARC.ARJ |findstr .ARC.ARJ |awk "{print $4}">%TEMP_FILE%
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @move /Y %LOG_DEST%\TMP\%%i \\{standby server}\archive >> %LOGFILE%
rem del /Q %TEMP_FILE% >> %LOGFILE%

Standby Site:
* Guessing archives are created with ARC extension. Other case scripts should be modified
* U should download gnu awk and arj32 utility from net and place then on somewhere at %PATH%
* You should modify {...} parameters for your environment
* Schedule script with the user account you reserved

logapply.bat
set ORACLE_SID={ORACLE_SID}
set LAGTIME=0.0417
rem set LAGTIME=0
set SCRPTLOC={where to place this script}
set LOG_DEST={log_archive_dest} set TEMP_FILE=%SCRPTLOC%\logapply.tmp
set LOGFILE=%SCRPTLOC%\logapply.log
dir /OD %LOG_DEST%\*.ARC.ARJ |find ".ARC.ARJ" |awk "{print $5}">%TEMP_FILE%
{Drive of {log_archive_dest}: for example C:, D: ...}
cd %LOG_DEST%
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @arj32 e -n %%i >> %LOGFILE%
sqlplus /nolog @%SCRPTLOC%\standby_recover.sql %LAGTIME% 1 >> %LOGFILE%
sqlplus /nolog @c:\temp\recover.sql >> %LOGFILE%
if {%ERRORLEVEL%} == {0} (
echo Database %ORACLE_SID% is available >> %SCRPTLOC%\sby.log
) else (
echo Database %ORACLE_SID% is NOT available >> %SCRPTLOC%\sby.log
)
del /Q c:\temp\recover.sql
del /Q %TEMP_FILE%

standby_recover.sql
connect sys/{sys password} as sysdba
set trimspool on linesize 200 head off feedback off sqlprompt "--" verify off
spool c:\temp\recover.sql
select 'connect sys/{sys password} as sysdba'||chr(10)||
'set autorecovery on'||chr(10)||
'recover standby database until time '''||
to_char(sysdate - &1,'YYYY-MM-DD:HH24:MI:SS')||''';'||chr(10)||
'exit;' from dual;
spool off
exit

logremove.bat
set ORACLE_SID={ORACLE_SID}
set SCRPTLOC={where to place this script}
set LOG_DEST={log_archive_dest}
set TEMP_FILE=%SCRPTLOC%\tmp.txt
set TEMP_FILE2=%SCRPTLOC%\tmp2.txt
set LOGFILE=%SCRPTLOC%\logremove.log
set LASTLOG=99999
echo.|time|date|find "current" >> %LOGFILE%
echo.|sqlplus /nolog @%SCRPTLOC%\lastarchno.sql |find "MAKEGREP"|awk "{print $2}" > %TEMP_FILE%
echo Set and Check if last archive no is valid >> %LOGFILE%
set LAST_ARCHNO=NONE
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @set LAST_ARCHNO=%%i
echo last archive is %LAST_ARCHNO% >> %LOGFILE%
rem
rem Following checks if log is recycled to beginning, then exits script. This stuation should be handled manually
rem
for %%i in (%LOG_DEST%\*%LASTLOG%.ARC) do goto end
rem
rem Following checks if last archive log is valid
rem
for %%i in (%LOG_DEST%\*%LAST_ARCHNO%.ARC) do goto cont
echo Last archive is not valid >> %LOGFILE%
goto end
:cont
echo %LAST_ARCHNO% > %SCRPTLOC%\lastarchno.txt
echo deleting archives >> %LOGFILE%
dir /O-N %LOG_DEST%\*.ARC |find ".ARC" |awk "{print $5}">%TEMP_FILE%
:repeat
set CURRENT_FILE=NONE
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @set CURRENT_FILE=%%i
if "%CURRENT_FILE%" == "NONE" goto end
call set TMP_STR=%%CURRENT_FILE:%LAST_ARCHNO%=%%
if NOT "%TMP_STR%" == "%CURRENT_FILE%" goto end
type %TEMP_FILE%|find /v "%CURRENT_FILE%" > %TEMP_FILE2%
type %TEMP_FILE2% > %TEMP_FILE%
set CURRENT_FILE=%LOG_DEST%\%CURRENT_FILE%
echo deleting %CURRENT_FILE% and %CURRENT_FILE%.ARJ >> %LOGFILE%
del /Q %CURRENT_FILE% >> %LOGFILE%
del /Q %CURRENT_FILE%.ARJ >> %LOGFILE%
goto repeat
:end
del /Q %TEMP_FILE% >> %LOGFILE%
del /Q %TEMP_FILE2% >> %LOGFILE%

lastarchno.sql
connect sys/{sys password} as sysdba
set trimspool on linesize 200 head off feedback off sqlprompt "--" verify off
select 'MAKEGREP' grep,max(sequence#) from v$log_history;
exit

Prod site:
* Share script folder of the standby where lastarchno.txt is in and schedule script on prod with the user account you reserved

logremoveprod.bat
set ORACLE_SID={ORACLE_SID}
set LOG_DEST={log_archive_dest}\OLD
set SCRIPT_DEST={where to place this script}
set LOGFILE=%SCRIPT_DEST%\logremoveprod.log
set TEMP_FILE=%SCRIPT_DEST%\logremoveprod.tmp
set TEMP_FILE2=%SCRIPT_DEST%\logremoveprod2.tmp
set LASTLOG=199999
echo.|time|date|find "current" >> %LOGFILE%
set LAST_ARCHNO=NONE
for /F "eol=; tokens=1" %%i in (\\{standby server}\scripts\lastarchno.txt) do @set LAST_ARCHNO=%%i
echo last archive is %LAST_ARCHNO% >> %LOGFILE%
rem
rem Following checks if log is recycled to beginning, then exits script. This stuation should be handled manually
rem
for %%i in (%LOG_DEST%\*%LASTLOG%.ARC) do goto end
rem
rem Following checks if last archive log is valid
rem
for %%i in (%LOG_DEST%\*%LAST_ARCHNO%.ARC) do goto cont
echo Last archive is not valid >> %LOGFILE%
goto end
:cont
echo checking archive validity >> %LOGFILE%
dir /O-N %LOG_DEST%\*.ARC |find ".ARC" |awk "{print $4}">%TEMP_FILE%
:rpt
set CURRENT_FILE=NONE
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @set CURRENT_FILE=%%i
if "%CURRENT_FILE%" == "NONE" goto hata
call set TMP_STR=%%CURRENT_FILE:%LAST_ARCHNO%=%%
if NOT "%TMP_STR%" == "%CURRENT_FILE%" goto cnt
type %TEMP_FILE%|find /v "%CURRENT_FILE%" > %TEMP_FILE2%
type %TEMP_FILE2% > %TEMP_FILE%
goto rpt
:hata
echo Applied last archive does not exists in list >> %LOGFILE%
goto end
:cnt
echo deleting archives >> %LOGFILE%
dir /O-N %LOG_DEST%\*.ARC |find ".ARC" |awk "{print $4}">%TEMP_FILE%
:repeat
set CURRENT_FILE=NONE
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @set CURRENT_FILE=%%i
if "%CURRENT_FILE%" == "NONE" goto end
call set TMP_STR=%%CURRENT_FILE:%LAST_ARCHNO%=%%
if NOT "%TMP_STR%" == "%CURRENT_FILE%" goto end
type %TEMP_FILE%|find /v "%CURRENT_FILE%" > %TEMP_FILE2%
type %TEMP_FILE2% > %TEMP_FILE%
set CURRENT_FILE=%LOG_DEST%\%CURRENT_FILE%
echo deleting %CURRENT_FILE% >> %LOGFILE%
del /Q %CURRENT_FILE% >> %LOGFILE%
goto repeat
:end
del /Q %TEMP_FILE% >> %LOGFILE%
del /Q %TEMP_FILE2% >> %LOGFILE%