分类存档: Useful Doc - 第2页

The 'OraOLEDB.Oracle.1' provider is not registered

error message:
The 'OraOLEDB.Oracle.1' provider is not registered
….

solution:

– Right click on the oracle_home folder (the root folder where your client is installed) and choose Properties.
– On the security tab add the local machine's Users group (or if you prefer, add the IIS and ASPNET users individually). Grant Read & Execute, List Folder Contents and Read privileges.
– Click “Apply”.
– Click “Advanced”
– Check “Replace permission entries on all child objects …”
– Click “Apply”. This will take a while to propagate the security grants down to every file and folder of the oracle client.

Cannot resolve the collation conflict …

today I create 4 databases for a new application, and it requires case sensitive collation, so I change the collation from “Latin1_General_CI_AI” to “Latin1_General_CS_AI”, but when I create user and check “Securables”, it pump out a error window saying

…..
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the UNION to operation. (Microsoft SQL Server, Error: 468)

After investigate, I found the reason and solution,

Reason:
This will occur when your query attempts to join a user table to a temporary table, and the tempdb database collation differs from the user database collation (sp_helpdb will show you default database collation).  

Solution:
Option1: Locate the query that is raising the error, identify the join e­xpression and use the COLLATE clause to tell SQL which collation to use so that the query semantics aren’t ambiguous.

Option2: Change the collation of the temp db database to be consistent between the user defined databases.

ORA-39213: Metadata processing is not available

I was asked to refresh database from prod to AT env, and Before the refreshment, I want to take a full export for the AT database with data pump, unfortunitly, I got following error,

———————————–

With the OLAP, Data Mining and Real Application Testing options
orA-39006: internal error
orA-39213: Metadata processing is not available
———————————–

and Finially I find the reason ,

The Data Pump could not use the Metadata API. Typically, this is caused by the XSL stylesheets not being set up properly.

Solution:
conn / as sysdba
execute dbms_metadata_util.load_stylesheets ;
exit

test the expdp again.

Oracle Character set and National Character Set

##CHARACTER SET
specifies the character set the database uses to store data. You cannot change the database character set after creating the database. The supported character sets and default value of this parameter depend on your operating system.

You can specify any supported character set except the following fixed-width, multibyte character sets, which can be used only as the national character set:  

JA16SJISFIXED  
JA16EUCFIXED  
JA16DBCSFIXED  

parameter is NLS_CHARACTERSET.

## NATIONAL CHARACTER SET
specifies the national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. You cannot change the national character set after creating the database. If not specified, the national character set defaults to the database character set. See oracle8 Reference for valid character set names.
parameter is NLS_NCHAR_CHARACTERSET in database

you can use following query to check the value of the value of bath parameters,

col parameter format a25
col value format a20
Select *
  FROM nls_database_parameters
Where parameter like ‘%NLS%’
/

solution for orA-39080

Get following error when trying to export full database with expdp,

———————————————–
Export: Release 10.2.0.4.0 – 64bit Production on Wednesday, 03 March, 2010 4:34:
13

Copyright (c) 2003, 2007, oracle.  All rights reserved.

Connected to: oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit
Production
With the OLAP, Data Mining and Real Application Testing options
orA-31626: job does not exist
orA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYS
orA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
orA-06512: at “SYS.KUPV$FT_INT”, line 600
orA-39080: failed to create queues “KUPC$C_1_20100303043415” and “” for Data Pum
p job
orA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
orA-06512: at “SYS.KUPC$QUE_INT”, line 1606
orA-24203: operation failed, queue table SYS.KUPC$DATAPUMP_QUETAB has errors
———————————————–

and I applied oracle 2010 Jan CPU patches to the database recently. so that might be the reason.

Here is my solution,
继续阅读 »

error code 74 when applying oracle CPU patches

I was asked to apply oracle 2010 Jan CPU patches to one important database server, and I wasn’t worry at all since I applied several servers days before. But today, I got error , see error message below,

————————————-

ApplySession failed during prerequisite checks: Prerequisite check “CheckActiveF
ilesAndExecutables” failed.
System intact, OPatch will not attempt to restore the system
——————————————————————————–

The following warnings have occurred during OPatch execution:
1) OUI-67078:Interim patch 9169460 is a superset of the patch(es) [  8307238 ] in OH D:\Oracle\ora1020
——————————————————————————–

OPatch failed with error code = 74

Execution of ‘cmd /C “D:\dowinst\2010Jan_CPU\9169460\custom\scripts\post.bat” -a
pply 9169460 ‘:

Return Code = 0

The local system has been patched and can be restarted.

——————————————————————————–

继续阅读 »

Oracle Audit

最近有人在问要怎么查询audit的数据,在网上找了点资料,还是纪录下来的好..

View Audit Trail
The audit trail is stored in the SYS.AUD$ table. It's contents can be viewed directly or via the following views:

DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS

The audit trail contains alot of data, but the following are most likely to be of interest:

Username : oracle Username.
Terminal : Machine that the user performed the action from.
Timestamp : When the action occured.
Object Owner : The owner of the object that was interacted with.
Object Name : The name of the object that was interacted with.
Action Name : The action that occured against the object. (Insert, Update, Delete, Select, EXECUTE)

V$SESSION_LONGOPS

发现一个很好用的view, 就是 V$SESSION_LONGOPS, 如果你在做一个用时很久的操作,但是你又需要给你的老板,客户一个estimation, 你就可以查询这个view拉~~

这个是我常用的query ,
select sid, serial#, target, target_desc,sofar, TIME_REMAINING  from v$session_longops ;

TIME_REMAINING 是以s为单位的,所以也可以将上述改成,

select sid, serial#, target, target_desc,sofar, TIME_REMAINING/60/60 Remain_Hour  
from v$session_longops ;

Control file autobackup

I find many large files(each file is about 12M) in dump folder, names are like ,
ISC01P_C-3518282637-20081111-05
I find many large files(each file is about 12M) in dump folder, names are like ,
ISC01P_C-3518282637-20081111-05

since there is no enough space on bdump, I would like to remove such files from the folder. But before that, i also need to figure out what’s the files for… and finally here is the result,

they are the backup image of control file. I find following configuration in the catalog database,

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 35 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘D:\ORACLE\ADMIN\isc01p\BDUMP\%d_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE ‘SBT_TAPE’ TO 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE ‘SBT_TAPE’ TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘D:\ORACLE\ORA1020\DATABASE\SNCFISC01P.ORA’; # default
———————————————————-

继续阅读 »

expdp – include & exclude

you can use parameter EXCLUDE to exclude the objects when expdp/impdp. find the format below.

EXCLUDE=TABLE:”in (‘TABLE1′,’TABLE2′,’TABLE3’)”

EXCLUDE=SYNONYM,SEQUENCE,PACKAGE,FUNCTION,PROCEDURE,VIEW,JOB,TABLE:”in (‘TABLE1′,’TABLE2′,’TABLE3’)”

for windows,

schemas=userA
include=TABLE:”IN \(‘TAB1’\)”
directory=dmpdir
dumpfile=DBNAME_USERA_tabs1.dmp
LOGFILE=DBNAME_USERA_tabs1.log