============ Exam 2 - DBA ============ V$TYPE_SIZE Architecture oracle DB set of tables,indexes,procedures,other data objects that store info that applications place into storage in the oracle product oracle Instance memory structure,BG processes,disk resources all working together to fulfill user data requests and changes 2 basic memory structures making the oracle instance SGA (system global area) PGA (program global area) SGA consists of buffer cache buffers are size of DB blocks store data blocks recently used via SQL statements looks here first for data blocks blocks can be become dirty, ie. dirty buffers maintains a dirty buffer queue, stores blocks here before writing them to disk shared pool library cache stores parsed info/exe plan for SQL statements dictionary cache (row cache) stores data from data dictionary redo log buffer stores online redo log entries before writing them to disk PGA helps user processes execute hold bind variable info,sort areas, aspects for cursor handling stores real values in place of bind variables for exe'ing SQL statements as the values the user wants to exe are not shareable SORT_AREA_SIZE specifies the maximum amount, in bytes, of Program Global Area (PGA) memory to use for a sort. If MTS is enabled, the sort area is allocated from the SGA. After the sort is complete and all that remains to do is to fetch the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is fetched, all memory is freed. The memory is released back to the PGA, not to the operating system. Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never exist; there is only one memory area of SORT_AREA_SIZE for each user process at any time. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk hold the intermediate sort runs. Session info in PGA or Shared Pool for MTS its in Shared pool MTS, all data read into shared pool on behalf of the user process via shared server processes Background processes DBWR database writer handles all data block writes to and from disk into the buffer cache LGWR log writer writes redo log entries from the buffer to online redo log files also writes log sequence of current redo log to datafile headers and control files also clears the dirty buffer queue the clears queue is used by the DBWR to write to disk this process is called a checkpoint also recovers committed data in the database buffer cache at time of instance failure SMON system monitor handles recovery from instance failure if other processes fail disk space management - coalescing free space PMON process monitor watches user processes, cleans remnants of failed processes rolls back RECO recoverer process (option) Db's using the distributed option handles resolution of distributed transactions against the DB ARCH archiver (optional) archives online redo logs CKPT checkpoint (optional) handles the checkpointing the lgwr would be doing normally LCK0..LCK9 lock process (optional) used with parallel server option handles locking of remote tables S000..S999 server process exe's data reads from disk on behald of user processes shared or dedicated if using MTS or not MTS, must option access to a shared server process via a dispatcher D001..D999 dispatcher Disk Utilization structures Logical extents, segments, tablespaces Physical tablespace data files, data blocks control files, redo log files, parameter, password tablespace parent of all logical structures, others stored within it hold segments segments store data of tables, indexes, rollback entries extents segments are made up of extents stores info corresponding to a table controls growth of a segment 1 or more physical data files tablespaces can share the same datafiles redo log files minimum of 2 redo logs avail to the lgwr redo log can consist of 1 or more redo log files, called members Authentication internally or externally (via OS) osoper and osdba roles basic set of roles to create/manage the DB for OS authentication only be granted through the OS they are OS specific and oracle handles the granting of these to the user only used if remote_login_password = none tries to give osoper then osdba sysoper and sysdba roles basic set of roles to create/manage the DB for oracle authentication sysoper start/stop db mount/open backup start archiving initiate DB recovery change DB to restricted mode sysdba all system privs with ADMIN OPTION create DB priv privs for time-based recovery must set remote_login_passwordfile=exclusive to add users to the PW file within oracle if remote_login_passwordfile=shared then a) all users needing sysdba must connect as SYS/passwordselect connect /, where / indicates external authentication via OS internal auto given osoper and osdba roles sysdba, collection of DB privs that are smiliar to those granted to internal connect / as sysdba authentication of DBA users when not via the OS is done by the use of a password file REMOTE_LOGIN_PASSWORD_FILE = [none, shared, exclusive] only SYS and INTERNAL users are recogised by the password file orapwd utility to manage the password file any login with sysdba or sysoper that create objects will be owned by SYS if use the connect ?/? as SYSDBA syntax Starting and stopping the DB startup Mount -> Open (controlfile accessed in both phases) startup