Sam's Oracle BLOG

MarsEdit

2012-03-20T11:06:00.001+13:00

MarsEdit, http://www.red-sweater.com/marsedit/, looking quite good.
Firstly, easy to setup, I give it a label and http address, the MarsEdit goes and asks for my google userid and password, linked to my blog.

I then get a nice interface, with my categories, i.e., tags, a list of all of my blog entries, so simple.
So here I am, writing a blog entry. Lets see what it looks like on the other side.

...

On the other side, it doesn't look too bad.

And on the other side -

yip looks pretty good.

Missing DBMS_SCHEDULER Jobs via OEM

2011-11-24T16:35:00.001+13:00

Under OEM 11.1, have noticed that SYS jobs are not included in the list of current jobs. Of course no DBA would create objects under SYS, however sometimes we do.

If you notice a job is running, even an os command, then query the dba_scheduler_jobs view. This will list all dbms_scheduler jobs, then use notmal dbms_scheduler options to work with this job, prefrebably drop the job.

Invisible Indexes

2011-11-17T15:14:00.001+13:00

This is a really nice feature, particulary for me, a development DBA. Essentially you can make an index invisible so the optimizer doesn't use this index. This is excellent testing new indexes. You don't need to spend the time waiting for an index to be recreated each time you want to compare the differences.

To make an index invisable then run:
sql> alter index <index name> INVISIBLE;

To make an index visible:
sql> alter index <index name> VISIBLE;

You can also make the optimizer use all invisible indexes by for the current session by:
sql> alter session set optimizer_use_invisible_indexes = true ;

TAR and Excluding Files

2011-11-12T21:07:00.001+13:00

This blog will quickly discuss how to exclude files when using TAR to create an archive.

The syntax to do this is:

tar cvfX <

Packaging Oracle Incidents

2011-11-11T10:55:00.001+13:00

Under version 11, oracle introduced an interesting mechanism called "adrci" aka

"AUTOIMATIC DIAGNOSTIC REPOSITORY"

This tool, among other tasks does the following:

- Automatically purges trace files, alert log files etc
- Manages "incidents"
- Packages incidents.

This blog will talk about how to manage the logs and manage incidents. Although many features discussed in this blog can be completed via OEM, I will be discussing how to do this from the command interface.

To Log On, run:

unix> adrci

To show your current environment, run:

adrci> show homes

To set a HOME environment, run:

adrci> set home

To show current incidents, run:

adrci> show incidents

ADR Home = /opt/oracle/admin/diag/rdbms/xxx/XXX:

*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ------------------
12426 ORA 3137 [12333] 2011-11-02 16:47:50.277000 +13:00
1 rows fetched

To purge a particular incident, run:

adrci> purge -i n nn nnn

To purge a type of data, run:

adrci> purge -age nn -type xxx

where age is defined in minutes and type can be "alert, incident, trace, dump etc"

11.2 Update Window Details

2011-11-08T20:48:00.003+13:00

One of the nice features introduced in recent oracle releases was automated maintenance jobs. This is a nice feature as oracle tries to automate STATS generation and automating execution of advisors.

Sometimes, however the default schedule for these maintenance jobs is not good enough, so to change these windows.

In a nutshell, you need to disable the window, set attributes, duration time, start time etc.

A sample would be:

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."SATURDAY_WINDOW"',
force=>TRUE);
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(1080, 'minute'));
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=3;BYMINUTE=0;BYSECOND=0');
END;

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."SATURDAY_WINDOW"');
END;

tar / compress in one command

2011-08-11T16:22:00.002+12:00

To tar and then compress in one command use the following:

tar cvf - directory | gzip > filename.tar.gz

To uncompress and untar, use the following:

cat filename.tar.gz | gunzip | tar xvf -

rman delete

2011-08-10T15:30:00.004+12:00

I have the following scenario. I have database backups I want to delete as I am recreating a database tomorrow morning, long story, but we have found a bug when running a block size of 16k.

I don't want to run the "RMAN DROP DATABASE INCLUDING BACKUPS NOPROMPT" command as the business testers want this environment back reasonably quickly.

What I did was:

unix> rman target /

rman> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

rman > DELETE NOPROMPT ALL;

Then it listed all the backup to remove, and I said "YES".

Now when I run the RMAN DROP DATABASE command tomorrow, it will complete in good time, not 2-3 hours it took to delete all my backups.

vi search and replace

2011-08-03T14:05:00.003+12:00

Use "%s/searchstring/replacestring/g

If the searchstring or replacestring has a "/", then place a "\" in front. This is an escape character meaning that any character that follows is taken as its literal meaning.

ksh Setting Variables only if the variable is not set

2011-05-16T11:34:00.001+12:00

ORATAB=${ORATAB:-/var/opt/oracle/oratab}

DATESTRING=${DATESTRING:-$(date)}

Xauth x11 forwarding on unix

2011-05-04T20:21:00.000+12:00

Use the following commands to set pup xauth forwarding on unix using putty.

1. Update putty X11 settings. Click the "ENABLE X11 FORWARDING", for each putty connection.

2. Login to unix server.

3. Run > xauth list

Output will be something like /unix:nn MIT ...

Where nn is a number like 10, 11, 12, ...

4. Start exceed / similar server.

5. Run > export DISPLAY=localhost:nn

If sudoing to another unix account then run the following:

1. Sudo to unix account

2. Run > xauth add unix:nn ... Output from xauth list command ran in step 3 above.

3. Run > export DISPLAY=localhost:nn

Now run your x11 application.

Hostid on solaris

2011-05-04T20:00:00.000+12:00

A pretty simple command, just run:

> hostid

Remove unwanted files from FLASH_RECOVERY_AREA

2011-02-22T11:31:00.003+13:00

Move backups from the flash recovery area to tertiary storage such as tape.

RMAN> BACKUP RECOVERY AREA;

Note : Flashback logs cannot be backed up outside the recovery area and so are not backed up by BACKUP RECOVERY AREA.

NOTE: Removing these files with an operating system utility will physically remove these files, but the database will still think the files exist and the problem of archival error may still occur. Use the following commands to synchronize the files with the database and use rman to remove these files.

RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;

RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;

4. Make sure that your guaranteed restore points are necessary. If not, delete them

SQL>Drop restore point restore_point_name

5. If flashback logs are enabled then make sure you have enough space for all the flashback logs. If its not required then you can turn off flashback.

SQL> Alter database FLASHBACK OFF;

6. Review your backup retention policy and if required change the RMAN RETENTION POLICY

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Exceptions :

- If RMAN is not part of backup strategy and archivelogs are going to FRA then manual intervention required for deletion of archivelogs. Periodically purse old archivelogs

for example

RMAN> Delete archivelog all completed before 'SYSDATE-7';

- By default RMAN backup goes to FRA. While taking RMAN backup if backup location explictly specified to flash recovery area location then those backup pieces are not considered as part of FRA for auto managment.

- For Archivelogs backup to FRA use USE_DB_RECOVERY_FILE_DEST rather than giving explict path of FRA

SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

- Bug 4911954

Details:
V$RECOVERY_FILE_DEST SPACE_USED and NUMBER_OF_FILES values may be wrong.
eg: Number of files in V$FLASH_RECOVERY_AREA_USAGE and V$RECOVERY_FILE_DEST are different.
This problem can lead to recovery area files being deleted even though there is no space pressure.
Fixed in : 10.2.0.3 , 11.1.0.6

- Bug 5106952

Details:
Flashback logs are not reclaimed by flash recovery area when using guaranteed
restore point (after dropping guaranteed restore point) or when changing
db_flashback_retention_target to a lower value.

11.2 recovery - free up spaceon DB_RECOVERY_FILE_DEST*

2011-02-14T10:53:00.002+13:00

Make more disk space available and increase DB_RECOVERY_FILE_DEST_SIZE

SQL> alter system set db_recovery_file_dest_size=xG  SCOPE=BOTH;  -- (larger amount)

2. Move backups from the flash recovery area to tertiary storage such as tape.

RMAN>BACKUP RECOVERY AREA;

Note : Flashback logs cannot be backed up outside the recovery area and so are not backed up by BACKUP RECOVERY AREA.

3. Run DELETE for any files that have been removed with an operating system utility. If you use host operating system commands to delete files, then the database will not be aware of the resulting free space.

RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK  ARCHIVELOG ALL;

RMAN>Delete expired backup;
RMAN>Delete  expired archivelog all;
RMAN>Delete force obsolete;

4. Make sure that your guaranteed restore points are necessary. If not, delete them

SQL>Drop restore point ;

5. If flashback logs are enable then make sure you have enough space for all the flashback logs. If its not required then you can turn off flashback.

SQL>Alter database FLASHBACK OFF;

6. Review your backup retention policy and if required change the RMAN RETENTION POLICY

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7  DAYS;

Exceptions :

- If RMAN is not part of backup strategy and archivelogs are going to FRA then manual intervention required for deletion of archivelogs. Periodically purse old archivelogs

for example

RMAN>Delete archivelog all completed before   'SYSDATE-7';

- For Archivelogs backup to FRA use USE_DB_RECOVERY_FILE_DEST rather than giving explict path of FRA

SQL> alter system set  log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'  scope=both;

- Bug 4911954

Details:             
  V$RECOVERY_FILE_DEST SPACE_USED and NUMBER_OF_FILES values may be wrong. 
  eg: Number of files in V$FLASH_RECOVERY_AREA_USAGE and V$RECOVERY_FILE_DEST are different. 
  This problem can lead to recovery area files being deleted even though there is no space pressure.
Fixed  in : 10.2.0.3 , 11.1.0.6

- Bug 5106952

Details:              
  Flashback logs are not reclaimed by flash recovery area when using guaranteed  
  restore point (after dropping guaranteed restore point) or when changing  
  db_flashback_retention_target to a lower value.

Zone Commands

2010-11-16T11:27:00.002+13:00

prstat - top like command that can be run on zones and give some nice memory / cpu summary information of what is going on with the zones.

/usr/sbin/arp -a - nice way of finding what the global zone is.

sql escape character setup

2010-09-22T15:38:00.003+12:00

in sqlplus setting the "&" as a literal, you need to do the following.

set escape \

select * from bla where blabla = 'xxx \&';

note: you should really set the escape sqlplus parameter in a glogin.sql or login.sql

sample login.sql using sql variabes

2010-09-21T17:22:00.003+12:00

Use the following to set up variables that are based on values from sql output.
Use the new_value option in the sqlplus column command.
See the example below which grabs the instance name of the database that is being used.

SQL> set termout off timing off feedback off
SQL> column instance new_value instance_name
SQL> select instance_name instance from v$instance ;
SQL> set termout on timing on feedback on
SQL> prompt &instance_name

note: The first set statement hides the output from running the v$instance sql query.

excel - auto calculations

2010-06-24T16:13:00.002+12:00

If excel is not auto calculating then check -

TOOLS/OPTIONS/CALCULATION and make sure that the automatic option is selected under "Calculation".

unix user management commands

2010-06-04T12:44:00.003+12:00

Various commands to create groups, add groups to users and make a group the default group.

Create New Group
# groupadd -g "groupid" "groupname"

Add Group to user, additional group
# usermod -G "groupname" "username"

Add Group to user, primary group
# usermod -g "groupname" "username"

unix - redirecting output

2010-04-16T11:07:00.003+12:00

In UNIX, run the following to ensure any errors (known as stderr) is not displayed on the screen.

Use 2> /dev/null

i.e.:

df -k 2> /dev/null

This works when using the ksh shell. There will be other derivitives when using other shells.

transportable tablespaces

2010-02-03T14:42:00.004+13:00

References: metalink note:77523.1

Refer to the relevant database documentation, correct version for restrictions when using this feature.

note:

a Ensure the following scripts have been run in the database. These would normally be run at database creation time.

$ORACLE_HOME/rdbms/admin/catplug.sql
$ORACLE_HOME/rdbms/admin/dbmsplts.sql
$ORACLE_HOME/rdbms/admin/prvtplts.plb

sql> desc dbms_plugts

b If running this procedure by a user other than sys then ensure the user has dba privileges and PUBLIC SYNONYMS for the following following have been created.

DBMS_PLUGTS
DBMS_TTS

1. Identify tablespaces that need to be migrated. note: Dependent data objects need to be incapsuated in the process. i.e.: Data + Index objects need to be included.

2. Put relevent tablespaces into readonly mode.

sql> alter tablespace ... readonly;

3. Export metadata from tablesspaces
unix> exp userid/password file=... tablespaces=... transport_tablespaces=y

note: multiple tablespaces need to be delimited by a comma.

4. Copy datafiles that make up the tablespaces identified above into the target environment.

5. Copy the export dat file created in step 3.

6. Import metadata into target database.

unix> imp ... tablespaces=... datafiles=... transport_tablespaces=y
note: tablespaces and datafiles need to be seperated by comma's.

7. Verify tablespaces are updated correctly by querying relevent views, dba_tablespaces and v$datafile.

8. Put tablespaces into read write mode in source and target databases.
sql> alter tablespace ... read write;

rank

2009-12-16T17:06:00.004+13:00

RANK and its mates can be useful functions to use when you need to order a list of records.

For example, I needed to transform a database from OMF (oracle managed files) to OSF (oracle standard files). As you may know, OMF bear no resemblance to the datafile standard most corporates seem to run, a typical omf data file might look like o1_mf_ts_ofsaa_2gt2g1hr_.dbf, while a osf might look like user_01.dbf

Being a good dba I wanted to script and automate the CREATE CONTROLFILE statement. My soltion to the above problem was to use the rank function.

So something like -

select tablespace_name, file_name, rank() over (PARTITION BY tablespace_name order by file_id) "id"

solved my problem and produced an output something like -

tablespace1 somefile1 1
tablespace1 somefile2 2
tablespace2 somefile1 1
...

I was then able to use the above statement to generate the datafile section of the create controlfile file.

EXIT from PL/SQL procedure block

2009-12-04T15:25:00.001+13:00

A simple method to EXIT from a PL/SQL block is to use the RETURN keyword. You should also be able to return an EXIT status.

So if we run something like -

1 BEGIN
2 dbms_output.put_line('one.');
3 return;
4 dbms_output.put_line('two.');
5* end;
n990538@edrtst> /
one.

PL/SQL procedure successfully completed.

instr, substr

2009-10-28T11:14:00.003+13:00

A couple of usefull oracle functions for manipulating strings.

instr(string1, string2, start position, nth appearance) returns the position where the string exists.

where
string1 is the original string to search from
string2 is the string to search in string 1
start posititon is optional and is the start position to start searching from
nth appearance is optional and is the number of occurances of string2 in string1.

instr('Tech on the net', 'e', 1, 3) would return 14.

substr(string1, start position, length) returns a string.

where
string1 is the string to check.
start position is the position in the string to start the substring from
length is optional and the length of the position.

substr('Tech on the net',1,5) would return 'Tech '

Often using a combination of the above functions is useful.

substr('Tech on the net',1, instr('Tech on the net', 'e', 1, 3)) would return ''Tech on the ne'

bug - 2942857 - OERI:12327 from complex view merging

2009-10-21T16:45:00.002+13:00

Bug 2942857 - OERI:12327 from complex view merging
DOC ID 2942857.8

Fixed: 9.2.05, 10.1.0.2

Complex view merging may fail with ORA-600[12327] if an COUNT(*) is used and the query has a subquery predicate against the COUNT(*) .

Workaround: Set "_complex_view_merging"=false

Affects EDR, EDMT, DWH.