TEMP space error

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

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

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

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET

configure device type disk clear;

database recovery

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

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

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

copy schema using dbms_datapump

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

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

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

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

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

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

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

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

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

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

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

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

Use the bookmark manager to export and import

generate user creation script

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

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

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

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

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

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] [] []

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

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

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

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

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

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

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

/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

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"

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

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

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

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

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

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

$>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

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

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

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

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

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;

restoring from archivelog backupset

list backup of archivelog logseq 3113;
change backupset 271837 available;
restore archivelog from logseq 3113;

recover database

list backup of archivelog until scn 21253467415
delete backup of archivelog until scn 21253467415;

-
RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;
RESTORE DATAFILE 3 PREVIEW;
RESTORE ARCHIVELOG FROM LOGSEQ 200 PREVIEW;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW;

shell script to clean the oracle dump directories

find . -name \*.log -mtime +1 -exec ls {} \;
find . -name \*.log -mtime +1 -exec rm {} \;

#!/usr/bin/ksh
#

PATH=${PATH}:/usr/local/bin

# Check Syntax
if [ $# -ne 2 ]
then
echo "Syntax Error: clean_dumps.sh ."
exit 1
else
DAYS=$2
fi

. /var/opt/oracle/test10g.env

# Redirect output to log
exec 1>${ORACLE_DBA}/${ORACLE_SID}_clean_dumps.`date +%w` 2>&1

echo "Start: `date`"

find ${ORACLE_DBA}/${ORACLE_SID}/udump -name "*.trc" -mtime +${DAYS} -exec ls -l {} \;
find ${ORACLE_DBA}/${ORACLE_SID}/udump -name "*.trc" -mtime +${DAYS} -exec rm -f {} \;

find ${ORACLE_DBA}/${ORACLE_SID}/bdump -name "*.trc" -mtime +${DAYS} -exec ls -l {} \;
find ${ORACLE_DBA}/${ORACLE_SID}/bdump -name "*.trc" -mtime +${DAYS} -exec rm -f {} \;

find ${ORACLE_DBA}/${ORACLE_SID}/cdump -name "core_*" -mtime +${DAYS} -exec ls -l {} \;
find ${ORACLE_DBA}/${ORACLE_SID}/cdump -name "core_*" -mtime +${DAYS} -exec rm -f -r {} \;


echo "\nEnd: `date`"

removing oracle shared memory segment in solaris

kill the oracle processes
u@h:OFSWDC:w>ps -ef|grep $ORACLE_SID| grep -v grep|awk '{print $2}'|xargs -i kill -9 {}

view the shared memory still hold by oracle
u@h:OFSWDC:w>ipcs -pmb
IPC status from as of Wed Oct 31 18:25:42 EDT 2007
T ID KEY MODE OWNER GROUP SEGSZ CPID LPID
Shared Memory:
m 19456 0x27926f00 --rw-r----- oracle dba 1140858880 6163 4954

remove it -
u@h:OFSWDC:w>ipcrm -m 19456

unix shell script to take oracle cold backup

define 3 = &backup_directory
define 4 = &instance_name
define 5 = &user_dump_directory

Set Heading Off
Set Verify Off
Set FeedBack Off
Set LineSize 132
Set PageSize 1000
set termout off
Spool cold_back.sql

select 'connect /as sysdba' from dual;

Select 'Startup Force' || CHR(10) ||
'Shutdown Normal' From Dual;

select '! mkdir '||'&&3'||'/'||to_char(sysdate,'yyyyddmm') from dual
/


/* Data Files */
Select '!cp ' || File_Name || ' &&3'||'/'||to_char(sysdate,'yyyyddmm') || CHR(10) ||'!gzip ' || '&&3' ||'/'||to_char(sysdate,'yyyyddmm')|| '/' || SubStr(File_Name, InStr(File_Name, '/', -1)+1) From Sys.DBA_Data_Files
/

/* Redo Log Files */
Select '!cp ' || Member || ' &&3'||'/'||to_char(sysdate,'yyyyddmm') || CHR(10) || '!gzip ' || '&&3'||'/'||to_char(sysdate,'yyyyddmm') || '/' || SubStr(Member, InStr(Member, '/', -1)+1) From V$LogFile
/

/* Control Files */
Select '!cp ' || name || ' &&3'||'/'||to_char(sysdate,'yyyyddmm') From V$controlfile
/

/* Init and Config Files */
Select '!cp $ORACLE_HOME/dbs/init&&4' || '.ora &&3'||'/'||to_char(sysdate,'yyyyddmm') From Dual
/

alter session set tracefile_identifier=coldbackup
/

alter database backup controlfile to trace
/

select '!mv '||'&&5/*COLDBACKUP*' ||' &&3'||'/'||to_char(sysdate,'yyyyddmm') FROM dual
/

Select 'Startup' From Dual;
Select 'Exit' from dual;
set termout on
PROMPT " Run the output cold_back.sql script [sqlplus -s '/as sysdba' @cold_back.sql ]"

Spool Off

setting up nmo for grid control

execute the following as root:
cd $ORACLE_HOME (your OEM HOME directory)
chmod 6750 bin/nm?
chmod 700 bin/emdctl
chmod 700 bin/emagent
chown root bin/nm?

as oem user:
emctl stop agent
emctl start agent

run the root.sh under agent directory

ORA-10980 in trace file while creating materialized view?"

I was getting ORA-10980 in trace files.
After granting all the permission still was not able to create the materialized view. but somehow putting driving_site hint and placing the local table on the right most I was able to create the mview.

SELECT /*+ DRIVING_SITE(cpf) DRIVING_SITE(dr) DRIVING_SITE(txn) DRIVING_SITE(tuc) DRIVING_SITE(rc) DRIVING_SITE(ru) */
mdevice.mPLACE_ID,
mdevice.mMODEL_ID,
TRUNC( cpf.dATE_TIME ) AS TXN_DATE,
COUNT(*) AS NUMBER_TXNS
FROM
FINANCIAL@MLINK cpf,
tDEVICE_RULE@MLINK dr,
tTXN_RULE@MLINK txn,
tTXN_USAGE_CLASSIFICATION@MLINK tuc,
tRULE_CLASSIFICATION@MLINK rc,
tRULE_USAGE@MLINK ru,
MV_MTREVICES mDevice
WHERE
--------