作者存档: Pazel

Oracle Rename datafile

set linesize 200
col tablespace_name format a8
col file_name format a45
select tablespace_name, file_id, file_name, bytes/1024/1024 size_m, AUTOEXTENSIBLE, INCREMENT_BY*1024/8 incre_MB
from dba_data_files
where tablespace_name=‘TESTTBS’
/

alter database datafile ‘/chhs32/chpwtst4/oradata/EDIDATATS_26.dbf’ resize 100M ;
or alter database datafile 310 resize 100M ;

alter tablespace TESTTBS read only ;

alter database datafile 310 offline ;

host mv /chhs32/chpwtst4/oradata/EDIDATATS_26.dbf /chhs32/chpwtst4/oradata/DIAMOND_128.dbf

alter database rename file ‘/chhs32/chpwtst4/oradata/EDIDATATS_26.dbf’ to ‘/chhs32/chpwtst4/oradata/DIAMOND_128.dbf’;

alter database datafile 310 online ;

alter tablespace TESTTBS read write

Error ORA-01775: looping chain of synonyms when export

C:\Documents and Settings\nai3119>expdp ‘/ as sysdba’ DIRECTORY=DMPDIR schemas=DOW_USER DUMPFILE=JVL
PS04P_RELEASE31_EXP0211.DMP LOGFILE=JVLPS04P_RELEASE31_EXP0211.LOG

Export: Release 11.2.0.2.0 – Production on Fri Feb 10 06:06:30 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Automatic Storage Management, Oracle Label Security, OLAP, Oracle Database Vault
and Real Application Testing options
ORA-39001: invalid argument value
ORA-01775: looping chain of synonyms

*************************************************************************

solution: query in database with below sql,

SELECT owner, object_name, object_type, status from dba_objects where object_name like ‘%SYS_EXPORT%’;

if you get a record like with type SYNONYM

OWNER                     OBJECT_NAME                  OBJECT_TYPE         STATUS
————————- —————————- ——————- ——-
PUBLIC                    SYS_EXPORT_SCHEMA_01         SYNONYM             VALID 

drop this SYNONYM manually,

DROP PUBLIC SYNONYM SYS_EXPORT_SCHEMA_01 ;

and try the export again.

 

Reason: Oracle will create a SYNONYM for datapump job when the job starts, once the job completes successfully the SYNONYM will be dropped as well, but if the job fails, the SYNONYM be dropped and you will get above error message.

 

Oracle REMAP_TABLE in data pump

I tried to use REMAP_TABLE in Oracle 10.2.0.5, but it doesn’t work at all. finally I found out there is a bug and this parameter doesn’t work, check here for detail,
http://yangtingkun.itpub.net/post/468/473037

To make sure that we can use this, we can also refer the step in this link,

http://yangtingkun.itpub.net/post/468/473311

 

SSIS Service default instance

you can find that default instance of SSIS here,

%ProgramFiles%\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

this file is XML format.

also make sure that your service account of your instance (the job in which the SSIS package is called) can access the default instance of the SSIS if your SSIS package is stored in MSDB.

 

Oracle 11g New Feature – password case sensitive

##Case Sensitive Passwords in Oracle Database 11g Release 1
Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases. The Database Configuration Assistant (DBCA) allows you to revert these settings back to the pre-11g functionality during database creation.

SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

–when case sensitive passwords are not enabled, the original case of the password is retained so it can be used if case sensitivity is subsequently enabled.

Physical Data Guard failover process

One of my application is oracle physical data guard, and server team found the memory on primary node is broken and BIOS is too old, so we have to replace RAM and update BIOS. Then the solution is to switch the standby database to primary and primary to standby – that means to switch the database role.

Today I finish this task, would like to record it since i don’t have enough RAM in my brain, I will forget the process.. 🙂

Step 1: Ask partner to shutdown application from their side, so no user is using the system when I’m switching the databases ;

Step 2: apply archive logs to standby database, make sure it’s syncing up with primary
archive log list
select max(sequence#) from v$log_history ;
recover standby database ;

Step 3: check both primary and standby database status, make sure they are ready for the switch ;
Select DATABASE_ROLE, SWITCHOVER_STATUS FROM V$DATABASE;
继续阅读 »

ORDER BY items must appear in the select list …

I got an error message on one of the sql,

orDER BY items must appear in the select list if Select DISTINCT is specified.

this database was upgraded from mssql2000 to mssql2005, but this sql works well on the old database, and also it works well on other mssql2005 pt, dev. so there must be something wrong with the prod database or db server or mssql2005 sw configuration.

I copied PD database to pt server and restored it there, the sql returned error too. so I believe it's about database.
I suddenly recalled that I changed the compatibility level on new sql2005 from “SQL Server 2000(80)” to “SQL Server 2005 (90)”, and once I changed it back, the sql runs fine.

Here is the steps how to change the compatibility level.
rigt click the database name > properties > Options > Compatibility Level > change it to “SQL Server 2000 (80)”

ORA-01861: literal does not match format string

Get following error when importing a schema to new database,

—————————–
orA-39083: Object type TABLE failed to create with error:
orA-01861: literal does not match format string
Failing sql is:
Create TABLE “BLOG”.”MT_AUTHOR” (“AUTHOR_ID” NUMBER(*,0) NOT NULL ENABLE, “AUTHO
R_API_PASSWORD” NVARCHAR2(60), “AUTHOR_CAN_Create_BLOG” NUMBER(1,0), “AUTHOR_CAN
_VIEW_LOG” NUMBER(1,0), “AUTHOR_EMAIL” NVARCHAR2(75), “AUTHOR_ENTRY_PREFS” NVARC
HAR2(255), “AUTHOR_EXTERNAL_ID” NVARCHAR2(255), “AUTHOR_HINT” NVARCHAR2(75), “AU
THOR_IS_SUPERUSER” NUMBER(1,0), “AUTHOR_NAME” NVARCHAR2(255) NOT NUL
—————————–
there is a column in this database defined like this,

“LOG_MODIFIED_ON” DATE DEFAULT ‘20000101000000’ NOT NULL ENABLE,  

and oracle thinks ‘20000101000000’  is not formated right, so it gives ora-01861 error.

Here is my troubleshooting steps, 继续阅读 »

终于好了,放心了

终于好了,放心了

不要生病了,快点好起来

你都发烧39.8C了,全身滚热,我好担心,好害怕。
不要生病了,快点好起来吧。