Scribbling Notes

ERROR: PST-initiated MANDATORY DISMOUNT of group DATA

2011-09-14T11:12:00.000-07:00

It means ASM is not able to offline the disks having issues so it dismount the entire diskgroup -

https://twiki.cern.ch/twiki/bin/view/PSSGroup/ASM_Internals

PST - Partner Status Table. Maintains info on disk-to-diskgroup membership.
COD - Continuing Operation Directory. The COD structure maintains the state of active ASM operations or changes, such as disk or datafile drop/add. The COD log record is either committed or rolled back based on the success of the operation. (source Oracle whitepaper)
ACD - Active Change Directory. The ACD is analogous to a redo log, where changes to the metadata are logged. The ACD log record is used to determine point of recovery in the case of ASM operation failures or instance failures. (source Oracle whitepaper)
OSM Oracle Storage Manager, legacy name, synonymous of ASM
CSS Cluster Synchronization Services. Part of Oracle clusterware, mandatory with ASM even in single instance. CSS is used to heartbeat the health of the ASM instances.
RBAL - Oracle backgroud process. In an ASM instance coordinated rebalancing operations. In a DB instance, opens and mount diskgroups from the local ASM instance.
ARBx - Oracle backgroud processes. In an ASM instance, a slave for rebalancing operations
PSPx - Oracle backgroud processes. In an ASM instance, Process Spawners
GMON - Oracle backgroud processes. In an ASM instance, diskgroup monitor.
ASMB - Oracle backgroud process. In an DB instance, keeps a (bequeath) persistent DB connection to the local ASM instance. Provides hearthbeat and ASM statistics. During a diskgroup rebalancing operation ASM communicates to the DB AU changes via this connection.
O00x - Oracle backgroud processes. Slaves used to connected from the DB to the ASM instance for 'short operations'.

ALTER DATABASE RECOVER TO LOGICAL STANDBY struck

2011-05-17T10:08:00.000-07:00

738643.1

one of the many reason - the

DBMS_LOGSTDBY.BUILD;

didnt finish yet.

orapwd and alter user for sys

2011-05-16T10:34:00.000-07:00

alter user for sys updates the orapwd file .

but changing the password thru orapwd do not change the data dictionary (sys password) - again this is my assumption on my observation ( check out?)

physical standby on realtime apply

2011-05-01T22:03:00.000-07:00

SQL> recover managed standby database using current logfile disconnect from session;

ORA-15110: no diskgroups mounted

2011-03-31T22:52:00.001-07:00

check if you are on spfile or not .

rman duplicate from active database

2011-03-31T14:32:00.000-07:00

1- TNS entry on both side

2- listener configuration is done

3- password for target and auxiliary should be same ( my personal observation)

4- use TNS(@ for both target and auxiliary

5- check the service name(dbms_service/listener)

ORA-16211: unsupported record found in the archived redo log

2011-03-23T19:06:00.000-07:00

getting

ORA-16211: unsupported record found in the archived redo log

on the logical standby database can be due to having force logging not enabled.

asm dropping disk

2011-03-10T20:17:00.000-08:00

for

ORA-01948: identifier's name length (31) exceeds maximum (30)

use the name from

select group_number, name, TOTAL_MB, FREE_MB

from V$asm_disk_stat;

alter diskgroup tan1_1 drop disk ' TAN1_2_0008';

ORA-1031 while connecting sys as sysasm

2011-03-02T17:15:00.001-08:00

v$pwfile_users

grant sysasm to sys

oracle restart - asm status

2011-02-28T16:44:00.000-08:00

somehow it doesn't show online after adding into crs stack

srvctl add asm -l LISTENER

but after bouncing the asm it shows properly

crsctl start resource ora.asm - forces a bounce.

alter system switch logfile hung

2011-02-14T13:35:00.000-08:00

most of the time

ALTER SYSTEM ARCHIVE LOG CURRENT clears it

unmounting asm diskgroup do not release the file descriptor

2011-02-05T03:48:00.000-08:00

issuing

select * from v$asm_disk in 11gR2 after the unmount/drop disk command. or apply the one off patch in 10g clears the file descriptor.

can be verified lsof /dev/rdisk/disk44 ( or if there is LVM lsof -d /dev/dgsap01 of lsof /dev/dgsap01/rasm* )

metalink Note - 402526.1

ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]

2011-01-06T09:44:00.001-08:00

in 11gR2

SQL> create spfile from pfile;

create spfile from pfile

ERROR at line 1:

ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]

lsts[0]]

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl stat res

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.evmd

TYPE=ora.evm.type

TARGET=ONLINE

STATE=ONLINE on m3p131

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: srvctl add asm -l LISTENER

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl stat res

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.asm

TYPE=ora.asm.type

TARGET=OFFLINE

STATE=OFFLINE

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.evmd

TYPE=ora.evm.type

TARGET=ONLINE

STATE=ONLINE on m3p131

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs:

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: sqlplus '/as sysasm'

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 6 09:43:53 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Automatic Storage Management option

SQL> create spfile from memory;

File created.

ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]

2011-01-06T09:44:00.000-08:00

in 11gR2

SQL> create spfile from pfile;

create spfile from pfile

ERROR at line 1:

ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]

lsts[0]]

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl stat res

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.evmd

TYPE=ora.evm.type

TARGET=ONLINE

STATE=ONLINE on m3p131

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: srvctl add asm -l LISTENER

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl stat res

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.asm

TYPE=ora.asm.type

TARGET=OFFLINE

STATE=OFFLINE

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=ONLINE

STATE=ONLINE on m3p131

NAME=ora.evmd

TYPE=ora.evm.type

TARGET=ONLINE

STATE=ONLINE on m3p131

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs:

m3p131:+ASM:/apps/oracle/product/11.2.0/grid/dbs: sqlplus '/as sysasm'

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 6 09:43:53 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Automatic Storage Management option

SQL> create spfile from memory;

File created.

RMAN-06024 during duplicate database for standby

2011-01-05T16:12:00.000-08:00

RMAN-06024 during duplicate database for standby

RMAN-06024: no backup or copy of the control file found to restore

alter system switch logfile on target should fix this

RMAN Duplicate For Standby Fails with Rman-06024: No Backup Or Copy Of The Control File Found To Restore [ID 466321.1]

ORA-00600: internal error code, arguments: [kfmrgReg01]

2010-12-17T15:44:00.000-08:00

while creating a diskgroup on a 10.2.0.4 ASM we encountered

ORA-00600: internal error code, arguments: [kfmrgReg01], [24287664],

[84759367], [], [], [], [], []

tried dd,rebuilding volume group but eventually shutting down the asm removed this issue. asm gone crazy

ASM -renamedg KFNDG-00305: file not found

2010-12-14T14:31:00.000-08:00

make sure you are issuing the renamedg from dba owned directory . as the renamedg create a file called rename_dg

and also make sure the diskgroup is dismounted

ORA-17628: Oracle error 19505 returned by remote Oracle server

2010-12-10T04:01:00.000-08:00

precreate the contolfile directory in asm diskgroup then retry the duplicate from active database

rman duplicate from active database -PARAMETER_VALUE_CONVERT

2010-12-09T18:16:00.001-08:00

incorrect/incomplete PARAMETER_VALUE_CONVERT can crash your target database . my target database crashed as i missed

it from rman duplicate while just trying to create the database with init.ora having only db_name entry

my controlfile/datafile/online log file in asm so i had to do as below

DUPLICATE TARGET DATABASE

TO t11gdact

FROM ACTIVE DATABASE

DB_FILE_NAME_CONVERT '+DATA/t11gdb/','+DATA/t11gdact/'

SPFILE

PARAMETER_VALUE_CONVERT '/u03/arch/t11gdb/','/u03/arch/t11gdact/','+DATA/t11gdb/','+DATA/t11gdact/'

SET SGA_MAX_SIZE '300M'

SET SGA_TARGET '250M'

SET LOG_FILE_NAME_CONVERT '+DATA/t11gdb/',

'+DATA/t11gdact/';

HPUX_SCHED_NOAGE

2010-12-07T16:33:00.001-08:00

a must for oracle in hp-ux

ORA-29701: unable to connect to Cluster Manager on

2010-12-07T14:52:00.000-08:00

checking the grep cssd I saw it was running as root user

+ASM:/home/oracle $ ps -ef | grep css

root 60799 20 0 10:14:39 ? 0:00 /bin/sh /sbin/init.d/init.cssd run

then asking the SA to do

localconfig delete

localconfig add

under OH fixed the issue by starting css the right way

oracle 35487 20 0 14:10:33 ? 0:00 /apps/oracle/product/10.2.0.3/bin/ocssd.bin

on 11gR2

mp012:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl start resource ora.cssd

CRS-2672: Attempting to start 'ora.cssd' on 'mp012'

CRS-2672: Attempting to start 'ora.diskmon' on 'mp012'

CRS-2676: Start of 'ora.diskmon' on 'mp012' succeeded

CRS-2676: Start of 'ora.cssd' on 'mp012' succeeded

mp012:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl modify resource "ora.cssd" -attr "AUTO_START=1"

mp012:+ASM:/apps/oracle/product/11.2.0/grid/dbs: crsctl modify resource "ora.diskmon" -attr "AUTO_START=1"

ORA-15096: lost disk write detected

2010-12-05T16:19:00.000-08:00

one of the asm diskgroup after dropping it was still showing in v$asm_disk with error state. bringing down the asm didnt help either. so had to do dd to clean up the disk properly and then was able to recreate it.

ORA-15037:disk '/dev/asmvg02/rasmdisk02' is smaller than mimimum of 4 MBs

2010-12-05T16:10:00.000-08:00

while creating a asm diskgroup I encountered this issue .

SQL> create diskgroup DFDG external redundancy disk

'/dev/asmvg02/rasmdisk02'; 2

create diskgroup DFDG external redundancy disk

ERROR at line 1:

ORA-15018: diskgroup cannot be created

ORA-15031: disk specification '/dev/asmvg02/rasmdisk02' matches no disks

ORA-15037: disk '/dev/asmvg02/rasmdisk02' is smaller than mimimum of 4 MBs

asking the SA he verified

vgdisplay -v /dev/asmvg02

diskinfo /dev/rdisk/disk11

and compared with the other working diskgroup disks.

but it didnt give any indication why i was hitting this error for this new diskgroup i am trying to create . So i gave a shot at dd and ifter that i was able to create diskgroup

+ASM:/dev/asmvg02: dd if=/dev/zero of=/dev/asmvg02/rasmdisk02 bs=4096 count=12800

12800+0 records in

12800+0 records out

+ASM:/dev/asmvg02: sqlplus '/as sysasm'

SQL> create diskgroup DFDG external redundancy disk

'/dev/asmvg02/rasmdisk02'; 2

Diskgroup created.

ORA-15063: ASM discovered an insufficient number of disks for diskgroup

2010-12-05T16:07:00.000-08:00

We encountered this issue after the san copy is done.

ORA-15032: not all alterations performed

ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DFDATA_2"

ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DFDATA_1"

Looking into asm_diskstring dont seem to be any issue . however seems like the device mapping file was changed so the /dev/asmvg01 directory was not showing the proper asm disk group name .

once the SA fixed the device mapping file it recognized the asm disks and the system back online

kill -9 doesn't work always

2010-05-09T19:40:00.000-07:00

well it didnt for me on a RHEL 5.3 for a process accessing NFS filesystem.

check - fuser -k

ORA-12853 / ORA-4031

2010-04-21T20:54:00.000-07:00

ORA-12801: error signaled in parallel query server P002, instance xt0p-2:stpa2 (

2) ORA-12853: insufficient memory for PX buffers: current 68544K, max needed 15360K

ORA-04031: unable to allocate 131120 bytes of shared memory (""shared pool"",""unknown object"",""sga heap(2,0)"",""PX msg pool"")

3 node rac . parallel query process was spawning to node -2 . increasing the shared pool size fixed the query . and also changed the parallel instance groups.

ORA-14266: data type or length of an index subpartitioning column may

2010-04-11T19:29:00.000-07:00

I was getting this error while trying to alter the data type for a column. as per the error . but as per the cause /action for this error there were no indexes/partitions on this column .

was not sure what is going on so i just dropped all the indexes( few normal index and partioned bitmap indexes) for this table and then when i try to do the alter it went fine .

move tablespace

2010-03-09T15:29:00.001-08:00

select 'alter table mtsuser.'||table_name || ' move tablespace mtsuser_tab_data ;'

from dba_tables

where owner='MTSUSER'

select 'alter index mtsuser.'||index_name || ' rebuild tablespace mtsuser_IND_DATA ;'

from dba_indexes

where table_owner='MTSUSER'

set linesize 500

SELECT 'ALTER TABLE mtsuser.'||table_name ||' move lob('||column_name||') store as (tablespace mtsuser_tab_data );' FROM DBA_LOBS

WHERE owner='MTSUSER'

select 'alter table mtsuser.'||TABLE_NAME ||' move partition '||PARTITION_NAME||' tablespace mtsuser_data;' from dba_tab_partitions

where table_owner='MTSUSER'

select 'ALTER INDEX mtsuser.'||index_name ||' rebuild partition ' || partition_name ||' tablespace tnarchive_index ;'

from dba_ind_partitions

where index_owner='MTSUSER'

select 'alter table mtsuser.'||table_name ||' move partition '||partition_name||' tablespace mtsuser_data lob('||column_name ||') store as

(tablespace mtsuser_lob_data);'

from dba_lob_partitions

where table_owner='MTSUSER'

remote_listener -

2010-01-13T14:32:00.000-08:00

It is recommended to set it up in RAC enviornment . but setting it up in regular standby databse gives rise to grid control alert ( observe the service update on both the network log files ) at times the grid control alert are misleading .

a good reference

http://oracledocaccess.blogspot.com/2009/04/remote-listener.html

UNDO in 9i and 10g

2009-12-23T13:51:00.000-08:00

it is advisable to have AUTEXTEND OFF or have a suitable MAXSIZE in UNDO datafiles.

ref-

http://www.orafaq.com/node/61

best practice

This following list of recommendations will help you manage your undo space to best advantage.

You need not set a value for the UNDO_RETENTION parameter unless your system has flashback or LOB retention requirements.
Allow 10 to 20% extra space in your undo tablespace to provide for some fluctuation in your workload.
Set the warning and critical alert thresholds for the undo tablespace alert properly. Please refer to "Managing Space in Tablespaces " for information on how to set alert thresholds for the undo tablespace.
To tune SQL queries or to check on runaway queries, use the value of the SQLID column provided in the long query or in the V$UNDOSTAT or WRH$_UNDOSTAT views to retrieve SQL text and other details on the SQL fromV$SQL view.

Always use indexes for Flashback Version Query.

ref oracle docs

Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger

In the following example, a system wide AFTER SUSPEND trigger is created and registered as user SYS at the database level. Whenever a resumable statement is suspended in any session, this trigger can have either of two effects:

If an undo segment has reached its space limit, then a message is sent to the DBA and the statement is aborted.
If any other recoverable error has occurred, the timeout interval is reset to 8 hours.

Here are the statements for this example:

CREATE OR REPLACE TRIGGER resumable_default AFTER SUSPEND ON DATABASE DECLARE    /* declare transaction in this trigger is autonomous */    /* this is not required because transactions within a trigger       are always autonomous */    PRAGMA AUTONOMOUS_TRANSACTION;    cur_sid           NUMBER;    cur_inst          NUMBER;    errno             NUMBER;    err_type          VARCHAR2;    object_owner      VARCHAR2;    object_type       VARCHAR2;    table_space_name  VARCHAR2;    object_name       VARCHAR2;    sub_object_name   VARCHAR2;    error_txt         VARCHAR2;    msg_body          VARCHAR2;    ret_value         BOOLEAN;    mail_conn         UTL_SMTP.CONNECTION; BEGIN    -- Get session ID    SELECT DISTINCT(SID) INTO cur_SID FROM V$MYSTAT;     -- Get instance number    cur_inst := userenv('instance');     -- Get space error information    ret_value :=     DBMS_RESUMABLE.SPACE_ERROR_INFO(err_type,object_type,object_owner,         table_space_name,object_name, sub_object_name);    /*    -- If the error is related to undo segments, log error, send email    -- to DBA, and abort the statement. Otherwise, set timeout to 8 hours.    --     -- sys.rbs_error is a table which is to be    -- created by a DBA manually and defined as    -- (sql_text VARCHAR2(1000), error_msg VARCHAR2(4000),    -- suspend_time DATE)    */     IF OBJECT_TYPE = 'UNDO SEGMENT' THEN        /* LOG ERROR */        INSERT INTO sys.rbs_error (            SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME            FROM DBMS_RESUMABLE            WHERE SESSION_ID = cur_sid AND INSTANCE_ID = cur_inst         );        SELECT ERROR_MSG INTO error_txt FROM DBMS_RESUMABLE             WHERE SESSION_ID = cur_sid and INSTANCE_ID = cur_inst;          -- Send email to receipient via UTL_SMTP package         msg_body:='Subject: Space Error Occurred                     Space limit reached for undo segment ' || object_name ||                     on ' || TO_CHAR(SYSDATE, 'Month dd, YYYY, HH:MIam') ||                    '. Error message was ' || error_txt;          mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25);         UTL_SMTP.HELO(mail_conn, 'localhost');         UTL_SMTP.MAIL(mail_conn, 'sender@localhost');         UTL_SMTP.RCPT(mail_conn, 'recipient@localhost');         UTL_SMTP.DATA(mail_conn, msg_body);         UTL_SMTP.QUIT(mail_conn);          -- Abort the statement         DBMS_RESUMABLE.ABORT(cur_sid);     ELSE         -- Set timeout to 8 hours         DBMS_RESUMABLE.SET_TIMEOUT(28800);     END IF;      /* commit autonomous transaction */     COMMIT;    END;

pga_aggregate_target

2009-12-23T13:20:00.000-08:00

v$pga_target_advice - estd_pga_cache_hit_percentage

v$pgastat - maximum PGA allocated

dba_hist_pgastat

from doc

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater

init parameter

pga_aggregate_target

hidden parameters

_pga_max_size

_smm_px_max_size

Tuning - check the hit , firstpass and multipass

--eliminate overalloc and maximize cache hit

SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024/1024) target_in_GB,

ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

ESTD_OVERALLOC_COUNT

FROM v$pga_target_advice

SELECT    program,    pga_used_mem "PGA Used",    pga_alloc_mem "PGA Alloc",    pga_max_mem "PGA Max"   FROM v$process;

SELECT    low_optimal_size/1024 "Low (K)",    (high_optimal_size + 1)/1024 "High (K)",    optimal_executions "Optimal",    onepass_executions "1-Pass",    multipasses_executions ">1 Pass"   FROM v$sql_workarea_histogram  WHERE total_executions <> 0;

check multipass
SELECT    optimal_count "Optimal",    round(optimal_count * 100 / total,2) "Optimal %",    onepass_count "OnePass",    round(onepass_count * 100 / total,2) "Onepass %",    multipass_count "MultiPass",    round(multipass_count * 100 / total,2) "Multipass %"   FROM (       SELECT          DECODE (SUM(total_executions), 0, 1, SUM(total_executions)) total,          SUM(optimal_executions) optimal_count,          SUM(onepass_executions) onepass_count,          SUM(multipasses_executions) multipass_count         FROM v$sql_workarea_histogram -- Limits consideration of queries with LOW_OPTIMAL_SIZE limit <64k> 64*1024)

SELECT *   FROM (SELECT             workarea_address,             operation_type,             policy,             estimated_optimal_size           FROM v$sql_workarea       ORDER BY estimated_optimal_size)  WHERE ROWNUM <= 20;

ref-

http://www.dba-oracle.com/tips_pga_aggregate_target.htm

it is important to remember that automatic PGA memory management via PGA_AGGREGATE_TARGET only applies to dedicated server connections. Memory allocation forshared server connections is still managed by SORT_AREA_SIZE, HASH_AREA_SIZE, and so forth

http://www.databasejournal.com/features/oracle/article.php/10893_2109921_1/Activating-Monitoring-and-Tuning-Automatic-PGA-Memory-Management-via-PGAAGGREGATETARGET-Under-Oracle-9i.htm

tasklist -v in windows

2009-12-05T23:51:00.000-08:00

works same as ps -ef ................

dbca inside dmz server

2009-06-23T10:50:00.000-07:00

doing the regular display setup do not work when you are inside dmz server .

so I had to do this .

keep your exceed open. ssh using my loginid first with X11 tunnelling on .

as user ou1234>

find the display by env . it should be something like
DISPLAY=localhost:11.0

then you should see a .Xauthority file in user ou1234's home directory .

do a chmod on this file . better put this inside user ou1234's .profile

$ cat .profile
chmod 660 .Xauthority

and then su as the oracle user

su - oracle

and then export DISPLAY to the display as you found inside ou1234's env variable
As oracle user
$ export DISPLAY=localhost:11.0
$ export XAUTHORITY=/home/nrusip01/.Xauthority
$ xclock

now you are ready to do any gui actvity.

recovery scenario where too many archived log

2009-04-22T16:45:00.000-07:00

check

v$log_history,

v$archived_log

V$RECOVERY_LOG -

[

Lists only the archived redo logs that Oracle needs to perform media recovery. It also includes the probable names of the files, using LOG_ARCHIVE_FORMAT.

Note: This view is only populated when recovery is required for a datafile. Hence, this view is not useful in the case of a planned recovery such as a user error.

]

If sufficient space is not available at the location indicated by the archiving destination initialization parameter, restore some or all of the required archived redo log files to an alternate location. Specify the location before or during media recovery using the LOGSOURCE parameter of the SET statement in SQL*Plus or the RECOVER ... FROM parameter of the ALTER DATABASE statement in SQL. For example, enter:

SET LOGSOURCE /disk2/temp   # set location using SET statement
DATABASE RECOVER FROM '/disk2/temp';  # set location in RECOVER statement itself

in rman
SET ARCHIVELOG DESTINATION TO  command

for temporary change
ARCHIVE LOG START '';

xming

2009-03-17T18:04:00.000-07:00

a free alternative for exceed.

cygwin installation

2009-03-13T13:54:00.001-07:00

mirrors.kernel.org(http) . make sure vim is checked.

recovery using online redo logs for a norarchivelog database

2009-03-09T19:08:00.000-07:00

after the platform services did the san migration somehow one of the datafile became corrupted and while i try to open the database it complains. this database is a norachivelog database . so I just felt lucky on how it worked . the platform services had taken a o/s backup while the database was running the around 1am before they did the san migration.

Database mounted.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01201: file 7 header failed to write correctly

so at this point I asked them to restore the datafile 7 that they took from last night . even though this waas a production database but there were no activity during the night on this weekend .

so once they restored the file . I mounted

SQL> recover database using backup controlfile;
ORA-00279: change 2213499829905 generated at 02/21/2009 08:43:13 needed for
thread 1
ORA-00289: suggestion :
/rgw/ora_bkup/flash_recovery_area/RGWP20/archivelog/2009_02_22/o1_mf_1_10088_%u_
.arc
ORA-00280: change 2213499829905 for thread 1 is in sequence #10088

Specify log: {=suggested | filename | AUTO | CANCEL}
/rgw/u2/oradata/rgwp20/redo02.log
ORA-00279: change 2213502398486 generated at 02/21/2009 22:07:33 needed for
thread 1
ORA-00289: suggestion :
/rgw/ora_bkup/flash_recovery_area/RGWP20/archivelog/2009_02_22/o1_mf_1_10089_%u_
.arc
ORA-00280: change 2213502398486 for thread 1 is in sequence #10089
ORA-00278: log file '/rgw/u2/oradata/rgwp20/redo02.log' no longer needed for
this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/rgw/u2/oradata/rgwp20/redo03.log
Log applied.
Media recovery complete.

--what a relief it was for me . because if it would have any issue further then probably i would have to go through a export restore . then I did

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> select * from v$recover_file;

no rows selected

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10087
Current log sequence 10089
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
0

SQL> alter database open resetlogs;

Database altered.

the database is back up and i am a happy man.

**** alert log .
alter database open resetlogs
Sun Feb 22 13:02:00 2009
RESETLOGS after complete recovery through change 2213530385545
Resetting resetlogs activation ID 1283279895 (0x4c7d4c17)
Sun Feb 22 13:02:05 2009
Setting recovery target incarnation to 2
Sun Feb 22 13:02:05 2009
Assigning activation ID 1331518248 (0x4f5d5b28)

***

but even though this production database was running in noarchivelog but still this database is a very highly visible database so I just did few homeowrk to make sure that everything is fine .

1-
SQL> begin
for r in( select owner,table_name from dba_tables where owner='RGWP20') loop
dbms_output.put_line( r.table_name );
dbms_stats.gather_table_stats( r.owner, r.table_name, estimate_percent => 5,
degree => 16, granularity => 'ALL', cascade => true,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1' );
end loop;
end; 2 3 4 5 6 7 8
9 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

2#took a export backup and compare the the backup before and now for
the tables /records

my lesson- a production database should always be in archivelog if you want life to be less stressful. but it is hard here to convince the application manager that we should convert it to archivelog. but will try again.

controlfile recovery for a db having few readonly tablespaces

2009-03-09T18:49:00.001-07:00

here is the situation- The platform services took a cold backup of a UAT database before 3 weeks. and now the application group want that backup to be restored to the UAT . so I asked the platform services to go ahead and overwrite the existing UAT db related filesystems.

after they restored the cold backup I try to open the database and it complains . so I had to list out all the datafiles(including the readonly datafiles( though I dont know which datafiles are readonly from these 250+ datafiles )) . and recreate the controlfile . then i did a recover database using backup controlfile . and somehow it asks for the log file so I had to do a recovery using online redo logs . and then i opened the database with resetlogs . after that still confused about the read only tablespaces I did a status check in dba_tablespaces and I found that the data dictionary had all the readonly ts information and even though the controlfile was recreated but still the database was able to indentify properly .

-not that of a big deal but it was a good learning for me .
lesson- never keep the controlfile outside the datafile filesystem. becuase it is a lot of headache when you took a cold backup and then keep scratching your head that why you are not able to use the backup to restore it . always make sure that a cold backup backs up all the files .

The readonly tablespaces hit me back . when the user reported this error.

ORA-00376: file 71 cannot be read at this time

ORA-01111: name for data file 71 is unknown - rename to correct file

ORA-01110: data file 71: '/u1/app/oracle/product/9.2.0/dbs/MISSING00071'

so I had to to just do trial and error

select file_id,file_name from dba_data_files
where file_name like '%MISS%'

select * from v$datafile where status='OFFLINE'

SQL> alter tablespace PDB_IX_DS1 online;
alter tablespace PDB_IX_DS1 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 246 - see DBWR trace file
ORA-01111: name for data file 246 is unknown - rename to correct file
ORA-01110: data file 246: '/u1/app/oracle/product/9.2.0/dbs/MISSING00246'

doing a alter datbase backup controlfile to trace reported these errors

ALTER DATABASE RENAME FILE 'MISSING00227'
# WARNING! This command can not be completed because the file
# needs to be renamed to a real file name.

anyway I guess I was just lucky . somehow able to patch up the missing links. but I will make sure to take a alter database backup controfile to trace for any database specifically with readonly tablespace before I do a cold backup.

it seems oradebug dump helps to identify the tablespace/datafile relationship . thanks to Rijaj in oracle list . have to try.

vmware windows xp guest disk expansion

2009-02-03T21:31:00.000-08:00

step -1 in host o/s
C:\ "C:\Program Files\VMware\VMware Workstation\v
mware-vdiskmanager" -x 10GB "C:\Virtual Machines\Windows XP Professional\Windows
XP Professional.vmdk"
Disk expansion completed successfully.

WARNING: If the virtual disk is partitioned, you must use a third-party
utility in the virtual machine to expand the size of the
partitions. For more information, see:
http://www.vmware.com/support/kb/enduser/std_adp.php?p_faqid=1647

step 2 - in guest o/s

used the home edition to resize the drive

http://www.partition-tool.com/easeus-partition-manager/help/resizing-and-moving-partition.htm

Dataguard switchover on 10g

2008-12-02T18:43:00.000-08:00

After doing a dataguard switchover (not using dgmgrl ) these were few common cases you might come across.

1- switch_over status in primary was NOT ALLOWED

I found that the log shipping was stopped to the new standby and
select database_role,switchover_status from v$database;
PRIMARY , NOT ALLOWED .

It did not put any alerts in alert.log or any message on v$dataguard_status . I was totally clueless about what is going on here. then I found that the log_archive_dest_state_2 which was originally set as defer was still defer after the switchover . as soon as I turn it into enable at the new primary it started sending the logs.

2- temp files were recreated on the new primary.

Errors in file /u1/app/oracle/admin/dtrt/bdump/dtrt_dbw0_8307.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/rgwt/u6/oradata/dtrt/temp01.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
File 201 not verified due to error ORA-01157
Tue Dec 2 14:31:12 2008
Dictionary check complete
Tue Dec 2 14:31:12 2008
SMON: enabling tx recovery
Tue Dec 2 14:31:12 2008
Re-creating tempfile /rgwt/u6/oradata/dtrt/temp01.dbf

3-standby redo log on the new standby.

if you have configured the standby redo log on the old standby and then you did not configured on the old primary then you might as well create it

startup nomount;
alter database mount standby database;
alter database add standby logfile group 4 ('/rgwt/u2/oradata/dtrt/sredo41.rlog') size 50M;
alter database recover managed standby database disconnect from session;

then verify v$standby_log ,v$logfile

4- client tns .

i had to cut paste the old standby address to top .

dtrt_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = xt33db002)
(Port = 2483)
)
(ADDRESS =
(PROTOCOL = TCP)
(Host = xt33db003)
(Port = 1521)
)
)
(CONNECT_DATA = (SERVICE_NAME = dtrt)
)
)

Oracle OpenWorld 2008

2008-09-21T22:04:00.000-07:00

First day at the Oracle Openworld .

Mr Alex Gorbachov's Clusterware presentation and Mr Chintala's presentation on Dell IT was very informative.

oracle 10g database characterset change

2008-07-10T19:04:00.000-07:00

ORACLE DB version 10.2.0.3

step 1- run csminst.sql it will create a user to hold csscan result. which the csalter will use to convert

step 2-Use csscan to scan the database . make sure that in the output file csscan creates you dont see any of your application user table. if you see then you must have to export and truncate those tables.

csscan \"sys/@10GDB3 as sysdba \" FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=UTF8 LOG=WE8check_jul10

step 3 - run the csalter.plb to change the database characterset in startup restrict mode. needless to say you must have taken a full backup before you run csalter.plb

SQL> @?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validility...
begin converting system objects
12722 rows in table SYS.WRH$_SQL_PLAN are converted
80 rows in table SYS.METASTYLESHEET are converted
53 rows in table SYS.WRI$_ADV_ACTIONS are converted
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
169 rows in table SYS.WRI$_ADV_OBJECTS are converted
900 rows in table SYS.WRH$_SQLTEXT are converted
38 rows in table SYS.WRI$_ADV_RATIONALE are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
4 rows in table SYS.RULE$ are converted
21 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted

PL/SQL procedure successfully completed.

Alter the database character set...
CSALTER operation completed, please restart database

PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

step 4- do this
SQL> alter database national character set UTF8;

Database altered.

step 5- check output 1 select * from nls_database_parameters
2* where parameter like '%CHARACTERSET%'

SQL> /

PARAMETER VALUE
------------------------------ -----------------------------------
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET UTF8

it shows that we changed the database characterset as well as the national characterset to UTF8.

I had a nice lesson while doing this process. I first took the export dump of all the user tables having nclob data reported by csscan . and then i truncated the tables. However I again got into problem while doing csalter . it gave me the below message

Checking data validility...
Exceptional data found in scanner result

PL/SQL procedure successfully completed.

Checking or Converting phrase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.

again doing the csscan i found 1 more user tables. and while doing export again with the same dump file adding this table I miss I got a dump file which has 0 records for all the other table . not only that I overwrite the dumpfile name . I lost the data but luckily it is a test application otherwise it would have been very painful.

Lesson learned- make sure to take a full export . and never keep the same dump file name while doing a export in the situation as this.

Free Software Heroes.

2008-06-15T11:21:00.000-07:00

I came across a interesting article . Here is the link

http://www.freesoftwaremagazine.com/columns/free_software_heroes_stallman_google_list_inspiring_individuals_who_made_everything_

3 node RAC on Enterprise Linux

2008-04-30T20:34:00.000-07:00

3 node RAC on Enterprise Linux

ocr, voting disk, spfile on raw devices
and the db files on the block device

storage on MSA1000 ( modular smart array) with 2 fc and gigabit ethernet card .
public eth0 , private eth1 , vip eth0:1

-set up user equivalence.
-checking the configuration of hangcheck timer module
-check the rawdevice mapping exists. for ocr,voting and asm spfile e.g raw -qa
-check that the ASM disk are configured for you -these are block device so you upgrade oracle software from 10.2.0.1 to 10.2.0.3 - (right? )

- install 10.2.0.1 clusterware
-patch oracle clusterware to 10.2.0.3 (rolling clusterware patch)

-install db software in one home to be used by both asm and db.
-patch to 10.2.0.3

-Create cluster database with dbca
- choose RAC database
- choose create database
- select all nodes in the cluster
- select general purpose db
- choose a db name prod
- uncheck configure the db for enterprise manager
- specify sys as password for all account
- choose "Automatic storage management(ASM)" as the storage mechanism
- Configuring ASM - since ASM has not yet been configured dbca allows you to do this at this point of database creation
-specify sys as the asm SYS password
-specify our ASM spfile raw device as the spfile for ASM.
-click the create new button to create a new ASM diskgroup DATA
-create another ASM diskgroup FRA
- choose oracle managed file and the +DATA diskgroup
- select speficy flashrecovery area and +FRA diskgroup
- enable archivelog if you want

- on the database services screen create 2 services
"oltp" preferred node1,node2 available node3
"batch" preferred node3 , available node1,node2

- continue clicking default till it finish .

and now " my second RAC install is finished "

my First RAC experience

2008-04-30T20:02:00.000-07:00

This week was amazing for me . on monday I did a 2 node RAC installation on solaris and on Tuesday I did a 3 node RAC on enterprise Linux .

2 node RAC10g on Solaris 10.

two v240 server with EMC clarion storage.
some routine work - user equivalence check , soft link to /usr/local/bin for ssh and scp . make sure your storage lun is outlined. then the public bge0 ,private bge1 ,vip bge0:1 address. (vip is configured by vipca at the end of clusterware. ..)
1- clusterware installation

2- ASM installation on a asm home
2.1 - As part of asm installation on the select configuration option - choose configure Automatic Storage management(ASM)
that leads you to define diskgroup . selected the candidate raw partitions . and the rest follows

3- database installation on a separate db home
3.1 - on the select configuration option choose only the Install database software only .

4- Create the cluster database
4.1 - run dbca- choose Oracle Real Application Clusters database -
on the Select the operation that you want to perform page
choose Configure Automatic Storage Management
on the Node select choose select all
enter the ASM sys user password
on the ASM disk groups it will allow you to create another FRA diskgroup if you want
select the raw parttions that you want to assigned to FRA
you should see for all the diskgroups the state shows mounted(num nodes/num nodes )
Click Finish and it ask you "Do you want to perform another operation ?" Choose Yes
Back to the operations screen where you select database .
on the node selection choose select all...
defined a db template- General purpose
on the management options enter the database name and the sid prefix.
then on step 6 of 16 use the same password for all account
On the storage option screen . select Automatic storage management -
select the DATA diskgroup to be used for the db storage . uncheck FRA
On the database files location screen . select oracle-managed file
Select the specify flash recovery area check box and enter FRA diskgroup to be used for fla
select some sample schema if you please
Create database service if you like ( to differentiate based on usage , batch for 1 node,oltp for both
then click , click
on the Creation option page select the create database option .
then click click click and you got a window
Starting cluster database "DEVL" and its instances "DEVL1,DEVL2 " in progress. ...
and now < I am done with my First RAC >

rman - noarchivelog db - recover noredo

2008-03-13T17:46:00.000-07:00

I encountered few issues while restoring a rman backup when the db is running in noarchivelog mode (i think the online redolog's availability has some influence on the rman backup restore strategy ) . so to be on a safeside I implemented the backup strategy with incremental backup for noarchivelog db. this one I tested on 10.2.0.2

Here is the backup script

============================
script rman_noarch_backup.rcv # ##########
SHUTDOWN IMMEDIATE;
STARTUP FORCE DBA;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
BACKUP
COPIES 1
INCREMENTAL LEVEL 0
MAXSETSIZE 1G
DATABASE
INCLUDE CURRENT CONTROLFILE
TAG 'BACKUP_1'
;
alter database open;
#########################################

and the restore operation is as below

vmtest:/export/home/oracle$ rman target /
RMAN> SET DBID 1177027667
executing command: SET DBID
RMAN> startup nomount;

RMAN> restore controlfile from '/export/home/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/o1_mf_ncnn0_BACKUP_1_3xmdomv3_.bkp';

RMAN> alter database mount;

RMAN> run{
restore database;
recover database noredo;
}

RMAN> alter database open resetlogs;

database opened

Generate schema creation script using datapump

2008-01-31T14:18:00.000-08:00

expdp system/manager directory=tmp_dir dumpfile=a.dmp content=metadata_only schemas=system
impdp system/manager directory=tmp_dir dumpfile=a.dmp sqlfile=p4.sql

moving windows o/s into a ubuntu vmware guest o/s

2008-01-24T12:12:00.000-08:00

software needed to move Windows XP
on a dell latitude D600/D620

my laptop came with windows xp . and my objective is to move away from windows XP to ubuntu .
in order to do that . I need to do 2 things
1- install ubuntu in a separate partition
2- using vmware converter take a backup of existing windows xp and then install vmware server 1.0 in ubuntu and open the windows xp .vmx as a guest vm.

I did a defragment on the 80G which freed 50G . and
1- partition magic - shrink your windows o/s partition and then the freed space.
2- vmware converter
3-ubuntu 7.10 - d600 is 32 bit and d620 is 64 bit . so you need appropriate install CD.
while installing careful at partitioning stage . not to destroy the existing windows installation
- manual
freespace - 54g
swap - primary - 1g
/ - primary - 3gb
/usr -logical - 3g
/tmp - 3g
/home - rest free space.
8. vmware server 1.0

Installing perl in solaris 8

2008-01-23T11:19:00.000-08:00

As root

install ActivePerl-5.8 -
pkgadd -d ActivePerl-5.8.8.822-sun4-solaris-2.6-cc-280952.pkg

then link perl to /usr/bin

need to install

DBI-1.59.tar
DBD-Oracle-1.17.tar

in order to install DBI-1.59

you have to install gcc,cc,make .

to install gcc you have to use pkg-get , pkgadd ....

then link gcc ,
to install make ./reconfigure and link make to /usr/bin/make

bash-2.05$ gcc -v
Reading specs from /opt/csw/gcc3/lib/gcc/sparc-sun-solaris2.8/3.4.5/specs
Configured with: ../sources/gcc-3.4.5/configure --prefix=/opt/csw/gcc3 --with-local-prefix=/opt/csw --without-gnu-as --with-as=/usr/ccs/bin/as --without-gnu-ld --with-ld=/usr/ccs/bin/ld --enable-threads=posix --enable-shared --enable-multilib --enable-nls --with-included-gettext --with-libiconv-prefix=/opt/csw --with-x --enable-java-awt=xlib --enable-languages=all
Thread model: posix
gcc version 3.4.5
bash-2.05$ make -v
GNU Make 3.81
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE.

This program built for sparc-sun-solaris2.9
as root
set your oracle environments

# ORACLE_BASE=/ofs/app/oracle/product
# export ORACLE_BASE
# ORACLE_HOME=$ORACLE_BASE/10.2.0.1
# export ORACLE_HOME
# ORACLE_SID=testasm
# export ORACLE_SID
# LD_LIBRARY_PATH=$ORACLE_HOME/lib32
# export LD_LIBRARY_PATH

then regenerate the makefile for DBI-1.59
perl Makefile.PL
make

to install DBD-Oracle-1.17
perl Makefile.PL
make
make test
make install

bash-2.05$ perl -v

This is perl, v5.8.8 built for sun4-solaris-thread-multi
(with 11 registered patches, see perl -V for more detail)

rman online backup with exclude undo tablespace

2008-01-18T13:15:00.000-08:00

Never try to disaster recover from a backup which was taken online with exclude undo.

the experience was pretty bad while trying to rebuild a test system from production . tried all

_ALLOW_RESETLOGS_CORRUPTION = TRUE
_CORRUPTED_ROLLBACK_SEGMENTS =( )

In UNIX you can issue this command to get the undo segment names:

$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u

recover database using backup controlfile until cancel

alter database open resetlogs

which through the bootstrap error

SMON: enabling cache recovery
Fri Jan 18 12:59:08 2008
Errors in file /ofs/app/oracle/admin/sfods/udump/sfods_ora_29046.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/datastore/sfods03/undotbs01.dbf'
Fri Jan 18 12:59:08 2008
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Fri Jan 18 12:59:08 2008

even tried to adjust_scn till level 7 but no help

ALTER SESSION SET EVENTS '10015 TRACE NAME ADJUST_SCN LEVEL 1';

So a lesson learned . no db full backup from now on using exclude undo.

NO MORE EXCLUDE UNDO -

calculate exp dump size for oracle db

2008-01-18T12:51:00.000-08:00

Thanks to Jared-

select sum(bytes)/(1024*1024*1024) bytes
from dba_segments
where segment_type not in (
'CACHE',
'ROLLBACK',
'TYPE2',
'UNDO',
'INDEX'
)

10% variation

df shows 100% on a empty filesystem

2008-01-17T16:16:00.000-08:00

In one of my oracle 10gR2 db on sunos8 server even though the archivelog filesystem was empty
but still df shows that it is 100% . so I had to do the below with the help of oracle-l

with Lisa Brandenburg's advice I did a
alter system set log_archive_max_processes=1 . which cleared arch1 process from
/ofs/archlogs01 . but still the arch0 process was active on this . which I could not kill but that brought down the filesystem from 100% to 52%.

I had to use fuser,

then on Tanel Podar's Advice I changed the archivelog moving script to check for fuser

#!/usr/bin/bash

ARCH_DEST=/ofs/archlogs02/oracle/PRRS
BACKUP_DEST=/obackup/oracle/PRRS/archlogs
for i in $ARCH_DEST/*.log ; do
echo "testing fuser" `fuser $i`
if [ -t `fuser $i` ] ; then
mv $i $BACKUP_DEST
echo "File $i is not open"
else
echo "`date` File $i still open..."
fi

done

exit 0

I had to use lsof to check the active process on the server.

opmnctl hangs while trying to start the grid control

2008-01-14T17:13:00.000-08:00

after switching the O/S from suse 9 to RH 5 when I try to start the opmnctl it just hanged . and then upon investigation around the log file I found that it do not find few .so files. so just installing those dependent rpms the grid worked fine .

TEMP space error

2007-12-20T15:44:00.001-08:00

dealing with ORA-01652 : unable to extend temp segment by 128 in tablespace FCPTEST

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

ALTER SESSION SET EVENTS '1652 trace name errorstack level 1 ';

or
alter system set events '1652 trace name errorstack level 1 ';
alter system set events '1652 trace name errorstack off ';

To deactivate:

ALTER SESSION SET EVENTS '1652 trace name context off';
ALTER SYSTEM SET EVENTS '1652 trace name context off';

monitoring sort space by statement-
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

alter database tempfile '/metrix/mtrx01/oracle/mtrx/temp01.dbf' resize 8G;

monitoring sort space by session-
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid,
S.module, S.program,
SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

monitoring temporary segments
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

1. Obtain the Oracle process identifier or the Operating System process
identifier (SPID) from v$process:

> sqlplus /nolog
SQL> connect / as sysdba
SQL> select pid, spid, username from v$process;

PID SPID USERNAME
---- ----- --------
8 25807 oracle

2. Attach to the process using ORADEBUG.

Using the Oracle process identifier:

SQL> oradebug setorapid 8

Unix process pid: 25807, image: oracleV804

- or -

Using the Operating System process identifier:

SQL> oradebug setospid 25807

Oracle pid: 8, Unix process pid: 25807, image: oracleV804

3. Turn on SQL Trace for the session.

SQL> oradebug event 10046 trace name context forever, level 12

Statement processed.

4. Turn off the SQL trace for the session.

SQL> oradebug event 10046 trace name context off

5. Format trace file using TKPROF.

grid control agent refresh

2007-12-19T14:02:00.000-08:00

1. Stop the agent
/bin/emctl stop agent

2. Clean start the agent:

2a Delete current upload and state files

rm -r /sysman/emd/state/*
rm -r /sysman/emd/collection/*
rm -r /sysman/emd/upload/*
rm /sysman/emd/lastupld.xml
rm /sysman/emd/agntstmp.txt
rm /sysman/emd/blackouts.xml
rm /sysman/emd/protocol.ini

2b Start the agent

/bin/emctl start agent

2c Issue an agent clearstate from the agent home

/bin/emctl clearstate agent

3. Resecure the agent (if the agent was secured in the first place).

/bin/emctl secure agent

4. Force an upload to the OMS

/bin/emctl upload

5. Wait 2 minutes.

/metrix/app/oracle/product/agent10g/bin/emctl stop agent

rm -r /metrix/app/oracle/product/agent10g/sysman/emd/state/*
rm -r /metrix/app/oracle/product/agent10g/sysman/emd/collection/*
rm -r /metrix/app/oracle/product/agent10g/sysman/emd/upload/*
rm /metrix/app/oracle/product/agent10g/sysman/emd/lastupld.xml
rm /metrix/app/oracle/product/agent10g/sysman/emd/agntstmp.txt
rm /metrix/app/oracle/product/agent10g/sysman/emd/blackouts.xml
rm /metrix/app/oracle/product/agent10g/sysman/emd/protocol.ini

/metrix/app/oracle/product/agent10g/bin/emctl start agent

/metrix/app/oracle/product/agent10g/bin/emctl clearstate agent

/metrix/app/oracle/product/agent10g/bin/emctl secure agent oracle10g

/metrix/app/oracle/product/agent10g/bin/emctl upload

rman to restore archivelog from backupset and synch a standby db

2007-12-19T11:44:00.000-08:00

To list the backup sets of the archive logs. Where or which
sets we have the archive logs.

RMAN>
list backup of archivelog logseq 5594;

BP Key: 1292331 Status: AVAILABLE Compressed: NO Tag: MANUAL_HOT_BKP_AL_ON_1213071620
Piece Name: /obackup/oracle/mtrx/hot/q4j3gsbs_1_1

List of Archived Logs in backup set 1292328
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 5594 21533054412 13-DEC-07 21533150295 13-DEC-07

cd /obackup/oracle/mtrx/hot/
ls -l | grep -v mtrx

will show all the files and rsync to the standby server:

for x in `ls -l | grep -v mtrx`
do
rsync -avvzrlpogt --stats --progress --rsync-path=/opt/csw/bin/rsync \
$x -e ssh oracle@asb-con-dbs-001:/obackup/oracle/mtrx/hot/
done

NOW on the standby server: asb-con-dbs-001

RMAN> list backup of archivelog sequence between 5594 and 5884;

Allow Rman to recover the database it will restore the archive
logs in the original place it picked them up.
you can verify by using ls -l /metrix/archlogs01/oracle/

Now to remove the old archive logs from standby db using rman:
DELETE noprompt ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE disk;

rman compressed backupset

2007-12-18T15:05:00.001-08:00

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET

configure device type disk clear;

database recovery

2007-12-14T19:06:00.000-08:00

Dec 13 after work hour 7pm-
I had this task to move the datafile from one filesystem to another filesystem and change the names.this production db was not so big (only around 75gb+ ) so I started moving the file one by one . and it was getting late so my friend coworker Howard volunteered to move few files from the bottom while I am doing from the top of the list. but while moving he moved one of the file USERS02.sun to USERS01.dbf . so here the unforeseen thing happened and we just lost one of our datafile USERS01.dbf .

and it has been not a while since we moved our backup strategy for this db to rman so as it was supposed to be on once a week hot full db backup and daily archivelog backup so i thought we might get it recovered and so I checked then the latest I could see was the Nov 28 backup which is almost 2 week old . so it was like I felt that we both might loose our job if we could not get this db up and running.

well I start to do a rman restore for the datafile and it took like 2 hour to restore from the compressed backuppiece of Nove 28 backup. and now comes the hard part to recover it . as I found that we need almost 1000+ archive log file to do a complete recover. this db was running on a 100gb SAN. so space also was a critical . so when I started rman recover it verified that all the log files are in disk ( archivelog backuppiece) . and it started restoring the log files from backuppiece which was in another /obackup file system to the 100gb /datastore . so I had to wait and watch as it restore and apply the archivelog and move back into the /obackup. and I also found that there was a 10gb temp file being used so we moved it to /obackup while the restore log process is going on . and it lasted till 3am in the morning and we were done with recovering the db and it was up and running . we both just thanked god and left for home. and thanks to rman which saved our job .

nov 28- rman hot backup

there was a cold backup on dec 4 which we could not find immediatly accessible

till dec 12 - rman log backup set

startup mount

restore datafile 25
recover datafile 25

alter database open .

so in this process here are my learning.

1- before i did the the rearranging of files I should have checked the backup available for the db
2- mv is dangerous it is equally as dangerous as rm.
3- size of a production db doesn't matter

Oracle streams setup to copy schema in 10gR2

2007-12-13T14:40:00.000-08:00

1- set up ARCHIVELOG on both side
1a- set up init parameter
global_names=true
job_queue_process=10 ( vary)
streams_pool_size= 200mb

2- set up streams admin on both side

CREATE TABLESPACE streams_tbs
DATAFILE '/u01/app/oracle/oradata/
 TEST10G1/streams_tbs.dbf' SIZE 25M;

CREATE USER strmadmin
IDENTIFIED BY strmadmin
DEFAULT TABLESPACE streams_tbs
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON streams_tbs;

GRANT CONNECT, RESOURCE, DBA
TO strmadmin;

BEGIN
 DBMS_STREAMS_AUTH.GRANT_ADMIN_
PRIVILEGE(
   grantee         => 'strmadmin',   
   grant_privileges => true);
END;
/


GRANT SELECT_CATALOG_ROLE
TO strmadmin;
GRANT SELECT ANY DICTIONARY
TO strmadmin;

3- set up supplemental log on both side -( it may not be needed . but not sure)
4- create global name
alter database rename global_name to devora.con.world;
alter database rename global_name to testasm.con.world;

4a- create db link and test

SQL> create database link testasm.con.world
CONNECT TO strmadmin
identified by strmadmin
using 'testasm';

create database link devora.con.world
CONNECT TO strmadmin
identified by strmadmin
using 'devora';

5- create a directory on source side( testasm.con.world)

create or replace directory admin as '/home/oracle/streams';

6. run this script in source db (testasm )

BEGIN

DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => 'scott',
source_directory_object => 'admin',
destination_directory_object => 'admin',
source_database => 'testasm.con.world',
destination_database => 'devora.con.world',
perform_actions => false,
script_name => 'schema_replication.sql',
script_directory_object => 'admin',
dump_file_name =>'sc1.dmp',
capture_name => 'capture_scott',
capture_queue_table => 'rep_capture_queue_table',
capture_queue_name => 'rep_capture_queue',
capture_queue_user => null,
propagation_name => 'prop_scott',
apply_name => 'apply_scott',
apply_queue_table => 'rep_dest_queue_table',
apply_queue_name => 'rep_dest_queue',
apply_queue_user => null,
log_file => 'exp.log',
bi_directional => false,
include_ddl => true,
instantiation => dbms_streams_adm.instantiation_schema_network

);

END;
/

it will creat a output schema_replication.sql in source db admin directory

and then run the schema_replication.sql in source db and then

run the below script in source db( the only diff between the above and below script is perform_actions => true, )

BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => 'scott',
source_directory_object => 'admin',
destination_directory_object => 'admin',
source_database => 'testasm.con.world',
destination_database => 'devora.con.world',
perform_actions => true,
script_name => 'schema_replication.sql',
script_directory_object => 'admin',
dump_file_name =>'sc1.dmp',
capture_name => 'capture_scott',
capture_queue_table => 'rep_capture_queue_table',
capture_queue_name => 'rep_capture_queue',
capture_queue_user => null,
propagation_name => 'prop_scott',
apply_name => 'apply_scott',
apply_queue_table => 'rep_dest_queue_table',
apply_queue_name => 'rep_dest_queue',
apply_queue_user => null,
log_file => 'exp.log',
bi_directional => false,
include_ddl => true,
instantiation => dbms_streams_adm.instantiation_schema_network
);
END;

and then you may see data flowing from source to target.

Free Linux Courses Online

2007-12-13T04:59:00.000-08:00

http://education-portal.com/articles/10_Sites_Offering_Free_Linux_Courses_Online.html

copy schema using dbms_datapump

2007-12-12T13:29:00.000-08:00

declare
hand number;
js varchar2(9); -- COMPLETED or STOPPED
begin

-- and owner in ('BACKUP','GATEKEEPER','REFDATA','TRANSDATA','WHSUSR','WHS_VIEWER')
FOR d1 IN ( select object_type, owner, object_name
from all_objects
where object_type in
('FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','SYNONYM','VIEW')
and owner in ('ACTIONLIST23')
)
LOOP
execute immediate 'drop '||d1.object_type||' '||d1.owner||'.'||d1.object_name ;
END LOOP ;

-- then run datapump import itself
hand := dbms_datapump.open (
operation => 'IMPORT' ,
job_mode => 'SCHEMA' ,
remote_link => 'prrs_link' ) ;

dbms_datapump.metadata_filter (
handle => hand ,
name => 'SCHEMA_LIST' ,
value => '''ACTIONLIST''');

dbms_datapump.metadata_remap(hand,'REMAP_SCHEMA','ACTIONLIST','ACTIONLIST23');

dbms_datapump.set_parameter (
handle => hand ,
name => 'TABLE_EXISTS_ACTION' ,
value => 'REPLACE' ) ;

dbms_datapump.start_job(hand) ;

/* wait for the job to finish */
-- dbms_datapump.wait_for_job(hand, js);
dbms_datapump.detach(hand) ;
end;
/

things to check
1- global_names ( false? )
2- with a loopback database link to copy inside the same db.
create public database link loopback
connect to system
identified by manager
using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.16.4.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = p4cl)
(INSTANCE_NAME = p4cl)
)
)'

example#2
create public database link loopback
connect to system
identified by manager
using
'(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.4.139)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)(SID = p4cl)))';

3- monitoring the progress of dp job $impdp system/manager attach

rman cold backup for read only tablespace

2007-12-09T22:08:00.000-08:00

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/obackup/oracle/PRRS/hot/%U';

backup full force tag rman_cold_backup database ;

FAST_START_MTTR_TARGET

2007-12-07T16:25:00.000-08:00

To enable MTTR(mean time to recover) advisory , set the init parameter
1- STATISTICS_LEVEL ( typical or ALL)
2- FAST_START_MTTR_TARGET

v$MTTR_TARGET_ADVICE shows the advisory information collected

Once you enable FAST_START_MTTR_TARGET disable (set to 0 ) or
remove the below parameters

1 - LOG_CHECKPOINT_INTERVAL
2 - LOG_CHECKPOINT_TIMEOUT
3 - FAST_START_IO_TARGET

fast-start checkpointing influences

1- instance and crash recovery times reduces
2- number of physical writes to the disk increases

FAST_START_MTTR_TARGET is designed to control the time it takes the database to do crash recovery.With fast-start checkpointing, Oracle automatically advances the thread checkpoint to control the amount of redo needed for recovery and limit the number of dirty buffers remaining in the cache so that recovery time is bounded.

the main components of crash recovery are :
1- instance startup
2- opening of all database files
3- cache recovery ( rolling forward)
4- transaction recovery( rolling back)- it is done by smon in the background along with normal user activity after the database is opened.so it is not factored into fast-start checkpointing calc.

For example, in a large environment it may take 50 seconds to start up the instance
and 10 seconds to open and set up all the datafiles. The final component is cache recovery,
or the roll forward phase. With FAST_START_MTTR_TARGET set to 180, the Oracle server
uses fast-start checkpointing to limit the cache recovery time to 120 seconds.

enable archivelog - asm instance

2007-12-05T19:36:00.000-08:00

alter system set log_archive_dest_1='location=+DGRP1/testasm/archlog' scope=spfile;
alter system set LOG_ARCHIVE_FORMAT = 'testasm%t_%s_%r.log' scope=spfile;
alter system set log_archive_start=TRUE scope=spfile;

startup mount

alter database archivelog

alter system reset log_archive_start scope=spfile sid='*';

log_archive_start is deprecated.

make sure to create the +DGRP1/testasm/archlog directory using asmcmd

rman backup for a norchivelog db in ASM

2007-12-05T17:01:00.001-08:00

rman backupset in ASM for a noarchivelog db

use asmcmd in ASM instance to create the directory +DGRP1/testasm/rman

then from target db

configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '+DGRP1/testasm/rman/%F';
configure channel device type disk format '+DGRP1/testasm/rman/%U';

startup mount

RMAN> backup database;

Starting backup at 05-DEC-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DGRP1/testasm/datafile/system.257.640537143
input datafile fno=00003 name=+DGRP1/testasm/datafile/sysaux.258.640537199
input datafile fno=00002 name=+DGRP1/testasm/datafile/undotbs1.259.640537235
input datafile fno=00004 name=+DGRP1/testasm/datafile/users.260.640537243
channel ORA_DISK_1: starting piece 1 at 05-DEC-07
channel ORA_DISK_1: finished piece 1 at 05-DEC-07
piece handle=+DGRP1/testasm/rman/09j2rria_1_1 tag=TAG20071205T165848 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 05-DEC-07

Starting Control File and SPFILE Autobackup at 05-DEC-07
piece handle=+DGRP1/testasm/rman/c-4233323468-20071205-00 comment=NONE
Finished Control File and SPFILE Autobackup at 05-DEC-07

migrating from a non-asm to Oracle asm instance

2007-12-05T16:25:00.000-08:00

Preparing a databse to migrate into ASM

1- determine the dbid

DBID=4233323468

2- check all the files

-- +----------------------------------------------------------------------------+
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2007 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : dba_files_all.sql |
-- | CLASS : Database Administration |
-- | PURPOSE : Reports on all data files, online redo log files, and control |
-- | files within the database. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+

SET LINESIZE 147
SET PAGESIZE 9999
SET VERIFY OFF

COLUMN tablespace FORMAT a29 HEADING 'Tablespace Name / File Class'
COLUMN filename FORMAT a64 HEADING 'Filename'
COLUMN filesize FORMAT 99,999,999,999 HEADING 'File Size'
COLUMN autoextensible FORMAT a4 HEADING 'Auto'
COLUMN increment_by FORMAT 99,999,999,999 HEADING 'Next'
COLUMN maxbytes FORMAT 99,999,999,999 HEADING 'Max'

BREAK ON report
COMPUTE SUM OF filesize ON report

SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_data_files d
, v$datafile v
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
WHERE
(d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_temp_files d
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
UNION
SELECT
'[ ONLINE REDO LOG ]'
, a.member
, b.bytes
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$logfile a
, v$log b
WHERE
a.group# = b.group#
UNION
SELECT
'[ CONTROL FILE ]'
, a.name
, TO_NUMBER(null)
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$controlfile a
ORDER BY 1,2

Generate this script in case you need to revert back to non-asm file.

set serveroutput on;
declare
cursor df is select file#, name from v$datafile;
begin
dbms_output.put_line('run');
dbms_output.put_line('{');
for dfrec in df loop
dbms_output.put_line('set newname for datafile ' ||
dfrec.file# || ' to ''' || dfrec.name ||''' ;');
end loop;
dbms_output.put_line('restore database;');
dbms_output.put_line('switch all;');
dbms_output.put_line('}');
end;

run
{
set newname for datafile 1 to '/ofs/app/oracle/oradata/testasm/system01.dbf' ;
set newname for datafile 2 to '/ofs/app/oracle/oradata/testasm/undotbs01.dbf' ;
set newname for datafile 3 to '/ofs/app/oracle/oradata/testasm/sysaux01.dbf' ;
set newname for datafile 4 to '/ofs/app/oracle/oradata/testasm/users01.dbf' ;
restore database;
switch all;
}

3- change the target db parameter to point to ASM

Also note that the target database uses an SPFILE on the local file system:

$ORACLE_HOME/dbs/spfileTESTDB.ora

Use the following steps to fully migrate an existing Oracle database from a local file system to ASM:

1. With the target database open, edit the initialization parameter control_files and db_create_file_dest to point to the ASM disk group +TESTDB_DATA1. Also configure db_recovery_file_dest to point to the ASM disk group +FLASH_RECOVERY_AREA:

SQL> ALTER SYSTEM SET control_files='+DGRP1' SCOPE=spfile;

System altered.

SQL> ALTER SYSTEM SET db_create_file_dest='+DGRP1' SCOPE=spfile;

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest='+FLASH_RECOVERY_AREA' SCOPE=spfile;

System altered.
SQL> SHUTDOWN immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1977176 bytes
Variable Size 83891368 bytes
Database Buffers 75497472 bytes
Redo Buffers 6406144 bytes
S
bash-2.05$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 5 14:55:14 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: testasm (not mounted)

RMAN> restore controlfile from '/ofs/app/oracle/oradata/testasm/control01.ctl';
S
Starting restore at 05-DEC-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DGRP1/testasm/controlfile/backup.256.640536949
Finished restore at 05-DEC-07

rman > restore controlfile from '/ofs/app/oracle/oradata/testasm/control01.ctl';

sql> alter database mount;

rman> BACKUP AS COPY DATABASE FORMAT '+DGRP1';

Starting backup at 05-DEC-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/ofs/app/oracle/oradata/testasm/system01.dbf
output filename=+DGRP1/testasm/datafile/system.257.640537143 tag=TAG20071205T145902 recid=1 stamp=640537194
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/ofs/app/oracle/oradata/testasm/sysaux01.dbf
output filename=+DGRP1/testasm/datafile/sysaux.258.640537199 tag=TAG20071205T145902 recid=2 stamp=640537226
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/ofs/app/oracle/oradata/testasm/undotbs01.dbf
output filename=+DGRP1/testasm/datafile/undotbs1.259.640537235 tag=TAG20071205T145902 recid=3 stamp=640537238
chann

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DGRP1/testasm/datafile/system.257.640537143"
datafile 2 switched to datafile copy "+DGRP1/testasm/datafile/undotbs1.259.640537235"
datafile 3 switched to datafile copy "+DGRP1/testasm/datafile/sysaux.258.640537199"
datafile 4 switched to datafile copy "+DGRP1/testasm/datafile/users.260.640537243"

RMAN>

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 555996 generated at 12/05/2007 14:52:53 needed for thread 1
ORA-00289: suggestion :
/ofs/app/oracle/product/10.2.0.1/dbs/arch1_2_640468119.dbf
ORA-00280: change 555996 for thread 1 is in sequence #2

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/ofs/app/oracle/product/10.2.0.1/dbs/arch1_2_640468119.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log
'/ofs/app/oracle/product/10.2.0.1/dbs/arch1_2_640468119.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

SQL> alter database open resetlogs;

Database altered.

sql> select tablespace_name, file_name, bytes from dba_temp_files;

alter database tempfile
'/ofs/app/oracle/oradata/testasm/temp01.dbf'
drop including datafiles;

alter tablespace temp add tempfile size 512m
autoextend on next 250m maxsize unlimited;

SQL> select tablespace_name, file_name, bytes from dba_temp_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
BYTES
----------
TEMP
+DGRP1/testasm/tempfile/temp.263.640537821
536870912

select a.group#, a.member, b.bytes
from v$logfile a, v$log b where a.group# = b.group#;

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
BYTES
----------
3
/ofs/app/oracle/oradata/testasm/redo03.log
52428800

2
/ofs/app/oracle/oradata/testasm/redo02.log
52428800

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
BYTES
----------

1
/ofs/app/oracle/oradata/testasm/redo01.log
52428800

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 size 50m;

Database altered.

SQL> alter system checkpoint global;

System altered.

alter system switch logfile;

alter database drop logfile group 2;

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 size 50m;

Database altered.

SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED

put the spfile into asm

SQL> create pfile ='/tmp/inittestasm.ora' from spfile;

File created.

SQL> create spfile='+DGRP1/testasm/spfiletestasm.ora' from pfile='/tmp/inittestasm.ora';

File created.

echo "SPFILE='+DGRP1/testasm/spfiletestasm.ora'" > $ORACLE_HOME/dbs/inittestasm.ora

shutdown immediate;

startup

RMAN> list copy of database;

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
6 1 A 05-DEC-07 555996 05-DEC-07 /ofs/app/oracle/oradata/testasm/system01.dbf
7 2 A 05-DEC-07 555996 05-DEC-07 /ofs/app/oracle/oradata/testasm/undotbs01.dbf
8 3 A 05-DEC-07 555996 05-DEC-07 /ofs/app/oracle/oradata/testasm/sysaux01.dbf
9 4 A 05-DEC-07 555996 05-DEC-07 /ofs/app/oracle/oradata/testasm/users01.dbf

RMAN > DELETE copy of database

creating raw partion on solaris and installing ASM

2007-12-03T18:19:00.000-08:00

bash-2.05$ su -
Password:
Sun Microsystems Inc. SunOS 5.9 Generic May 2002
# format
Searching for disks...done

AVAILABLE DISK SELECTIONS:
0. c0t0d0
/pci@1f,4000/scsi@3/sd@0,0
1. c0t1d0
/pci@1f,4000/scsi@3/sd@1,0
2. c0t2d0
/pci@1f,4000/scsi@3/sd@2,0
3. c0t3d0
/pci@1f,4000/scsi@3/sd@3,0
Specify disk (enter its number): 3
selecting c0t3d0
[disk formatted]

FORMAT MENU:
disk - select a disk
type - select (define) a disk type
partition - select (define) a partition table
current - describe the current disk
format - format and analyze the disk
repair - repair a defective sector
label - write label to the disk
analyze - surface analysis
defect - defect list management
backup - search for backup labels
verify - read and display labels
save - save new disk/partition definitions
inquiry - show vendor, product and revision
volname - set 8-character volume name
! - execute , then return
quit
format> print
`print' is not expected.
format> partition

PARTITION MENU:
0 - change `0' partition
1 - change `1' partition
2 - change `2' partition
3 - change `3' partition
4 - change `4' partition
5 - change `5' partition
6 - change `6' partition
7 - change `7' partition
select - select a predefined table
modify - modify a predefined partition table
name - name the current table
print - display the current table
label - write partition map and label to the disk
! - execute , then return
quit
partition> print
Current partition table (original):
Total disk cylinders available: 7506 + 2 (reserved cylinders)

Part Tag Flag Cylinders Size Blocks
0 unassigned wm 0 - 7505 16.86GB (7506/0/0) 35368272
1 unassigned wm 0 0 (0/0/0) 0
2 backup wm 0 - 7505 16.86GB (7506/0/0) 35368272
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 0 0 (0/0/0) 0
5 unassigned wm 0 0 (0/0/0) 0
6 unassigned wm 0 0 (0/0/0) 0
7 unassigned wm 0 0 (0/0/0) 0

partition> modify
Select partitioning base:
0. Current partition table (original)
1. All Free Hog
Choose base (enter number) [0]? 1

Part Tag Flag Cylinders Size Blocks
0 root wm 0 0 (0/0/0) 0
1 swap wu 0 0 (0/0/0) 0
2 backup wu 0 - 7505 16.86GB (7506/0/0) 35368272
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 0 0 (0/0/0) 0
5 unassigned wm 0 0 (0/0/0) 0
6 usr wm 0 0 (0/0/0) 0
7 unassigned wm 0 0 (0/0/0) 0

Do you wish to continue creating a new partition
table based on above table[yes]? yes
Free Hog partition[6]? 6
Enter size of partition '0' [0b, 0c, 0.00mb, 0.00gb]: 500mb
Enter size of partition '1' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '3' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '4' [0b, 0c, 0.00mb, 0.00gb]: 15gb
Enter size of partition '5' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '7' [0b, 0c, 0.00mb, 0.00gb]:

Part Tag Flag Cylinders Size Blocks
0 root wm 0 - 217 501.57MB (218/0/0) 1027216
1 swap wu 0 0 (0/0/0) 0
2 backup wu 0 - 7505 16.86GB (7506/0/0) 35368272
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 218 - 6893 15.00GB (6676/0/0) 31457312
5 unassigned wm 0 0 (0/0/0) 0
6 usr wm 6894 - 7505 1.38GB (612/0/0) 2883744
7 unassigned wm 0 0 (0/0/0) 0

Okay to make this the current partition table[yes]? yes
Enter table name (remember quotes): "asm"

Ready to label disk, continue? y

partition> q

FORMAT MENU:
disk - select a disk
type - select (define) a disk type
partition - select (define) a partition table
current - describe the current disk
format - format and analyze the disk
repair - repair a defective sector
label - write label to the disk
analyze - surface analysis
defect - defect list management
backup - search for backup labels
verify - read and display labels
save - save new disk/partition definitions
inquiry - show vendor, product and revision
volname - set 8-character volume name
! - execute , then return
quit
format> verify

Primary label contents:

Volume name = < >
ascii name =
pcyl = 7508
ncyl = 7506
acyl = 2
nhead = 19
nsect = 248
Part Tag Flag Cylinders Size Blocks
0 unassigned wm 0 - 217 501.57MB (218/0/0) 1027216
1 unassigned wm 0 0 (0/0/0) 0
2 backup wm 0 - 7505 16.86GB (7506/0/0) 35368272
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 218 - 6893 15.00GB (6676/0/0) 31457312
5 unassigned wm 0 0 (0/0/0) 0
6 unassigned wm 6894 - 7505 1.38GB (612/0/0) 2883744
7 unassigned wm 0 0 (0/0/0) 0

format> q
#

# ls -lL c0t3d0*
crw-r----- 1 root sys 32, 24 Nov 28 18:04 c0t3d0s0
crw-r----- 1 root sys 32, 25 Nov 28 18:04 c0t3d0s1
crw-r----- 1 root sys 32, 26 Nov 28 18:04 c0t3d0s2
crw-r----- 1 root sys 32, 27 Nov 28 18:04 c0t3d0s3
crw-r----- 1 root sys 32, 28 Nov 28 18:04 c0t3d0s4
crw-r----- 1 root sys 32, 29 Nov 28 18:04 c0t3d0s5
crw-r----- 1 root sys 32, 30 Nov 28 18:04 c0t3d0s6
crw-r----- 1 root sys 32, 31 Nov 28 18:04 c0t3d0s7
# cd
# pwd
/
# mkdir asmdisks
# cd asmdisks
# mknod disk1 c 32 28
# ls -l
total 0
crw-r--r-- 1 root other 32, 28 Dec 3 15:07 disk1
# cd disk1
ksh: disk1: not a directory
# chown oracle:dba disk1
# ls -l
total 0
crw-r--r-- 1 oracle dba 32, 28 Dec 3 15:07 disk1
#

I GOT this error

sh: sysdef: not found ( while running the runInstaller)

while installing the software mkae change diskovery path to /asmdisk1/disk1

i am trying with external redundancy

run when the installer asks /ofs/app/oracle/product/10.2.0.1/root.sh

migrating oracle from windows to solaris

2007-12-03T11:49:00.000-08:00

for platform difference ( little endian to big endian)

DECLARE
l_ts_names DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
l_dirs DBMS_STREAMS_TABLESPACE_ADM.DIRECTORY_OBJECT_SET;
l_dp_job_name varchar2(200);
l_log_file DBMS_STREAMS_TABLESPACE_ADM.FILE;
BEGIN
l_ts_names (1) := 'USERS';
l_ts_names (2) := 'REPORTS';
l_dirs (1) := 'DBF_DIR';
l_log_file.directory_object := 'DBF_DIR';
l_log_file.file_name := 'pull_ts_log.log';

DBMS_STREAMS_TABLESPACE_ADM.PULL_TABLESPACES(
datapump_job_name => l_dp_job_name,
database_link => 'dwdb',
tablespace_names => l_ts_names,
tablespace_directory_objects => l_dirs,
conversion_extension => 'sun',
log_file => l_log_file
);
END;

rman restore on different filesystem

2007-11-30T11:15:00.000-08:00

set dbid 21417441799
startup nomount

-restore your spfile or pfile

restore controlfile from '/obackup/oracle/mtrx/hot/c-1651548318-20071129-00';

shutdown abort;

startup mount;

catalog start with '/obackup/oracle/mtrx/hot/';

run {

allocate channel c1 device type disk;

set newname for datafile 1 to '/ofs/prrs02/mtrx22/system01.dbf';
set newname for datafile 2 to '/ofs/prrs02/mtrx22/undotbs01.dbf';
set newname for datafile 3 to '/ofs/prrs02/mtrx22/sysaux01.dbf';
set newname for datafile 4 to '/ofs/prrs02/mtrx22/users01.dbf';
set newname for datafile 5 to '/ofs/prrs02/mtrx22/tools01.dbf';
set newname for datafile 6 to '/ofs/prrs04/mtrx22/indx01.dbf';
set newname for datafile 7 to '/ofs/prrs04/mtrx22/mtrxdata01.dbf';
set newname for datafile 8 to '/ofs/prrs04/mtrx22/mtrxdata02.dbf';
set newname for datafile 9 to '/ofs/prrs04/mtrx22/mtrxdata03.dbf';
set newname for datafile 10 to '/ofs/prrs03/mtrx22/mtrxdata04.dbf';
set newname for datafile 11 to '/ofs/prrs03/mtrx22/mgrtdata01.dbf';
set newname for datafile 12 to '/ofs/prrs03/mtrx22/proddata01.dbf';
set newname for datafile 13 to '/ofs/prrs03/mtrx22/reporter01.dbf';
set newname for datafile 14 to '/ofs/prrs04/mtrx22/mtrxdata05.dbf';
set newname for datafile 15 to '/ofs/prrs05/mtrx22/mtrxdata06.dbf';
set newname for datafile 16 to '/ofs/prrs05/mtrx22/mtrxdata07.dbf';
set newname for datafile 17 to '/ofs/prrs05/mtrx22/proddata02.dbf';

set newname for datafile 18 to '/ofs/prrs05/mtrx22/METRIX_INDX01.dbf';

sql "alter database rename file ''/metrix2/mtrx10g01/oracle/mtrx10g/mtrx10g/redo01.log''
to ''/ofs/prrs03/mtrx22/redo01.log'' ";

sql "alter database rename file ''/metrix2/mtrx10g01/oracle/mtrx10g/mtrx10g/redo02.log''
to ''/ofs/prrs03/mtrx22/redo02.log'' ";

sql "alter database rename file ''/metrix2/mtrx10g01/oracle/mtrx10g/mtrx10g/redo03.log''
to ''/ofs/prrs03/mtrx22/redo03.log'' ";

set until scn 21417441799;

restore database;
switch datafile all;

recover database;

}

alter database open resetlogs;

ORA-19951: cannot modify control file until DBNEWID is completed

2007-11-30T11:03:00.000-08:00

nid system/manager dbname=mtrx22

while running nid on a cloned database I forgot to check the tempfile which resulted into following error

NID-00111: Oracle error reported from target database while executing
begin dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged,
:nmchged); end;
ORA-01116: error in opening database file /metrix2/mtrx10g02/oracle/mtrx10g/temp01.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6164
ORA-06512: at line 1

After that no matter How I try to open the db or add the tempfile it didnt allow me .somehow not sure what I missed but the REVERT option didnt help me much.

A lesson learned.

make sure all the datafiles,tempfiles
are in place before you run nid. it is better if you have a fulll backup.

oracle 11g installed on ubuntu /vmware on Dell laptop

2007-11-20T11:47:00.000-08:00

hardware - Dell Latitude D600 with 1 GB RAM
Host O/S - Windows XP
vmware 2.0
Ubuntu 7.10
filesystem xfs
Oracle 11g Enterprise edition

Reference.
http://www.pythian.com/blogs/654/installing-oracle-11g-on-ubuntu-linux-710-gutsy-gibbon

ubuntu vmware networking /etc/network/interfaces

2007-11-19T19:34:00.000-08:00

cat /etc/network/interfaces
auto lo
iface lo inet loopback
#to enable dhcp
# The primary network interface - use DHCP to find our address
iface eth0 inet dhcp
gateway: 10.247.16.253
auto eth0

it fixed these things
1- now i am able to ping from host machine to the guest OS ubuntu inside vmware 2.0.
2- when I try to logon to oracle using sqlplus '/as sysdba' it used to give me TNS error .
3- trying to start lsnrctl start was just hanging it was not even creating trace files.

Firefox bookmark

2007-11-16T19:06:00.000-08:00

Use the bookmark manager to export and import

generate user creation script

2007-11-16T17:19:00.000-08:00

http://toolkit.rdbms-insight.com/gen_cre_users.php

set pagesize 0
set escape on
select 'create user ' || U.username || ' identified ' ||
DECODE(password,
    NULL, 'EXTERNALLY',
    ' by values ' || '''' || password || ''''
    )
|| chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||
'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace ||
decode (account_status,'LOCKED', ' account lock',
  'EXPIRED', ' password expire',
  'EXPIRED \& LOCKED', ' account lock password expire',
  null)
||
';'
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system & default users
where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',
'QS_CB','QS_CS','PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;
set pagesize 100
set escape off

generate tablespace creation script

2007-11-16T17:13:00.000-08:00

Ref# http://www.blacksheepnetworks.com/security/resources/www.think-forward.com/sql/gents.htm
spool gents.lst

set serveroutput on size 1000000
DECLARE

 CURSOR get_ts IS SELECT * FROM dba_tablespaces
                  WHERE tablespace_name != 'SYSTEM';

 CURSOR get_df (p_ts VARCHAR2) IS
                  SELECT * from dba_data_files
                  WHERE tablespace_name = p_ts;

 l_str VARCHAR2(10);

BEGIN

FOR ts_rec IN get_ts LOOP

 dbms_output.put_line ('CREATE TABLESPACE '||ts_rec.tablespace_name);

 -- For each tablespace loop through the datafiles
 FOR df_rec IN get_df (ts_rec.tablespace_name) LOOP

   IF get_df%ROWCOUNT = 1 THEN
      l_str := 'DATAFILE';
   ELSE
      l_str := ',';
   END IF;

   dbms_output.put_line (l_str||' '
               ||chr(39)||df_rec.file_name||chr(39)
               ||' SIZE '||df_rec.bytes||' REUSE ');

   if df_rec.autoextensible = 'YES' then

      dbms_output.put_line (' AUTOEXTEND ON'
                            ||' NEXT '||df_rec.increment_by );

      if df_rec.maxbytes = 68719443968 then
         dbms_output.put_line (' MAXSIZE UNLIMITED');
      else
         dbms_output.put_line (' MAXSIZE '||df_rec.maxbytes);
      end if;

   end if;

 END LOOP;

/* Extent Management Clause */

  dbms_output.put_line  ('EXTENT MANAGEMENT ' ||ts_rec.extent_management );

  if ts_rec.extent_management = 'LOCAL' then

     if ts_rec.allocation_type = 'SYSTEM' then

        dbms_output.put_line (' AUTOALLOCATE ');

     else

        dbms_output.put_line (' UNIFORM SIZE '||ts_rec.initial_extent);

     end if;

  end if;

  if ts_rec.extent_management = 'DICTIONARY' then

    dbms_output.put_line ('DEFAULT STORAGE (INITIAL '||ts_rec.initial_extent
       ||' NEXT '||ts_rec.next_extent
       ||' MINEXTENTS '||ts_rec.min_extents
       ||' MAXEXTENTS '||ts_rec.max_extents
       ||' PCTINCREASE '||ts_rec.pct_increase||' ) ');

  end if;

  dbms_output.put_line  (' ONLINE;');
  dbms_output.new_line;

END LOOP;

END;
/
spool off

Add extra space to ubutu guest os in vmware

2007-11-16T16:02:00.000-08:00

sudo apt-get install gparted

1-Select your virtual machine Ubuntu and then "right-click",
choose "Settings" -> Add -> Hard Disk -> choose IDE/SCSI -> decide the capacity -> disk file nameubuntu(2).vmdk [your new virtual disk]

2. boot up your ubuntu

sudo apt-get install xfsprogs

mkfs.xfs /dev/sdb

fdisk -l

mkdir /oracle
mount /dev/sdb /oracle

modify your "/etc/fstab" file, and adding
/dev/sdb /oracle xfs defaults 0 0

chmod 777 /oracle

install rpm file

2007-11-15T20:57:00.000-08:00

Ref#http://www.ubuntugeek.com/install-rpm-files-in-ubuntu.html

install alien in Ubuntu

sudo apt-get install alien

Example

Suppose we have a avg antivirus avg71lms-r30-a0782.i386.rpm file

To convert .rpm to debian

sudo alien -k avg71lms-r30-a0782.i386.rpm

Now you should be having avg71lms-r30-a0782.i386.deb file

To install .deb file

sudo dpkg -i avg71lms-r30-a0782.i386.deb

If you don’t use -k option you should see avg71lms_r30-1_i386.deb file the difference is it will add 1

ubuntu samba

2007-11-15T15:46:00.000-08:00

sudo apt-get install samba

sudo gedit /etc/samba/smb.conf
change browsable and writable to yes

sudo /etc/init.d/samba start

sudo smbpasswd -a your_username

and create a folder name sharesoftware and make it shared in ubuntu

and then connect from windows -
map network drive
\\10.247.16.140\sharesoftware

and access using your username and password

To mount a remote windows system directly upon your current ubuntu system you'll need:

* The smbfs kernel module
* The smbfs package.
* A mountpoint - this is just an empty directory.
* The Name/IP address of the host to connect to.
* Login credentials to the remote share - both username and password.
* Local root privileges to do the mount, and load the kernel module.

apt-get install smbfs

as a small example lets assume I wish to mount the "C:" drive of remote machine "recurring.my.flat" to the local machine mystery.my.flat
I would begin by loading the kernel module:

mount -t smbfs -o username=Administrator,password=Password //recurring/c$ /mnt/recurring

5232: session setup failed: ERRDOS - ERRnoaccess (Access denied.)
SMB connection failed

See the following link: will help you with samba and windows devices

http://www.debian-administratrion.org/articles/165

ubuntu Networking

2007-11-15T15:37:00.000-08:00

http://www.ubuntugeek.com/ubuntu-networking-configuration-using-command-line.html

The basics for any network based on *nix hosts is the Transport Control Protocol/ Internet Protocol (TCP/IP) combination of three protocols. This combination consists of the Internet Protocol (IP),Transport Control Protocol (TCP), and Universal Datagram Protocol (UDP).

By Default most of the users configure their network card during the installation of Ubuntu. You can however, use the ifconfig command at the shell prompt or Ubuntu’s graphical network configuration tools, such as network-admin, to edit your system’s network device information or to add or remove network devices on your system

Configure Network Interface Using Command-Line

You can configure a network interface from the command line using the networking utilities. You configure your network client hosts with the command line by using commands to change your current settings or by editing a number of system files.

Configuring DHCP address for your network card

If you want to configure DHCP address you need to edit the /etc/network/interfaces and you need to enter the following lines replace eth0 with your network interface card

sudo vi /etc/network/interfaces

# The primary network interface - use DHCP to find our address
auto eth0
iface eth0 inet dhcp

Configuring Static IP address for your network card

If you want to configure Static IP address you need to edit the /etc/network/interfaces and you need to enter the following lines replace eth0 with your network interface card

sudo vi /etc/network/interfaces

# The primary network interface
auto eth0
iface eth0 inet static
address 192.168.3.90
gateway 192.168.3.1
netmask 255.255.255.0
network 192.168.3.0
broadcast 192.168.3.255

After entering all the details you need to restart networking services using the following command

sudo /etc/init.d/networking restart

Setting up Second IP address or Virtual IP address in Ubuntu

If you are a server system administrator or normal user some time you need to assign a second ipaddress to your Ubuntu machine.For this you need to edit the /etc/network/interfaces file and you need to add the following syntax.Below one is the only example you need to chnage according to your ip address settings

sudo vi /etc/network/interfaces

auto eth0:1
iface eth0:1 inet static
address 192.168.1.60
netmask 255.255.255.0
network x.x.x.x
broadcast x.x.x.x
gateway x.x.x.x

You need to enter all the details like address,netmask,network,broadcast and gateways values after entering all the values save this file and you need to restart networking services in debian using the following command to take effect of our new ipaddress.

After entering all the details you need to restart networking services using the following command

sudo /etc/init.d/networking restart

Setting your ubuntu stytem hostname

Setting up your hostname upon a ubuntu installation is very straightforward. You can directly query, or set, the hostname with the hostname command.

As an user you can see your current hostname with

sudo /bin/hostname

Example

To set the hostname directly you can become root and run

sudo /bin/hostname newname

When your system boots it will automatically read the hostname from the file /etc/hostname

If you want to know more about how to setup host name check here

Setting up DNS

When it comes to DNS setup Ubuntu doesn’t differ from other distributions. You can add hostname and IP addresses to the file /etc/hosts for static lookups.

To cause your machine to consult with a particular server for name lookups you simply add their addresses to /etc/resolv.conf.

For example a machine which should perform lookups from the DNS server at IP address 192.168.3.2 would have a resolv.conf file looking like this

sudo vi /etc/resolv.conf

enter the following details

search test.com
nameserver 192.168.3.2

NOTE#-

my ubuntu is installed on vmware on host os windows xp so I switched to bridge networking when I am on dhcp at work and I switch to NAT when I am working at home on wireless networking. not sure if it is the right way?

ORA-07445: exception encountered: core dump [opidsa()+480] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []

2007-11-12T12:03:00.000-08:00

we hit these errors after upgrading to solaris 10.2.0.3 . metalink suggest a regular shutdown or flushing shared pool . of applying patch Patch 5648872

nfsstat - solaris

2007-11-07T15:04:00.000-08:00

nfsstat -m
/obackup/obackup_share from asb-sac-nms-001:/obackup
Flags: vers=3,proto=tcp,sec=sys,hard,intr,link,symlink,acl,rsize=32768,wsize=32768,retrans=5,timeo=600
Attr cache: acregmin=3,acregmax=60,acdirmin=30,acdirmax=60

/etc/vfstab

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

2007-11-07T14:53:00.000-08:00

After moving the backup directory to a NFS for one 10gR2 db I started getting this error.

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/07/2007 14:16:10
ORA-19504: failed to create file "/obackup/oracle/mtrx/hot/ffj0f358_1_1"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2

Soln-

Error is documented in Metalink Doc Id : Note:387700.1

ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS

Solution
To implement the solution, please execute the following steps:

From the erorrs that we see from the RMAN stack this looks like Bug 5146667
This behaviour has been observed on Solaris and AIX Platform.

WORKAROUND:
As suggested in the bug the workaround recommended is to use the Event 10298.

1) set the Event 10298 in the init file
event="10298 trace name context forever, level 32"
if you are using the spfile then the following can be done
SQL> alter system set event='10298 trace name context forever, level 32'scope= spfile ;

Once you set the above parameter restart the instance check as follows
SQL> select name, value from v$parameter where name = 'event';
NAME VALUE
---------- ------------------------------------------------------------
Event 10298 trace name context forever, level 32

1 row selected.
Then try the backups again

2) PATCH
Check if a one off patch for 10.2.0.1 for your platform is available
Please follow next steps to download and test it :

1. Login to Metalink
2. Go to Patches and Updates -> Simple Search
3. Enter patch number 5146667 and platform you are on ( your version of OS )
4. Download the patch
5. Read the README file for installation instructions and test it to see if it fixes your problem

unlink

2007-11-07T14:51:00.000-08:00

the reverse of ln

ln -s /obackup/oracle/mtrx mtrx
unlink mtrx

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

2007-11-07T14:26:00.000-08:00

After moving the rman backup for a solaris 5.8 to a nfs it started complaining

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/07/2007 14:16:10
ORA-19504: failed to create file "/obackup/oracle/mtrx/hot/ffj0f358_1_1"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2

impdp

2007-11-05T17:55:00.000-08:00

expdp system/managerpopv dumpfile=reporter.dmp directory=tmp schemas=reporter

impdp system\acomp directory=tmp dumpfile=opvdb.dmp remap_schema=opvdb:dest_schema remap_tablespace=source_tablespace:dest_tablespace

impdp system/manager directory=temp_dir dumpfile=ovdb.dmp remap_schema=ovdb:popv_ovdb remap_tablespace=owstage:afc_data logfile=ovdb.log

ref http://www.oracle-dba-online.com/data_pump_import_utility.htm

*importing full dump file

impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp FULL=y LOGFILE=full_imp.log
impdp hr/hr DUMPFILE=dpump_dir1:expfull.dmp FULL=y LOGFILE=dpump_dir2:full_imp.log

*Importing Objects of One Schema to another Schema

The following example loads all tables belonging to hr schema to scott schema

$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott

If SCOTT account exist in the database then hr objects will be loaded into scott schema.
If scott account does not exist, then Import Utility will create the SCOTT account with an
unusable password because, the dump file was exported by the user SYSTEM and imported by
the user SYSTEM who has DBA privileges.

*Loading Objects of one Tablespace to another Tablespace.

You can use remap_tablespace option to import objects of one tablespace to another tablespace
by giving the command

$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_TABLESPACE=users:sales

*Generating SQL File containing DDL commands using Data Pump Import

You can generate SQL file which contains all the DDL commands which Import would
have executed if you actually run Import utility

The following is an example of using the SQLFILE parameter.

$ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp SQLFILE=dpump_dir2:expfull.sql

A SQL file named expfull.sql is written to dpump_dir2.

*Importing objects of only a Particular Schema

If you have the IMP_FULL_DATABASE role, you can use this parameter to perform a schema-mode import
by specifying a single schema other than your own or a list of schemas to import.
First, the schemas themselves are created (if they do not already exist),
including system and role grants, password history, and so on.
Then all objects contained within the schemas are imported.
Nonprivileged users can specify only their own schemas.
In that case, no information about the schema definition is imported,
only the objects contained within it.

Example

The following is an example of using the SCHEMAS parameter.
You can create the expdat.dmp file used in this example by running the example provided for the
Export SCHEMAS parameter.

$impdp hr/hr SCHEMAS=hr,oe DIRECTORY=dpump_dir1 LOGFILE=schemas.log

DUMPFILE=expdat.dmp

The hr and oe schemas are imported from the expdat.dmp file.
The log file, schemas.log, is written to dpump_dir1

*Importing Only Particular Tables

The following example shows a simple use of the TABLES parameter to import only
the employees and jobs tables from the expfull.dmp file.
You can create the expfull.dmp dump file used in this example by running the example
provided for the Full Database Export in Previous Topic.

$impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLES=employees,jobs

This will import only employees and jobs tables from the DUMPFILE.

*Running Import Utility in Interactive Mode

Similar to the DATA PUMP EXPORT utility the Data Pump Import Jobs can also be suspended, resumed or killed. And, you can attach to an already existing import job from any client machine.

For Example, suppose a DBA starts a importing by typing the following command at one client machine CLNT1 by typing the following command

$impdp scott/tiger@mydb FULL=y DIRECTORY=data_pump_dir
DUMPFILE=full.dmp LOGFILE=myfullexp.log JOB_NAME=myfullJob

After some time, the DBA wants to stop this job temporarily. Then he presses CTRL+C to enter into interactive mode. Then he will get the Import> prompt where he can type interactive commands

Now he wants to stop this export job so he will type the following command

Import> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([y]/n): y

The job is placed in a stopped state and exits the client.

After finishing his other work, the DBA wants to resume the export job and the client machine from where he actually started the job is locked because,
the user has locked his/her cabin. So now the DBA will go to another client machine and he reattach
to the job by typing the following command

$impdp hr/hr@mydb ATTACH=myfulljob

After the job status is displayed, he can issue the CONTINUE_CLIENT command to resume logging mode
and restart the myfulljob job.

Import> CONTINUE_CLIENT

A message is displayed that the job has been reopened, and processing status is output to the client.

Note: After reattaching to the Job a DBA can also kill the job by typing KILL_JOB, if he doesn’t
want to continue with the import job.

oracle TDE

2007-11-05T17:49:00.001-08:00

connect / as sysdba
or creating a wallet a folder named wallet should be present in the directory
$ORACLEBASE/admin/$ORACLESID

Location of wallet can be changed if required. This can be done by entering the directory path for WALLET_LOCATION parameter in sqlnet.ora file as shown below

WALLET_LOCATION = (SOURCE=
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY=/u02/wallets)
)))

alter system set key identified by "welcome1";

For later sessions, you do not want to use the command given above; you need the wallet to be open (it has been closed when you shut down your database), but you don't want to create a new master key. Then the command is:

alter system set wallet open identified by "welcome1";

create table cust_payment_info
(first_name varchar2(11),
last_name varchar2(10),
order_number number(5),
credit_card_number varchar2(16) ENCRYPT NO SALT,
active_card varchar2(3));

insert into cust_payment_info values
('Jon', 'Oldfield', 10001, '5446959708812985','YES');
insert into cust_payment_info values
('Chris', 'White', 10002, '5122358046082560','YES');
insert into cust_payment_info values
('Alan', 'Squire', 10003, '5595968943757920','YES');
insert into cust_payment_info values
('Mike', 'Anderson', 10004, '4929889576357400','YES');
insert into cust_payment_info values
('Annie', 'Schmidt', 10005, '4556988708236902','YES');
insert into cust_payment_info values
('Elliott', 'Meyer', 10006, '374366599711820','YES');
insert into cust_payment_info values
('Celine', 'Smith', 10007, '4716898533036','YES');
insert into cust_payment_info values
('Steve', 'Haslam', 10008, '340975900376858','YES');
insert into cust_payment_info values
('Albert', 'Einstein', 10009, '310654305412389','YES');

/devel/app/oracle/admin/dev10g/wallets

view sqlnet.ora
WALLET_LOCATION = (SOURCE=
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY=/devel/app/oracle/admin/dev10g/wallets)
)))

SQL> alter system set key identified by "pass";

System altered.

Example 3-3 Adding Salt to an Encrypted Column

ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);

Example 3-4 Removing Salt from an Encrypted Column

ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);

expdp system/manager dumpfile=test.dmp directory=tmp_dir tables=scott.emp encryption_password=pass

impdp system/manager tables=emp dumpfile=test.dmp directory=tmp_dir encryption_password=pass

Import: Release 10.2.0.1.0 - Production on Tuesday, 18 September, 2007 12:17:55

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39180: unable to encrypt ENCRYPTION_PASSWORD
ORA-28365: wallet is not open

SQL> create directory test as 'c:\temp';
Directory created.

SQL> grant all on directory test to public;
Grant succeeded.

impdp system/manager tables=emp dumpfile=test.dmp directory=tmp_dir encryption_password=pass REMAP_SCHEMA=scott:system remap_tablespace=users:system

after reboot of database

SQL> alter system set encryption wallet open identified by "pass";

System altered.

prereq- oracle 10g on linux 64bit

2007-11-05T17:01:00.000-08:00

/usr/sbin/useradd nobody

/etc/sysctl.conf

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

/etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

/etc/pam.d/login

session required pam_limits.so

For the Bourne, Bash, or Korn shell, add the following lines to the
/etc/profile file (or the /etc/profile.local file on SUSE systems):

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

mkdir -p /mount_point/app/oracle_sw_owner
chown -R oracle:oinstall /mount_point/app/oracle_sw_owner
chmod -R 775 /mount_point/app/oracle_sw_owner

e.g /u01/app/oracle

[root@localhost oracle]# mkdir /u01
[root@localhost oracle]# mkdir /u01/app
[root@localhost oracle]# mkdir /u01/app/oracle
[root@localhost oracle]# chown -R oracle:oinstall /u01/app/oracle
[root@localhost oracle]# chmod -R 775 /u01/app/oracle

ASM

rpm -Uvh oracleasm-support-1.0.0-1.i386.rpm \
oracleasm-2.4.9-e-enterprise-1.0.0-1.i686.rpm \
oracleasmlib-1.0.0-1.i386.rpm

/etc/init.d/oracleasm configure

Dataguard 9i

2007-11-05T13:36:00.001-08:00

primary
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_FORMAT=sfpvtl_%t_%s.log

# Parameters for DataGuard Primary database role
LOG_ARCHIVE_DEST_1='LOCATION=/sfpvtl/archlogs01/oracle/sfpvtl'

# Location of the StandBy Database
LOG_ARCHIVE_DEST_2 = 'SERVICE=sfpvtl_stdby'
LOG_ARCHIVE_DEST_STATE_2 = ENABLE

# Primary Database 2 archive log file system
LOG_ARCHIVE_DEST_3 = 'LOCATION=/sfpvtl/archlogs02/oracle/sfpvtl'
LOG_ARCHIVE_DEST_STATE_3 = DEFER

standby
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_FORMAT=sfpvtl_%t_%s.log

# Parameters for DataGuard Primary database role
LOG_ARCHIVE_DEST_1='LOCATION=/sfpvtl/archlogs01/oracle/sfpvtl'

# Location of the StandBy Database
LOG_ARCHIVE_DEST_2 = 'SERVICE=sfpvtl_stdby'
LOG_ARCHIVE_DEST_STATE_2 = ENABLE

# Primary Database 2 archive log file system
LOG_ARCHIVE_DEST_3 = 'LOCATION=/sfpvtl/archlogs02/oracle/sfpvtl'
LOG_ARCHIVE_DEST_STATE_3 = DEFER

#LOG_ARCHIVE_DEST=/sfpvtl/archlogs01/oracle/sfpvtl
fal_client='sfpvtl_pri'
fal_server='sfpvtl_stdby'

standby_archive_dest='/sfpvtl/standbylogs01/oracle/sfpvtl'
standby_file_management='auto'

rman-

BACKUP DATABASE
INCLUDE CURRENT CONTROLFILE FOR STANDBY
PLUS ARCHIVELOG;

rsync -avvzrlpogt * --rsync-path=/opt/csw/bin/rsync -e ssh oracle@asb-con-dbs-001:/obackup/oracle/aug24/

rman target / auxiliary sys/syspopv@popv_stdby
duplicate target database for standby nofilenamecheck;

startup nomount
ALTER DATABASE MOUNT STANDBY DATABASE;
alter database recover managed standby database disconnect from session;

read only

startup nomount
alter database mount standby database;
alter database open read only;

RMAN "memory fault"

2007-11-05T11:00:00.000-08:00

saw this in a oracle-l post.
From Mr Dave . Herring

In working with Oracle on this issue, they had me recreate my controlfiles, then add a second channel allocation as part of the DELETE OBSOLETE, this one to disk.

All backups perform the following as their last step:

SQL 'ALTER SESSION SET tracefile_identifier=CONTROLFILE';
SQL 'ALTER DATABASE BACKUP CONTROLFILE TO trace';

... and for some reason not being able to automatically remove obsolete versions of this controlfile backup was part of the problem

follows the situation

> I've got a regular job to delete obsolete pieces from our RMAN catalog,
> which now is failing with "Memory fault". The system is Tru64 5.1b
> running Oracle 9.2.0.6 and the RMAN catalog is 10.2.0.2. Backups are to
> tape using Legato Networker 7.1.1.
>
> The procedure is very simple, just connecting to the target and catalog,
> allocating a channel and deleting:
>
> CONNECT CATALOG ../..@..
> CONNECT TARGET ../..@..
> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt_tape;
> DELETE NOPROMPT OBSOLETE;
>
> The ONLY thing I get back is:
>
> RMAN retention policy will be applied to the command
> RMAN retention policy is set to redundancy 4
> Memory fault
>
> No Oracle errors show up in either database. I can't find messages or
> info anywhere as to why this is happening. I tried setting my retention
> to larger numbers, thinking there's too many pieces to delete, but that
> didn't work. Current retention is 4 copies. I also tried using RECOVERY
> WINDOW TO xxx DAYS, which failed too. I even truss'ed the session and it
> ends with:
>
> ...
> write(10, "\0BD\0\006\0\0\0\0\003 ^".., 189) = 189
> read(10, "01 m\0\006\0\0\0\0\00701".., 2064) = 365
> write(10, "\0BD\0\006\0\0\0\0\003 ^".., 189) = 189
> read(10, "01 t\0\006\0\0\0\0\00701".., 2064) = 372
> Incurred fault #32, FLTBOUNDS %pc = 0x000000012105DD84 addr =
> 0x000000011FFF4D00
> Received signal #11, SIGSEGV [default]
> siginfo: SIGSEGV SEGV_MAPERR addr=0x0000000000000058
> Err#11 Error 11 occurred.
> *** process killed ***

solaris upgrade to 10.2.0.3 from 10.2.0.1

2007-11-02T20:14:00.000-07:00

install the patch p5337014_10203_SOLARIS64.zip

startup upgrade
@?/rdbms/admin/catupgrd.sql
shutdown immediate

as this was a primary db on a dataguard config.

we just created a tar out from the 10.2.0.3 in primary and extracted into the standby db

and started the standby db in standby recovery mode and automatically all the archivelog were transmitted from primary to standby and the db was automatically upgraded . we didnt have to run catupgrd on standby.

rsync

2007-11-02T18:15:00.001-07:00

rsync -avvzrlpogt p5337014_10203_SOLARIS64.zip --rsync-path=/opt/csw/bin/rsync -e ssh oracle@asb-sac-nms-001:/obackup/oracle/popv/

check the status

oracle@asb-con-dbm-001:~/software> rsync -avvzrlpogt p5337014_10203_SOLARIS64.zip --stats --progress --rsync-path=/opt/csw/bin/rsync -e ssh oracle@asb-sac-nms-001:/obackup/oracle/popv/

rsync from remote to local

/opt/csw/bin/rsync --progress -avvzrlpogt --rsync-path=/opt/csw/bin/rsync -e ssh oracle@10.247.48.18:/obackup/obackup2/PRRS/cold/20071109/prrs03/syst*.dbf .

nohup rsync

nohup rsync -avvzrlpogt --stats --progress * --rsync-path=/usr/local/bin/rsync -e ssh oracle@172.16.4.135:/obackup/oracle/mtrx/hot/

ctrl+z
bg %1

tail -f nohup.out

color your windows command editor

2007-11-02T15:44:00.000-07:00

set the properties -> edit option -> quick edit mode.

create a DWORD value in Windows registry HKEY_CURRENT_USER\Console\EnableColorSelection and set it to 1

now select something on the cmd.exe editor and press ctrl or alt +0..9

tkprof

2007-11-02T15:12:00.000-07:00

alter session set "_dump_qbc_tree"=1;
alter session set "_simple_view_merging"=false;

SQL> alter session set events '10046 trace name context
 forever,level 12';

Session altered.

SQL> alter session set events '10046 trace name context off';

Session altered.

tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
            [print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
print=integer    List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename  List SQL statements and data inside INSERT statements.
sys=no           TKPROF does not list SQL statements run as user SYS.
record=filename  Record non-recursive statements found in the trace file.
waits=yes|no     Record summary for any wait events found in the trace file.
sort=option      Set of zero or more of the following sort options:
  prscnt  number of times parse was called
  prscpu  cpu time parsing
  prsela  elapsed time parsing
  prsdsk  number of disk reads during parse
  prsqry  number of buffers for consistent read during parse
  prscu   number of buffers for current read during parse
  prsmis  number of misses in library cache during parse
  execnt  number of execute was called
  execpu  cpu time spent executing
  exeela  elapsed time executing
  exedsk  number of disk reads during execute
  exeqry  number of buffers for consistent read during execute
  execu   number of buffers for current read during execute
  exerow  number of rows processed during execute
  exemis  number of library cache misses during execute
  fchcnt  number of times fetch was called
  fchcpu  cpu time spent fetching
  fchela  elapsed time fetching
  fchdsk  number of disk reads during fetch
  fchqry  number of buffers for consistent read during fetch
  fchcu   number of buffers for current read during fetch
  fchrow  number of rows fetched
  userid  userid of user that parsed the cursor

Sort options: default

tkprof output
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

rman restore validate database taking a long time

2007-11-02T11:45:00.000-07:00

One of the database was taking really long time while doing the rman restore validate database.
somehow I found out the oracle process for this db was running under oinstall . and the oracle user which was only part of dba group was doing the rman. not sure if it is the cause but after making oracle user also as part of oinstall it started to validate as expected.

solaris
bash-2.03$ id -a
uid=600(oracle) gid=600(dba) groups=600(dba),1005(oinstall)

still confused....

truss a os process

2007-11-02T11:37:00.000-07:00

$>ps -ef| grep 16069| grep -v grep
oracle 16069 1 0 14:07:17 ? 0:00 ora_d000_mtrx

$>truss -o a.log -p 16069

$>cat a.log
poll(0x106785F40, 2, 60000) (sleeping...)

few other tools that helps
ps
prstat
pstatck -

for linux - strace
tru64 trace

trace over dblink

2007-11-02T11:20:00.000-07:00

CONNECT / AS SYSDBA
grant alter session to <>;
alter system set timed_statistics=true;
alter system set max_dump_file_size=unlimited;
CONNECT <>/<>
/* create this procedure on local and remote site */
CREATE OR REPLACE PROCEDURE SETTRACE_ON
as
c1 integer;
r1 integer;
BEGIN
c1:=dbms_sql.open_cursor;
dbms_sql.parse(c1,'alter session set timed_statistics=true', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
dbms_sql.parse(c1,'alter session set max_dump_file_size=unlimited', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
dbms_sql.parse(c1,'alter session set tracefile_identifier=''mv''', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
dbms_sql.parse(c1,'alter session set events ''10046 trace name context for
ever, level 12''',dbms_sql.v7);
r1:=dbms_sql.execute(c1);
END;
/

CREATE OR REPLACE PROCEDURE SETTRACE_OFF
as
c1 integer;
r1 integer;
BEGIN
c1:=dbms_sql.open_cursor;
dbms_sql.parse(c1,'alter session set events ''10046 trace name context off''',dbms_sql.v7);
r1:=dbms_sql.execute(c1);
END;
/

connect /
execute SETTRACE_ON
execute SETTRACE_ON@
execute dbms_mview.refresh(....);
--wait for 15 mins and CTRIL+C
exit;

trace oracle session

2007-11-02T11:18:00.000-07:00

dbms_system.set_bool_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'timed_statistics',
bval => true)
dbms_system.set_int_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'max_dump_file_size',
intval => 2147483647)

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> alter session set events '10046 trace name context off';

Session altered.

oradebug

2007-11-02T11:16:00.001-07:00

SELECT p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid =<397>

sql>oradebug setospid
sql>oradebug unlimit
sql>oradebug dump errorstack 3;

sql>oradebug dump errorstack 3;

sql>oradebug dump errorstack 3;
sql>oradebug tracefile_name
sql>exit

firefox magic

2007-11-01T22:04:00.000-07:00

Type about:config in the address bar

network.http.max-connections-per-server =32
network.http.max-persistent-connections-per-proxy =16
network.http.max-connections = 64
network.http.max-persistent-connections-per-server = 10
network.http.pipelining = true
network.http.pipelining.maxrequests = 200
network.http.request.max-start-delay = 0
network.http.proxy.pipelining = true
network.http.proxy.version = 1.0

rman backup of controlfile

2007-11-01T16:19:00.000-07:00

RMAN> list backup of controlfile completed before 'sysdate -4/24';

RMAN> delete noprompt backup of controlfile completed before 'sysdate -4/24';

crosscheck backup of controlfile;
delete noprompt expired backup of controlfile;