Andrew Fraser DBA

More at Kris Rice.

2) Relax and Recover Martin Nash

A free tool to make a bootable .iso file for fast bare metal recovering of any server, onto which the normal backup files could then be restored. Free GPL licence. Linux only.

3) Who changed my Plan? Presanth Kothuri

dbms_sqldiag.dump_trace – to generate a 10053 trace for any SQL in the shared pool

Evolution:

• 9i – bind variable peeking
• 10g – dynamic sampling
• 11gR1 – adaptive cursor sharing (bind aware cursor, multiple explain plans based on bind variables) , extended statistics
• 11gR2 – cardinality feedback
• 12c – Adaptive Query Optimisation

Problem causes:

• statistics
• bind variable peeking
• cardinality feedback
• Bad SQL code
• Missing indexes
• Oracle Bugs, especially with cardinality feedback – e.g. partition pruning not used, global index used instead.

11gR1+ Adaptive Cursor Sharing limits bind variable numbers to 14. Runs slowly once, makes as bind aware.

12c Adaptive Plans – fix bad plans on the fly. Adaptive statistics – gather and persist statistics as statement is running.

Outlines – deprecated since 11g.

SQL Profiles – opt_estimate can auto generate and apply. Dbms_sqltune or OEM GUI, part of SQL tuning advisor. See in v$sqlplan.other_xml. Can use dbms_sqltune import_sql_profile (see blog).
SQL Plan Baselines
SQL Baselines

SQL Patches – undocumented, used by SQL repair advisor but can be used to create hints and inject them into SQL.

SQL Plan Baselines (11g) – dbms_spm
optimizer_capture_sql_plan_baselines = true – to gather
optimizer_use_sql_plan_baselines = true – to apply them
Plans are either accepted=used or unaccepted=evolution
Can move plans from e.g. test > production and from outlines > SQL plan baselines.
You can manually mark a plan as fixed in place.
Plan evolution – in 11g manually run dbms_spm.evolve, in 12c is an automatic nightly task.
12c has sqlobj$plan.
Dbms_sqltune.create_sqlset / .load_sqlset
Very powerful, do for every upgrade.
dbms_spm.configure ( plan_retention_weeks , 26 ) to change how long SQL plan baselines are kept for.

Signature column in sqlobj$ is used to match SQL = sql_text with no whitespace.

dbms_sqltune = SQL Profiles = needs diagnatics + tuning pack licence. Use force_match=yes for query different in literals
dbms_spm = SQL Plan Baselines = free with Enterprise Edition
dbms_sqldiag_internal = SQL Patches = undocumented, so licence free.

4) Oracle Enterprise Manager Cloud Control 12c High Availability Failover Zahid Anwar and Clive Archibald

Needed because Cloud Control could be running critical jobs, backups, alerting.

Create symbolic links on both servers, ‘/em’, for OMS install. This will be shared network storage.
vi /etc/hosts – set up shared OMS host name
vi /etc/oraInst_oms.loc – point to share storage with ‘inventory_loc=/em/…’
Install middleware and agent home both into /em
Change host name to shared host name
’emctl status oms -details’ – shows full config of servers etc.
’emctl status agent’
OMS lock file location – for performance change this to local storage. Everything else uses shared storage.
In DNS set TTL60 – time to live 60 seconds before DNS entry points to DR server. DNS is faster than specifying two addresses in tnsnames.ora
OMS + one EE database is licence free, all additional RAC or dataguard servers need to pay for a licence.

If running OMS and database together on server, need two inventories:
Shared inventory /etc/orainst_oms.loc – for main OMS, floats.
Local inventory /etc/orainst.loc – on both boxes, monitors local database.

Network response times recommended:
1ms – for full 12c Cloud Control
10ms – for older 10g OEM or with less options running.

Recommend physical server for database, but VMS ok for OMS.

Underlying RAC database also needs service:
srvctl add service .. -d ..
Create service on primary
Database trigger on startup on both databases to check and startup service if is primary currently.

5) Database Security in the Cloud Oded Raz

Oracle Cloud either:
1) Schema edition – get a schema, 50gb max.
2) Database Cloud Virtual Image – get root on a server, same as Azure and Amazon.
3) (coming soon) Database Cloud Automated – same as (2) but Oracle handle install and patching.
4) (coming soon) Fully managed database instance – Oracle handle almost all DBA tasks.

Schema edition 50gb max size, others unlimited.

Three prices:
1) SE1 – up to 16 OCPUs
2) EE (includes transparent data encryption)
3) EE High Performance – includes partitioning etc. options.
Pay per month or per hour.

Dbms_crypto string + key -> returns encrypted data. Needs very good key management.
Better (for encrypted data at rest) = Transparent Data Encryption. Uses a database wallet, which needs managed. Very low overhead on hardware ~% if you buy hardware with dedicated crypto CPU, 2-3% overhead otherwise.

Data Redaction (dynamic masking) e.g. call centre users get ‘xxxx-xxxx-4898’ while billing department gets full credit number. Dbms_redact.add_policy.
Only hides the result set, not the where clause, so can be hacked by e.g. a PL/SQL loop.

Data Masking (physical masking) – a grid control option when copying e.g. live -> test.

Database Vault privileged user control – has a new user, database vault owner, which is a super-DBA.
Then everyone else, even root, sysdba, dba, can only do what DVO grants them.
But is a very expensive extra cost option, so not used in practice – except is included with Oracle Cloud.

Oracle Label Security – grant permissions on rows.

Oracle Database Firewall – firewall for SQL statements + can alert.
Oracle Audit Vault – huge data warehouse for storing all the ODF generated info.
Practical alternative to database auditing.
Both available for non oracle databases.

6) MySQL Database Administration Mario Beck

Storage engines:
InnoDB – storage engine, owned by Oracle, has ACID, crash recovery, row locking, referential integrity, full text, etc. Is the default storage, used 99% time now, but some of the system objects still not moved to this.
MyISAM – old junk original storage engine. Still used for system objects.
Cluster etc. storage engines – rarely used.

MySQL config file = my.cnf (linux) aka my.ini (traditionally named windows).
Is kept in /etc or /etc/mysql or BASEDIR, check location with ‘lsof -p < mysql.pid' (linux) or windows system service definition -defaults_file= (often c:\windows, c:\, BASEDIR). Crucial parameter - innodb_buffer_pool_size=Xg = the buffer cache size. MySQL has an 'oracle' mode for SQL syntax. 'MySQL Workbench' is a separate download, free, has: Design - E-R, reverse engineer, compare. Development - stored procedures etc. Administration - edit my.cnf file, start/stop, monitor performance. Migration - wizard MySQL>MySQL, Oracle>MySQL.

Backup:
1) MySQL dump – very slow, small databases only. Often people put database into read only mode for duration.
2) Snapshot filesystem – non MySQL solution, needs read only mode for the duration.
3) Replication – and backup slave system = best option.

Monitoring:
Error log in DATADIR/hostname-err.log
General logfile – optional, for full auditing, not often used.
Slow query log – DATADIR/hostname-slow.log – read with mysqldumpslow. By default has all queries > 10s.
SQL – show variables like ‘%’ , show status like ‘%’.
Performance schema in MySQL5.6
SYS schema (like v$)
Workbench GUI for performance.

MySQL Enterprise Edition (costs) works with Oracle Enterprise Manager for MySQL. Also has MySQL Enterprise Monitor.
MySQL Enterprise Backup = full backup solution, although need to use InnoDB (the default) for online backup.

High Availability:
1) Replication = Master (read-write) -> Slave (read only)
2) MySQL Fabric = dgmgrl style monitoring and transparent application failover for (1)
3) OS or VMWare failover
4) MySQL Cluster – stores data in NDB which shards data across a set of active-passive pairs, e.g.:
Pair 1: Active-Passive = 1/3 data.
Pair 2: Active-Passive = 1/3 data.
Pair 3: Active-Passive = 1/3 data.

7) Oracle DB In-Memory Zohar Elkayam