OCP Training class notes

Standard disclaimer: Use the information that follows at your own risk. If you screw up a system, don't blame it on me...
mailto: dkoleary@olearycomputers.com

Hopefully, this will help organize the notes from the OCP boot camp that I went through a couple of weeks back. The raw (word) doc are available below. The list that follows is broken up into two sections: the SQL section, the dba1 test and the dba2 test in the second section. The info below contain the information that I think is interesting or that I'm likely to forget. Go to the class and get your own notes if you want the entire outline...

The dba 1 notes are below the sql notes and can be accessed directly here DBA 2 notes are even further down, available here

The training referenced a number of views that I wanted to track as well. I will generate other pages for more indepth look at these; however, just to track them for the time being: oracle_views.html

My to-do list is here. Hopefully, this will get shorter over time...

SQL test notes:

sqldeveloper doesn't seem all that bad - even for a UNIX guy that's been doing UNIX admin for 20 years. Not the end all/be all, but it's useful
Review of sql categories:
1. DDL: Data definition language (create/alter/rename/etc)
2. DML: Data Manipulation language (insert/upate/delete/merge)
3. DCL: Data control language (grant/revoke)
4. TCL: Transaction control language (commit/rollback)
Connection errors:
1. could not resolve the connect identifier Client (tnsnames.ora) problem.
2. no listener Server (listener.ora) problem.
3. Listener does not currently know the service requested Either/or - typo problem, possibly.
sql concepts:
1. projection: restricting the columns returned
2. selection: filtering the rows
3. join: joining tables together.
quoting:
1. double quotes
```
select vitor''s mom''s friend' from dual
```
2. Perlish quote word:
```
select q'< victor's mom's friend>' from dual
```
3. The bracket can be anything that matches, not necessarily <>

Joining:

Holy crap! they changed the whole sorting thing since I was studying oracle 8! It's pretty easy and intuitive once you start doing it a time or two, but ugg! The old stuff still works and, from what everyone was saying, no plans on making it go away.
Natural join: letting oracle figure it out based on column names that match in the tables. Seems like a bad idea to me.
```
select department_name, last_name, first_name
from employees e natural join departments d;
```

equivalent statement in older syntax:

where e.department_id = d.department_id and 
e.manager_id = d.manager_id;

one more example: self join - always cool

select e.last_name, e.first_name, d.department_name, mgr.last_name
from employees e join departments d on e.department_id = d.department_id
join employees mgr on d_manager_id = mgr.employee_id
order by d.department_name, e.last_name;

LAST_NAME       FIRST_NAME DEPARTMENT_NAME                LAST_NAME
--------------- ---------- ------------------------------ ---------------
Rajs            Trenna     Shipping                       Fripp
Rogers          Michael    Shipping                       Fripp
Sarchand        Nandita    Shipping                       Fripp

Date info

Displaying dates can be interesting. Quite the number of options:

select last_name, first_name,
to_char(hire_date, 'fmDay, "the" ddth "of" Month, Yyyysp') as hiredate
from employees

Walsh           Alana
Friday, the 24th of April, One Thousand Nine Hundred Ninety-Eight

nvl/nvl2/decode

seems like a hokey way to try to add if/then/else style logic to sql statements.

nvl: converts null values to something:

select last_name, first_name,
nvl(commission_pct,0)
from employees
order by last_name

LAST_NAME       FIRST_NAME NVL(COMMISSION_PCT,0)
--------------- ---------- ---------------------
Sciarra         Ismael                         0
Seo             John                           0
Sewall          Sarath                       .25
Smith           William                      .15

nvl2 is the if/then/else portion.

select last_name, first_name,
nvl2(to_char(commission_pct), 'Commission', 'No commission')
from employees
order by last_name

LAST_NAME       FIRST_NAME NVL2(TO_CHAR(
--------------- ---------- -------------
Tucker          Peter      Commission
Tuvault         Oliver     Commission
Urman           Jose Manue No commission

decode is just plain wierd. Need to read up on that one a bit.

Set operators:
1. What are they:
  1. union: returns combined rows of two queries, sorting them, and removing duplicates.
  2. union all: returns combined rows of two queries, w/o sorting, or removing duplicates
  3. intersect: returns rows that appeaer in both queries, sorted, and duplicates removed
  4. minus: returns only rows that are in the first query but not in the second.
2. Rules:
  1. Only first query column names will name the columns
  2. All column types in both queries must be the same. In other words, if the first column of the first query is a scalar, the first column of the second query must also be scalar - not date or something else.
  3. union, intersect, and minus always combine the rows, sort them, then remove duplicates.
  4. order by clase must be at the end of all queries; it would impact sorting order if it affected one of the subqueries only.

DBA notes

Deleting oracle from a windows system:
1. Use the ora uninstaller
2. regedit: delete the localmachine->softare->oracle key
3. delete c:\program files\oracle
4. reboot
5. delete c:\oracle
Database config options:
1. global database name should be hierarchical - the fully qualified domain name of the server, for instance.
2. Grid contol available only if the management agent is installed. Looks a lot like the enterprise manager, but don't call it that. Enterprise manager works on one and only one system.
3. database control synonyms:
  1. dbconsole
  2. sysman
  3. enterprise manager
system accounts
1. names:
  1. sys: sysdba
  2. system: effectively root but can't start up the database.
  3. sysman: enterprise manager id; used to collect/report performance metrics and other info.
2. As a side note: be very careful of the password you use in dbca to create databases. When creating the databases on my test boxes, I used on of my generic unix passwords that had a '&' in it. DBCA wasn't able to create the practice schemas, when I started the database sysman got locked almost immediately, and enterprise manager never did work. It was an entertaining slue of problems due to a single cause.
Useful directories/files:
1. ${ORACLE_HOME}/install/portlist.ini: lists out the ports used by the various access methods (but not the listener)
```
cat portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (oci1) = 1158
Enterprise Manager Agent Port (oci1) = 3938
```
2. ${ORACLE_HOME}/rdbms: contains generic sql scripts; if oracle tells you to run a script, it's probably going to be in there. I'm not sure if I wrote the name down wrong, or if there's a difference between the linux and windows distros, but ${ORACLE_HOME}/rdbms contains directories on my linux box, not scripts. it may be ${ORACLE_HOME}/rdbms/admin...
3. ${ORACLE_HOME}/database (windows) | ${ORACLE_HOME}/dbs (unix); contains the oracle password file, the init*.ora file, and the spfile.
Oracle initialization process:
1. startup nomount
  1. Allocates memory
  2. Reads the spfile (parameter) file.
  3. Starts the oracle processes
2. startup mount
  1. Reads the control files
3. alter database open
  1. Locks the data files
  2. Starts redo generation
  3. User sessions allowed
Failure scenarios:
1. Startup nomount
  1. executables corrupted or infected by viruses
  2. spfile missing or corrupted
  3. spfile must point to a control file
  4. startup nomount [ pfile | spfile ] to provide a temporary file. At this point, you can use sql commands to manipulate the parameters
    1. create pfile from spfile
    2. create spfie from pfile
    3. original pfile, created when the database was installe can be located in ${ORACLE_HOME}/admin/pfile. NOTE: the only pfile available on my linux test boxes are the ones actively being used. Once again, this may be a windows thing.
2. startup mount: all control files must be found and readable. No other requirement.
3. alter databaes open
  1. Recently restored datafiles (different scns)
  2. Missing datafiles
  3. smon inability to sync datafiles.
4. Shutdown modes:
  1. normal: waits for all user sessions to voluntarily exit. I'm not that patient.
  2. transactional: waits for transactiosn to exit. I'm not even this patient.
  3. immediate: ongoing transactions are automatically rolled back.
  4. abort: kill -9; guarantees an instance recovery on restart.
Oracle architecture:
1. Instance: memory and processes
  1. SGA:
    1. DB buffer cache
      1. Location of data being manipulated
      2. Biggest chunk of memory usage
      3. server process retrieves data from disk for the user
    2. Shared pool
      1. dictionary cache: data dictionary
      2. library cache: sql execution plans
    3. Log buffer: before and after data is stored in log buffer before getting written out by lgwr
  2. Processes
    1. smon: responsible for initiating media recovery.
    2. pmon:
    3. dbwr: writes data to the data files.
    4. lgwr: writes redo logs
    5. ckpt: Time betwen runs affects crash recovery. Signals dbwr to write dirty buffers and updates datafiles/control files with system change number (SCN)
2. Database: files and online redo logs
  1. control files: min 1 required but should be multiplexed.
  2. online redo logs: must have at least two groups, min 1 member per group
  3. datafiles.
3. Logical structures:
  1. tablespace
    1. provides the mapping between datafiles (physical) and other oracle structures
    2. segments: any structure that contains data:
      1. types:
        
        table segments
        index segments
        undo segments (now synonymous w/rollback segmetns)
      2. made up of extents, which are made up of data blocks.
    3. Data dictionary
    4. apparently I missed taking notes on some of this...
Creating a database: use dbca. My undying respect to peeps that can hack out a database creation script from the command line. While studying for the oracle 8 ocp, I tried that several times - with limited success. DBCA automates the process quite nicely and can generate sql scripts that can be used elsewhere. If you ask it nicely, it'll even add the practice schemas (as long as you don't have an '&' in your password).
SQL/PLSQL review:
1. Mostly review of the first test. Apparently, sometime in the past, sql/plsql was not a required test for ocp; it is now...
2. alter [database | system]
  1. alter system modifies the running system - the instance.
  2. alter database modifies the parameters that affect the datafiles, control files, etc.
3. Query tools:
  1. sqlplus
  2. isqlplus (java(?)/web interface; going away.
  3. sqldeveloper
Various useful command lines:
1. sqlplus /nolog; connect / as sysdba
2. isqlplus start
3. emctl [ start | stop | status ] dbconsole
4. lsnrctl [ start | stop | status ]
5. sqlplus ${user}/${pwd}@${sid}
privileged accounts
1. sysdba: can see/manipulate all database objects
2. sysoper: can't see the actual data, but can start/stop/open the database.
To convert to archive log mode (more on this later):
1. startup mount;
2. alter database [ archivelog | noarchivelog ]
3. alter database mount; alter database open
alter system:
1. syntax: alter system set ${parm} = ${value} scope = [spfile | memory | both ]
2. Alter system commands can only be done to memory or the spfile - not to the pfile as that's read only once on database start.
1 to many relationship between:
1. database
2. tablespace => data files
3. segment
4. extent
5. data block
data blocks are the smallest unit of I/O and should be some multiple of OS datablock.
Tablespaces
1. Only 2 required:
  1. system
  2. sysaux
2. Guidelines:
  1. Separate data that will contend for resources (indexes and their tables being the normal example)
  2. Separate objects that have different storage reqs (binary/movies vs indexes, for instance)
  3. Store different partitions in different tablespaces
3. Types of tablespace:
  1. Permanent
  2. temporary
  3. undo
4. create tablespace command notes:
  1. Command syntax
  2. bigfile tablespace:
    1. Typically used w/ASM
    2. Only one datafile in the tablespace
  3. Extent management
    1. dictionary | local (default)
    2. IDs where the extent management is going to take place. Dictionary is old school.
    3. segment space management auto, in the create tablespace command, used to specify local management.
    4. Oracle managed files (not tested) allows oracle to manage all the data files for you; used w/db_create_file_dest init parameter.
Users:
1. create user command notes:
  1. Command syntax
  2. profile used for password, security, and resource constraints. Getting either replaced or augmented by resource plans (to be discussed later).
  3. Resource limits not enforced unless the resource_limit init parameter is set.
2. privileges:
  1. system privileges:
    1. syntax: grant ${priv} to ${user} [ with admin option ]
    2. with admin option allows ${user} to grant the privilege to other users.
    3. Downstream privileges are not removed when the original user is dropped or privileges are revoked.
  2. object privileges:
    1. syntax: grant ${priv} to ${user} [ with grant option ]
    2. with grant option allows ${user} to grant those rights to other users.
    3. Downstream privileges are revoked when the original user is dropped or privileges revoked.
3. Roles:
  1. Fairly simple concept but hard to nail down what privileges, specifically, a user has via grants or grants from roles.
  2. Enterprise manager actually presents the data in a very clear manner.
  3. Removing specific roles from the defaults supplied to a user:
    1. alter user default role all except ${roles to be removed}
    2. alter user default role ${roles to be supplied}
4. Profiles:
  1. default profile doesn't limit anything.
  2. alter user dkoleary profile dba_profile;
  3. drop role cascade switches any uses with that assigned role back to the default one. w/o the cascade option, the command would fail if users currently had the role assigned.
5. Object name spaces:
  1. Objects within a specific namespace obviously can't have duplicate names.
  2. In each schema, there are multiple name spaces:
    1. general ns:
      1. tables
      2. views (materialized and normal)
      3. sequences
      4. private synonyms
      5. standalone procedures and functions
      6. packages
    2. In there own namespace:
      1. indexes
      2. constraints
      3. clusters
      4. datbase triggers
      5. private database links
      6. dimensions
  3. So, you can have a constraint, an index, a cluster, and a table, all named dkoleary
Manipulating data in a database:
1. ACID acronym - this one seems silly:
  1. Atomicity: Transactions all happen or none of them do. Standard ATM deposit/withdrawal example.
  2. Consistency: Results of queries must be consistent with the state of the database when the query started.
  3. Isolation: Uncommitted transactions must be invisible to the rest of the world.
  4. Durability: Once a transaction is committed, it must be impossible to lose it.
2. Moving data in and out of a database.
  1. data pump
    1. set of packages in dbms_datapump
    2. commands: impdb/expdb.
      1. instructor implied the native import/export processes actually call those.
      2. Wrapper functions for the dbms_datapump package.
    3. data pump can generate:
      1. sql files
      2. dump files
      3. log files
    4. data pump paths: A number of constraints will affect which path data pump chooses. The user has no control over it.
      1. direct: reads the oracle data files directly.
      2. extneral: reads the data into the buffer cache first.
    5. triggers need to be disabled before exporting/importing to avoid having those fired for each row.
  2. sqlldr: my old friend.
  3. transportable tablespaces: pretty cool idea.
    1. Moves a tablespace from one database to another.
    2. Rules:
      1. locally managed
      2. no keys outside the tablepsace (foreign key references)
      3. schema must exist on the remote databae.
      4. same version of oracle in both instances.
  4. External file: Uses an external file as part of the database. About all we went into it.
Security
1. public privilege: a set of privileges (role?) which everyone has. grant select on hr.employees gives everyone access to the employees table.
2. Dangerous packages
  1. utl_file: read/write OS files
  2. utl_tcp: create/read network sockets.
  3. utl_smtp: send mail; uses utl_tcp.
  4. utl_http: create read http; uses utl_tcp.
3. o7_dictionary_accessibility
  1. provides data dictionary access to users. grant select any table priv does not give access to the data dictionary
  2. useful for auditors
4. remote_login_passwordfile
  1. Values
    1. NONE: no one can log into the database as sys except on the box directly.
    2. EXCLUSIVE: pwd file is limited to the specific instance only.
    3. SHARED: All the oracle instances on the system using the same oracle home share the same pwd file.
  2. Allows other users to login w/sysdba privs even remotely.
  3. v$pwdfile_usrs to see who's listed.
5. Auditing
  1. Techniques/levels:
    1. sysdba auditing: done to OS logs.
    2. database auditing: can track use of certain keywords.
    3. value based auditing: pl/sql triggers
    4. fine grained auditing: done through a package.
  2. sysdba auditing:
    1. audit_sys_operations [ true | false ].
    2. if set true, audits sysdba activity to audit_file_dest
    3. Side note: sysdba connections/startup/shutdown will get audited regardless to audit_file_dest. Not a whole lot of info, but it does tell you someone connected. On my Linux system, the audit looks like:
```
$ cat ora_4508.aud
Audit file /oracle/admin/oci1/adump/ora_4508.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2.0/db_1
System name:    Linux
Node name:      ocidb1.olearycomputers.com
Release:        2.6.18-128.el5xen
Version:        #1 SMP Wed Jan 21 09:07:41 EST 2009
Machine:        x86_64
Instance name: oci1
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 4508, image: oracle@ocidb1.olearycomputers.com (TNS V1-V3)

Mon Jun 29 10:07:02 2009
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/0
STATUS: 0
```
  3. audit_trail instance parameter:
    1. Possible values:
      1. NONE: auditing is disabled.
      2. OS: Auditing is enabled, with all audit records directed to the operating system's audit trail.
      3. db|true: Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$)
      4. db_extended: As db, but the SQL_BIND and SQL_TEXT columns are also populated.
      5. xml and xml_extended: same as db and db_extended, except records are sent in xml format to the OS audit trail.
    2. Nice article on oracle-base regarding auditing.
    3. Applicable views:
      1. dba_audit_trail
      2. dba_audit_object
      3. dba_audit_statement
      4. dba_audit_session
  4. Fine grained auditing. Didn't go into much depth. Seems like it's a LOT of work and a lot of data to parse through.
oracle net
1. Supported protocols
  1. TCP/IP
  2. Named pipes
  3. Socket Direct protocol (SDP)
  4. IPC
2. Establishing a connection
  1. connect scott/tiger@ocp10g
    1. @ = network connection
    2. ocp10g: connect string; looked up in tnsnames.ora
  2. easy connect:
    1. connect scott/tiger@${host}:${port}/${instance}
    2. Special note: there doesn't appear to be the concept of current host w/sqlplus. You connect this way to a systme, then run connect oe/oe you're back on the original host.
  3. Local naing: tnsnames.ora
3. Files:
  1. Directory: ${ORACLE_HOME}/network/admin
  2. filenames
    1. sqlnet.ora: profile - specifies resolution order, etc.
    2. tnsnames.ora: client
    3. listener.ora: server
4. database server config:
  1. Generally, databases will now do dynamic registration which makes the listener.ora file a whole lot easier to maintain. (Starting as of which oracle version?)
  2. Dynamic registration
    1. local_listener: identifies the port/network address of the listener.
    2. db connects to local_listener and registers itself using the database sid.
    3. Side note: local_listener is null on my test system. Perhaps this is set if the port is non-standard?
5. Client config
  1. Use netmanager to get it running; avoids all the complexity of hte files themseles.
  2. load balancing: alternates betwen the available hosts.
  3. source_route=on: used for connecting to oracle databases through a firewall or for multiplexing net connections.
  4. also possibility of proxy net connections.
6. tnsping: utility for testing availability of oracle databases and that the configuration files are done correctly.
  1. Interestingly, all the ezconnects tests, regardless of whether the tgt databae exists or not succeeds using tnsping.
  2. After creating a new tnsnames entry using netmgr, tnsping oci2 works.
Shared server:
1. Dedicated vs shared
  1. Dedicated server, one client connection talks to one dedicated server connection which talks to the PGA.
  2. Shared server uses a limited number of dispatchers to support a large number of connections.
2. process
  1. Clients talk to dispatchers
  2. Dispatcher places requests on the input queue.
  3. Shared server pool monitors input queue, fetches, binds, executes.
  4. Shared server pool returns results to response queue.
  5. Dispatchers (not necessarily the same one as started this mess) monitors the response queue to return results to the client.
3. Instructor implied that dedicated servers were falling out of favor. Not sure how that ties in with the pros/cons
4. Pros/Cons
  1. Useful for lot of small, short connections.
  2. Not so useful for long running, long lasting connections
5. Configuration
  1. Number of instance parameters; however, only the dispatchers is required.
  2. shared_servers controls the target number of dispatchers that; set this to the expected number of connections.
  3. max_shared_servers is the maximum expected.
  4. dispatchers specifies the number of dispatchers to launch at instance startup
6. views
  1. v$circuit: current load
  2. v$shared_server_status: server status
  3. v$dispatcher: dispatcher status
  4. v$shared_server_monitor: shh! it's a secret.
Managing database performance:
1. Invalid objects:
  1. Invalid objects happen when objects on which pl/sql or views change or go away.
  2. query
```
select owner, object_name, object_type
from dba_objects
where status = 'INVALID'
order by owner, object_name;
```
  3. To reconcile
```
alter ${type} ${name} compile
```
  4. To id view dependencies: describe dba_dependencies
2. Unusable indexes:
  1. select owner, index_name from dba_indexes where status = 'UNUSABLE';
  2. alter index ${name} rebuild [ tablespace ${new} nologging online
    1. Tablespace on which to build the index if different.
    2. nologging produces no redo logs. bummer about that restore that's coming up.
    3. online: prevents the table from locking during the rebuild.
  3. skip_unusable_indexes
    1. prior version of oracle would break/abort if the indexes were invalid.
    2. this instance parameter defaults to true which allows oracle to bypass invalid/unusable indexes.
3. Optimizer
  1. Stats collected reside in following views:
    1. dba_tables
    2. dba_tab_columns
    3. dba_indexes
4. Gathering stats
  1. Manually the pl/sql package collects more stats, so is preferred:
    1. analyze ${object} compute statistics
    2. exec dbms_stats_gather_table_stats('${schema}','${table}');
  2. Automatically via the scheduler or via Enterprise manager which puts it in the scheduler for you.
  3. Stats aren't real time; they need tob e collected periodically.
Monitoring oracle
1. Automatic workload repository:
  1. mmon: takes info as supplied to the v$ tables and delivers it to the AWR once per hour.
  2. statistics_level values:
    1. basic: - basically turned off.
    2. typical: default and good for most environments
    3. all: very hard hitting and very detailed.
  3. AWR is in the sysaux tablespace owned by the sysman schema.
  4. ADDR generates historic data.
  5. Active Session history (ASH) generates current data.
2. Diagnostic advisors which use the AWR:
  1. Segment advisor: makes recomendations for shrinking
  2. sql adivsor: advises on tuning sql.
  3. undo advisor: advises on sizing undo tablespace.
  4. mean time to recovery advisor: helps on setting the mttr which affects redo log generation and lgwr activity.
Undo Management
1. undo tablespace: one and only one active at a time. Can have multiple defined, but only one can be active.
2. Purposes of undo:
  1. rollback of data changes - undo DML.
  2. Provide read consistency for queries; Lower priority than 2a.
  3. Used for several flashback technologies.
3. Undo instance parameters:
  1. undo_management = auto; means you're using an undo tablespace.
  2. undo_tablespace; seems fairly self explanatory
  3. undo_retention: guideline for desired amount of time to keep undo even after they've expired.
4. retention_guarantee:
  1. used on undo tablespace (create tablepace/alter tablespace)
  2. guarantees the retention of undo data until the undo_retention parameter.
  3. Used when queries are more important than updates. Normal processing is the other way around.
5. Related errors:
  1. ORA-30036: unable to extend: ran out of undo space.
  2. ORA-1555: snapshot too old: the required undo segments were expireed and overwritten.
General backup info
1. Categories of failure:
  1. statement failure
    1. space allocation:
      1. alter session enable resumable;
      2. session hangs until the space gets extended instead of crapping out totally.
    2. invalid data
    3. insufficient privileges
    4. logic errors
  2. User process failuire
  3. network failure
  4. Media failure - be it loss of a disk or some asshat removing a datafile.
    1. Restore the lost/damaged file, recover to the desired SCN.
    2. Must have:
      1. multiplexed copies of control files.
      2. online redo logs
      3. archived redo logs
  5. Instance failure
    1. Only committed transactions are rolled forward during instance recovery.
    2. Rolling back is not part of instance recovery; that's done after the database is open.
    3. Instance recovery is automatic and can't be stopped.
2. Tuning instance recovery:
  1. Disable checkpoint tuning by setting fast_start_mttr_target=0. Curious; it seems to default to that:
```
SQL> show parameter fast_start_mttr_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target               integer     0
```
  2. Use mttr advisor to help identify a good number for that parm.
Backing up oracle databases
1. What needs to be backed up
  1. datafiles
  2. control files
  3. archive logs
  4. spfiles
2. Terminology:
  1. Whole vs partial
    1. whole includes datafiles, control files and spfiles.
    2. partial is some of those.
  2. Full/incrememtnal
    1. Everything regardless of last backup.
    2. Everything since the last full or incremental backup.
  3. online/offline - online must be in archivelog mode.
  4. image copy vs backupset - rman concept.
    1. image copy is a copy created w/copy, cp, dd, etc.
    2. backupset
      1. rman proprietary format that
      2. oracle aware; won't contain null space
      3. allows compression, incremental backups, etc.
3. rman settings:
  1. device settings: disk backup points to an area indicated by db_recovery_file_dest which defaults to flash_recovery_area. db_recovery_file_dest_size limits the size of the resulting files. It didn't sound like the system is going to know or respect OS file size limits so ensure this is set properly.
  2. backup set settings
  3. poicy settings
    1. alter database enable block change tracking using file ${file}
    2. Tracks changes to the database block by block.
4. Repository:
  1. basically, a small database.
  2. Unless specified, backup metadata is stored in the control files and automatically deleted after 7 days.
5. backing up control files:
  1. alter database backup control file to trace
  2. Sends a sql script to regen the control files to user_dump_dest
Recoverying oracle databases.
1. generic info
  1. Anything multiplexed can simply be copied over. This includes control files (database is down) and redo logs.
  2. alter database clear unarchived logfile group # removes and recreates an archive log member that has unarchived data. Make another backup immediately!
  3. Incomplete recovery: restore to a previous date/time or SCN.
  4. restore vs recovery: restore replaces a datafile; recovery means make consistent with the rest of the database.
2. Recovering control files: database is down.
  1. multiplexed: copy file to new location, restart the database.
  2. nonmultiplexed (bad DBA!)
    1. sqlplus /nolog; connect / as sysdba
    2. Copy/paste sql from backup control file to trace, starting from the startup nomount to the end of the file.
    3. sql handles everthing after that; works pretty well.
3. Recoverying redo logs.
  1. alter database clear logfile group #
    1. may have to alter system switch logfile a number of times to clear off the current archive log
    2. Once done, run the command to clear and regreate the logfile group.
  2. alter database add logfile member '${file}' to group # to add new members to logfiles.
4. Recoverying datafiles:
  1. non-critical datafiles:
    1. noarchivelogmode: cancel Christmas; you're restoring full. `
    2. archivelogmode: restore the file, apply the redo logs as needed.
  2. critical: (system/undo)
    1. startup nomount
    2. rman target='sys/$pwd@${sid}'
    3. restore tablespace system
    4. recover tablespace system
rman
1. example usage:
  1. report schema
  2. connect target ${user}/${pwd}@${sid} (doesn't have to be on the same physical system)
  3. sql 'alter tabespace example offline | online';
  4. show all
  5. list backup
  6. list obsolete
2. rman calls dbms_backup_restore pl/sql packages
3. rman and dbms_backup_restore are in the db kernel so datbase does not have to be open to be able to use it.
4. Server processes and channels:
  1. Channel is a server process that copies files to:
    1. disk
    2. system backup to tape (sbt)
  2. polling process to monitor backups
5. Recovery catalog
  1. Repository refers to the backup metadata. Without a catalog, the repository is maintained in the database control file for (default) 7 days.
  2. Catalog is a small oracle instance that maintains the backup/rman metadata.
  3. Can't connect to a catalog *after* connecting to a target.
  4. Advantages:
    1. No 7 day metadata retention window: Possible to update control_file_record_keep_time; however, that'll end up using the control file for a purpose for which it's not intended - what could go wrng?
    2. Ability to store metadata for multiple database backups
    3. can store rman scripts
    4. protects against loss of control files (and thusly, all the backup metadata)
6. Environment:
  1. set nls_date_format='dd-mm-yy:hh24:mi:ss' to see times int he recovery info.
  2. nls_lang must match the database.
7. Create a recovery catalog:
  1. install/create database as normal
  2. create user rman identified by rman account unlock;
  3. grant recovery_catalog to rman;
  4. Exit sqlplus and execute rman
  5. connect catalog rman/rman@${sid};
  6. create catalog
  7. connect catalog rman/rman@${sid};
  8. connect target sys/${pwd}@${sid}; First time connecting, you will get an error about the target database not being found in the recovery catalog.
  9. register database
  10. Follow on connections can be done via rman target sys/${pwd}@${sid} catalog rman/rman@${sid}
Using rman to backup a database - specifically, incremental backups
1. Types of incrementals:
  1. differential: backs up any changes since last backup
  2. cumulative: backs up any changes since last level 0 backup.
2. level 0 incremental backup is basically a full backup. Must be listed as an incremental level 0, though.
3. Interesting point: can use incremental backups to update image copies of a database.
  1. first, create a copy of the database
```
backup as copy incremental level0 database tag db_whole_copy
```
  2. Next, incremental
```
run {
allocate channel dl type diks
backup incremental level 1
for recovery of copy with tag db_whole_copy
database tag db_copy_upd;

recover copy of database with tag dd_whole_copy;
delete backupset tag db_copy_upd;
		
```
Flashback for dba1 test
1. Technologies:
  1. Undo flashbacks:
    1. flashback query: queries a table at some point in the past.
    2. flashback table: in order to flasback a table, you have to enable row movement:
```
alter table ${table} enable row movement
```
    3. flashback version
  2. flashback drop; reverses a drop table command. Uses a recycle bin in each tablespace. alter system recycle_bin [ true | false ] to run recycle bin on/off.
  3. flashback databaes
    1. must be in archivelog mode;
    2. length of time available is dependent on size of flash recovery area. Maintains a separate flashback log similar in concept to archive logs
    3. Steps to turn it on/off:
      1. startup mount
      2. alter database flashback on/off
    4. Steps to search for a specific thing using flashback database.
      1. shutdown abort
      2. flashback database to timestamp to_timestamp($time)
      3. alter database read-only Search for thing
      4. shutdown abort
      5. startup mount
      6. recover database until time ${time}
      7. alter database read-only Search for thing
      8. iterate flashback/recover until you get to the time you're looking for.
      9. Once you're satisfied, alter database resetlogs
2. Other examples:
  1. flashback table ${name} to before drop rename to ${new_name}
  2. select * from ${table} as of timestamp to_timestamp($time)
    1. execute dms_flasback_enable_at_time to set the entire session to flashback time.
    2. execute dbms_flashback_disable to revert to current time
  3. flasback table query to move an entire row back in time; enable row movement and your indexes are trashed. Must maintain referential integrity.
  4. flashback versions query identifies what's changed over time for a particular row.
  5. flashback transactions query retrieves all undo data for a specific transaction. can produce sql commands to reverse the changes.
Automatic storage management
1. I don't see this as being overly useful except for small oracle installations. Not sure. Not planning on playing with this at all for the time being.
2. ASM can only be used for datafiles; not for oracle home or binaries.
3. ASM doesn't do the actual I/O; it's basically pointers to the actual device drivers.
4. Pretty much have to use rman for backups as there are no directly accessible datafiles.
5. Generally, one ASM instance supports one database.... seems wasteful.
Globalization:
1. utf8 is the industry standard coding; unicode character set where a single character can be 1 to 4 bytes.
2. nls:
  1. nls_language
  2. nls_date_language
  3. nls_sort
3. Precedence of nls settings
  1. statement
  2. session
  3. client environment
  4. instance
  5. database
4. changing character sets on existing databases is a bad idea; instructor says data loss is possible.
5. NLS_LANG is an environment variable, NLS_LANGUAGE is session variable
6. timestamp with timezone is relative to the client regardless of client's geographic location relative to the database.

DBA 2 test

Recovering from user errors
1. purge
  1. purge tablespace ${ts} # to remove everything in the tablespace.
  2. purge tablespace ${ts} user ${user}
  3. purge user_recylebin
  4. purge dba_recyclebin
2. flashback query:
  1. dbms_flashback_enable_at_timestamp to set the entire session back in time. dbms_flashback_disable to come back. Can't use any dml while regressed.
  2. select * from ${table} as of timestamp to_timestamp(${time})
3. flashback table query
  1. IDs the changes made to a table and constructs sql to reverse those changes.
  2. Rules:
    1. All related constraints must be maintained. May have to flashback multiple tables depending on complexity.
    2. Must enable row movement.
    3. If any ddl happened on the table in the target time period, flashback isn't possible.
    4. No row locking during flashback is attempted.
    5. Can't flashback any sys tables.
  3. flashback table emp to timestamp to_timestamp(...)
4. flashback versions query:
  1. psuedo columns appear in the results but are not physicall part of the table.
    1. versions_startscn
    2. versions_starttime
    3. versions_endscn
    4. versions_endtime
    5. versions_xid
    6. versions_operations
  2. psuedo colums displayed when using keywords versions between ${scn} | timestamp and ${scn} | timestamp
5. flashback transactional query:
  1. Use versions query to ID the xid that changed the data.
  2. select ${govno}, undo_sql
    from flashback_transaction_query
    where xid=hextoraw('${xid}');
database/block corruption
1. types
  1. Physical: disk is failing
  2. Logical: datablock doesn't match the oracke formatting. Typically signals one of three things:
    1. Mismatch between OS and oracle datablock sizes
    2. OS problem.
    3. H/W problem.
2. Applicable init parameters:
  1. db_block_checksum=true: checks disks by putting a checksum into each block as it's being written. I believe this one should be set true.
  2. db_block_checking=true: checks the block's checksum ever time the memory buffer from that block is accessed. Ouchies. KILLS database performance.
  3. Both parameters are enabled on the system tablespace.
3. Detecting:
  1. SQL error: sql query has to actually access the ocrrupted row.
  2. alert log: "corrupt block found"; also identifies the block/file number.
  3. trace file: may have the data that was corrupted, if you're lucky.
  4. dbverify:
    1. works on datafiles - live or backup; nothing else (archive logs, control files, etc)
    2. influx used to describe blocks that were being accessed when dbverify attempted to read them.
  5. analyze ${structure} ${name} validate structure [ cascade ] cascade option will analyze table and associated structures.
  6. dbms_repair package
    1. checks objects for problems and makes them usable - doesn't repair the corruption.
    2. dbms_repair_admin_table; creates the admin table that's a prereq for running the commands.
    3. dbms_repair_check_object(${parms})
    4. select ${stuff} from repair_corrupt_tab;
    5. run pl/sql to mark blocks as corrupt.
    6. dbms_repair_fix_corrupt_blocks: marks blocks as unusable.
  7. rman:
    1. Verifies blocks on backup; in fact, it aborts during backups if corrupted blocks are detected.
    2. set maxcorrupt for datafile # to ### sets a tolerance for corrupt blocks to enable rman to continue.
    3. views
      1. v$database_block_corruption
      2. v$backup_corruption
      3. v$copy_coruption
    4. block media recovery
      1. advantages
        
        file doesn't have to be online.
        mttr is much reduced.
      2. views
        
        v$database_block_corruption
        v$backup_corruption
        v$copy_coruption
      3. block media recovery
        
        advantages
        
        file doesn't have to be online.
        mttr is much reduced
        
        Syntax:
        
        blockrecover datafile 7 block 5;
        blockrecover datafile 7 block 5,6,7 datafile 9 block 21,25
        blocrecover ${file_info} from tag ${tag}; # get the information from the backup identified by $tag
        blockrecover $file corruption list until sysdate - 7; # get the information from a backup that's older than 7 days.
tuning
1. statistics_level=typical. Basic sucks and doesn't collect much data.
2. ADDM: Automatic database diagnostic monitor
  1. Analyzes snapshots and generates periodic reports.
  2. dbms_workload_repository manages the AWR
  3. Runs after mmon gets a snapshot of the database performance metrics.
  4. Possible recommnedations:
    1. Configuration related recommendations will be directly displayed.
    2. potentially told to run other advisors.
3. Advisors:
  1. SQL tuning: focuses on the sql query itself.
    1. works with:
      1. top sql in the sga
      2. sql tuning sets
      3. AWR captured snapshots
      4. snapshots - preserved or otherwise.
    2. makes recommendations on:
      1. optimizer stats
      2. sql profiling
      3. access paths - high level; may suggest running sql access advisor
      4. sql structural analysis.
  2. sql access advisor: focuses on indexes and/or materialized views
  3. memory advisor: graphs and predicts affects of memory tweaks. If running the automatic shared memory manager, this advisor will be disabled.
  4. MTTR: archive log/redo advice. Predicts I/O numbers based on different sizes of redo logs and how often dbwr should be running.
  5. segment advisor: fragmentation
  6. undo advisor
Monitoring/managing storage
1. online/archive redo log file writer:
  1. works as fast as possible to ensure all committed transactions are on disk.
  2. log switches
  3. archive log files and performance:
    1. can have up to 10 destinations
    2. always launch as many archivers as you have destinations.
2. space errors and resumbable statements:
  1. alter session enable resumable timeout ${secs} name '$name'
  2. at the system level: alter system set resumable_timeout = $secs
  3. session settign requires resumable priv:
    grant resumable to dkoleary
  4. dba_resumable is the view to examine for this.
  5. Possiblity of writing a pl/sql trigger to add more space upon a suspend.
3. Monitoring/managing segment sizes
  1. Useful to do periodically when data in tables grows/shrinks regularly.
  2. steps:
    1. alter table ${name} enable row movement;
    2. alter table ${name} shrink space;
    3. alter table ${name} disable row movement;
4. Indexes
  1. ID wasted space:
    1. analyze index ${name} validate structure
    2. select lf_rows_len, del_lf_rows_len
      from index_stats where name = ${index}
      IDs how many rows have been deleted. If an appreciable number, rebuild the index.
    3. alter index ${name} shrink space removes the deleted rows but leaves the high water mark.
    4. To reset the highwater mark, rebuild the index:
      ater index ${idx} rebuild online
      online is necessary or the table gets locked.
    5. alter index ${idx} rebuild online tablepsace ${new_tbs} to rebuild and move the index to a new tablespace.
  2. Monitoring usage
    1. alter index ${idx} monitoring usage;
    2. After some time or running queries:
    3. alter index ${idx} nomonitoring usage;
5. Alternative storage formats:
  1. default: organization heap: fast inserts, slower for selections
  2. index ordered table (IOT)
    1. Table is stored in the order of the index - the iot is effectively an index.
    2. good for retrieval, sucks for inserting.
    3. Primary key and first couple of columns are the index; the remainder is stashed in an overflow table.
    4. Any secondary indexes require a mapping table.
  3. index clusters:
    1. group of tables that are stored in one physical segment
    2. tables must share a common column
    3. Increases the speeds of joins since they're already done.
  4. hash clustered tables
    1. similar to index clusters but index is a hash instead of btree.
    2. index should be on *very* unique data (ssn, for instance)
    3. hash index is quicker but more cpu intensive.
    4. required for very fast access to data.
    5. Range searches would suck wind.
  5. sorted hash clustered tables: same as above but the table is sorted so range queries would b quicker
Skipping ASM storage; that just seems like a bad idea to me.
Monitoring/managing memory
1. SGA components
  1. Required:
    1. Shared pool
    2. database buffer cache
    3. log fubber
  2. Optinoal
    1. large pool
    2. streams pool
    3. java pool
    4. db buffer cache keep pool
    5. db buffer cache recylce pool
    6. db# blocksize pools (for user w/tables that have blocksizes different that the majority)
2. shared pool
  1. manual sizing based on library cache usage since it's the least used section.
  2. 43 some odd sections to the shared pool.
3. database buffer cache.
  1. LRU and checkpoint queues. Some serious performance potentials here, I'd imagine.
  2. More data than can fit in a small number of bullet points.
4. Views:
  1. v$sgastat
  2. v$sgainfo
  3. v$shared_pool_advice
  4. v$sga_target_advice
5. automatic memory management:
  1. use of sga_target instance parm sets up automatic memory management.
  2. variables
    1. sga_target
    2. db_cache_size
    3. shared_pool_size
    4. large_pool_size
    5. java_pool_size
  3. Set sga_target to be larger than the smaller guys but smaller than sga_max
  4. select sum(bytes)/1024/1024 "megs" from v$sgastat to find out what it's at now.
  5. total sga size can be limited via the sga_max_size parm
Automating administrative tasks
1. Go figure; it's not scripting. That would be *way* too easy.
2. dbms_scheduler package controls programatic access to the scheduler
3. jobs:
  1. specified what to do and when to do it.
  2. can be:
    1. pl/sql
    2. sql statements
    3. java
    4. external procedure
    5. OS script
  3. create_job procedure has four variations (overloaded) that can accept some of the following:
    1. program_name: one set of code.
    2. schedule: frequency (1/day, eg)
    3. job_class: associates jobs w/resource management groups
    4. window: (mon-wed 0000-0200 eg)
4. preconfigured jobs:
  1. purge_log: cleans out the scheduler log
  2. gather_stats: runs the optimizer

Document:
URL:
Last updated: