作者存档: Pazel - 第2页

好累

最近老是觉得好累啊,腰板直不起来, 干什么都没有精神。

看到网上有人说,不能让生活把你累垮了,要学会驾驭它。而对于我,感觉生活驾驭了我。

我想把自己从家务中解脱出来,特别是做饭;

我想每年去一个向往的地方;

我想每天能有时间学习英语和业务知识;

我想热爱生活,每天都开开心心,而不是每天都在抱怨周围;

好想快乐起来,但是就是快乐不起来。

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.

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.