月度存档: 五月 2010

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.

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.