(The author is pressing in late October 2002, participating in the OCP database training, according to the actual experience in the attendance and recent years, especially in everyone's encouragement, to complete the reference for everyone in actual use --2003 / 1/1) Note: All examples in the following are based on Oracle8i for Win2k Server, may differ in different versions and operating systems. I. Oracle Database Basic Concept and Architecture 1. What is an Oracle Database, DBA? Oracle is a registered trademark of Oracle, USA, and its home product Oracle database is an RDBMS (relational database management system), and we usually say Oracle That is, an Oracle database-includes all physical data and a combination of objects such as physical, memory, and processes. DBA (Database Administrator) Database Administrators, mainly responsible for the design, maintenance, monitoring, management, backup, security, and developer coordination, etc., which is the core location of the data information management system. 2. Examples and Database Internal Structure Oracle Database consists of an instance and a database. Certains refer to Oracle's memory and background processes and some configuration files; ● SGA: System global zone. It is a group of memory structures, including buffer cache data cache, Redo log buffer redo log buffer, Shared Pool Shared Pool (Dictionary Cache Data Dictionary Cache, library cache shared SQL pool, user session user session), etc .; ● Background process : SMON (System Monitor Process), DBWR (Database Write Process), PMON (Process Monitor Process), CKPT (Checkpoint Process), LGWR (Log Sticker Process), etc .; database refers to data files, redo log files And control files; Second, Oracle Database Administration 1. Start and close Database 1 Start Database D: / ORANT8I / BIN> SVRMGRL ORAVLE MANAGER Release 3.1.7.0.0 - Production Copyright (C) 2000, Oracle Corporation. All Rights Reserved. Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production with The Partitioning Option Jserver Release 8.1.7.0.0 - Production SVRMGR> Connect Internal; Connection is successful. SVRMGR> Startup has launched an Oracle instance. The system global region has 121522204 bytes Fixed Size 75804 bytes Variable Size 57708544 bytes Database Buffers 63660032 bytes The 77824 bytes have been loaded into the database. The database has been opened. 3 steps to start: Nomount: Start Oracle Instance Mount: Install Database Open: Open Database SVRMGR> Startup Nomount The Oracle instance is launched. The system global region has 121522204 bytes Fixed Size 75804 bytes Variable Size 57708544 bytes Database Buffers 63660032 Bytes Redo Buffers 77824 bytes SVRMGR> ALTER DATABASE MOUNT; statement has been processed.
SVRMGR> ALTER DATABASE OPEN; statement has been processed. (Note: If you have multiple databases on the server, you should first perform SET ORACLE_SID = WWFDB before performing SVRMGRL. The database system ID to be operated in this example is WWFDB) 2 Turn off the database SVRMGR> Shutdown Close the database. The database has been removed. Oracle instance is closed. Turn off the four ways of the database Normal: Normally shut down the database. Do not force the user to disconnect, no new connections, but waiting for all connections until disconnect. Immediate: Disconnect all connections, roll back, do not allow new connections. Abort: Disconnect all connections immediately, terminate all transactions, and instance recovery is required when starting next time. After waiting for the transaction, it is disconnected. SVRMGR> Shutdown IMMEDIATE has turned off the database. The database has been removed. Oracle instance is closed. (Note: Do not use the Abort parameters, in fact it is close to the database server, suddenly power-down, the next time you turn it takes a long time to perform instance recovery.) 2. Database Manual creation and related parameters Configuration Although Oracle Manager is provided. ENTERPRISE MANAGER Such a graphics management tool can almost work in DBA, but when the database occurs or remotely managed, the manual command is indispensable, in addition, manual operation allows you to understand the mechanism of Oracle deeper. Manually create a database: 1 Create a parameter file; Oracle provides a parameter example file when installing, you can modify it as a template; (I am here in d: /orant8i/database/initwwfdb.ora)
# A actual initialization file # CopyRight (c) 1991, 2000 by oracle corporation # ############################################################################################################################################################################################################################################################################## ############################################################# ORA file # # This file is provided by Oracle Corporation to help you customize # your RDBMS installation for your site. Important system parameters # are discussed, and example settings given. # Some parameter settings are generic to any size installation. # for parameters that require different values in different size # installations, three scenarios have been provided:.. SMALL, MEDIUM # and LARGE Any parameter that needs to be tuned according to # installation size will have three settings, each one commented # according to installation size # # Use the following table to approximate the SGA size needed for the # three scenarious provided in this file: # # ------- Installation / Database size ------ # SMALL MEDIUM LARGE # Block 2K 4500K 6800K 17000K # Size 4K 5500K 8800K 21000k # # to set up a database That Multiple Instances Will B e using, place # all instance-specific parameters in one file, and then have all # of these files point to a master file using the IFILE command. # This way, when you change a public # parameter, it will automatically change on all instances. This is # necessary, since all instances must run with the same value for many # parameters. for example, if you choose to use private rollback segments, # these must be specified in different files, but since all gc_ * # parameters must be the same on all instances, they should be in one file # # INSTRUCTIONS:.. Edit this file and the other INIT files it calls for # your site, either by using the values provided here or by providing # your own Then place an IFILE =
line into each instance-specific # INIT file that points at this file # # NOTE:. Parameter values suggested in this file are based on conservative # estimates for computer memory availability You should adjust values upward # for modern machines # ###.. ######################################################################################################################################################################################################################################################################################################## ################### DB_NAME = "WWFDB" instance_name = wwfdb service_names = wwfdb db_files = 1024 # itial # db_files = 80 # small # db_files = 400 # medium # db_files = 1500 # large control_files = ("d: /wwfdb/control01.ctl", "d: /wwfdb/control02.ctl", "d: /wwfdb/control03.ctl") open_cursors = 300 max_enabled_roles = 30 db_file_multiblock_read_count = 8 # INITIAL # db_file_multiblock_read_count = 8 # SMALL # db_file_multiblock_read_count = 16 # MEDIUM # db_file_multiblock_read_count = 32 # LARGE db_block_buffers = 7771 # INITIAL # db_block_buffers = 100 # SMALL # db_block_buffers = 550 # MEDIUM # db_block_buffers = 3200 # LARGE shared_pool_size = 31457280 # Initial # shared_pool_siz e = 3500000 # SMALL # shared_pool_size = 5000000 # MEDIUM # shared_pool_size = 9000000 # LARGE large_pool_size = 614400 java_pool_size = 20971520 log_checkpoint_interval = 10000 log_checkpoint_timeout = 1800 processes = 150 # INITIAL # processes = 50 # SMALL # processes = 100 # MEDIUM # processes = 200 # LARGE parallel_max_servers = 5 # SMALL # parallel_max_servers = 4 x (number of CPUs) # MEDIUM # parallel_max_servers = 4 x (number of CPUs) # LARGE log_buffer = 32768 # INITIAL # log_buffer = 32768 # SMALL # log_buffer = 32768 # MEDIUM # log_buffer =
163840 # LARGE #audit_trail = true # if you want auditing timed_statistics = true # if you want timed statistics max_dump_file_size = 10240 # limit trace file size to 5M each # Uncommenting the line below will cause automatic archiving if archiving has # been enabled using ALTER DATABASE . ARCHIVELOG log_archive_start = true log_archive_dest_1 = "location = D: / wwfdb / archive" log_archive_format = %% ORACLE_SID %% T% TS% S.ARC # If using private rollback segments, place lines of the following # form in each of your instance -specific init.ora files: #rollback_segments = (RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6) # If using public rollback segments, define how many # rollback segments each instance will pick up, using the formula # # of rollback segments = transactions / transactions_per_rollback_segment # In this example each instance will grab 40/5 = 8 # transactions = 40 # transactions_per_rollback_segment = 5 # Global Naming - enforce that a dblink has same name as the db it connects to glo bal_names = true # Edit and uncomment the following line to provide the suffix that will be # appended to the db_name parameter (separated with a dot) and stored as the # global database name when a database is created. If your site uses # Internet Domain Names for e-mail, then part of your e-mail address after # the '@' is a good candidate for this parameter value. # db_domain =
us.acme.com # global database name is db_name.db_domain # Uncomment the following line if you wish to enable the Oracle Trace product # to trace server activity. This enables scheduling of server collections # from the Oracle Enterprise Manager Console. # Also, if the oracle_trace_collection_name parameter is non-null, # every session will write to the named collection, as well as enabling you # to schedule future collections from the console. # oracle_trace_enable = true oracle_trace_collection_name = "" # define directories to store trace and alert files background_dump_dest = D:.. / wwfdb / bdump #Uncomment this parameter to enable resource management for your database #The SYSTEM_PLAN is provided by default with the database #Change the plan name if you have created your own resource plan # resource_manager_plan = system_plan user_dump_dest. = D: / WWFDB / UDUMP DB_BLOCK_SIZE = 8192 Remote_login_PasswordFile = Exclusive OS_AUTHENT_PREFIX = "" # The following parameters are needed for the ad vanced Replication Option job_queue_processes = 4 job_queue_interval = 10 open_links = 4 distributed_transactions = 500 mts_dispatchers = "(PROTOCOL = TCP) (PRE = oracle.aurora.server.SGiopServer)" # Uncomment the following line when your listener is configured for SSL # (listener . Ors_Dispatcher = "(protocol = tcps) (pre = oracle.aurora.server.sgiopserver)" compatible = 8.1.0 sort_area_size = 65536 sort_area_retained_size =
65536 ② creation associated with the instance directory md wwfdb cd wwfdb md bdump md udump md archive ① create an instance of ORADIM -NEW -SID wwfdb -INTPWD shbj2003 -STARTMODE auto -PFILE D: /orant8i/database/initwwfdb.ora ② start the instance SET ORACLE_SID = wwfdb SVRMGRL SVRMGRL> connect internal / shbj2003 SVRMGRL> startup nomount; ③ creating a database CREATE dATABASE wwfdb LOGFILE GROUP 1 'D: /WWFDB/WWFDB1A.LOG' SIZE 1M, GROUP 2 'D: /WWFDB/WWFDB2A.LOG' SIZE 1M MAXLOGFILES 10 DATAFILE 'D: /WWFDB/sys1wwfdb.dbf' SIZE 100M AUTOEXTEND oN NEXT 20M MAXSIZE 200M MAXDATAFILES 150 CHARACTER SET ZHS16GBK; ④ modify listener listening to restart the service opens the file D: /orant8i/network/ADMIN/listener.ora file, SID_LIST_LISTENER Item SID_LIST, Add: (SID_DESC = (Global_DBNAME = WWFDB) (SID_NAME = D: / ORANT8I) (SID_NAME = WWFDB)) Execute the LSNRCTL RELOAD Restart Monitoring Service 5 Install Data Dictionary Create a Data Dictionary view through the Catalog.sql file (including SQL.BSQ's data dictionary Basic Table), create a PL / SQL environment via CatProc.sql, and create a user resource set via PuPBLD.SQL.
