|
“Freedom is not
worth having if it does not include the freedom to make
mistakes.” Gandhi |
|
“Happiness is when
what you think, what you say, and what you do are in harmony.”
Mahatma Gandhi |
Oracle 10g
Database New Features
Final Exam
Evaluation
Questions and Answers:
Regular Expression
Q: What is Regular
Expression (REGEXP) in the Oracle 10g Database?
A: It
is a method for simple and complex patterns for searching and
manipulating a text. You can search, extract, format, and manipulate a
text in the database. At the beginning, it appears that the syntax is
not very intuitive but by second look, it may look easy. The technique
more reflects as UNIX style regular expressions.
Q: What are functions of
REGEXP?
A: Interfaces:
Oracle Regular Expressions are implemented by the following functions
available in SQL and PL/SQL.
· REGEXP_LIKE,
· REGEXP_REPLACE,
· REGEXP_INSTR,
and
· REGEXP_SUBSTR
Q: What are the
Metacharacters in REGEXP?
Metacharacters :
The following is a list of supported Oracle metacharacters use in
Oracle Regular Expressions.
|
Syntax |
Description |
Classification |
|
. |
Match any character |
Dot |
|
a? |
Match ‘a’ zero or
one time |
Quantifier |
|
a* |
Match ‘a’ zero or
more time |
Quantifier |
|
a+ |
Match ‘a’ one or
more time |
Quantifier |
|
a|b |
Match either ‘a’
or ‘b’ |
Alternation |
|
a{m} |
Match ‘a’ exactly
m times |
Quantifier |
|
a{m,} |
Match ‘a’ at
least m times |
Quantifier |
|
a{m,n} |
Match ‘a’ between
m and n times |
Quantifier |
|
[abc] |
Match either ‘a’
or ‘b’ or ‘c’ |
Bracket Expression |
|
(…) |
Group an expression |
Subexpression |
|
|
Match nth
subexpression |
Backreference |
|
[:cc:] |
Match character class
in bracket expression |
Character Class |
|
[.ce.] |
Match collation
element in bracket expression |
Collation Element |
|
[=ec=] |
Match equivalence
class in bracket expression |
Equivalence Class |
Q: What are the Character
Classes?
A: Character
Classes: They are sensitive to the
underlying character set such as the [:lower:] character class.
The following is a list of
Oracle supports character classes, based on character class
definitions in NLS classification data:
|
Character Class
Syntax |
Meaning |
|
[:alnum:] |
All alphanumeric
characters |
|
[:alpha:] |
All alphabetic
characters |
|
[:blank:] |
All blank space
characters. |
|
[:cntrl:] |
All control
characters (nonprinting) |
|
[:digit:] |
All numeric digits |
|
[:graph:] |
All [:punct:],
[:upper:], [:lower:], and [:digit:] characters. |
|
[:lower:] |
All lowercase
alphabetic characters |
|
[:print:] |
All printable
characters |
|
[:punct:] |
All punctuation
characters |
|
[:space:] |
All space characters
(nonprinting) |
|
[:upper:] |
All uppercase
alphabetic characters |
|
[:xdigit:] |
All valid hexadecimal
characters |
Q: Consider a simple query
to convert the ‘McLean’ city name to a more readable format (Mc
Lean). You should look for any instance for a lower case letter
immediately followed by an upper case letter. Your query should record
these two letters in backreferences by using subexpressions, then
replaces the first one, followed by a space, then followed by the
second letter.
A:
SQL> SELECT
REGEXP_REPLACE(‘McLean’,
‘([[:lower:]])([[:upper:]])’,
‘1 2’) as “City”
FROM dual;
Q: How to use REGULAR
EXPRESSIONS in Oracle
A: Keep
this in your mind that these functions support CHAR, VARCHAR2, CLOB,
NCHAR, NVARCHAR, and NCLOB datatypes.
Q: What does the
REGEXP_LIKE function?
A: It
returns a Boolean indicating whether the pattern matched or not.
Q: Consider to write an
expression that could search for common inflections of the verb ‘try’.
A: The
following regular expression will match try, trying, tried, and tries.
SQL> SELECT
REGEXP_LIKE (‘We are
trying to make the subject easier.’,
‘tr(y(ing)? | (ied) | (ies))’)
as REGEXT_SAMPLE
FROM dual;
Q: What does the
REGEXP_SUBSTR function?
A: It
returns the actual data that matches the specified pattern.
Q: Consider to write an
expression that could return the ‘trying’ specified pattern.
A:
SQL> SELECT
REGEXP_SUBSTR (‘We are
trying to make the subject easier.’,
‘tr(y(ing)? | (ied) | (ies))’)
as REGEXT_SAMPLE
FROM dual;
Q: What does the
REGEXP_INSTR function?
A: It
returns the character position of either the beginning or end of the
match.
Q: Consider to write an
expression that could return the position of ‘trying’ specified
pattern.
A:
SQL> SELECT
REGEXP_INSTR (‘We are
trying to make the subject easier.’,
‘tr(y(ing)? | (ied) | (ies))’)
as REGEXT_SAMPLE
FROM dual;
Q: What does the
REGEXP_REPLACE function?
A: It
looks for an occurrence of a regular expression and replaces it with
the contents of a supplied text literal.
Q: Query a list of all
employees’ name that hired between 1996 and 1999.
A:
SQL> SELECT ename FROM
emp
WHERE REGEXP_REPLACE
(TO_CHAR(hire_date, ‘YYYY’),
‘^199[6-9]$’);
You used ‘^’ to
indicate that the beginning of the line has to be 199, and [-] with $
to specify range of valid characters.
Q: What is occurrence in
the REGEXP functions?
A: All
functions take an occurrence that specifies you require the nth
matching expression in REGEXP_SUBSTR and REGEXP_INSTR, the default for
which is 1.
Q: Consider extracting the
third field being the Oracle system identification in a column.
A:
SQL> SELECT
REGEXP_SUBSTR(‘system/password@myhost:1521:mysid’,
‘[^:]+’, 1, 3) as “SID
name”
FROM dual;
UNDO Advisor
Q: What is the UNDO
advisor?
A: You
can size your UNDO tablespace with the UNDO Advisor. The Snapshot Too
Old error will be noted in the database alert history. Assuming that
the UNDO tablespace is UNDOTBS1, to check the time and problem on the
UNDO tablespace do the following.
SQL> SELECT
time_suggested, reason
FROM dba_alert_history
WHERE object_name = ‘UNDOTBS1’
/
You can use the Database
Control home page to utilize the UNDO Advisor to get recommendations
to correct the problem. From the Database Control home page, click on
the Administration tab and then UNDO Management. On that page change
the Analysis Time Period field to Last One Hour and click the Update
Analysis button. Now, you should see a recommendation from UNDO
Advisor. You should be able also to change the size and apply the
changes.
Q: How do you check the
time and problem on the UNDO tablespace?
A:
SQL> SELECT
time_suggested, reason
FROM dba_alert_history
WHERE object_name = ‘UNDOTBS1’
/
To correct the problem: You
can use the Database Control home page to utilize the UNDO Advisor to
get recommendations to correct the problem. From the Database Control
home page, click on the Administration tab and then UNDO Management.
On that page change the Analysis Time Period field to Last One Hour
and click the Update Analysis button. Now, you should see a
recommendation from UNDO Advisor. You should be able also to change
the size and apply the changes.
The Oracle Data Pump Export
and Import utilities
Q: Why do you use Data Pump
Export and Import?
A: The
expdp and impdp tools support all the original exp and imp
functionalities plus many new features. With previous release, you
could only move the transportable tablespace across Oracle databases
that were running on the same architecture and operating system. With
Data Pump, you are able to transport data files from one plateform to
another. Only you have to make sure that both source and target
databases set their COMPATIBLE initialization parameter to 10.0.0 or
greater.
Q: Export the DEPT and EMP
records that deptno is 10 or 30 from the ISELF schema.
A:
# expdp
FILE=/u02/oradata/ora10g/EXPDAT02.DMP
FILESIZE=2048M
LOG=/u02/oradata/ora10g/EXPDAT.LOG
TABLES=ISELF.CUSTOMER,ISELF.DEPT,ISELF.EMP
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
CONSISTENT=n
RECORD=n
QUERY='WHERE deptno IN (10,
30)'
Q: Export the iself, outln
and system schemas.
A:
# expdp
FILE=/u02/oradata/ora10g/EXPDAT05.DMP
FILESIZE=2048M
LOG=/u02/oradata/ora10g/EXPDAT.LOG
OWNER=ISELF,OUTLN,SYSTEM
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
CONSISTENT=n
RECORD=n
Q: How do you import the
DEPT and EMP tables with recalculating statistics and committing after
each array insert?
A:
# impdp
FILE=/u02/oradata/ora10g/EXPDAT.DMP
LOG=/u02/oradata/ora10g/IMPORT.LOG
FROMUSER=iself
TABLES=emp,dept
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
IGNORE=y
COMMIT=y
RECALCULATE_STATISTICS=y
DATAFILES=n
Q: Perform a Parallel Full
Export on the DIR1, DIR2 directory objects and make sure that each
file be 2 GB in size.
A:
$ expdp
FULL=y
PARALLEL=2
DUMPFILE=DIR1:exp1%U.dmp,
DIR2:exp2%U.dmp
FILESIZE=2G
The %u implies that
multiple files may be generated and start at 01 with a two-digital
number.
Q: Export only all
functions, tables, procedures (proc1 and proc2 only), and all views
that starts with the ‘EMP’ characters from the iself and SCOTT
schemas.
A:
$ expdp
SCHEMAS=iself,scott
DIRECTORY=private_exp_space
DUMPFILE=expdat01.dmp
INCLUDE=function
INCLUDE=table
INCLUDE=procedure:”in (‘proc1’,’proc2’)”
INCLUDE=view:”like ‘EMP%’”
Either you should use
INCLUDE or EXCLUDE.
Q: Generate a SQL script
from an existing export dump file.
A:
$ impdp
DIRECTORY=private_exp_space
DUMPFILE=expdat01.dmp
SQLFILE=MyScript.sql
Q: Move objects from one
tablespace to another by using the REMAP_TABLESPACE option.
A:
$ impdp
SCHEMAS=iself
REMAP_TABLESPACE=iself_tablespace:urself_tablespace
Q: How can you read from
your exported file directly without importing them into your database?
A:
SQL> CREATE TABLE
external_emp
(ename, sal, comm)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY
private_exp_space
LOCATION ( ‘expdat01.dmp’)
)
PARALLEL AS
SELECT ename, sal, comm.
FROM emp WHERE deptno IN
(10, 30);
Q: What is an endian
format?
A: The
endian format or Byte ordering is a format that will affect the
results when data is written and read. For example, the 2-bytes
integer value 1 is written as 0x0001 on a big-endian system and as
0x0100 on a little-endian system. To determine the endian format of a
platform do the following query:
SQL> SELECT
p.endian_format
FROM
v$transportable_platform p, v$database d
WHERE p.platform_name =
d.platform_name
/
The
v$transportable_platform view contains all supported platforms. In
order to convert form one platform to another platform uses the rman
utility. The following is an example of how to convert from one
platform to another.
$ rman TARGET=/
RMAN> CONVERT DATAFILE
‘/local/oradata/school/*’
FROM PLATFORM = ‘Solari
[tm] OE (32-bit)’
DB_FILE_NAME_CONVERT =
‘/local/oradata/school/data’
, ‘/remote/oradata/data’;
Backup and Recovery
Enhancements
Q: What is the Flash
Recovery Area?
A: It
is a unified storage location for all recovery-related files and
activities in an Oracle Database. It includes Control File, Archived
Log Files, Flashback Logs, Control File Autobackups, Data Files, and
RMAN files.
Q: How do you define a
Flash Recovery Area?
A: To
define a Flash Recovery Area set the following Oracle Initialization
Parameters.
SQL> ALTER SYSTEM SET
db_recovery_file_dest_size = 100G;
SQL> ALTER SYSTEM SET
db_recovery_file_dest = ‘/u10/oradata/school’;
Q: How do you use the
V$RECOVERY_FILE_DEST view to display information regarding the flash
recovery area?
A:
SQL> SELECT name,
space_limit, space_used,
space_reclaimable,
number_of_files
FROM v$recovery_file_dest;
Q: How can you display
warning messages?
A:
SQL> SELECT object_type,
message_type,
message_level, reason,
suggested_action
FROM dba_outstanding_alerts;
Q: How do you backup the
Flash Recovery Area?
A:
RMAN> BACKUP RECOVERY
FILES;
The files on disk that have
not previously been backed up will be backed up. They are full and
incremental backup sets, control file auto-backups, archive logs, and
datafile copies.
Q: How to use the best
practice to use Oracle Managed File (OMF) to let Oracle database to
create and manage the underlying operating system files of a database?
A:
SQL> ALTER SYSTEM SET
db_create_file_dest = ‘/u03/oradata/school’;
SQL> ALTER SYSTEM SET
db_create_online_dest_1 =
‘/u04/oradata/school’;
Q: How to enable Fast
Incremental Backup to backup only those data blocks that have changed?
A:
SQL> ALTER DATABASE
enable BLOCK CHANGE TRACKING;
Q: How do you monitor block
change tracking?
A:
SQL> SELECT filename,
status, bytes
FROM
v$block_change_tracking;
It shows where the block
change-tracking file is located, the status of it and the size.
Q: How do you use the
V$BACKUP_DATAFILE view to display how effective the block change
tracking is in minimizing the incremental backup I/O?
A:
SQL> SELECT file#,
AVG(datafile_blocks), AVG(blocks_read),
AVG (blocks_read/datafile_blocks),
AVG(blocks)
FROM v$backup_datafile
WHERE used_change_tracking
= ‘YES’ AND incremental_level > 0
GROUP BY file#;
If the AVG (blocks_read/datafile_blocks)
column is high then you may have to decrease the time between the
incremental backups.
Q: How do you backup the
entire database?
A:
RMAN> BACKUP DATABASE;
Q: How do you backup an
individual tablespaces?
A:
RMAN> CONFIGURE DEFAULT
DEVICE TYPE TO DISK;
RMAN> BACKUP TABLESPACE
system;
Q: How do you backup
datafiles and control files?
A:
RMAN> BACKUP DATAFILE 3;
RMAN> BACKUP CURRENT
CONTROLFILE;
Q: Use a fast recovery
without restoring all backups from their backup location to the
location specified in the controlfile.
A:
RMAN> SWITCH DATABASE TO
COPY;
RMAN will adjust the
control file so that the data files point to the backup file location
and then starts recovery.
Q: How can you begin and
end backup on the database level?
A:
SQL> ALTER DATABASE
BEGIN BACKUP;
Copy all the datafiles…
SQL> ALTER DATABASE END
BACKUP;
Q: How do you set the flash
recovery area?
A:
SQL> ALTER SYSTEM SET
db_recovery_file_dest_size = 100G;
SQL> ALTER SYSTEM SET
db_recovery_file_dest = ‘/u10/oradata/school’;
Q: How do you gather
information regarding the flash recovery area?
A:
SQL> SELECT name,
space_limit, space_used,
space_reclaimable,
number_of_files
FROM v$recovery_file_dest;
The Flashback feature
Q: What is the flashback?
A: The
flashback gives users the capability to query past version of schema
objects, query historical data, and perform change analysis.
Q: How it works?
A: Every
transaction logically generates a new version of the database. You can
navigate through these versions to find an error and its cause.
Q: Why do need to use the
flashback?
A: It
eliminates restore process and it is faster than traditional
point-in-time recovery.
Q: What is its
architecture?
A: Now,
one more log was added as Flashback Database log. The Oracle database
server regularly logs before images of data blocks in the Flashback
Database logs from Flashback buffer in the SGA Oracle memory. The
Flashback Database must be enabled. When it is enabled, the new RVWR
(Recovery Version Writer) background process will be started. The RVWR
background process sequentially writes Flashback Database data from
the flashback buffer to the Flashback Database logs which are
circularly reused.
Q: How do you configure
Flashback Database?
A:
Assuming:
The database is in archive
mode.
The database flash recovery
area was configured.
Set the database flashback
retention time target.
SQL> ALTER SYSTEM SET
db_flashback_retention_target = 2880; -- Ex: for two days.
Enable Flashback Database.
Before altering your database, the database must be in MOUNT EXCLUSIVE
mode, ARCHIVELOG mode, and the Flashback be enabled. To check whether
it is enable do the following SQL statement.
SQL> SELECT flashback_on
FROM v$database;
SQL> ALTER DATABASE
FLASHBACK ON;
If you disable the
flashback (OFF), all existing Flashback Database logs are deleted
automatically.
Q: How do you Flashback a
database?
A: The
FLASHBACK DATABASE command force the database back to a past time or
SCN. See the following examples:
SQL> FLASHBACK DATABASE
TO TIMESTAMP (sysdate-5/24); -- Go back 5 hours from now.
SQL> FLASHBACK DATABASE
TO SCN 65473;
Q: How do you monitor
Flashback Database?
A: Use
the V$FLASHBACK_DATABASE_LOG view to display the approximate lowest
SCN and time to which you can flash back your database.
SQL> SELECT
oldest_flashback_scn, oldest_flashback_time
FROM
v$flashback_database_log;
Q: How do you use the
V$FLASHBACK_DATABASE_LOG view to determine how much disk space is
needed to meet the current flashback retention target?
A:
SQL> SELECT
estimated_flashback_size, flashback_size
FROM
v$flashback_database_log;
Q: How do you use the
V$FLASHBACK_DATABASE_STAT view to monitor the overhead of logging
flashback data?
A:
You can use this to adjust the retention time or the flash recovery
area size.
SQL> SELECT * FROM
v$flashback_database_stat;
Q: How do you exclude a
tablespace from flashback database?
A: If
you do not want the USER tablespace to be included to log Flashback
Database data, do the following SQL statement.
SQL> ALTER TABLESPACE
users FLASHBACK OFF;
Q: When are you not able to
Flashback Database?
A:
· The
control file has been restored or recreated,
· A
tablespace has been dropped,
· A
data file has been shrunk, and
· A
RESETLOSG operation is required.
Q: How can you query the
content of the recycle bin by using the DBA_RECYCLEBIN view?
A:
SQL> SELECT * FROM
dba_recyclebin WHERE can_undrop = ‘YES’;
SQL> SHOW RECYCLEBIN
Q: How do you restore from
recycle bin?
A: Use
the FLASHBACK TABLE command to recover a table and all its possible
dependent objects form the recycle bin.
SQL> DROP TABLE
iself.emp;
SQL> SELECT
original_name, object_name, type, ts_name,
dropttime, related, space
FROM dba_recyclebin
WHERE original_name = ‘EMP’;
SQL> FLASHBACK TABLE emp
TO BEFORE DROP;
SQL> FLASHBACK TABLE emp
TO BEFORE DROP RENAME TO
employee;
SQL> FLASHBACK TABLE emp
TO TIMESTAMP to_timestamp (’14:45’,’HH24:MI’);
Q: How do you reclaim the
recycle bin?
A: By
using PURG option.
SQL> PURGE TABLE emp; --
Purges the specified table.
SQL> PURGE TABLESPACE
scott_ts USER scott; -- All the Scott’s objects.
SQL> PURGE RECYCLEBIN;
-- Purges all user objects.
SQL> PURGE
DBA_RECYCLEBIN; -- Purges all the objects.
Q: How can you perform
queries on the database as of a certain clock time or SCN?
A:
SQL> SELECT versions_xid,
sal, versions_operation
FROM emp
VERSIONS BETWEEN TIMESTAMP
sysdate-10/24 AND sysdate
WHERE empno = 100;
Q: How can you use the
CURRENT_SCN column in the V$DATABASE view to obtain the current SCN?
A:
SQL> SELECT current_scn
FROM v$database;
Q: How can you enforce to
guaranteed UNDO retention?
A: You
can do one of the following SQL statements.
SQL> CREATE UNDO
TABLESPACE my_undotbs1
DATAFILE ‘my_undotbs01.dbf’
SIZE 10G AUTOEXTEND ON
RETENTION GUARANTEE;
SQL> ALTER TABLESPACE
my_undotbs1
RETENTION GUARANTEE;
Q: How can you check the
UNDO retention?
A:
SQL> SELECT
tablespace_name, retention FROM dba_tablespaces;
Q: How can you recover
deleted file?
A:
Connect as sysdba and
flashback the table.
SQL> CONNECT / AS SYSDBA
Use the FLASHBACK TABLE
command to recover a table and all its possible dependent objects form
the recycle bin.
Check what do you have in
your recycle bin.
SQL> SELECT
original_name, object_name, type, ts_name,
dropttime, related, space
FROM dba_recyclebin
WHERE original_name = ‘FLASHBACK_TABLE’
/
SQL> FLASHBACK TABLE
iself.emp TO BEFORE DROP;
Q: How do you test that
your recovery was successful?
A:
SQL> SELECT count(*)
FROM flashback_table;
Automatic Database
Diagnostic Monitor
Q: What does the Automatic
Database Diagnostic Monitor?
A: The
Automatic Database Diagnostic Monitor (ADDM) maintains a
self-diagnostic to a database. It will either perform a treatment or
refer it to specialists such as the SQL tuning advisor.
Q: How does ADDM work?
A: The
Oracle database automatically gathers statistics from the SGA every 60
minutes and stores them in the Automatic Workload Repository (AWR) in
the form of snapshots. These snapshots are similar to STATSPACK
snapshots. The MMON process, it is a process that schedules the ADDM
to run automatically to detect problems proactively for every two last
snapshots. It is possible also to invoke an ADDM analysis manually.
Q: Where can I access the
latest ADDM run?
A: Go
to the Database Control home page, on the Diagnostic Summary section
you will see the number of ADDM finding from the previous automatic
run. Click on the Performance Findings link. The Automatic Database
Diagnostic Monitor (ADDM) page will be display with the details of the
latest ADDM run.
Q: How can I turn the ADDM
process off?
A: By
default the ADDM process is enabled since the STATISTICS_LEVEL
initialization parameter is TYPICAL. By setting these parameters to
BASIC, it will stop to run automatically.
Q: How can you check your
ADDM default setting?
A: Execute
the following SQL statement.
SQL> SELECT
parameter_value, is_default
FROM
dba_advisor_def_parameters
WHERE advisor_name = ‘ADDM’
/
Q: How can I retrieve ADDM
Reports using SQL?
A: You
should type the following SQL statement to display the most recent
ADDM report using a SQL command.
SQL> SELECT
dbms_advisor.GET_TASK_REPORT(task_name)
FROM dba_advisor_tasks
WHERE task_id = (SELECT
max(t.task_id)
FROM dba_advisor_tasks t,
dba_advisor_log l
WHERE t.task_id = l.task_id
AND t.advisor_name = ‘ADDM’
AND l.status = ‘COMPLETED’
/
or
SQL> @$ORACLE_HOME/rdbms/addmrpt
Q: What is the Automatic
Shared Memory Management (MMAN)?
A: It
maintains the management of the most important shared memory
structures. For example, if your system runs OLTP during the day and
large parallel batch jobs at night, you may not need to decrease
buffer cache and increase large pool in order to satisfy the needs of
your nightly jobs. The MMAN background process should do that.
Q: How do you enable or
disable Automatic Shared Memory Management?
A: Go
to your Database Control page. Click on the Administration tab, select
Memory Parameters under the Instance heading, and click the SGA tab.
Now, you are able to enable or disable. When you enable it, you can
enter the total SGA size or the SGA_TARGET value. If you set
SGA_TARGET to 0, Automatic Shared Memory Management will be disabled.
Q: How do you determine the
actual size of the auto-tuned components in the SGA?
A: When
the SGA_TARGET value is set to no-zero, you can determine the actual
size of the auto-tuned components in the SGA by the following SQL
statement.
SQL> SELECT component,
current_size/1024/1024
FROM
v$sga_dynamic_components
/
Notice that if the
SGA_TARGET value is no-zero and no value for an auto-tuned SGA
parameter, then the values of the auto-tuned SGA parameters in the
v$parameter view is 0. You will see the values if you assigned a value
for any of the auto-tuned parameters.
SQL> SELECT name, value,
isdefault
FROM v$parameter
WHERE name LIKE ‘%size’
/
Q: How do you change the
SGA_TARGET value?
A: You
can change it by using the ALTER SYSTEM command dynamically. The value
can be increased up to the value of SGA_MAX_SIZE.
Q: What is Automatic
Checkpoint Tuning?
A: It
will make the best effort to write out dirty buffers without adverse
impact on the database automatically. To enable it you should set the
FAST_START_MTTR_TARGET value to a nonzero value and all the checkpoint
parameters will be ignored.
Oracle Manageability
Infrastructure
Q: What are the components
of Oracle manageability Infrastructure?
A: Automated
tasks
Server-Generated alerts
Advisory Framework
Automatic Workload
Repository
Q: Describe Automatic
Routine Administration tasks:
A: You
can use the Scheduler, to submit a task that needs to be performed for
keeping the database in tune. To add a task, go to the Database
Control home page, click on the Administration tab, click the Jobs
link in the Scheduler section, and then create the task. You may add a
task using PL/SQL. See the following example: Assuming that you have
already created the ONLINE_BKUP procedure to perform online backup.
You now want to add that task to the WEEKEND_WINDOW.
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
Job_name => ‘online_bkup’,
Job_type => ‘STORED_PROCEDURE’,
Job_action => ‘myonline_backup’,
Job_class => ‘AUTO_TASKS_JOB_CLASS’,
Scheduler_name=> ‘WEEKEND_WINDOW’);
END;
/
Q: Describe
Server-Generated alerts:
A: If
a problem was detected, the Oracle server will send an (email) alert
message with possible corrective actions. The difference between
Enterprise Manager Alerts and Server-Generated alerts is mainly that
the metrics threshold validations are performed by MMON, which unlike
Enterprise Manager should access SGA. These alerts appear in
DBA_OUTSTANDING_ALERTS and, when cleared, they go to DBA_ALERT_HISTORY.
To set alert thresholds, go to database home page, click Manage
Metrics in the Related links section. Then click the Edit Thresholds
button. You can also use the DBMS_SERVER_ALERT.SET_THRESHOLD
procedure. For example:
SQL> BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD
(
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,
DBMS_SERVER_ALERT.OPERATOR_GE,
‘8000’,
DBMS_SERVER_ALERT.OPERATOR_GE,
‘10000’, 1, 2, ‘school’,
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,
‘payroll’);
END;
/
Q: Describe Advisory
Framework:
A: They
are server components that provide a DBA with useful feedback about a
database resource utilization and performance. The following are the
list of advisors: ADDM, SQL Tuning Advisor, SQL Access Advisor, PGA
Advisor, SGA Advisor, Segment Advisor, and UNDO Advisor. To open the
Advisors Central page, go to the Database Control home and click on
the Advisor Central link in the list of Related Links. The
DBMS_ADVISOR package contains all constants and procedure declarations
you need for all advisors. There are Advisor views such as
DBA_ADVISOR_{TASKS | LOG | OBJECTS | RECOMMENDATIONS | ACTIONS}.
Q: What is Automatic
Workload Repository (AWR)?
A: It
provides services to Oracle components to collect, maintain, process,
and access performance statistics for problem detection and
self-tuning purposes. The MMON (Manageability Monitor) background
process will transfer the memory version of the statistics every 60
minutes to disk on a regular basis and MMNL (Manageability Monitor
Light) whenever the buffer is full. The workload repository resides in
the SYSAUX tablespace. A baseline can be identified by executing the
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE procedure. To run Automatic
Workload Repository Reports run the following SQL script.
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt
Q: Manually invoke the ADDM
advisor to analyze the database between snapshots 60 and 66. Then, use
the task name to get the results from the analysis
A: Define
a binding variable to hold the task name and another variable to hold
task id.
SQL> VARIABLE tname
VARCHAR2 (60)
SQL> VARIABLE taskid
NUMBER
Create an advisor task of
the particular ADDM type.
SQL> EXEC
dbms_advisor.create_task(‘ADDM’, :taskid, :tname);
Set the required parameters
to run this specific type of task.
SQL> EXEC
dbms_advisor.set_task_parameter(:tname, ‘START_SNAPSHOT’, 60);
SQL> EXEC
dbms_advisor.set_task_parameter(:tname, ‘END_SNAPSHOT’, 66);
Execute the task.
SQL> EXEC
dbms_advisor.execute_task(:tname);
Use the task name to get
the results from the analysis.
SQL> SELECT
dbms_advisor.get_task_report(:tname)
FROM dba_advisor_tasks t
WHERE t.task_name = :tname
AND
t.owner = SYS_CONTEXT (‘userenv’,
‘session_user’)
/
Q: As sysdba, create a
special procedure to de-queue alert information from the ALERT_QUE.
Then give an object privilege to SYSTEM to use it.
A: Logging
into SQL*PLUS as sysdba
SQL> CONNECT / as sysdba
Add a new subscriber
ALERT_MYUSERS to the internal ALERT_QUE queue.
SQL> EXEC
dbms_aqadm.add_subscriber (
‘SYS.ALERT_QUE’,
AQ$_AGENT(‘ALERT_MYUSERS’,’’,0));
Grant user SYSTEM the right
to dequeue from the ALERT_QUE.
SQL> BEGIN
dbms_aqadm.enable_db_access(
agent_name=>’ALERT_MYUSERS’,db_username=>’SYSTEM’);
END;
SQL> BEGIN
dbms_aqadm.grant_queue_privilege
(
Privilege=>’DEQUEUE’,
queue_name=>’ALERT_QUE’,
Grantee=>’SYSTEM’);
END;
Now, write a Stored PL/SQL
procedure that is used by user SYSTEM to dequeue alert information
from the ALERT_QUE.
SQL> CREATE OR REPLACE
PROCEDURE my_dequeue IS
dequeue_options
dbms_aq.dequeue_options_t;
message_properies
dbms_aq.message_properties_t;
message ALERT_TYPE;
message_handle RAW(16);
BEGIN
dequeue_options.consumer_name
:= ‘ALERT_MYUSERS’;
dequeue_options.wait :=
dbms_aq.no_wait;
dequeue_options.naviagtion
:= dbms_aq.first_message;
dequeue_options.dequeue_mode
:= dbms_aq.remove;
dbms_aq.dequeue (
queue_name => ‘SYS.ALERT_QUE’,
dequeue_options =>
dequeue_options,
message_properties =>
message_properties,
payload => message,
msgid => message_handle);
Dbms_output.put_line(‘This
is my alert message dequeue…’);
END;
/
Grant ‘EXECUTE’ object
privilege on MY_DEQUEUE to SYSTEM.
SQL> GRANT EXECUTE ON
MY_DEQUEUE TO SYSTEM;
|
