Category Archives: SQL

Oracle: file needs recovery (offline mode)

Posted on by
Reply


select d.file# f#, d.name, d.status, h.status
from v$datafile d, v$datafile_header h
where d.file# = h.file#
and (d.status not in ('SYSTEM','ONLINE') or h.status != 'ONLINE' );

If there are such files, the recovery is necessary:

  1. restore the file from the backup
  2. recover datafile ‘&the_file_name’ ;
  3. alter database datafile ‘&the_file_name’ online;

Another possibility (if there are a lot of files):

  1. restore the files from the backup
  2. recover tablespace ‘&tbs_name’ ;
  3. alter tablespace ‘&tbs_name’ online;

MS SQL & Sybase: recreate master database

Posted on by
Reply

Sybase:
before 12.5: buildmaster (bldmatr for NT)
buildmaster -d master_device -s size_in_2k_pages

12.5 and later: dataserver with some options (sqlsvr for NT)
dataserver -d master_device -b size -forcebuild -z page_size -Z size_of_master_db

MS SQL:

before MS 2005:
rebuildm utility (GUI)

MS SQL 2005:

  1. Start server in single-user mode (sqlserver.exe -m)
  2. setup.exe /qn INSTANCENAME=instance_name REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=new_SA_Password
  3. Restart server in normal mode

Oracle: password function

Posted on by
Reply

Find the name of the current password function


SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';

Change the password function for the profile:


ALTER PROFILE &profile. LIMIT PASSWORD_VERIFY_FUNCTION &function_name.;

Example of the password function:


CREATE OR REPLACE FUNCTION dummy_func
(USERNAME VARCHAR2, PASSWORD VARCHAR2, OLD_PASSWORD VARCHAR2)
RETURN BOOLEAN IS
BEGIN
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password is the same as the username');
END IF;
RETURN(TRUE);
END;

Oracle: PERFSTAT.STATS$MUTEX_SLEEP_PK violated

Posted on by
1

There is a known problem with Oracle statspack report in 10g, which could cost You at least one lost statspack snapshot.

The following message is written into the alert log:


ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at “PERFSTAT.STATSPACK”, line 5264
ORA-06512: at “PERFSTAT.STATSPACK”, line 104
ORA-06512: at line 1

There is a Note 382993.1 in Metalink, which describes this bug.

The workaround is simple:

alter table perfstat.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;

create index perfstat.STATS$MUTEX_SLEEP_PK on
STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION);

… and check the Metalink for the news, if the bug was fixed.

[amazon-product]007222360X[/amazon-product]

Oracle: read the entries from alert log

Posted on by
Reply

Sometimes it’s necessary to read the information from the alert log together with the timestamp of the error.

The very simple Unix-shell script helps to do this:


echo
echo 'Enter # of lines:'
read NUM
tail -$NUM alert_${ORACLE_SID}.log | awk '
BEGIN {prev="" ; ret=1 }
/^(...-|Error)/ { if ( prev !~ /^(...-|Error)/ ) { print "" ; print prev;} print $0;ret=0}
{prev=$0}
END { exit ret } '

This code could be assigned to some hotkey in Your favorite telnet application, so You just need to press something like “Alt+A” to see the quick review of the recent Oracle errors.

Example:

Enter # of lines:
1000

Sep 01 19:42:03 2009
Errors in fileARC0: Archiving not possible: error count exceeded

Sep 01 19:42:06 2009
Errors in file testsrv_arc0_23486.trc:
ORA-16038: log 1 sequence# 112 cannot be archived
ORA-19502: write error on file “”, blockno (blocksize=)
ORA-00312: online log 1 thread 1: ‘redo_g1_m1.log’

Oracle: export as sysdba

Posted on by
Reply

The following trick could be used, if You want to start Oracle export as sysdba: use double quotes around the single quotes.

Warning! Dump consistency could not be guaranteed, if the export is done under sys user!


exp userid=" '/ as sysdba' " parfile=exp.par

Possible parameter file:

file=mydump.dmp
compress=n
log=mydump.log
direct=y
full=n
owner=SCOTT
consistent=y
feedback=10000