1

I have to change timezone for the particular Database(SID). Where I have DB Server is having multiple Database (SID) Configured and installed. When i have connected Particular SID and run below Query :

alter database set time_zone='-05:00'

I got below error: 

ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

But when i am running alter database set time_zone = 'EST'; query also it did not give error but

Note: I have multiple Database configured in same DB Server I need to change the timezone for particularly to one Database (SID). I cant change in system (OS) level and DB Server level globally.

i am not able to change time zone any one can help.

Gautam
  • 3,707
  • 5
  • 36
  • 57
  • Why do you want to change the Database time zone? This time zone has no influence on any client behavior. – Wernfried Domscheit Nov 16 '15 at 14:00
  • We need to enabled to QA to test application particular to some time zone from the application site. Our Test is particular the date store in DB. Other wise we need to change so many SQL with timezone conversion logic in the application layer. – Gautam Nov 16 '15 at 14:08
  • As I said, DBTIMEZONE has no influence to any application item. Check this documentation for more information: [Setting the Database Time Zon](http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#sthref532) – Wernfried Domscheit Nov 16 '15 at 14:17
  • Yes you are correct, I am afraid but my application testing was having specific database timezone application other wise i need to change the code query and redeploy the application, now It is worked as i have planned. T – Gautam Nov 17 '15 at 06:33

1 Answers1

1

I have done the following steps it worked for me : 

$ ps -ef|grep pmon

This will show list as below : 

ORADEV    7554     1  0 Oct28 ?        00:00:03 ora_pmon_MDEV230
ORADEV   20649 32630  0 03:39 pts/9    00:00:00 grep pmon
ORADEV   23386     1  0 Nov12 ?        00:00:00 ora_pmon_MQA230POC

I have added following entry in the oraenv fles as :

$ vi oraenv ( It will open file in Vi Editor) Added the following entry at end of the files: 

if [[ ${ORACLE_SID} = "MQA230POC" ]]; then
   TZ=EST+05EDT
   export TZ
   echo "Time Zone set to EST"
else
  TZ=PST+08EDT
   export TZ
   echo "Time Zone set to PST"
fi

if [[ ${ORACLE_SID} = "MQA230POC" ]]; then This line will is critical for selecting particular Database.

And run the following command and Test and Restart Database :

$ . oraenv ORACLE_SID = [MQA230POC] ? The Oracle base for ORACLE_HOME=/orasw/database12c/product/12.1.0.2/dbhome_1 is /orasw/database12c Time Zone set to EST

$ sqlplus sys as sysdba
Enter password:XXXXX ( provide password) 

It will give message as below : 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

--Run Below Command to Restart DB: 

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

It worked for me I am able to set different timezone for Different Database which i was seeking for. Hope it will help others.

Gautam
  • 3,707
  • 5
  • 36
  • 57
  • You do not change the Database time zone by that! You changed the time zone of database operating system (i.e. the environment where the DB runs on it). Have a look at this answer to see the difference: http://stackoverflow.com/questions/29271224/how-to-handle-day-light-saving-in-oracle-database/29272926#29272926 – Wernfried Domscheit Nov 17 '15 at 06:52