intro to oracle
Post on 02-Jun-2018
233 Views
Preview:
TRANSCRIPT
-
8/11/2019 Intro to Oracle
1/91
Introduction to Oracle
Prepared by: Shayne Capo
Senior Database AdministratorOpera Global Technical Services
Opera Global Technical Services
-
8/11/2019 Intro to Oracle
2/91
Opera Global Technical Services
Oracle
Architectural
Components
-
8/11/2019 Intro to Oracle
3/91
Opera Global Technical Services
Architectural Components
There are several files, processes and memorystructures in an Oracle Server. Some of them areused when processing a SQL statement (ormanipulating an application like Opera).
Others are used to improve the performance of the
database, ensure that the database can be recoveredin the event of a software or hardware error, orperform other tasks necessary to maintain thedatabase.
The Oracle server consists of:
Oracle InstanceOracle database
-
8/11/2019 Intro to Oracle
4/91
Opera Global Technical Services
Oracle Server
-
8/11/2019 Intro to Oracle
5/91
Opera Global Technical Services
Oracle instance
A combination of background processes and memorystructures that access an Oracle database
Instance must be started before accessing thedatabase
Every time started, a System Global Area (SGA) is
allocated and Oracle background processes arestarted
Background processes perform functions on behalf ofthe invoking process
They consolidate functions that would otherwise behandled by multiple Oracle programs running foreach user
Always opens one and only one database
-
8/11/2019 Intro to Oracle
6/91
Opera Global Technical Services
Oracle Instance
-
8/11/2019 Intro to Oracle
7/91
Opera Global Technical Services
Oracle database service
-
8/11/2019 Intro to Oracle
8/91
Opera Global Technical Services
Oracle Database
The physical structure consists of 3 file types + otherfile structures
-
8/11/2019 Intro to Oracle
9/91
Opera Global Technical Services
-
8/11/2019 Intro to Oracle
10/91
Opera Global Technical Services
Overview of Primary Components
System Global Area (SGA) Shared Pool
Database Buffer Cache
Redo Log Buffer Cache
Large Pool Background Processes
Program Global Area (PGA)
-
8/11/2019 Intro to Oracle
11/91
Opera Global Technical Services
System Global Area (SGA)
The SGA consists of several memory structuresShared pool
Database buffer cache
Redo log buffer
Other structures (e.g. locklatch management,statistical data)
There are two optional memory structures that can
be configured within the SGALarge Pool
Java pool
-
8/11/2019 Intro to Oracle
12/91
-
8/11/2019 Intro to Oracle
13/91
-
8/11/2019 Intro to Oracle
14/91
Opera Global Technical Services
Large Pool
Optional area of memory in the SGA configured onlyin a shared server environment
LARGE_POOL_SIZE
Services the parsing requirements for Javacommands
Required when installing and using Java (the Opera
application does) JAVA_POOL_SIZE
Java Pool
-
8/11/2019 Intro to Oracle
15/91
Opera Global Technical Services
Redo Log Buffer Cache
Records all changes made to the database data blocks Primary purpose is recovery Changes recorded within are called redo entries Redo entries contain information to reconstruct or redo changes
In Oracle Database 10g, Automatic Shared MemoryManagement (ASMM) feature is introduced to automaticallydetermine the size of Database buffer cache (default pool),
Shared pool, Large pool and Java pool by setting the parameterSGA_TARGET.
SGA_TARGET
-
8/11/2019 Intro to Oracle
16/91
Opera Global Technical Services
Overview of Primary Components
-
8/11/2019 Intro to Oracle
17/91
Opera Global Technical Services
Background Processes
The relationship between the physical andmemory structures is maintained andenforced by Oracles background processes
Mandatory processes:
DBWn PMON CKPTLGWR SMONOptional processes:
ARCn Dnnn SnnnPnnn LCKn QMNnLMON LMDn RECO
-
8/11/2019 Intro to Oracle
18/91
Opera Global Technical Services
Database Writer (DBWn)
DBWn writes when: Checkpoint occurs Dirty buffers reach
threshold There are no more free
buffers Tablespace OFFLINE,
READ ONLY, BEGINBACKUP
Table DROP, TRUNCATE Timeout
-
8/11/2019 Intro to Oracle
19/91
Opera Global Technical Services
Log Writer (LGWR)
LGWR writes:
At Commit
When 1/3 of the LogBuffer is full
Before DBWn writes tothe datafiles
When 1MB of redo isgenerated
Every 3 seconds
-
8/11/2019 Intro to Oracle
20/91
Opera Global Technical Services
System Monitor (SMON)
Responsible for:
Instance recovery
Rolling forward
Opening the database
Rolling back
Coalescing free space
Deallocating temporary
segments
-
8/11/2019 Intro to Oracle
21/91
-
8/11/2019 Intro to Oracle
22/91
Opera Global Technical Services
Checkpoint (CKPT)
Responsible for: Signaling DBWn at
checkpoints
Updating datafile
headers Updating control files
Reducing time torecover
Ensure committed datais written to disk
-
8/11/2019 Intro to Oracle
23/91
Opera Global Technical Services
Archiver (ARCn)
Responsible for:
Optional backgroundprocess
Automatically archives
online redo logs whenrunning in ARCHIVELOGmode
-
8/11/2019 Intro to Oracle
24/91
-
8/11/2019 Intro to Oracle
25/91
Opera Global Technical Services
Oracle
Storage
Structures
-
8/11/2019 Intro to Oracle
26/91
Opera Global Technical Services
Physical vs. Logical
-
8/11/2019 Intro to Oracle
27/91
Opera Global Technical Services
Datafiles
A datafile is a physical structure Can hold data for only one tablespace
Can resize dynamically
Space allocated upon creation
Opera database datafiles:System01.dbf, sysaux01.dbf, tempseg01.dbf,
undotbs01.dbf, findata01.dbf, finindx01.dbf,logdata01.dbf, logindx01.dbf, namedata01.dbf,nameindx01.dbf, opera_data01.dbf,opera_indx01.dbf, oxi_data01.dbf, oxi_indx01.dbf,quickdata01.dbf, quickindx01.dbf, ratedata01.dbf,rateindx01.dbf, resvdata01.dbf, resvindx01.dbf,tools01.dbf
-
8/11/2019 Intro to Oracle
28/91
Opera Global Technical Services
Tablespaces
Largest logical unit
Can reside in one or more datafiles
May contain one or more segments
Can be taken offline
Can be made readonly (SYSREAD)
OPERA database tablespaces:
system, sysaux, tempseg, undotbs, findata,
finindx, logdata, logindx, namedata, nameindx,opera_data, opera_indx, oxi_data, oxi_indx,quickdata, quickindx, ratedata, rateindx, resvdata,resvindx, tools
-
8/11/2019 Intro to Oracle
29/91
Opera Global Technical Services
Logical Overview
SEGMENTS Second largest logical unit Can belong to only one tablespace, but can reside in
multiple datafiles Is made up of one or more extents
EXTENTS Third largest logical unit Can belong to only one segment and cannot spawn datafiles Is made up of contiguous Oracle BlocksWhen segments grow, new extents are added
BLOCKS Smallest logical unit Can belong to only one extent Corresponds to one or more operating system blocks DB_BLOCK_SIZE=8m in OPERA
-
8/11/2019 Intro to Oracle
30/91
Opera Global Technical Services
Create
Database
-
8/11/2019 Intro to Oracle
31/91
Opera Global Technical Services
Create Database CommandCREATE DATABASE "opera"MAXINSTANCES 32MAXLOGHISTORY 1
MAXLOGFILES 192MAXLOGMEMBERS 3MAXDATAFILES 1024DATAFILE 'd:\oracle\oradata\OPERA\system01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10240K MAXSIZE
2048M EXTENT MANAGEMENT LOCALSYSAUX DATAFILE 'd:\oracle\oradata\OPERA\sysaux01.dbf' SIZE 120M AUTOEXTEND ON NEXT 10240K
MAXSIZE 2048MDEFAULT TEMPORARY TABLESPACE TEMPSEG TEMPFILE 'd:\oracle\oradata\OPERA\tempseg01.dbf' SIZE 20M
reuse AUTOEXTEND ON NEXT 640K MAXSIZE 2048M
UNDO TABLESPACE "UNDOTBS1" DATAFILE 'd:\oracle\oradata\OPERA\undotbs01.dbf' SIZE 200M reuseAUTOEXTEND ON NEXT 5120K MAXSIZE 2048MCHARACTER SET UTF8NATIONAL CHARACTER SET UTF8LOGFILEGROUP 1 ('d:\oracle\oradata\OPERA\redo01a.log','d:\oracle\oradata\OPERA\redo01b.log') SIZE 102400K,GROUP 2 ('d:\oracle\oradata\OPERA\redo02a.log','d:\oracle\oradata\OPERA\redo02b.log') SIZE 102400K,GROUP 3 ('d:\oracle\oradata\OPERA\redo03a.log','d:\oracle\oradata\OPERA\redo03b.log') SIZE 102400K,GROUP 4 ('d:\oracle\oradata\OPERA\redo04a.log','d:\oracle\oradata\OPERA\redo04b.log') SIZE 102400K,
GROUP 5 ('d:\oracle\oradata\OPERA\redo05a.log','d:\oracle\oradata\OPERA\redo05b.log') SIZE 102400K,GROUP 6 ('d:\oracle\oradata\OPERA\redo06a.log','d:\oracle\oradata\OPERA\redo06b.log') SIZE 102400K,GROUP 7 ('d:\oracle\oradata\OPERA\redo07a.log','d:\oracle\oradata\OPERA\redo07b.log') SIZE 102400K,GROUP 8 ('d:\oracle\oradata\OPERA\redo08a.log','d:\oracle\oradata\OPERA\redo08b.log') SIZE 102400K,GROUP 9 ('d:\oracle\oradata\OPERA\redo09a.log','d:\oracle\oradata\OPERA\redo09b.log') SIZE 102400K,GROUP 10 ('d:\oracle\oradata\OPERA\redo10a.log','d:\oracle\oradata\OPERA\redo10b.log') SIZE 102400K;
-
8/11/2019 Intro to Oracle
32/91
Opera Global Technical Services
Data
Dictionary
-
8/11/2019 Intro to Oracle
33/91
Opera Global Technical Services
Data Dictionary
Central to the database Describes the database and all its objects
Set of READ ONLY tables and views
Maintained by the Oracle Server
Stored in the SYSTEM tablespace
Owned by SYS
Modified by DDL statements
-
8/11/2019 Intro to Oracle
34/91
Opera Global Technical Services
Data Dictionary
Provides information about:Logical and physical structures
Definitions and space allocations of objects
Integrity constraints
Users, Roles and Privileges
Auditing information
-
8/11/2019 Intro to Oracle
35/91
-
8/11/2019 Intro to Oracle
36/91
Opera Global Technical Services
Data Dictionary Views
Three sets of views, each with a different scope
DBA, ALL, USER views can be accessed only whenthe database is up and running
Examples are: xxx_tables
xxx_users
xxx_database
-
8/11/2019 Intro to Oracle
37/91
Opera Global Technical Services
Dynamic Views
Virtual tables that gather current database activityfrom the last database startup database in mounted state Information accessed from memory and control files Synonyms begin with V$ Listed in V$FIXED_TABLE Examples are:V$SESSIONV$INSTANCE
V$SGAV$VERSION
-
8/11/2019 Intro to Oracle
38/91
Opera Global Technical Services
Control
File
-
8/11/2019 Intro to Oracle
39/91
Opera Global Technical Services
Control File
Small physical binary fileAutomatically created at
create database
Maintains integrity of
database Continuously updated
Contains databaseinformation
Belongs to only onedatabase
-
8/11/2019 Intro to Oracle
40/91
Opera Global Technical Services
Control File
A control file contains:
Database name
Tablespace names
Name and location of datafiles and redo log files
Current redo log sequence number
Checkpoint information
Begin and end of undo segment
Redo log archive information
Backup information (RMAN only)
-
8/11/2019 Intro to Oracle
41/91
Opera Global Technical Services
Control Files
initOPERA.ora
#################################File Configuration
################################control_files=("d:\oracle\oradata\opera\control01.ctl","g:\oracle\oradata\opera\control02.ctl")
-
8/11/2019 Intro to Oracle
42/91
Opera Global Technical Services
Create Controlfile SyntaxCREATE CONTROLFILE REUSE DATABASE "OPERA" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32MAXLOGMEMBERS 3MAXDATAFILES 200MAXINSTANCES 8MAXLOGHISTORY 292
LOGFILEGROUP 1 ('B:\ORACLE\ORADATA\OPERA\REDO01A.RDO', 'D:\ORACLE\ORADATA\OPERA\REDO01B.RDO' ) SIZE 100M,
.
.
GROUP 10 ('B:\ORACLE\ORADATA\OPERA\REDO10A.RDO', 'D:\ORACLE\ORADATA\OPERA\REDO10B.RDO' ) SIZE 100M-- STANDBY LOGFILEDATAFILE'B:\ORACLE\ORADATA\OPERA\SYSTEM01.DBF','B:\ORACLE\ORADATA\OPERA\UNDOTBS01.DBF','B:\ORACLE\ORADATA\OPERA\SYSAUX01.DBF','B:\ORACLE\ORADATA\OPERA\FINDATA01.DBF','B:\ORACLE\ORADATA\OPERA\FINDATA02.DBF',
'B:\ORACLE\ORADATA\OPERA\FININDX01.DBF','B:\ORACLE\ORADATA\OPERA\FININDX02.DBF',..
'B:\ORACLE\ORADATA\OPERA\OPERA_DATA08.DBF','B:\ORACLE\ORADATA\OPERA\LOGDATA04.DBF'CHARACTER SET UTF8;
-
8/11/2019 Intro to Oracle
43/91
Opera Global Technical Services
Control File
Information about the control file can be obtained byquerying the following:
V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION
-
8/11/2019 Intro to Oracle
44/91
Opera Global Technical Services
Redo Logs
-
8/11/2019 Intro to Oracle
45/91
Opera Global Technical Services
Redo Logs
Record all changes made to data Provides a means to recover the database
Can be organized into groups (multiplexing)
Minimum of two groups required
-
8/11/2019 Intro to Oracle
46/91
Opera Global Technical Services
How Redo Logs Work
Written in a cyclic fashionWhen one group is full LGWR moves to the next
group
Log switch and checkpoint occurs
-
8/11/2019 Intro to Oracle
47/91
Opera Global Technical Services
Redo Log Information
Information about the redo logs can be obtained byquerying the following:
V$LOG
V$LOGFILE
V$LOGHIST
V$BACKUP_REDOLOG
-
8/11/2019 Intro to Oracle
48/91
Opera Global Technical Services
Archived Redo Logs
Filled online redo logs can be archived The two advantages are:
A backup of the datafiles + redo logs + archivelogs can be used to restore the database up to the
last committed transaction.The backup can be made online.
When running in ARCHIVELOG mode a redo log filecannot be overwritten until
Checkpoint has completed
Redo Log has been archived
By default the database is created in NOARCHIVELOGmode
-
8/11/2019 Intro to Oracle
49/91
-
8/11/2019 Intro to Oracle
50/91
Opera Global Technical Services
Enable Archiving
Set initOPERA.ora archiving parametersMount the database in exclusive mode
D:\>sqlplus sys/opera10g as sysdba
SQL>Startup mount exclusive
Set the database in ARCHIVELOG mode
SQL>ALTER DATABASE ARCHIVELOG;
Startup the databaseSQL>ALTER DATABASE OPEN;
-
8/11/2019 Intro to Oracle
51/91
Opera Global Technical Services
InstanceManagement
-
8/11/2019 Intro to Oracle
52/91
-
8/11/2019 Intro to Oracle
53/91
Opera Global Technical Services
Initialization Files
Instance specific Instance parameters set explicit or implicit(default)
Database name
Memory allocation
Control file names
Undo Segment settings
pfile and spfile
Comment out parameters #
IFILE points to location of pfile
initOPERA.ora
-
8/11/2019 Intro to Oracle
54/91
Opera Global Technical Services
Database States
Startup / Open (Database open) Shutdown (Database closed)
Nomount (Instance started)
Mount (Control files opened)
-
8/11/2019 Intro to Oracle
55/91
-
8/11/2019 Intro to Oracle
56/91
Opera Global Technical Services
Alter Database Startup
ALTER DATABASE command:
ALTER DATABASE {MOUNT | OPEN}
ALTER DATABASE OPEN [READ WRITE | READ ONLY]
READ WRITE: (default) enables normal access
READ ONLY: no redo log will be generated
Database required to be in nomount or mount mode.
-
8/11/2019 Intro to Oracle
57/91
Opera Global Technical Services
Shutdown Command
Shutdown command:
SHUTDOWN [NORMAL | TRANSACTIONAL |
IMMEDIATE | ABORT]
-
8/11/2019 Intro to Oracle
58/91
Opera Global Technical Services
Monitor the Instance
alertSID.log file:Records results of major events
Very useful for diagnosing database errors
alertOPERA.log
Each entry has a timestamp
Located in BACKGROUND_DUMP_DEST
Trace files in USER_DUMP_DEST
-
8/11/2019 Intro to Oracle
59/91
-
8/11/2019 Intro to Oracle
60/91
Opera Global Technical Services
Tablespaces
AndDatafiles
-
8/11/2019 Intro to Oracle
61/91
Opera Global Technical Services
Tablespaces and Datafiles
All objects in the database are stored logically inTABLESPACES and physically in DATAFILES
TABLESPACES:- Belong to only one Database- Consist of one or more Datafiles- Are divided in Segments, Extents and Blocks
DATAFILES- Belong to only one Tablespace
- Physical files on the operating system
f bl
-
8/11/2019 Intro to Oracle
62/91
Opera Global Technical Services
Types of Tablespaces
SYSTEMCreated in create database
Contains the data dictionary
Contains the System Undo Segment
NON-SYSTEM
Eases space administration
Separate segments
Can aid in controlling user space quotas
-
8/11/2019 Intro to Oracle
63/91
Opera Global Technical Services
Space Management
Locally managed tablespace:Free extents managed in the tablespace
A bitmap records free extents
Dictionary-managed tablespace:
Free extents managed in the data dictionary
Extent allocation or deallocation triggers a DMLstatement on the data dictionary
L ll M d T bl
-
8/11/2019 Intro to Oracle
64/91
Opera Global Technical Services
Locally Managed Tablespaces
Reduced contention on the data dictionary No undo data generation when space allocation or
deallocation occurs No coalescing required UNIFORM sized extents are more reusable
CREATE TABLESPACE opera_data DATAFILEg:\oracle\oradata\opera\opera_data01.dbf SIZE512M AUTOEXTEND ON NEXT 100M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
-
8/11/2019 Intro to Oracle
65/91
Opera Global Technical Services
Dictionary Managed Tablespaces
Extents managed in the data dictionary Each segment may have a different storage clause
Coalescing required
CREATE TABLESPACE opera_data DATAFILEg:\oracle\oradata\opera\opera_data01.dbf SIZE512M EXTENT MANAGEMENT DICTIONARY DEFAULTSTORAGE (initial 1M NEXT 1M PCTINCREASE 0);
U d T bl
-
8/11/2019 Intro to Oracle
66/91
Opera Global Technical Services
Undo Tablespace
Store undo segments Cannot store any other objects
Extents are locally managed
CREATE UNDO TABLESPACE undotbs DATAFILEg:\oracle\oradata\opera\undotbs01.dbf SIZE 512M;
T T bl
-
8/11/2019 Intro to Oracle
67/91
Opera Global Technical Services
Temporary Tablespace
Used for sort operations
Cannot store any permanent objects Locally managed extents Tempfiles are always NOLOGGING
CREATE TEMPORARY TABLESPACE tempseg TEMPFILEg:\oracle\oradata\opera\temp01.dbf SIZE 512MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
Default temporary tablespace define at database creation. Can change the default temporary tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACEtempseg;
Cannot be taken offline or dropped until a new temporarytablespace is available
R d O l T bl
-
8/11/2019 Intro to Oracle
68/91
Opera Global Technical Services
Read Only Tablespace
The following command makes a tablespace readonly:
ALTER TABLESPACE sys_read READ ONLY;
This causes a checkpointData within the tablespace is available only for
SelectsObjects can be dropped
SYS_READ tablespace
R i i T bl
-
8/11/2019 Intro to Oracle
69/91
Opera Global Technical Services
Resizing Tablespaces
ALTER DATABASE ALTER TABLESPACE
Can change the size of a tablespace by:
Alter the size of the datafile
Alter the datafile to have AUTOEXTEND turned on
Add a datafile
ALTER DATABASE DATAFILEg:\oracle\oradata\opera\opera_data01.dbf RESIZE 4096m;
ALTER DATABASE DATAFILE
g:\oracle\oradata\opera\opera_data01.dbf AUTOEXTEND ONNEXT 100m MAXSIZE 4096m;
ALTER TABLESPACE opera_data ADD DATAFILEg:\oracle\oradata\opera\opera_data02.dbf size 2048m;
M i R i D t fil
-
8/11/2019 Intro to Oracle
70/91
Opera Global Technical Services
Moving or Renaming Datafiles
Shutdown the database Physically on the OS move the datafile
Startup mount the database
Execute: ALTER DATABASE RENAME FILE
g:\oracle\oradata\opera\opera_data01.dbf TOh:\oracle\oradata\opera_data01.dbf;
Open the database
T bl I f ti
-
8/11/2019 Intro to Oracle
71/91
Opera Global Technical Services
Tablespace Information
Information about tablespaces and datafiles can beobtained in the following views: TablespacesDBA_TABLESPACESV$TABLESPACE
DatafilesDBA_DATA_FILESV$DATAFILE
Temporary files
DBA_TEMP_FILESV$TEMPFILE
-
8/11/2019 Intro to Oracle
72/91
Opera Global Technical Services
32bit and 64bitArchitectures
32bit A hit t
-
8/11/2019 Intro to Oracle
73/91
Opera Global Technical Services
32bit Architecture
Windows Server 2003
32-bit x86 systems
Mainstream deployments where 64bit applications ordrivers are unavailable
Most common on servers with 1-4 processors
64bit A hit t
-
8/11/2019 Intro to Oracle
74/91
Opera Global Technical Services
64bit Architecture
Windows Server 2003 x64 EditionsGradually replacing 32bit windows as mainstream
offering
Can combine 32bit and 64bit software
Windows Server 2003 for Itanium-based Systems
Intended for the most demanding database andlarge deployments on 8-way+
Designed for pure 64bit software stacks
32bit s 64bit Memo Limits
-
8/11/2019 Intro to Oracle
75/91
Opera Global Technical Services
32bit vs. 64bit Memory Limits
Memory Model
-
8/11/2019 Intro to Oracle
76/91
Opera Global Technical Services
Memory Model
Process Memory restrictions32bit 4GB (Total user & kernel)
64bit 8TB
32bit Windows
All instances memory requirements must fit in the4GB limit
64bit Windows
A process has 8TB to play with
-
8/11/2019 Intro to Oracle
77/91
Opera Global Technical Services
4GB RAM Tuning
-
8/11/2019 Intro to Oracle
78/91
Opera Global Technical Services
4GB RAM Tuning
Increase addressable memory available to the Oracleprocess by adding the /3GB switch to the boot.ini file:
Reboot server to enable
Whats wrong with 32bit?
-
8/11/2019 Intro to Oracle
79/91
Opera Global Technical Services
Whats wrong with 32bit?
Nothing..but 32bits = 4GBper process
Use /3GB switch in boot.ini
Doing this restricts the OS
non-paged pool Certain OS pools get cut in
half
Why 64bit?
-
8/11/2019 Intro to Oracle
80/91
Opera Global Technical Services
Why 64bit?
Each threaddedicated user connection
All Oracle memory has to come out of the addressspace
Options on Windows
Windows ItaniumFirst 64bit database onWindows
Windows x64EM64T/Opteroncan recompile32bit to 64bit
-
8/11/2019 Intro to Oracle
81/91
Opera Global Technical Services
-
8/11/2019 Intro to Oracle
82/91
Opera Global Technical Services
OPERAInformation
OPERA Commands
-
8/11/2019 Intro to Oracle
83/91
Opera Global Technical Services
OPERA Commands
sqlplus opera/opera Select * from installed_app;
select license_code from installed_app_licenses;
ALTER USER user_name IDENTIFIED BY
new_password; DROP USER user_name [CASCADE];
OPERA File Locations
-
8/11/2019 Intro to Oracle
84/91
Opera Global Technical Services
OPERA File Locations
alertOPERA.logG:\oracle\admin\opera\bdump
Arvhive logsD:\oracle\admin\opera\archive
Control FilesD:\oracle\oradata\opera\control01.ctlG:\oracle\oradata\opera\control02.ctl
OPERA datafiles:\oracle\oradata\opera\.dbf
-
8/11/2019 Intro to Oracle
85/91
-
8/11/2019 Intro to Oracle
86/91
Opera Global Technical Services
Daylight
SavingsTime
DST Changes
-
8/11/2019 Intro to Oracle
87/91
Opera Global Technical Services
DST Changes
The Energy Policy Act of 2005 was signed into law inAugust 2005 to extend daylight saving time.
Beginning in 2007, daylight saving time in the U.S. willbegin on the second Sunday in March and end the firstSunday in November rather than beginning on the first
Sunday in April and ending the last Sunday in October, asit did in the past.
Under the new rules for 2007, DST will start on March 11,2007 end on November 04, 2007. As a result the database
may report incorrect time zone data between 03/11/200704/01/2007 and between 10/28/200711/04/2007 (andon different dates in subsequent years), unless therequired patches are applied.
What is the database timezone?
-
8/11/2019 Intro to Oracle
88/91
Opera Global Technical Services
What is the database timezone?
The database time zone is not as important as it sounds. First ofall it does not influence functions like sysdate, or systimestamp.These function take their contents (date and time, and in thecase of systimestamp also time zone) completely from the OSwithout any "Oracle" intervention.
The only function of the database time zone is that it functionsas a time zone in which the values of the "TIMESTAMP WITHLOCAL TIME ZONE" (TSLTZ) datatype are normalized when theyare stored in the database.
However, these values are always converted into the sessiontime zone on insert and retrieval, so the actual setting of thedatabase time zone is more or less immaterial.
DST Patch
-
8/11/2019 Intro to Oracle
89/91
Opera Global Technical Services
DST Patch
Who needs the DST patch?ONLY locations where the database is not in the
same time zone as the hotel would beaffected. Mostly larger sites will be impacted, like
Candlewood datacenter. All sites just need tomake sure they have the OS patches/updatesapplied.
The patch is applied to the database. It replaces twofiles and requires the database to be restarted.
The patch is included in the Micros 403 database CD.
-
8/11/2019 Intro to Oracle
90/91
-
8/11/2019 Intro to Oracle
91/91
Thank You
top related