Test case: flashback table

scott@TEST10G> drop table t;

Table dropped.

scott@TEST10G> create table t(a int);

Table created.

scott@TEST10G> insert into t values(1);

1 row created.

scott@TEST10G> insert into t values(2);

1 row created.

scott@TEST10G> commit;

Commit complete.

scott@TEST10G> select sysdate from dual;

SYSDATE
-------------------
2008-11-16 13:23:44


scott@TEST10G> insert into t values(3);

1 row created.

scott@TEST10G> insert into t values(4);

1 row created.

scott@TEST10G> commit;

Commit complete.

scott@TEST10G> select * from t;

         A
----------
         1
         2
         3
         4


scott@TEST10G> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

scott@TEST10G> alter table t enable row movement; 

Table altered.

scott@TEST10G> flashback table t to timestamp to_date('2008-11-16 13:23:44');

Flashback complete.

scott@TEST10G> select * from t;

         A
----------
         1
         2



Must enable row movment:

scott@TEST10G> alter table t disable row movement;

Table altered.

scott@TEST10G>
scott@TEST10G> select table_name, row_movement from user_tables where table_name='T';

TABLE_NAME                     ROW_MOVE
------------------------------ --------
T                              DISABLED

7 rows selected.

scott@TEST10G> select * from t;

         A
----------
         1
         2

scott@TEST10G> flashback table t to timestamp to_date('2008-11-16 13:23:44');
flashback table t to timestamp to_date('2008-11-16 13:23:44')
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


scott@TEST10G>

Note: to flashback to 15 min ago, we can use the following syntax:

SQL> flashback table hr.employees, hr.departments
to timestamp systimestamp - interval '15' minute;

Disable a scheduler job

To disable a schedular job, if it is running, it may be necessary to stop it before it can be disabled.

Below is an exmaple that I did today for a production change request:
( changed the real database name)

BV_DATA@mydbname> select owner, job_name, state from dba_scheduler_jobs where job_name like 'ANYNAME_%';

OWNER                JOB_NAME                       STATE
-------------------- ------------------------------ ---------------
BV_DATA              ANYNAME_TAMQFL02ANK_CORR_JOB     DISABLED
BV_DATA              ANYNAME_MOBLAL02ANK_CORR_JOB     DISABLED
BV_DATA              ANYNAME_MOBLAL01ANK_CORR_JOB     RUNNING
BV_DATA              ANYNAME_LPTPPA01ANK_CORR_JOB     DISABLED

4 rows selected.

BV_DATA@mydbname> exec dbms_scheduler.stop_job ('ANYNAME_MOBLAL01ANK_CORR_JOB');

PL/SQL procedure successfully completed.

BV_DATA@mydbname> exec dbms_scheduler.disable(user||'.ANYNAME_LPTPPA01ANK_CORR_JOB',true);

PL/SQL procedure successfully completed.

BV_DATA@mydbname> select owner, job_name, state from dba_scheduler_jobs where job_name like 'ANYNAME_%';

OWNER                JOB_NAME                       STATE
-------------------- ------------------------------ ---------------
BV_DATA              ANYNAME_TAMQFL02ANK_CORR_JOB     DISABLED
BV_DATA              ANYNAME_MOBLAL02ANK_CORR_JOB     DISABLED
BV_DATA              ANYNAME_MOBLAL01ANK_CORR_JOB     DISABLED
BV_DATA              ANYNAME_LPTPPA01ANK_CORR_JOB     DISABLED

startup open restrict

Not familar with this command before. Did a test as follows:

sys@TEST10G> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST10G> startup open restrict
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             163580804 bytes
Database Buffers          440401920 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.

sys@TEST10G> select logins from v$instance;

LOGINS
----------
RESTRICTED

sys@TEST10G> alter system disable restricted session;

System altered.

sys@TEST10G> select logins from v$instance;

LOGINS
----------
ALLOWED

Oracle 10g - Purge Recyclebin

denis@test10g> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$4Pq19seDSl6+wC0qPEIl8g==$0 TABLE
BIN$5Axx4CZGRuSCF/IRkjHSAQ==$0 TABLE
BIN$ChfXT4unSY+zQgw7Voq1uQ==$0 TABLE
BIN$5LE/p9ZnRXaEWOMM9P02Ow==$0 TABLE
BIN$dl4rs24DSku72JGFBT3CVA==$0 TABLE
BIN$/DNAvyGPQxyRQm2mymxJ8Q==$0 TABLE
BIN$eoUDn9iZQZu8008avWIZTw==$0 TABLE
BIN$s/TtyOpCScOyQF4ryjTqoQ==$0 TABLE
BIN$Bg8JK9uhTYSuYKgj8RAHEQ==$0 TABLE
BIN$pQRYjS68TIWaxHVzVJPm3w==$0 TABLE
BIN$jiQonp+AR4qiyJrUtXuS1g==$0 TABLE
BIN$llYoG9Z/TPuHAqtp2lZl+Q==$0 TABLE
BIN$621+M0RgSOekCDcAcAUZ1w==$0 TABLE
BIN$O9Bb4JGdS2Ws/IHpXMvybw==$0 TABLE
BIN$oSZCxPp1SXWBseQhvo7XTw==$0 TABLE
BIN$LHMtbGiDSPyM7p0jFGzsxA==$0 TABLE
BIN$rhxtKwpeTFCUfajaRKGlew==$0 TABLE
T                              TABLE
BIN$ZC5/LPvySYW4O/iF+yz/6g==$0 TABLE
BIN$9s/ipMyTQ+yRrO3lpB0L/w==$0 TABLE
BIN$LfTIv03ES+eN1r3twcQD2g==$0 TABLE
BIN$1gapbvADSTKwC+vCkqa0xA==$0 TABLE
BIN$GYBu155XRl6HLLepitUvBQ==$0 TABLE
BIN$L87e8DnqSPWL8l4CQbPdpA==$0 TABLE
BIN$fofZqfT0QK2oKEkGy9y3Ag==$0 TABLE
BIN$mJbc7OqwQ4mvD+exbZMH3Q==$0 TABLE
BIN$M24sVrWOTWuf1W3Fn/qglA==$0 TABLE

27 rows selected.

denis@test10g>
denis@test10g>
denis@test10g> purge recyclebin;

Recyclebin purged.

denis@test10g> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE

denis@test10g>

Archived redo log destination filling up

What can you do while the archived redo log destination gets filling up? One of the clients I worked before has set up a cron job to monitor the space and automatically move the old archived redo logs to the second location if certain threshold reaches. The script also does the cleaup for those acrhived logs older than three days ago. The client adopted NetApp snapshot for backup solution.

The current client I am working for uses different strategy. They also monitor the space, however instead of moving the arhived log, they change the archived redo log destination dynamically when the threshold reaches. In addtion, there is a rman job runing every 10-15 min to backup archived redo logs to tape and cleanup the logs.

When I try to test the syntax of changing archived redo log destination dynamically, I get confused by Oracle online documentation. After searching internet and test a little while, I have learned that the 'location' keyword is necessary and no space inside the quotation mark is allowed. While Oracle documention shows the space there. Examples are shown as follows:


- missing 'location' keyword

sys@DB10G> alter system set log_archive_dest_1='C:\temp';
alter system set log_archive_dest_1='C:\temp'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


- correct syntax

sys@DB10G> alter system set log_archive_dest_1='location=C:\temp';

System altered.

- space can not appear inside the quotation mark

sys@DB10G> alter system set log_archive_dest_1='location = C:\temp';
alter system set log_archive_dest_1='location = C:\temp'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


sys@DB10G> alter system set log_archive_dest_1='location = C:\';
alter system set log_archive_dest_1='location = C:\'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


- This is correct

sys@DB10G> alter system set log_archive_dest_1='location=C:\';

System altered.

Oracle 10g - Drop database

Following tests show that drop database works in 10g, but not in 9i

tx2qdfsdb1:TOY10G: /ora01/orabkup/MYDBQ/.temp_toy10g/oracle/admin/TOY10G > sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Sep 8 15:32:24 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

sys@TOY10G> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TOY10G> startup mount;

ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 2029392 bytes
Variable Size 167774384 bytes
Database Buffers 92274688 bytes
Redo Buffers 6356992 bytes
Database mounted.
sys@TOY10G> drop database;
drop database
*

ERROR at line 1:

ORA-12719: operation requires database is in RESTRICTED mode


sys@TOY10G> alter system enable restricted session;

System altered.

sys@TOY10G> drop database;

ERROR:

ORA-01034: ORACLE not available

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options
------------------

tx2qdfsdb1:TOY9I: /ora01/orabkup/MYDBQ/.temp_toy10g/ora01/oradata/TOY9I > sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Sep 8 15:38:42 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

idle> conn / as sysdba

Connected to an idle instance.

idle> startup mount;

ORACLE instance started.

Total System Global Area 589269776 bytes
Fixed Size 731920 bytes
Variable Size 553648128 bytes
Database Buffers 33554432 bytes
Redo Buffers 1335296 bytes
Database mounted.
idle> drop database
2 ;

*

ERROR at line 2:
ORA-02026: missing LINK keyword

SQL Commads for manage log files

Chapter 1 Management of logfile

1. forcing log switch
sql> alter system switch logfile;

2. forcing checkpoints
sql> alter system checkpoint;

3. adding online redolog groups
sql> alter database add logfile [group 4]
sql> ('/disk3/log4a.rdo', '/disk4/log4b.rdo') size 1M;

4. adding online redo log members
sql> alter database add logfile member
sql> '/disk3/log1b.rdo' to group 1,
sql> '/disk4/log2b.rdo' to group 2;

5. changes the name of the online redo logfile
sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log'
sql> to 'c:/oracle/oradata/redo01.log'

6. drop online redo log groups
sql> alter database drop logfile group 3;

7. drop online redo log members
sql> alter database drop logfile member
sql> 'c:/oracle/oradata/redo01.log';

8. clearing online redo log files
sql> alter database clear [unarchived] logfile
sql> 'c:/oracle/log2a.rdo'

9. using logminer analyzing redo logfiles
(a) in the init.ora specify utl_file_dir=' '
(b) sql> execute dbms_logmnr_d.build('oradb.ora', 'c:\oracle\oradb\log');
(c) sql> execute dbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log, dbms_logmnr.new);
(d) sql> execute dbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log', dbms_logmnr.addfile)
(e) sql> execute dbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora');
(f) sql> select * from v$logmnr_contents(v$logmnr_dictionary, v$logmnr_parameters, v$logmnr_logs);
(g) sql> execute dbms_logmnr.end_logmnr;