moving to the web: how to prepare for the switch ari kaplan independent consultant, ioug 2000 -...
TRANSCRIPT
Moving to the Web: Moving to the Web: How to Prepare for How to Prepare for
the Switchthe SwitchAri KaplanAri Kaplan
Independent Consultant, www.arikaplan.comIndependent Consultant, www.arikaplan.com
IOUG 2000 - Anaheim, CAIOUG 2000 - Anaheim, CA
Topic OverviewArchitecture:
Scaleable, Reliable, Recoverable
Hardware Component Overview
Maintaining a 24x7 Database:
Three Backup Methods
Automated Standby with Advanced
Replication
Parallel Server
ALTER SYSTEM
Additional Oracle8i Features for the Internet
Topic OverviewTechnology Options:
WebDB / NCA / PL/SQL + HTML
Ecommerce
Internet Terminology
Oracle Terminology
Management:
Personnel Issues
How to Send E-mail and PagesAutomatically in UNIX
Other Issues
Scaleable, Reliable, RecoverableScaleable, Reliable, Recoverable Scalability:
–Hardware–Software–Modular Design
Reliability:
–Minimize Downtime–Performance Goals
Recoverability:
–Test Database Backups–Code and Content Recoverability
Hardware Component OverviewHardware Component Overview
Proxy Server
Database Servers
Disk Arrays
Backup Devices
Application Servers
LAN/WAN
Web Servers
Firewall
Web Users Telnet Users
Maintaining a 24x7 Database:Maintaining a 24x7 Database:Three Backup MethodsThree Backup Methods
Method 1: Cold Backup Definition Archivelog Mode / Point-in-Time Shutting Down the Database
Issues
Maintaining a 24x7 Database:Maintaining a 24x7 Database:Three Backup MethodsThree Backup Methods
Method 2: Hot Backup Definition CommandsALTER TABLESPACEALTER TABLESPACE INDEX_TS INDEX_TS BEGIN BACKUPBEGIN BACKUP;;
ALTER TABLESPACEALTER TABLESPACE INDEX_TS INDEX_TS END BACKUPEND BACKUP;;
ALTER DATABASE SWITCH LOGFILEALTER DATABASE SWITCH LOGFILE;;
Redo Activity / Performance
Maintaining a 24x7 Database:Maintaining a 24x7 Database:Three Backup MethodsThree Backup Methods
Method 3: Export (logical backup)++ Recover individual tables Recover individual tables
Example: if a user accidentally drops the CUSTOMER table in the database:Example: if a user accidentally drops the CUSTOMER table in the database:
Without Export:Without Export:
Recover the database to the point-in-time before the CUSTOMER table was dropped.Recover the database to the point-in-time before the CUSTOMER table was dropped.
1) Determine when the table was dropped.1) Determine when the table was dropped.
2) Recover the entire database or tablespace from your hot/cold backup and apply redo logs until 2) Recover the entire database or tablespace from your hot/cold backup and apply redo logs until just before the time of the DROP command.just before the time of the DROP command.
3) If you dependent data in other tables, bring those tables to the same point-in-time consistency.3) If you dependent data in other tables, bring those tables to the same point-in-time consistency.
With Export:With Export:
Use the import facility to recreate the table. It would contain data up to the point that the export was Use the import facility to recreate the table. It would contain data up to the point that the export was made. This is an easy method that is straightforward and would not bring the entire tablespace made. This is an easy method that is straightforward and would not bring the entire tablespace (or database) to the same point-in-time.(or database) to the same point-in-time.
Maintaining a 24x7 Database:Maintaining a 24x7 Database:Three Backup MethodsThree Backup Methods
Method 3: Export (logical backup)++ Recover individual tables Recover individual tables
++ Can generate DDL commands Can generate DDL commands
++ Can recreate just indexes, triggers, grants, etc. (no data) Can recreate just indexes, triggers, grants, etc. (no data)
++ Can copy tables among schemas, and users Can copy tables among schemas, and users
++ Can be used to migrate database among operating systems Can be used to migrate database among operating systems
-- Cannot recover the database or tablespace to a point-in- Cannot recover the database or tablespace to a point-in-timetime
-- “Incremental” exports inefficient (the full 100 million record “Incremental” exports inefficient (the full 100 million record
table is exported if just 1 record has changed.)table is exported if just 1 record has changed.)
Automated Standby Databases Automated Standby Databases with Advanced Replicationwith Advanced Replication
Automated Standby - why? How does it work?
– ““ALTER DATABASE MOUNT STANDBY” to get ALTER DATABASE MOUNT STANDBY” to get the standby database into the standby database into Standby Standby mode.mode.
– Sending of redo logsSending of redo logs When does it not work?
Automated Standby Databases Automated Standby Databases with Advanced Replicationwith Advanced Replication
Advanced Replication: what is it?– Normal Replication: snapshots– Multimaster Replication
Downsides to Advanced Replication:– Scalability– Primary Key definitions
Oracle Parallel ServerOracle Parallel Server
OPS (Oracle Parallel Server):
SERVER B 4 CPUs Instance B
SERVER A 4 CPUs Instance A
Shared / Networked Datafile Disks
Oracle Parallel ServerOracle Parallel Server Downsides:Downsides:
– Hardware costsHardware costs
– Update Overhead: IDLMUpdate Overhead: IDLM
– INIT.ORA setupINIT.ORA setup
– Rollback SegmentsRollback Segments
– BackupsBackups
Oracle8i improvements:Oracle8i improvements:
– Cache FusionCache Fusion
ALTER SYSTEMALTER SYSTEM
ALTER SYSTEM SET max_dump_file_size = max_dump_file_size = 100000;100000;
Oracle8i updates for this command Change the initSID.ora file
The ALTER SYSTEM command can improve uptime
SELECTSELECT NAME, VALUE NAME, VALUEFROMFROM v$parameter v$parameter
WHEREWHERE issys_modifiable IN issys_modifiable IN (’DEFERRED’, ‘IMMEDIATE’);(’DEFERRED’, ‘IMMEDIATE’);
ALTER SYSTEMALTER SYSTEMSELECTSELECT NAME, VALUE NAME, VALUEFROMFROM v$parameter v$parameter
WHEREWHERE issys_modifiable IN (’DEFERRED’, ‘IMMEDIATE’); issys_modifiable IN (’DEFERRED’, ‘IMMEDIATE’);
NAMENAME VALUEVALUE ISSYS_MODISSYS_MOD
timed_statisticstimed_statistics FALSEFALSE IMMEDIATEIMMEDIATE
log_checkpoint_intervallog_checkpoint_interval 50000005000000 IMMEDIATEIMMEDIATE
log_checkpoint_timeoutlog_checkpoint_timeout 2160021600 IMMEDIATEIMMEDIATE
object_cache_optimal_sizeobject_cache_optimal_size 102400102400 DEFERREDDEFERRED
background_dump_destbackground_dump_dest /u02/oracle/PHIS/bdump/u02/oracle/PHIS/bdump IMMEDIATEIMMEDIATE
sort_area_sizesort_area_size 50960005096000 DEFERREDDEFERRED
Additional Oracle8i Features for Additional Oracle8i Features for the Internetthe Internet
Oracle8i brings many new features that greatly improve the performance Oracle8i brings many new features that greatly improve the performance and uptime of an Internet-based database.and uptime of an Internet-based database.
The export and import facilities now can remember cost-based optimizer The export and import facilities now can remember cost-based optimizer statistics so that you do not need to regenerate them after an import.statistics so that you do not need to regenerate them after an import.
Online Index Rebuild (improves the 24x7 uptime) Online Index Rebuild (improves the 24x7 uptime)
ALTER TABLE DROP column_name;ALTER TABLE DROP column_name;
Fast Start RecoveryFast Start Recovery
Log Miner toolLog Miner tool
Statistics: ANALYZE TABLE in parallelStatistics: ANALYZE TABLE in parallel
Mentioned elsewhere:Mentioned elsewhere:
Multiple Archivelog DestinationsMultiple Archivelog Destinations
Expanded “ALTER SYSTEM” Initialization Parameter ChangesExpanded “ALTER SYSTEM” Initialization Parameter Changes
Automated Standby DatabaseAutomated Standby Database
Advanced ReplicationAdvanced Replication
Cache Fusion for Oracle Parallel ServerCache Fusion for Oracle Parallel Server
Technology Options: WebDBTechnology Options: WebDB
Administration Screen
Browse
Build
SitesBuild and monitor a web page wholly
independent of a database
Monitor end user and database activity
Browse database by schema, object_type, or name
Monitor
Administer
Build user interface components and database objects
Administer privileges and listener settings
Technology Options: WebDBTechnology Options: WebDB
Sample Query Screen (from Oracle Demo)
Technology OptionsTechnology Options: : PL/SQL PL/SQL Interfacing with HTMLInterfacing with HTML
UTL_HTTPUTL_HTTP package retrieves data from Web package retrieves data from Web servers through HTTP.servers through HTTP.
UTL_HTTP.REQUESTUTL_HTTP.REQUEST: receives the first 4000 : receives the first 4000 bytes of data from a URLbytes of data from a URL
UTL_HTTP.REQUEST_PIECESUTL_HTTP.REQUEST_PIECES: receives a : receives a table of 4000-byte pieces of data from a table of 4000-byte pieces of data from a URL.URL.
The The UTL_HTTP UTL_HTTP package is created by package is created by $ORACLE_HOME/rdbms/admin/utlhttp.sql$ORACLE_HOME/rdbms/admin/utlhttp.sql
Technology Options: Technology Options: ECommerce
B-to-C B-to-B EDI Some Web Sites:
–www.ecash.comwww.ecash.com–www.microsoft.comwww.microsoft.com–www.cybercharge.comwww.cybercharge.com–www.cybercheck.comwww.cybercheck.com–www.ecommerce.comwww.ecommerce.com
Technology Options: Technology Options: Internet Internet TerminologyTerminology
XML: eXtensible Markup Language JAVA: Cross-Platform / JDBC / SQLJ CORBA: CCommon ommon OObject bject RRequest equest BBroker roker
AArchitecturerchitecture ASP: Active Server Page Can be written in VBScript, Jscript, and ActiveX Can be written in VBScript, Jscript, and ActiveX
Data Objects (ADO)Data Objects (ADO)
Technology Options: Oracle Technology Options: Oracle TerminologyTerminology
NC: Network ComputerNCA: Network Computing
Architecture
iFS: internet File System
Management: Personnel IssuesManagement: Personnel Issues
Staff Skills IssuesStaff Skills Issues Full-time vs. ConsultantsFull-time vs. Consultants Staff Support IssuesStaff Support Issues
Management: How to Send E-mail and Management: How to Send E-mail and Pages Automatically in UNIXPages Automatically in UNIX
echo "echo "Subject: Database $ORACLE_SID is down on $HOSTNAME at Subject: Database $ORACLE_SID is down on $HOSTNAME at `date`"`date`" > $EMAIL_FILE > $EMAIL_FILEecho "echo "== db_main.sh: Sending an e-mail that database is == db_main.sh: Sending an e-mail that database is down!down!" >> $EMAIL_FILE" >> $EMAIL_FILEcat $EMAIL_FILE | mail [email protected] cat $EMAIL_FILE | mail [email protected] [email protected] [email protected]@host.com [email protected] $EMAIL_FILErm $EMAIL_FILEecho "echo "== db_mail.sh: E-mail sent== db_mail.sh: E-mail sent""
You should be sure that a mail messaging system
is installed. (“Sendmail”)
Management: How to Send E-mail and Management: How to Send E-mail and Pages Automatically in UNIXPages Automatically in UNIX
You can also send an email using Oracle Applications, by using “Alert Manager”
Use a third-party company:Use a third-party company:
httphttp://://wwwwww..notepagernotepager.net/.net/webgatewebgate..htmhtm
httphttp://://wwwwww..sharpcom.com/pageme/index.htmsharpcom.com/pageme/index.htm
http://www.beepit.comhttp://www.beepit.com
Send pages to groupsSend pages to groups
Automated scripts / alert_log checkAutomated scripts / alert_log check
Management: Register the NameManagement: Register the Name What’s a domain / URL?What’s a domain / URL?
Steps:Steps:
1)1) Check if the name exists Check if the name exists
2)2) Purchase the name: Purchase the name:
www.networksolutions.comwww.networksolutions.com
www.mydomain.comwww.mydomain.com
Cost: roughly $75 for purchase, $35 for annual upkeep.Cost: roughly $75 for purchase, $35 for annual upkeep.
3)3) Check “.com”, “.net”, and “.edu” domains Check “.com”, “.net”, and “.edu” domains
4)4) Add “*sucks” and register those domains as well. For Add “*sucks” and register those domains as well. For example, example, wwwwww..microsoftsucksmicrosoftsucks..comcom
Management: Vendor SupportManagement: Vendor Support
Vendor SupportVendor Support Ensure supply chain and responsivenessEnsure supply chain and responsiveness
24 X 7 Support24 X 7 Support Keep CSI numbers Keep CSI numbers
Have DBAs share TARsHave DBAs share TARs
Where to Now?Where to Now? There are many discussion Newsgroups on the internet for you to give There are many discussion Newsgroups on the internet for you to give
questions and get answers:questions and get answers:
comp.databases.oracle.servercomp.databases.oracle.server
comp.databases.oracle.toolscomp.databases.oracle.tools
comp.databases.oracle.misccomp.databases.oracle.misc
These can be accessed through a newsgroup program or “These can be accessed through a newsgroup program or “www.deja.comwww.deja.com””
Ari’s free Oracle Tips web page at:Ari’s free Oracle Tips web page at:
There are over 360 tips and answers to questions that have been posed to There are over 360 tips and answers to questions that have been posed to me over the years. This paper will be downloadable from the web page as me over the years. This paper will be downloadable from the web page as well.well.
Other good sites with links: Other good sites with links: www.orafaq.org, www.orafans.com, www.orafaq.org, www.orafans.com, www.ioug.org, www.orasearch.com, www.revealnet.com, www.ioug.org, www.orasearch.com, www.revealnet.com, www.lazydba.com, www.dbdomain.comwww.lazydba.com, www.dbdomain.com