I'm going to migrate my current db 9.2.0.8 (Timezone version is 1!) to a new server 11.2.0.3, so I was wondering if I must upgrade timezone version of my current 9iR2 to version 4! Before upgrade it...Btw my new server has timezone v14.
View 4 RepliesHow to change db timezone parameter. My application team has asked me to change the db timzone parameter by following sql.
ALTER database SET TIME_ZONE = '+10:00';
Is this way is the correct one , and do we have any impact on database and does it require a db bounce.
I need the timezone of the server that the DB sits on. I don't want the offset. I need the region name. This is because some regions use daylight savings time and some do not. Plus an SA can change the time of the OS. Another company manages our servers and DBs in production and we don't have contact with them. We could use a lookup talbe and just populate it, then update it when we find out what it is in production. The problem is that I have seen cases where the timezone on servers change. Considering the lack of contact between the teams, we really need a reliable way to get the timezone out of the DB.
We tried several ways. My list is below and I explain why this is not working.
examples:
sessiontimezone: this is the timezone of my server. In theory it should be the same as the DB. We cannot take the risk that this will be out of sync. dbtimezone: This gives the offset. Such as -5:00 for US EST. There are multiple regions that have this. Some do not use daylight savings time and some do. We would need America/New York instead.
sessiontimezone gives the timezone setting for the client. This can be altered.
dbtimezone just gives the offset such as -5:00
We get data feeds from different parts of the world. We get some data based data that is local to that regions timezone. We need to partition on this field. So we need to add a field to the DB and normalize it to the time local to our DB Server. So if we get a record from california and the DB is on a server in US EAST, we add 3 hours. The offset won't work...
1. a timezone that we are getting from may not be in daylight savings time. We are partitioning by hour.
2. We would hit daylight savings time in New York before we hit it in California, so we would need to account for that in the math.
This hourly partition is a fixed and hard requirement. We need this to be absolutely accurate.
Here is what we tried:
What I want (pseudo-code): “Select XXX as timezone_region_name” to return “America/New_York” or “UTC”. It may be that the timezone was not set for the database at install time, and if it were, these queuries would work.
-- FAILED
SELECT DBTIMEZONE FROM dual;
--FAILED
select systimestamp, to_char(systimestamp, 'TZR'), extract (timezone_region from systimestamp) from dual;
--FAILED
SELECT systimestamp
AT TIME ZONE DBTIMEZONE "DB Time"
FROM DUAL;
--FAILED
select to_char(systimestamp, 'TZR') from dual;
When I try to store the xml as clob with element:
<revisedDate version="1">2013-06-10T11:00:00+11:00</revisedDate>
It is throwing �ORA-01830: date format picture ends before converting entire input string�
But when I insert with
<revisedDate version="1">2013-06-10T11:00:00+05:30</revisedDate>,
it is getting inserted successfully.
PS: my db timezone is +00:00
If I run the following query it's executing fine.
SELECT CAST(( FROM_TZ(CAST(SYSDATE AS TIMESTAMP),'CST') AT TIME ZONE ('Japan'))
AS DATE) FROM DUAL;
If I run the following query,I am getting the error.
ORA-01882: timezone region not found
SELECT CAST(( FROM_TZ(CAST(SYSDATE AS TIMESTAMP),'LMT') AT TIME ZONE ('philippines'))
AS DATE) FROM DUAL;
What is the timezone for Philippines.
I have an interesting problem. I have a server in the UK and I have three databases globally - UK (FINUKQ1), NY (FINNYQ1) and TK (FINTKQ1). The NY and TK databases will be migrated to the UK server as part of a consolidation project. Obviously, Time Zones and DST are the biggest concern for me.Here was my plan for the implementation (concentrating on Time Zone/DST issues).
Added to .profile:
--------------------
if [[ "$ORACLE_SID" = "FINTKQ1" ]]; then
export TZ="Asia/Tokyo"
elif [[ "$ORACLE_SID" = "FINNYQ1" ]]; then
export TZ="America/New_York"
else
unset TZ
fi
echo "TZ=$TZ"
Once environment has been set start database and listener.
Change database TZ:
-----------------
alter database set time_zone='Asia/Tokyo';
alter database set time_zone='America/New_York';
Check database TZ:
select dbtimezone from dual;
Add trigger:
------------
CREATE OR replace TRIGGER sys.timezone_check
AFTER startup ON DATABASE
DECLARE
[code]...
The trigger was there to put the database in restricted session if it is started in the wrong TZ and an error written to the alert log. The check was done on the dbtimezone and the dbname. Quite simple. Only issue is that it doesn't work as I wanted as dbtimezone and TZ are separate and disparate entities. i.e. I can start the TK database up in UK time (which will be incorrect for this project), and check the dbtimezone and it will show as 'Asia/Tokyo'.
Is there a better way of checking? Any trigger like this to compare TZs etc?
I am using the below query to get time stamp along with time zone format.
>>SELECT CURRENT_TIMESTAMP FROM DUAL
Now the requirement i got is i need to get convert the format in DD-MON-YY, HH:MM:SS, Timezone (GMT or PST)
how to convert this format. Or is there any alternative time stamp option to do conversion.
I've written this code to present the problem:
declare
region_date varchar2(100);
region varchar2(100);
offset_result varchar2(100);
begin
region_date := '2013-04-07 01:59 Australia/Sydney';
region := 'Australia/Sydney';
select
to_char(
[code]....
What I want to do is to use the variable "region" for the timezone specification as well,And I seem to be unable to, and don't understand why.....
why when I get an "ORA-00907: missing right parenthesis" with this value,Whereas, it works when I put "AT TIME ZONE 'Australia/Sydney'" there?
I have one issue My server is in france and it is in french timezone but when I query for sysdate it returns US time.
In '/etc/sysconfig/clock/'
Zone= europe/paris
UTC= true
echo $TZ variable is returning nothing.
sysdate = us time
systimestamp= us time
current_timestamp = french time
current_date = french time
dbtimezone= europe/warsove, sessiontimezone=+2.00( which is also europe timezone offset)
tz_offset(dbtimezone)=+2.00, tz_offset(sessiontimezone)= +2.00 i.e europe
os timezone= europe/paris.
This command "./emctl config agent getTZ" is also returning timezone as europe/paris
Also in "emd.properties" file "agentTZRegion" parameter is set to europe/paris
Oracle version= 11.2.0
Now I don't understand why this sysdate and systimestamp is returning "US time zone" while everything else is returning french time zone.
I'm in the process of testing a restore/recover from a simulated full system and media loss. A level 0 backup was taken from the server, and I'm trying to restore/recover to a point in time on a second server. I have created the database (with the same name) and have been able to successfully restore both the controlfile and spfile from the autobackup.
how does RMAN treat the "set until time"? The level 0 was taken on a database/server that is in CST while the database I'm trying to do a restore/ recover to is in EST. So when trying to do a point in time recovery, should I specify the time in EST or CST?
I'm planning to upgrade a small database (~150GB) from 10.2.0.3 on windows 2003 23bit to 11.2.0.3 RAC on Linux 5.8.The database contains oracle spatial too. A suitable method and link to document to be followed.
View 2 Replies View RelatedI recently performed an upgrade on a new server from oracle 10gr2 to oracle 11gr2 (11.2.0.3).
I take the rman backup from oracle 10g server and restore it on new server where I installed oracle 11gr2.
But on my previous oracle 10gr2 server I enabled the auditing. After doing successful upgrade now when I try to login with any user except sys I receive the following error:
SQL> conn scott/tiger
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "OBJ$EDITION": invalid identifier
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "OBJ$EDITION": invalid identifier
I got the workaround by setting the parameter audit_trail=FALSE (Previous value was DB_EXTENDED) .But I want my auditing to be enabled as per y requirements.
is the upgrade patch the same for standard edition and enterprise edition or there are two separate patches?
View 5 Replies View RelatedI have installed 11g ,i.e 11.0.6 then applied the 11.0.7 patch after that , when i tried to open the db in upgrade getting the error like
$ sqlplus '/as sysdba'
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 26 02:56:43 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
[code]...
We are using Oracle standard edition 10.2.0.2 on Solaris x86-64 platform, and I'm trying to estimate reasonability of movement to 10.2.0.4.A disadvantage I see is according to 10.2.0.2 "Availability and Known Issues" amount of "issues introduced" is about 10, but in 10.2.0.4 there are 63 "issues introduced", according to similar document. I suspect that some of bugs can be eliminated by applying 10.2.0.4.3 patchset, but I'm not sure that all of them will be eliminated. A benefit of 10.2.0.4 (and especially 10.2.0.4.3) is presence of all necessary critical patches (up to Jan2010).
View 4 Replies View Relatedi want to upgrade my database version is 10.2.0.1.0 to 10.2.0.4.0 steps and patch information on windows
View -1 Replies View Relatedi m planning upgrade my server from 10.2.0.1 to 11.2...we have license for oracle 10g standard edition.
tell me the procedure from licensing point.
We plan on using our current enterprise application, which is using 10.2.0.5.0 on the backend, until the end of 2012. Then we're going to switch over to a whole new system.
Our vendor is telling us that Oracle will stop issuing security patches and bug fixes for Oracle 10g at the end of 2011. Should we upgrade to 11g? If we do not upgrade to 11g, then we would be on 10g for about one year without these security patches and bug fixes. Should this be a concern?
I'm planning on upgrading our 10.2.0.4 database to 11.2.0.1 on the same server. Would like to hear the best efficient method to do so from the experts.. My first option is to use datapump method, so I can take a backup and uninstall 10g on the server, Install 11g and import the data. But as I was reading through the documents on the upgrade, I think it is best to use DBUA instead. I'm planning to follow this document to upgrade using DBUA. [URL]
Few doubts on this..
1. Since I will be upgrading the existing 10g database, I suppose I need not uninstall 10g prior to the upgrade. I read that DBUA will shutdown the database during the upgrade. Currently we have Oracle 10g software in c:oracleproduct10.2.0db_1 and the data files are in different disk drives and also in c:oracleoradata folder. So will DBUA move the physical datafiles to the new location?
2. Do I need to stop 10g listener prior to running DBUA?
3. Will I be able to uninstall 10g after the upgrade?
We have a 9.2.0.5 1.09TB Database running on windows 2000 Advanced server(32Bit) and we want to go to Windows 2008R2(64bit)on 11gR2(64bit).
View 3 Replies View Relatedi was created this same thread in Dba-village. i need to attache my log file
i want to upgrade my database 8i to 10g, for testing purpose i install 10g with started database and i created needed users and i import using dump
imp system/passwd file=dumpfile.dmp log=logfile.log rows=y ignore=y statistics=none full=y
the log file gave some errors and warnings i will attach my log file
Point for upgrading my 10G to 11G. It is a standalone DB on windows 2003. I have already install the 11G software on a different oracle_home. I need know to re-direct my datafiles to the new oracle_home.
View 1 Replies View RelatedOracle has changed the way to upgrade the software, i'm completely lost and angry i used to be able to just download the patch and install however the new patch that came out on the 13th of September is completely different, I just want to upgrade the current version of 11.2.0.1 to 11.2.2.0 somehow the software i got wont let me keeps saying the "software is on version 10.2.1. need to upgrade" how to upgrade to that? these are the files I have after the unzip of all 7 downloads
drwxr-xr-x 8 oracle oinstall 4096 Sep 7 01:10 database
drwxr-xr-x 8 oracle oinstall 4096 Sep 7 01:12 grid
drwxr-xr-x 6 oracle oinstall 4096 Sep 7 01:16 examples
drwxr-xr-x 6 oracle oinstall 4096 Sep 7 01:17 client
drwxr-xr-x 7 oracle oinstall 4096 Sep 7 01:19 gateways
drwxr-xr-x 20 oracle oinstall 4096 Sep 17 15:50 deinstall
[code]....
Order time is not properly displayed in the local timezone format and day light saving calculation is also another issue. Description:
We have web application & a centralized database where users can create orders globally. When orders are created time is saved in UTC format in the centralized global database.
somehow along with the UTC order time, timezone description is also being stored in one of the Database table as
UTC time as: '28-NOV-2010 11:24:54 PM'
Timezone description:
(GMT) Casablanca, Monrovia
(GMT) Greenwich Mean Time : Dublin, Edinburgh, Lisbon, London
(GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna
(GMT+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague
(GMT+01:00) Brussels, Copenhagen, Madrid, Paris
(GMT+01:00) Sarajevo, Skopje, Warsaw, Zagreb
(GMT+01:00) West Central Africa
(GMT+02:00) Athens, Beirut, Istanbul, Minsk
(GMT+02:00) Bucharest
(GMT+02:00) Cairo
(GMT+02:00) Harare, Pretoria
(GMT+02:00) Helsinki, Kyiv, Riga, Sofia, Tallin
.....
...
With this information how can I map timezone description to V_$TIMEZONE_NAMES. I read Oracle automatically converts to local timezones by considering day light saving timings when TZNAME is given as input with the datetime. by using above timezone descriptions how can I get TZNAMES which are in V_$TIMEZONE_NAMES.
I have a requirement in which a particular a timestamp column (date1) whose values are in CET timezone needs to be converted to EST and day light savings should be taken care of in the conversion logic. I should not use any ddl statements for altering the timezone and all.
View 9 Replies View RelatedI have a table with the following columns
EMPLOYEEIDNUMBER(12,0)
PUNCHDTM DATE
TIMEZONEIDNUMBER(12,0)
I want to return any results where any employee id that has 2 different timezoneid's on the same date. I would actually like, if its possible, to select these entries to display on one row per employee per day. So for example
EMPLOYEEID - PUNCHDTM - TIMEZONEID - PUNCHDTM - TIMEZONEID
12345 - 6/20/2012 5:00 am - 123 - 6/20/2012 10:00am - 456
To me who is newer with SQL this sounds like i would be 'joining' the table to itself so i've searched for that but not found what i need.
I tried updating the oracle database from version 11.2.0.1 to 11.2.0.3 but I have no clear procedure. I chose the option of the installer "upgrade database" and install the software on a new home. To migrate, must be all oracle services stopped? In the migration phase, it is normal to ask me listener data, etc .... or installer now detects the same database?
View 1 Replies View RelatedI tried to upgrade oracle 10.2.0.4 to 11.2.0.2 on the Solaris 10 server, but after 40 minutes it hangs bei oracle XML Database.
View 5 Replies View RelatedI have been trying to upgrade my personal laptop with 11G from 10G. Since I didn't had any important data, so i removed 10G and tried to go for a fresh install of 11G.
Below are the entries of my files:
Listener.ora
# listener.ora Network Configuration File: C:appNikproduct11.2.0dbhome_1NETWORKADMINlistener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL.WORLD)
(ORACLE_HOME = C:appNikproduct11.2.0dbhome_1)
[code]........
If i start the database in no mount it doesn't give any error. Once I try to mount it, it gives the following error:
SQL> alter database mount
2 ;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string C:APPNIKPRODUCT11.2.0DBHO
ME_1DATABASEORA_CONTROL1.CTL
, C:APPNIKPRODUCT11.2.0DB
HOME_1DATABASEORA_CONTROL2.C
TL
control_management_pack_access string DIAGNOSTIC+TUNING
Currently, We are running oracle 11.2.0.1 on Linux server. There are 32 DB instances on this server. As per requirement, we need to install 11.2.0.2 DB on the same server. Is it advisable to have both 11.2.0.1 and 11.2.0.2 databases on the same server with different ORACLE_HOME.
How about ORACLE_BASE?? OR, do we need to upgrade all the DB's from 11.2.0.1 to 11.2.0.2. During the upgrade process, if i need to take backup with exp/imp utility, what is the best way to take 32 DB's backups instead of taking each DB backup at a time?