分类存档: Useful Doc

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

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, 继续阅读 »

ORA-01035

One of my clients gets following error from a database,

orA-01035: only available to users with RESTRICTED SESSION privilege

I guess another DBA in NA was doing some migration for this DB, and set up this database into restricted mode by , here is the solution,

solution 1: alter system disable restricted session;
solution 2: shutdown database and startup again.

firewall exception for sqlserver connection

If you want to connect to a sqlserver database which is in a different domain or there is a firewall between client and server, then you need to read the detail below to request a firewall exception to allow the connection.

If you are using a default instance, for example in sql2k, the default connection string is the server name, then the default listening port of SQL server is TCP 1433, you can request to open the firewall exception – allow TCP 1433 from client to server. that will be enough.

If you are using a named instance, for example connectin string is like \, then two ports shouled be allowed from the client to server , they are TCP and UDP 1434. for example, if your connection string is mydbserver\sqlinstance1,2588 then you need to request TCP 2588 and UDP 1434.

***the end***

Relocate datafiles

One of my DB server drivers was not enough any more, and I had to use the index driver to store the table datafile, because the app was loading data to database and the required space was large. and now enough space is added to my table driver, so I want to relocate my files from index driver to table driver.

Here are my steps,

Step 1: list all the datafile,

col tablespace_name format a10
col file_name format a55
Select tablespace_name, file_name FROM dba_data_files

you can get the wrong file name if you have own rules to locate files.

Step 2: backup database,
exp, expdp, or RMAN, backup your database before any big change.

Step 3: shutdown database,
SET oracle_sid=testdb
sqlplus /nolog
conn / AS sysdba
shutdown IMMEDIATE;

Step 4: move datafile using brtools or copy using OS level

Step 5: startup mount database
startup MOUNT

Step 6: execute this command to change controlfile for new location of datafile
Alter DATABASE RENAME FILE 'old_file_name' TO 'new_file_name' ;

–note, full name here.

Step 7. start database
Alter DATABASE OPEN;

and then check dba_data_files to verify your datafile name.

Step 8: backup your database. – we backup database once any large change applied to database.

I also note that there is a way to rename file when the database open, the step should be,

Step 1: Alter TABLESPACE tbs_name OFFLINE NORMAL;
Step 2: Rename the datafiles using the operating system.
Step 3: rename datafile
Alter TABLESPACE tbs_name RENAME DATAFILE 'old_file_full_name' to 'new_file_full_name';
Step 4: Alter TABLESPACE tbs_name ONLINE ;
Step 5: backup your database. – we backup database once any large change applied to database.

ora-00376: FILE n can’t be read at this time

I was not lucky these days, got following error when trying to logon one of my database,

—————–
ora-00604: error occurred AT recursive SQL LEVEL 1
ora-00376: FILE 7 can’t be read at this time
ora-01110: data FILE 7: ‘G:\ORACLE\ORADATA\RPT03T\RPT03T_AUDIT_DATA01.DBF’
ora-02002: error WHILE writing TO AUDIT trail
ora-00604: error occurred AT recursive SQL LEVEL 1
ora-00376: FILE 7 can’t be read at this time
ora-01110: data FILE 7: ‘G:\ORACLE\ORADATA\RPT03T\RPT03T_AUDIT_DATA01.DBF’
—————–

and I suddenly recalled, I got an email from North Amrican counterpart DBA that driver G: on our database server was crashed last night. so I guess that’s the root cause, but right now I need to find a solution to fix it.

Here are my steps, just keep it for further reference,

Step 1.  check which files need recovery
Select * FROM v$recover_file;

select file#,status from v$datafile where stauts=’RECOVER’;

Step 2: My datafiles are still there, i don’t need to restore my datafile from TSM or other backup tools, i can just perform recover,

Alter DATABASE DATAFILE 7 OFFLINE;

recover   datafile   7;

Alter DATABASE DATAFILE 7 ONLINE;

the result likes,
—————————
SYS@rpt03t>Alter DATABASE DATAFILE 7 OFFLINE;

Database altered.

SYS@rpt03t>recover   datafile   7;
Media recovery complete.
SYS@rpt03t>Alter DATABASE DATAFILE 7 ONLINE;

Database altered.
—————————

and query v$recover_file again, you will find the datafile you recovered just now disappear in this dictionary. that means the datafile was recovered successfully.

ERROR:Property IsLocked is not available for Login

Error:

Property IsLocked is not available for Login ‘[login_name]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

Solution:
If that SQL Instance is defaulted with CHECK_POLICY & CHECK_EXPIRATION on login properties you wouldn’t be able to open using SSMS, to accomplish the task of changing default database for the logins firstly you need to run the following statement:

alter login [login_name] with password = ‘yourpwd’ unlock, check_policy = off, check_expiration = off

or
when you create user you need to uncheck <Enforce password policy>,<Enforce password expiration>.

MSSQL2K5 install WMI error2147746132(0x80040154)

I got follow error when installing MSSQL2K5 on windows2003 SP2,

the SQL server SYSTEM configuration checker cannot be executed due TO WMI configuration ON the machine DESHKSDOWA029 ERROR: 2147746132(0x80040154)

I troubleshoot this error for over 2 days, but no luck. and we consolut some Microsoft guy, this is a old issue with WMI, here is the root cause and solution,

CAUSE:
————————–
C:\Windows\Systeem32\WBEM folder was missing permission for “SystemName\Users” account
  
  
RESOLUTION :
————————–
Added “SystemName\Users” account under ‘C:\Windows\Systeem32\WBEM’ folder.
Provided  Provided “Read & Execute” permission on  it.
After this, we were able to complete the SQL Server installation.

and this is a very helpful solution, I tried a lot of ways to re-register the WMI classes or install WMI other conponent, but all were helpless. I’m happy I got this solution so I can’t wait to share this on my homepage. hope it can help you as well.