RMAN is the obvious choice for performing hot backups and managing the flash recovery area but the plethora of options go well beyond a basic “backup database” command.  Rather than starting from scratch with scripting, adding options one at a time, and going through several cycles of needless debugging, why not use our Linux bash script and accompanying cross-platform RMAN command file complete with error handling and comments.  The RMAN command file works like a template, making it easy to activate the options you want and remove the one you don’t.  The embedded comments help to clarify your backup strategy and serve as a training tool.  If you’re not totally comfortable with RMAN yet these scripts will get you there in a jiffy.

I recently installed Oracle Grid Control 11g on my linux workstation. Below are the steps that I went through to get it working.  Prior to installing Control, I had already installed Grid Infrastructure 11.2.0.2 and Oracle Database 11.2.0.2 and created a database to be used as the Grid Control repository.

Step 1) Download the Grid Control software:
- go to technet.oracle.com, click Download and select Enterprise Manager
- Select Enterprise Manager 11g Grid Control Release 1 (11.1.0.1.0)
- select Linux x86-64 and download all 3 files:
GridControl_11.1.0.1.0_Linux_x86-64_1of3.zip
GridControl_11.1.0.1.0_Linux_x86-64_2of3.zip
GridControl_11.1.0.1.0_Linux_x86-64_3of3.zip
- unzip all 3 files into a staging location

Step 2) Download and Install Oracle Web Logic Server

- go to technet.oracle.com, click Download and select Fusion Middleware (incl. Weblogic Server)
- Under “Oracle Weblogic Server 10.3.6 and Previous Releases”, click “see all releases”
- select Oracle Weblogic Server 11gR1 (10.3.2) - Package Installer and download the file under Linux (32-bit JVM).

The filename is wls1032_linux32.bin and is a self-extracting executable so to launch the installation, just run ./wls1032_linux32.bin. This will bring up a GUI installation program. I accepted the defaults and installed into /opt/oracle/product/Middleware.  I was prompted to specify 2 passwords during the install: weblogic and nodemanager.

Step 3) Install Grid Control

From the staging location where you unzipped the files, execute ./runInstaller to bring up the Grid Control Installation GUI.  The first screen prompts you to enter your Oracle Support credentials so you can download and install the updates. If you have a valid Oracle Support contract, I suggest you take this option as it saves you the trouble of separately installing any recommended/mandatory patches.  After selecting this option, the Installer connected to Oracle Support, downloaded several patches and proceeded with the main installation.  During the installation, I ran into a few problems that required attention before it would complete:

• compat-db-4.1.25 was not installed so I installed it from my Linux software distribution CD
• Database Control had been configured in my repository database and had to be removed: 
  emca -deconfig dbcontrol db -repos drop -SYS_PWD <sys password> -SYSMAN_PWD <sysman password>
• Incorrect database settings:
  session_cached_cursors >=200
  log_buffer >=10485760
  processes >=500
  UNDO tablespace >=200M

After fixing each of these issues, I proceeded with the installation and Grid Control 11g was successfully installed.  I was prompted to specify 2 passwords during the installation: sysman and the agent registration password

Step 4) Install Grid Control Agent on each node that you want to manage

 Before Grid Control can manage a database, you must first install the Grid Control agent on the database server.  The agent installation software installs with Grid Control so you can copy it from there to each database server and then install it.  The installers are platform specific, so in my case, I copied $GC_HOME/sysman/agent_download/11.1.0.1.0/linux_x64/agentDownload.linux_x64 to a staging location on each of my RAC nodes.  I then installed the agent with the following command, specifying the installation directory as well as the names of my cluster and nodes.  The “-y” at the end will bypass the software updates portion of the installation.

 ./agentDownload.linux_x64 -b /u01/app/11.2.0/agent11g -n mdlinux-cluster -c "mdlinux-vm1,mdlinux-vm2" -y

Because I had created a registration password during the installation of Grid Control, I also need to secure the agent:

emctl secure agent
emctl start agent
emctl upload agent

Step 5) Discover the nodes in Grid Control

After you configure the Grid Control agent and it uploads its data, the hosts should become visible in Grid Control and you can then add the databases.

Some database migrations are still performed using export / import (either datapump or the original export / import); for both of these the optimizer statistics are preserved during the export / import but there are a few subtleties to be aware of. For example, if you run dbms_stats.gather_schema_stats shortly after an import you will typically lose most of the existing column histograms, obviously if the histograms are important their loss will cause problems.

Let us discuss (briefly) why / how this could happen. The default statistics gathering routine in Oracle (since version 10) is for the database to run a scheduled job during a maintenance window. In 10g you can see this job via

select owner, job_name, enabled, state, last_start_date from dba_scheduler_jobs where job_name = ‘GATHER_STATS_JOB’

in 11g via

select task_name, status, last_good_date from dba_autotask_task where task_name = ‘gather_stats_prog’

This job (effectively) calls dbms_stats with the default of Oracle recommended setting for the ‘method_opt’ parameter which is ‘for all columns size auto’ which according to the documentation “this setting means that DBMS_STATS decides which columns to add histogram to where it believes that they may help to produce a better plan.” Although it is not documented in great detail the ‘decides’ part of this depends upon two things

a) If Oracle detects ’skew’ in the values in a column
b) If that column is used in the WHERE clause

In other words and here is the key point, by default, Oracle will decide what columns should have histograms based on both the skew and the workload and immediately after an initial import the workload history is missing. This ‘workload history’ is actually recorded in the table sys.col_usage$, you can inspect it using the query

select O.OBJECT_NAME, C.NAME, CU.*
from SYS.COL$ C,
     SYS.COL_USAGE$ CU,
     DBA_OBJECTS O
where C.OBJ# = CU.OBJ# AND
      C.INTCOL# = CU.INTCOL# AND
      C.OBJ# = O.OBJECT_ID AND
      O.OWNER = 'YOUR_USER'
order by 1,2

Given this here are some suggestions, the examples use a single schema but obviously could be extended to multiple schemas easily enough.

1.
Prior to export save away the existing statistics using the commands
execute DBMS_STATS.CREATE_STAT_TABLE(’YOUR_USER’,'EXP_STATS’);
execute DBMS_STATS.EXPORT_SCHEMA_STATS(’YOUR_USER’,'EXP_STATS’);
You should not need to restore these statistics but it is better to have them available if required

2.
Prior to export, record the number of histograms that currently exist using

select count(*)
from dba_tab_col_statistics
where histogram <> ‘NONE’ and
owner = ‘YOUR_USER’

3.
After completion of the import, check the same histograms exist i.e. run the same query as in 2 on the imported database. If the number of histograms differs significantly or you encountered other issues with the statistics you can import the statistics explicity via
execute DBMS_STATS.IMPORT_SCHEMA_STATS(’YOUR_USER’,'EXP_STATS’);
under most conditions you will not need to do this.

4.
If the number of histograms match then (optionally) re-gather the statistics keeping the existing histograms using the ‘repeat’ option for method_opt i.e.

execute dbms_stats.gather_schema_stats(ownname=>’YOUR_USER’,ESTIMATE_PERCENT=>100,
METHOD_OPT=>’FOR ALL COLUMNS SIZE REPEAT’);

(Obviously you should adjust the estimate_percent depending upon your data volume).

5.
Confirm that you have the same histograms as before using the query in 2.

You will now have updated statistics but have preserved the previously generated histograms. Over time Oracle will fill in the col_usage$ information based on the workload which should ensure the histograms that are valuable are preserved.

Oracle Restart is a new feature of Oracle 11 that will automatically restart certain Oracle processes in the event of a failure.  A manual shutdown will not cause it to restart, as Oracle assumes you intend for it to be down.  This is a great feature and can really help minimize downtime in certain cases.  Imagine your production database crashes in the middle of the night.  By the time your pager wakes you up and you login, your database can already have restarted itself.  Of course, you would need to do a post-mortem to figure out why it crashed but it can save precious minutes when high availability is important. It is also responsible for starting Oracle when your server is rebooted.  This takes the place of the dbora start/stop scripts that have been around for many years. 

Oracle Restart (aka “High Availability Services” aka “has”) is controlled at a high level by the crsctl command.  If you have administered a RAC cluster, then you are already familiar with this command as it is used to manage Cluster Ready Services (crs).  Use “enable” and “disable” to have HAS start automatically when the server is rebooted.  Use “stop” and “start” to start or stop HAS manually.

$ crsctl config has
CRS-4621: Oracle High Availability Services autostart is disabled.
$ crsctl enable has
CRS-4622: Oracle High Availability Services autostart is enabled.
$ crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.
$ srvctl config database -d DB11202
PRCD-1027 : Failed to retrieve database DB11202
PRCR-1115 : Failed to find entities of type resource that match filters
((NAME == ora.db11202.db) && (TYPE == ora.database.type)) and contain attributes
VERSION,ORACLE_HOME,DATABASE_TYPE
Cannot communicate with crsd

In this case, HAS was not running so I receive the error.  I can use “crsctl start has” to start HAS as well as all of the services that it controls.

$ crsctl start has
$ srvctl status database -d DB11202
Database is running.

Now that HAS is running, I can use it to manage resources with srvctl: 

$ srvctl config database -d DB11202
Database unique name: DB11202
Database name: DB11202
Oracle home: /opt/oracle/product/database/11.2.0.2
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups:
Services:

$ srvctl start database -d DB11202
$ srvctl status database -d DB11202
Database is running.
$ srvctl stop database -d DB11202
$ srvctl status database -d DB11202
Database is not running.

You can do the same with the listener and your ASM instance:

$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is not running
$ srvctl start listener
$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): mdlinux

$ srvctl status asm
ASM is running on mdlinux
$ srvctl stop asm
$ srvctl start asm

I recently ran into a situation where some tables were accidentally dropped from a production database. I was able to restore the data pretty easily and afterwards, the obvious question came up: Who did it? If Database Auditing were enabled and configured, then likely I could have answered the question just by querying the dba_audit_trail. Unfortunately, this wasn’t the case. So I decided to use logminer to see if I could figure it out.

First, I needed to identify the logfiles that I thought might contain the information. We knew that there was an approximately 1 hour window when it occurred. Since this was a 4 node RAC cluster, I needed to take into consideration the redo log thread# as well as the sequence#. If I happened to know which instance this occurred on, I could look at just that thread. But of course,  no such luck.

select first_time, sequence#, thread#
from v$log_history
where first_time between '26-APR-2012 16:30:00'
and '26-APR-2012 17:40:00'
order by thread#,sequence# asc;

FIRST_TIME SEQUENCE# THREAD#
------------------ ---------- ----------
26-APR-12 16:37:24 89558 1
26-APR-12 16:55:04 89559 1
26-APR-12 17:22:12 89560 1
26-APR-12 16:47:28 81138 2
26-APR-12 16:55:02 81139 2
26-APR-12 17:20:01 81140 2
26-APR-12 16:38:24 83479 3
26-APR-12 16:55:03 83480 3
26-APR-12 17:13:25 83481 3
26-APR-12 17:27:05 83482 3
26-APR-12 17:38:21 83483 3
26-APR-12 16:46:10 78195 4
26-APR-12 16:55:03 78196 4
26-APR-12 17:27:07 78197 4

So, I used rman to restore each archived log file from backup:

RMAN> restore archivelog sequence 83479 thread=3;
RMAN> list archivelog sequence 83479 thread=3;

Starting restore at 27-APR-12 08:45:32
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=3 sequence=83479
channel ORA_DISK_1: reading from backup piece
channel ORA_DISK_1: restored backup piece 1
piece handle=+RECO_01/proddb/backupset/2012_04_26/annnf0_tag20120426t165515_0.1
100.781635517 tag=TAG20120426T165515
channel ORA_DISK_1: restore complete, elapsed time: 00:02:36
Finished restore at 27-APR-12 08:48:09

List of Archived Log Copies
Thrd Seq S Low Time Name
------- ---- ------- - ------------------ ----
3 83479 A 26-APR-12 16:38:24 +RECO_01/proddb/archivelog/2012_04_27/thread_3_seq_83479.552.781692457

So, now I have restored an archived logfile that may contain the statements I am looking for so  I can use Logminer to dig deeper:

conn / as sysdba
execute dbms_logmnr.add_logfile(
logfilename=>'+RECO_01/proddb/archivelog/2012_04_27/thread_3_seq_83479.552.781692457',
options => dbms_logmnr.new);
execute dbms_logmnr.start_logmnr;
select unique operation from v$logmnr_contents;

I did these steps for each logfile looking for any DDL operations, specifically “drop table” statements.  Finally, on the last logfile I found what I was looking for! There were 1168 DDL statements that occurred. This query gave me the details I needed:

select session_info, sql_undo, sql_redo,username, timestamp
from v$logmnr_contents
where operation='DDL' and upper(sql_redo) like 'DROP%' order by timestamp desc;

login_username=JSMITH client_info= OS_username=jsmith
Machine_name=jsmith_dev OS_program_name=sqlplus@jsmith_dev (TNS V1-V3)

DROP TABLE "AUTH_PERMISSION" CASCADE CONSTRAINTS AS "BIN$vp6UFqYresLgQEiKCdYy4w==$0" ;
JSMITH 26-APR-12 16:45:10

That is the answer I was looking for. Jsmith did it!

How to set it up

This is actually well documented here:

http://www.tonyex.net/TuneWiki/doku.php?id=plsql:dbms_trace

It basically involves preparing Oracle for tracing by creating the necessary trace tables, and then executing a SQL trace command in the beginning of the stored procedure that you are tracing; something like:

execute immediate 'alter session set EVENTS=''10938 TRACE NAME CONTEXT LEVEL 5''';

When I tried this on a v10.2.0.3 database, the result was not a trace file (as the article above states); instead, there were rows filled into the sys.plsql_trace_runs and sys.plsql_trace_events tables. There’s one entry for each execution in the plsql_trace_runs table, and one row for every line executed in the plsql_trace_events table.

What really helped was running this query into an HTML spoolfile:

set markup html on
spool test.html

SELECT DECODE(PTE.event_unit_owner, NULL, NULL, PTE.event_unit_owner ||'.' || PTE.event_unit) event_unit,
    PTE.event_line, PTE.stack_depth, PTE.event_comment, ASRC.text, PTE.event_seq,
    DECODE(PTE.proc_owner, NULL, NULL, PTE.proc_owner || '.') || PTE.proc_unit proc_unit, PTE.proc_line,
    PTE.event_time, PTE.event_unit_kind , excp,errorstack
FROM PLSQL_TRACE_EVENTS PTE, ALL_SOURCE ASRC
WHERE REPLACE(PTE.event_unit_kind, ' SPEC', '') = ASRC.TYPE (+)
    AND PTE.event_unit_owner = ASRC.owner (+)
    AND PTE.event_unit = ASRC.name (+)
    AND PTE.event_line = ASRC.line (+)
    AND PTE.runid = 12
    AND PTE.event_unit_owner NOT IN ('SYS', 'SYSTEM')
ORDER BY PTE.event_seq

spool off
set markup html off

The ‘errorstack’ column is a BLOB, and it will actually contain the error itself, when there is a line executed that has an EVEN_COMMENT = ‘Exception raised’.

You can basically just step through the results of the query above, to figure out what’s really happening. Find the ‘Exception Raised’ lines, and see what’s happening before then!

A few caevets:

1. You need to add the trace call above into your existing PL/SQL package/procedure/function.
2. You need to recompile the PL/SQL that you plan to trace using the ‘debug’ mode, as in: alter package xxx compile debug; alter package xxx compile debug body;
3. Note that if this is a heavily-used procedure that you’re testing, then the plsql_trace_run and plsql_trace_events tables may get very very large very quickly, so you probably want to enable that tracing a very short amount of time.

Happy tracing!

A recent late-night incident at a client site involved a database with extreme performance problems following a storage system failure and subsequent database restart. The client observed excessive swapping and paging with the run queue length breaking triple digits and the server and database becoming virtually unresponsive.  The server had not been restarted and the database instance parameters were confirmed to be identical to the settings prior to the database bounce. The SGA was on the order of 50 GB with the buffer cache consuming most of that. After a period of speculation about what might be causing the problem we noticed that the huge pages usage didn’t look quite right. The operating system was RHEL 5.4.

$ cat /proc/meminfo | grep Huge
HugePages_Total: 25600
HugePages_Free:  25431
HugePages_Rsvd:    169
Hugepagesize:     2048 kB

With a running database featuring a 50 GB SGA there should be more huge pages reserved.  They won’t all get reserved upon startup but more than 169 should have been reserved at this point for sure.  So what’s the deal with huge pages and could they be behind this performance issue?

There are many DBAs who swear by Linux-64 huge pages when it comes to managing databases with large SGAs, say on the order of 16 GB and larger.  An in-depth discussion of memory architecture is beyond the scope of this blog and over my head anyway.  Suffice it to say, however, that when SGAs get larger then you might see performance suffer without the built-in efficiency of huge pages. But when you become reliant on their use how do you prevent the trap described above? 

As of 11.2.0.2 there is a new instance parameter, USE_LARGE_PAGES, which can be set to one of the following three values:

TRUE  - Use huge pages if they’re available (default). Some combination of large and small pages for the SGA is okay.
FALSE - Don’t use huge pages even if they’re available
ONLY  - Allocation of huge pages for the entire SGA is required for the database to start

Since the client database above was 11.2.0.3 if we had set USE_LARGE_PAGES=only then when we had tried to bounce the database it wouldn’t have started and we would have seen something like this in the alert log:

Fri Apr 20 15:16:11 2012
Starting ORACLE instance (normal)
****************** Large Pages Information *****************
Parameter use_large_pages = only

Large Pages unused system wide = 25431 (26041344 KB) (alloc incr 4096 KB)
Large Pages configured system wide = 25600 (51200 MB)
Large Page size = 2048 KB

ERROR:
  Failed to allocate shared global region with large pages, unix errno = 12.
  Aborting Instance startup.
  ORA-27137: unable to allocate Large Pages to create a shared memory segment

ACTION:
  Total Shared Global Region size is 51200 MB. Increase the number of
  unused large pages to atleast 25600 (51200 MB) to allocate 100% Shared Global
  Region with Large Pages.
***********************************************************

This would have flagged the problem immediately.  We would noticed that there were shared memory segments stuck in memory that couldn’t be reallocated prompting a server reboot, which incidentally did solve the problem.

For questions about huge pages in general and how to configure them see Metalink doc ID 361468.1 or give us a call.  It is important to note that use of huge pages is not compatible with Automatic Memory Management (AMM) configured through the instance parameters MEMORY_TARGET and MEMORY_MAX_TARGET.  You can use the legacy Automatic Shared Memory Management (ASMM) configured through the instance parameters SGA_TARGET, SGA_MAX_SIZE, and PGA_AGGREGATE_TARGET, however.

This is an overview of some of the flashback features of Oracle 11g: Flashback Query, Flashback Database, Flashback Drop and Flashback Table. These features are all similar in that they allow you to access data as it existed in the past, yet they are very different in their purpose and execution.

Flashback Query - this allows you to query a table as it was at a given time in the past. This can be helpful in various troubleshooting scenarios and also used to recover one or more rows rows to a past point in time. In order for this to work, the data must still be in the UNDO tablespace so you need to size it accordingly and have your undo_retention set appropriately.  Otherwise, you will receive “ORA-01555: snapshot too old”

Use the “AS OF TIMESTAMP” syntax to execute a Flashback Query

SELECT * FROM EMPLOYEE AS OF TIMESTAMP
TO_TIMESTAMP('2012-03-19 10:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE fname=’MIKE’ and lname=’DEAN’;

In case you need to restore the record, you combine the above with an insert as follows:

INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2012-03-19 10:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE fname=’MIKE’ and lname=’DEAN’);

Flashback Database - Flashback Database allows you to restore your database to a prior point in time by undoing all the changes that have taken place since that time. This is different than a Point-In-Time Recovery in that you don’t restore a backup and then roll forward, instead you “rewind” the database by applying flashback logs. This operation can be very fast, because you do not need to restore the backups. This in turn results in much less downtime following data corruption or human error. Keep in mind that this is NOT a replacement for regular backups (preferably rman hot backups) as it DOES NOT protect you in the case of media failure, filesystem corruption, accidental deletion or anything else that effects the physical data files. It is used to undo logical data corruption, such as from a failed application upgrade. It can also be very useful in Test databases where you want to test a process repeatedly, starting with an identical data set each time.  There is overhead associated with generating and managing the flashback logs but for many databases, this is well worth it.

In order to enable Flashback Database, put the database is mount mode and run “alter database flashback on”. The flashback logs will be written to your FRA as defined by the db_recovery_file_dest parameter. The db_flashback_retention_target parameter defines how long you wish to save your flashback logs.

Here is an example of enabling Flashback Database, creating a restore point and then flashing back to that restore point:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.

SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

SQL> create restore point flashback_test;
Restore point created.
SQL> select name,time from v$restore_point;
NAME                 TIME
-------------------- ---------------------------------------------------------------------------
FLASHBACK_TEST       17-APR-12 09.17.46.000000000 AM

SQL> create table flashback_test as select * from all_objects;
Table created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> flashback database to restore point flashback_test;
Flashback complete.

NOTE: You can also flashback to a given time or SCN as well as to a restore point

SQL> alter database open resetlogs;
Database altered.
SQL> select * from flashback_test;
select * from flashback_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

The table I created after the restore point is no longer there; the database was “rewound” to a prior point in time.  Related to this is the guaranteed restore point, which can be created even if Flashback Database has not been enabled. 

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> create restore point restore_point_test guarantee flashback database;
Restore point created.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
SQL> shutdown immediate

Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> flashback database to restore point restore_point_test;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> drop restore point restore_point_test;

Once you are confident that you won’t be rolling back, make sure to drop the restore point or you will continue to generate flashback logs. 

Flashback Drop - allows you to recover a non-SYS table that was dropped by making use of the Recycle Bin.  This is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before it was dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table.  You can bypass the Recycle Bin by dropping the table with the “purge” option.

SQL> drop table drop_test;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
DROP_TEST        BIN$veGmdD1Yy9/gQKjAYIkPIg==$0 TABLE        2012-04-17:10:21:10
SQL> flashback table drop_test to before drop;
Flashback complete.

SQL> drop table drop_test purge;
Table dropped.
SQL> show recyclebin;
SQL> flashback table drop_test to before drop;
flashback table drop_test to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

Flashback Table - This can be used to restore data in a table to past point in time, based upon time or SCN.  This is similar to the Flashback Query in that it relies on the data being present in the UNDO tablespace. 

SQL> create table flashback_test (col1 number);
Table created.
SQL> alter table flashback_test enable row movement;
Table altered.
--insert a bunch of rows
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1048161
SQL> select count(*) from flashback_test;
  COUNT(*)
----------
       192

SQL> delete from flashback_test;
192 rows deleted.
SQL> commit;
Commit complete.
SQL> flashback table flashback_test to scn 1048161;
Flashback complete.
SQL> select count(*) from flashback_test;
  COUNT(*)
----------
       192

Oracle 10g introduced a new feature known as ‘Automatic Shared Memory Management (ASMM)’. As its name suggests this feature allows an Oracle instance to automatically adjust some of its internal memory sizes:

shared pool
large pool
java pool
buffer cache
streams pool
pga_aggregate_target <- 11g only if using memory_target

The setting of sga_target (10g or 11g) or memory_target (11g) to a non-zero value provides a total amount of memory that Oracle can adjust between the above components as it sees fit. When using this feature if you have any of the above parameter set to non zero values then they act as minimums e.g. if you have

sga_target=10G
db_cache_size=6G

then your buffer cache should not fall below 6G, it can go above 6G but Oracle is effectively now only managing 4G of memory automatically. When initially introduced this feature had some bugs (not surprisingly given the complexity of the underlying memory structures ) that could cause multiple resizes to occur quickly which could in turn cause ‘cursor: pin S wait on X’ waits. Also under some circumstances (particularly with poorly behaved applications that do not use bind variables correctly) it was prone to grow the shared pool to large sizes which in turn could cause shared pool latching issues. Of course patches are available for the bugs and later versions continue to improve.

The new wrinkle is that in 11.2 ASSM resizes can happen automatically even when both sga_target=0 and memory_target=0 i.e. when this feature was formerly turned off. Here is a query from v$memory_resize_ops from a client that was started with a pfile that has sga_target=0 and no memory target defined. 

As you can see, after the database started the shared pool was grown 4 times each by 256M over a 4 hour period. For those of us with Oracle support you can find more details in metalink note 1269139.1 but the main message here is that ‘turning off’ ASMM no longer prevents resize operations from running and there is nothing logged into the alert log at present so the only way to detect it is happening is to be monitoring v$sga_resize_ops or v$memory_resize_ops.

Ian Jones wrote on March 11 about the often-overlooked issue of Daylight Saving Time (DST) and its impact on scheduled jobs.  We had another client with a related glitch but this time with the beloved DBMS_SCHEDULER facility.  Their jobs were suddenly running an hour behind schedule even though the server time was right and the sysdate value had the right time as well.  The time zone used by the scheduler appeared correct given its East Coast location:

SQL> alter session set nls_date_format=’dd-MON-yy hh24:mi:ss’;

Session altered.

SQL> select sysdate from dual;

SYSDATE

——————-

27-MAR-12 09:35:06

SQL> select dbms_scheduler.stime from dual;

STIME

—————————————————————————

27-MAR-12 08.35.10.083878000 AM US/EASTERN

But this query doesn’t quite give us the information we need because the DBMS_SCHEDULER employs a special data type, TIMESTAMP WITH TIME ZONE, which has embedded DST information in it.  So if we alter the display format for that data type, we get:

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT =’DD/MM/YYYY HH24:MI:SS TZR TZD’;

Session altered.

SQL> select dbms_scheduler.stime from dual;

STIME

—————————————————————————

27/03/2012 08:35:10 US/EASTERN EST

Now it is clear to see that the scheduler thinks we’re still in Eastern Standard Time, not Eastern Daylight Time.  But why?  The answer lies in the DST changes made in 2007 where the move to Daylight time in the spring took effect on the second Sunday in March.  In had previously taken effect on the first Sunday in April.  Given that this was March 27 it fell into that rule-change period.  This database was running 10.2.0.1 software which sports the Version 2 time zone files.  In 10g and above you can run a query to see the version of your time zone files.

SQL>  SELECT version FROM v$timezone_file;

VERSION

————————–

      2

The 2007 change wasn’t enforced at that point.  It wasn’t until Version 3 of the time zone files that we saw the 2007 changes reflected.  So the client was looking at the possibility of patching their software to get the right time zone files.  A time zone files update is relatively risk-free but for 10.2.0.1 it involves obtaining the 10.2.0.2 (or better) patch and extracting the necessary files - not a quick fix.  Fortunately there was a quick fix available.  By clearing the default time zone attribute assigned to the scheduler, the time zone enforced by the systimestamp value would be used which was correct in this case.  sysdate and systimestamp come from system calls to the OS and use the host server time.

SQL> exec dbms_scheduler.set_scheduler_attribute(’default_timezone’,NULL);

PL/SQL procedure successfully completed.

SQL> select dbms_scheduler.stime from dual;

STIME

—————————————————————————

27/03/2012 10:16:24 -04:00

SQL> select systimestamp from dual;

SYSTIMESTAMP

—————————————————————————

27/03/2012 10:16:36 -04:00

Bingo! The scheduler time is now correct. The best long-term solution would be to patch up the database to 10.2.0.5 or something but this would at least get them by for now.