remidian

Can't find what you're looking for? Use of one of the search websites below …

Home → Archive by category "oracle"

Category Archives: oracle

PL/SQL quickie; generate RMAN set newname script to different mountpoints

Script below generates RMAN newname file where database A is copied to database B AND database B has ‘conflicting’ mountpoints set up. Mountpoints setup on database A is like /m001/, /m002/, /m003/, /m004/ but mountpoints on database B side is just /m001/ and /m002. /m001 has only 800 G free space so I place everything in /m001 until i_num_bytes reaches 800G then all the reset goes to /m002.

declare i_num_bytes number := 0; v_mount varchar2(100); v_file varchar2(100); begin for i in (select file_id, file_name, bytes from dba_data_files order by file_id) loop i_num_bytes := i_num_bytes + i.bytes ; -- 800g if i_num_bytes < 858993459200 then v_mount := '/m001/oradata/********/' ; else

Generate scp datafiles script with regular expression

Need to transfer a bunch of databases with pattern {a…9} to {t…8}.

Please copy databases below;

adummy19 to tdummy18 – server070 (*) adummy29 to tdummy28 – server526 adummy39 to tdummy38 – server526 adummy49 to tdummy48 – server526 adummy59 to tdummy58 – server526 adummy69 to tdummy68 – server526 adummy79 to tdummy78 – server526 adummy89 to tdummy88 – server526

Created PL/SQL script below to get me the scp commands;

declare
 function replace_sid ( piv_string in varchar2)
  return varchar2
 as
  db_name_cur varchar2(64);
  db_name_new varchar2(64);
 begin
  -- current db_name
  select value into db_name_cur from v$parameter where name = 'db_name';
  
  -- new db_name 
  db_name_new := regexp_replace( db_name_cur, '(^a)', 't');
  db_name_new := regexp_replace( db_name_new, '(9$)', '8');
   
  -- return replacement
  return replace( piv_string, db_name_cur, db_name_new);
 end;
 
begin
 for i in ( select file_name from dba_data_files ) 
 loop
  dbms_output.put_line ( 'scp ' || i.file_name || ' ahc526:' || replace_sid( i.file_name));
 end

Segment Advisor dump (R 10.1)

Dump of running segment advisor in Oracle 10.1

@See: http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_advis.htm http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2049277600346543592 http://www.oracle-base.com/articles/10g/SpaceObjectTransactionManagement10g.php /

For tablespace

create or replace procedure sa_tablespace
(
  piv_tablespace in varchar2,
  piv_task       in varchar2 default null
)
  authid current_user
 
as
 
  obj_id number;
  task   varchar2(100);
 
begin
 
   if piv_task is null then
     task := 'SA TABLESPACE ' || piv_tablespace ;
   else
     task := piv_task;
   end if;
 
  -- remove task
  begin
    dbms_advisor.delete_task ( task_name     => task);
  exception
    when others then null ;
  end;
 
  -- create task
  dbms_advisor.create_task (
    advisor_name     => 'Segment Advisor',
    task_name     => task);

expdp / impdp

Some expdp impdp commands. Quotes in ‘/as sysdba’ needs escape slashes

/m001/oradata/EXPORT>expdp \'/ as sysdba\' schemas=owner1,owner2,owner3 directory=EXP_DIR2 dumpfile=<filename> logfile=<filename>

ORAENV_ASK

ORAENV_ASK=no 

export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql

monitor progress of long running job with dbms_application_info.set_session_longops

Investigating a major performance issue with a PL/SQL loop running for more then 12 hours. Added dbms_application_info.set_session_longops to have entry in v$session_longnops. 

DECLARE
 
  -- @what: display elapsed msecs
  -- @link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2389164927483
  n_starttime NUMBER;
  n_endtime   NUMBER;
  n_elapsed   NUMBER;
  
  d_date      DATE   := SYSDATE;
 
  -- @what: dbms_application_info.set_session_longops
  -- @link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1099233454171#18173358903352
  l_nohint     NUMBER DEFAULT dbms_application_info.set_session_longops_nohint;
  l_rindex     NUMBER DEFAULT l_nohint;
 
  n_slno      NUMBER;
  n_totalwork NUMBER := 847; -- init total work, number of records in loop recordset is 847
  n_sofar     NUMBER := 0;
 
BEGIN
 
  FOR i IN
  (
          SELECT  *********.DBKey  FloorDBKey,

change database id of database

SQL script to change database id of database. 

shutdown immediate
startup mount
host nid target=/
disconnect
connect / as sysdba
startup mount
alter database open resetlogs;

  • / oracle
  • # 3625
  • SEP, 27 2010

a winner, flashback query; AS OF TIMESTAMP

Recovering data from a malicious SQL update carried out on a production database. Instead of restoring the whole database to recover some lost data I gave flashback query a try and even thougg the ‘undo_retention’ parameter was set to only 5 minutes oracle recovered the data from the table from about 18 hours before.

SQL Statements below recover the data from a timestamp just before the offending SQL update was carried out. I saved the data to a _BAK table.

From: Visser, Remi Sent: Wednesday, September 22, 2010 3:37 PM To: ******** Subject: recoverd *********** table using oracle flashback query

Importance: High

Hi ****

I was able to recover the table using flashback query. See below.

Please check the data in the table ********_BAK it contains the data in ******** as of 2010-09-22 09:00:00. The offensing update was carried out at 9:10 hours.

When the data is OK I can merge the data from the _BAK table to

Purging SYSAUX tablespace (Purging AWR reports)

Dump below is of a session clearing the sysaux tablespace. It turned out that something went wrong with the automatic AWR gathering/purging and the sysaux tablespace became very large.

Thanks to this blog I found script below wich displays the various sysaux components and their sizes. 

SQL> col "Space (M)" for 999,999.99  
SQL> 
SQL> SELECT 
  2    occupant_name,  
  3    round( space_usage_kbytes/1024) "Space (M)",  
  4    schema_name, 
  5    move_procedure
  6  FROM 
  7    v$sysaux_occupants  
  8  ORDER BY 
  9    1  
 10  /
OCCUPANT_NAME          Space (M) SCHEMA_NAME          MOVE_PROCEDURE
-------------------- ----------- -------------------- ----------------------------------------
AO

  • / Grid
  • # 2472
  • FEB, 19 2008

Manually triggering Metric Collection (clearing -tablespace- Alerts)

Manually triggering metric collection in Oracle Enterprise Manager (GRID control)

Here’s the scenario:

You have an Alert that your tablespace is ‘full’, You enlarge the tablespace, The ‘Tablespace Full Alert’ is still in your dashbord for the next 24 (x) hours since you’ve configured this metric to be collected once every 24 (x) hours, You want however to ‘remove’ (clear) this Alert. In order to achive this you will have to to re-evaluate this metric collection …

Here is how you do it (for anything storage related on your database server): 

$ emctl control agent runCollection <hostname>:host host_storage

See the Oracle docs, 2.7.6 Reevaluating Metric Collections. It shows you how to retrieve the correct collection name for any metric.

You will find yourself grep-ing through ‘$ORACLE_HOME/sysman/admin/metadata/*xml’ and ‘$ORACLE_HOME/sysman/admin/default_collection/*xml’ …

  • / Grid
  • # 9151
  • JAN, 28 2008

Increase idle time for OMS frontend sessions

Some additional configuration directives.

To increase the idle time for the OMS frontend sessions edit the ‘oracle.sysman.eml.maxInactiveTime’ configuration parameter your Oracle Management Server’s emoms.properties file. The parameter value unit is in minutes and the default is 15 minutes which is a bit short I suppose. I have set it to 4 hours as you can see in the example below. 

$ cat $ORACLE_HOME/sysman/config/emoms.properties | grep maxInactiveTime
oracle.sysman.eml.maxInactiveTime=240
$

For more info see the ‘Enterprise Manager Advanced Configuration‘ book

Batch SQL statements in parallel (multiple) sessions

So it was decided ‘this batch’ had to be done ‘this weekend’. Thousands of SQL statements some of them would take minutes others hours… And MAKE SURE it’s finished monday.

I hate babysitting multiple sessions having to create multiple SQL Scripts to spread the workload (it’s on a 20+ CPUS’s database server) So I wrote this PL/SQL ‘application’ in a hurry to enable starting multiple databases sessions in nohup, each session picks up the next SQL statement ‘available’ (WHERE endtime IS NULL). It will save me some sleep.

Setup

The table holding my SQL statements
CREATE TABLE batchsql
 ( batchid              VARCHAR2(64)    NOT NULL
 , sqlid                INTEGER         NOT NULL UNIQUE
 , sqltext

Really delete (‘corrupt’) Agent from OMS repository

Here’s how to overcome ORA-20206 in Oracle GRID environment, agent status unclear…

Scenario

Removed (Windows) Agent from Grid. (With SQL Server Management plugin) Tried to restart this SAME agent, errors below occur. 
# $ORACLE_HOME/sysman/log/emagent.log contains
 
2008-01-17 14:13:52 Thread-4032 EMAgent started successfully (00702)
2008-01-17 14:14:21 Thread-2720 <Upload Manager> Unsuccessful Upload attempts for XML file exceeds specified limit=3, 
      Agent will shutdown (00851)
2008-01-17 14:14:21 Thread-4032 EMAgent abnormal terminating (00704)

# $ORACLE_HOME/sysman/log/emdctl.trc contains
 
2008-01-17 14:13:32 Thread-752 WARN  http: snmehl_connect: connect failed to (<agent_hostname>:3872): 
      No connection could be made because the target machine actively refused it.
 (error = 10061)
2008-01-17 14:13:32 Thread-752 ERROR main: nmectla_agentctl: Error connecting to 
      https://<agent_hostname>:3872/emd/main/. Returning status code 1
2008-01-17 14:14:23 Thread-172 WARN  http: snmehl_connect: connect failed to 
      (<agent_hostname>:3872): 
      No connection could be made because the target

Clearing Alerts in Oracle GRID Control

Here’s how to clear alerts from the Oracle GRID manually.

For some obscure reason Oracle is very reluctant in providing the information that is described here below. Search Metalink and you will find no entries… The information below is from dbasupport.com and helps me to clear alerts that the Agent does NOT clear for me.

sysman.em_severity
[REMI@DB01.REMIDIAN.COM]
SQL>  @desc sysman.em_severity
.
.
PROCEDURE DELETE_CURRENT_SEVERITY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TARGET_GUID                  RAW                     IN
 P_METRIC_GUID

invalid objects after clean ctxsys installation (oracle text)

The following objects are invalid after a clean ctxsys install.

Invalid objects
[REMI@DB01.REMIDIAN.COM]
SQL> @oj-in
OWNER                  OBJECT_NAME                      OBJECT_TYPE     CREATED    LAST_DDL_TIME TIMESTAMP           STATUS
---------------------- -------------------------------- --------------- ---------- ------------- ------------------- -------
CTXSYS                 DBMS_DATA_MINING_DUMMY           PACKAGE         08-11-2007 08-11-2007    2007-11-08:16:26:32 INVALID
                       DBMS_DATA_MINING_DUMMY

dbverify in nohup

Suffering from corruption in your database, it is nearly just as challenging as in real life…

The other day we suffered from a curruption in a 500+ Gigabyte database, as a sequentual dberify would take about 16+ hours I wrote this one below which starts the dbv’s in nohup in parallel. My dbv finished within the hour, system performance will drop though;) 

[SYSTEM@DB01.REMIDIAN.COM]
SQL> l
  1  select
  2  'nohup dbv' ||
  3   ' file=' || file_name ||
  4   ' blocksize=4096' ||
  5   ' logfile=' || file_id || '_' || substr( file_name, instr(file_name,'/',-1) +1) || '.log &'
  6  from
  7* dba_data_files

[SYSTEM@DB01.REMIDIAN.COM]
SQL> /
 
'NOHUPDBVFILE='||FILE_NAME||'BLOCKSIZE=4096LOGFILE='||FILE_ID||'_'||SUBSTR(FILE_NAME,INSTR(FILE_NAME,'/',-1)+1)||'.LOG&'
------------------------------------------------------------------------------------------------------------------------
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\USERS01.DBF blocksize=4096 logfile=4_C:\ORACLE\ORADATA\DB01\DB01\USERS01.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\SYSAUX01.DBF blocksize=4096 logfile=3_C:\ORACLE\ORADATA\DB01\DB01\SYSAUX01.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\UNDOTBS01.DBF blocksize=4096 logfile=2_C:\ORACLE\ORADATA\DB01\DB01\UNDOTBS01.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\SYSTEM01.DBF blocksize=4096 logfile=1_C:\ORACLE\ORADATA\DB01\DB01\SYSTEM01.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\EXAMPLE01.DBF blocksize=4096 logfile=5_C:\ORACLE\ORADATA\DB01\DB01\EXAMPLE01.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\P1PERG001.DBF blocksize=4096 logfile=7_C:\ORACLE\ORADATA\DB01\DB01\P1PERG001.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\P1PERG002.DBF blocksize=4096 logfile=8_C:\ORACLE\ORADATA\DB01\DB01\P1PERG002.DBF.log &

  • / 10g
  • # 1104
  • SEP, 5 2007

Oracle Advisor – DBMS_ADVISOR

Some ADDM dumps.

Remove all advisory reports (ADM). 

[REMI@DB01.REMIDIAN.COM]
SQL> select 'exec dbms_advisor.delete_task(''' || TASK_NAME || ''' )' from dba_advisor_tasks;
 
'EXECDBMS_ADVISOR.DELETE_TASK('''||TASK_NAME||''')'
----------------------------------------------------------------
exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_1300592007' )
exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_2700592007' )
exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_2220592007' )
exec dbms_advisor.delete_task('ADDM:1488676071_1_33108' )
exec dbms_advisor.delete_task('ADDM:1488676071_1_33113' )
 
[REMI@DB01.REMIDIAN.COM]
SQL>

  • / 11g
  • # 894
  • AUG, 25 2007

11g installation on Linux (Centos)

@wip

  • / 10g
  • # 1446
  • AUG, 14 2007

new explain plan options (dbms_xplan)

I still need to upgrade to 10g …

I still tend to use this old fashioned ‘set autot trace exp’
[REMI@DB01.REMIDIAN.COM]
SQL> set autotrace trace exp
[REMI@DB01.REMIDIAN.COM]
SQL> select * from dual;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 Bytes
          =2)
While in 10g the explain plan is pretty awseome – dbms_xplan package
[REMI@DB01.REMIDIAN.COM]
SQL> explain plan for select * from dual;
 
Explained.
 
[REMI@DB01.REMIDIAN.COM]
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 3543395131
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |

delete large number of records with intermediate commits

Have to delete a large number of records and running out of undo tablespace ‘ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDO’? Script below does a delete with intermediate commits ever ‘x’ records. 

declare
 
  -- commit every 'i_commit' rows
  i_commit   pls_integer := 1000;
 
  -- row counter, 'i_rowcount' rows deleted
  i_rowcount pls_integer := 0;
 
begin
 
  -- define 'infinite' loop
  loop
 
    -- delete 'i_commit' rows
    delete from obj
    where rownum <= i_commit;
 
    i_rowcount := i_rowcount + sql%rowcount;
 
    -- now it's time to exit the loop
    if sql%rowcount = 0 then
       commit;
       exit;
    end if;
 
    commit;
 
  end