Archive

Archive for the ‘Oracle’ Category

Finding exact SCNs for Oracle Incomplete Restore

Using reference from goldparrot below really helped:
https://goldparrot.wordpress.com/2011/05/16/how-to-find-exact-scn-number-for-oracle-restore/

2) Get the greatest of either absolute_fuzzy_change# or checkpoint_change# for your datafile backups.

The quickest way to roll forward just beyond the backup to open resetlogs is
to query v$backup_datafile.
You then just need to choose the greatest of either the absolute_fuzzy_change# or checkpoint_change#
for your backupset.

You can modify the query to your liking, and even add in many details from stamp and recid, but below
gets the job done quickly. Just choose the day in which your level0 or level1 completed and modify appropriately.

=============================================
` col fuzz# format 99999999999999999999999999
` col chkpnt# format 99999999999999999999999999

select max(absolute_fuzzy_change#) fuzz#, max(checkpoint_change#) chkpnt# from
(select file#, completion_time, checkpoint_change#, absolute_fuzzy_change# from v$backup_datafile
where incremental_level = 0
and trunc(completion_time) = to_date(‘JUN-20-2010′,’MON-DD-YYYY’)
and file# <> 0
order by completion_time desc
);
===============================================

This will return 2 SCN numbers. Pick the greatest of the 2.

FUZZ# CHKPNT#
——————– ——————–
23138984359 23138974759

Categories: Oracle, Uncategorized

NFS mount options for CRS Voting Disk and OCR

December 1, 2016 Leave a comment

Operating
System

Mount options for Binaries

Mount options for
Oracle Datafiles

Mount options for CRS Voting Disk and OCR

Sun Solaris *

rw,bg,hard,nointr,rsize=32768,
wsize=32768,tcp,noac,vers=3,suid

rw,bg,hard,nointr,rsize=32768,
wsize=32768,tcp,noac,
forcedirectio, vers=3,suid

rw,bg,hard,nointr,rsize=32768,
wsize=32768,tcp,vers=3,noac,
forcedirectio

AIX (5L) **

rw,bg,hard,nointr,rsize=32768,
wsize=32768,tcp,
vers=3,timeo=600

cio,rw,bg,hard,nointr,
rsize=32768,
wsize=32768,tcp,noac,
vers=3,timeo=600

cio,rw,bg,hard,intr,rsize=32768,
wsize=32768,tcp,noac,
vers=3,timeo=600

HPUX 11.23 ****

rw,bg,vers=3,proto=tcp,noac,hard,
nointr,
timeo=600,rsize=32768,
wsize=32768,suid

rw,bg,vers=3,proto=tcp,noac,
forcedirectio,hard,nointr,
timeo=600,
rsize=32768,wsize=32768,suid

rw,bg,vers=3,proto=tcp,noac,
forcedirectio,hard,nointr,
timeo=600
,rsize=32768,wsize=32768,suid

Windows

Not Supported

Not Supported

Not Supported

Linux x86

rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,vers=3,
timeo=600, actime=0

rw,bg,hard,nointr,rsize=32768,
wsize=32768,tcp,actimeo=0,
vers=3,timeo=600

rw,bg,hard,nointr,rsize=32768,
wsize=32768,tcp,noac,vers=3,
timeo=600

Linux x86-64

rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,vers=3,
timeo=600, actime=0

rw,bg,hard,nointr,rsize=32768,
wsize=32768,tcp,actimeo=0,
vers=3,timeo=600

rw,bg,hard,nointr,rsize=32768,
wsize=32768,tcp,vers=3,
timeo=600,
noac

Linux – Itanium

rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,vers=3,
timeo=600, actime=0

rw,bg,hard,nointr,rsize=32768,
wsize=32768,tcp,actimeo=0,
vers=3,timeo=600

rw,bg,hard,nointr,rsize=32768,
wsize=32768,tcp,noac,vers=3,
timeo=600

* The NFS mount option “forcedirectio” is required on Solaris platforms when mounting the OCR/CRS files when using Oracle 10.1.0.4 or 10.2.0.2 or later (Oracle bug 4466428)
** AIX is only supported with NAS on AIX 5.3 TL04 and higher with Oracle 10.2.0.1 and later (NetApp)
*** NAS devices are only supported with HPUX 11.23 ONLY

Categories: Oracle, Uncategorized

Oracle allow remote sys access

September 15, 2016 Leave a comment

SYS password is not necessarily the same for all the instances. Make sure remote_login_passwordfile is configured properly in the instances, and the password file exists for instance SID1 on the server in ORACLE_HOME/dbs/orapwSID1.

If you do not know the correct SYS password, you can recreate the password file, for example:

orapwd file=$ORACLE_HOME/dbs/orapwsid1 password= force=y

Format is important.

Categories: Oracle

Find size of table in Oracle (includes indexes and LOBs)

September 20, 2013 Leave a comment
COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;
Categories: Oracle

Disable SYS_AUTO_SQL_TUNING_TASK from DBMS_SCHEDULER and DBMS_JOBS

December 12, 2012 Leave a comment

Why should you disable SYS_AUTO_SQL_TUNING_TASK on Production DB systems?
On Production DBs, SQL should have been thoroughly tested prior to release into Production. SYS_AUTO_SQL_TUNING_TASK may cause SQL plans to change and in doing so would affect the performance of your queries.

Here’s how to disable SYS_AUTO_SQL_TUNING_TASK from DBMS_SCHEDULER and DBMS_JOBS:
conn / as sysdba

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL);
END;
/

If you are running this particular task, you may notice these errors:
“Process 0x%p appears to be hung in Auto SQL Tuning task”
“Current time = %u, process death time = %u”
“Attempting to kill process 0x%p with OS pid = %s”
“OSD kill skipped for process %p”
“OSD kill succeeded for process %p”
“OSD kill failed for process %p”

These errors usually indicate the job ran over the scheduled maintenance window. To extend the window to 21600s:
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter(‘SYS_AUTO_SQL_TUNING_TASK’, ‘LOCAL_TIME_LIMIT’, 21600);
END;
/

Otherwise, you may choose to disable the tuning process, just like above:
conn / as sysdba

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL);
END;
/

Categories: Oracle

Oracle database version differences (standard edition one, standard edition, enterprise, 10g express edition)

Click link below to view PDF:
Oracle database differences

Categories: Oracle

Oracle – generate sql and email as HTML

April 28, 2012 Leave a comment

report=”report.htm”
email=”email@email.com”

sqlplus -s <<EOD
/ as sysdba
set markup html on
set feed off head off pages 0 lines 200
spool "$report"

EOD

[ -f “$report” ] && uuencode “$report” “$report” | mailx -s “Your report” “$email”

Categories: Linux, Oracle