oracle: auto kill lock session

refer: http://www.via6.com/topico.php?tid=66496

create or replace procedure kill_locked_usr(itime in integer) as
--grant select on v$session to user;
--grant select on v$lock to user;
my_cursor number;
my_statement varchar2(100);
result integer;
cursor c1 is
select 'alter system kill session ' || '''' || v.sid || ',' || v.SERIAL# || ''' immediate' command
from v$session v
where v.SID in (select distinct(l.sid) from v$lock l where
l.block = 1 and
l.ctime > itime);
-- select 'alter system kill session ' || '''' || to_char(a.sid) || ',' ||
-- to_char(a.serial#) || ''''
-- from v$session a, v$lock b
-- where a.sid = b.sid
-- and b.lmode = 6
-- and a.username like 'THE_BOREING_USER'
-- and b.ctime > time;
begin
open c1;
loop
fetch c1
into my_statement;
exit when c1%notfound;
dbms_output.put_line(my_statement);
my_cursor := dbms_sql.open_cursor;
dbms_sql.parse(my_cursor, my_statement, dbms_sql.v7);
--dbms_output.put_line('1 - ' || my_statement);
result := dbms_sql.execute(my_cursor);
---- execute immediate sqltxt;
dbms_sql.close_cursor(my_cursor);
end loop;
close c1;
result := 0;
end;
/

select * from user_objects o
where o.status <> 'VALID';

VARIABLE JOBNO NUMBER;
begin
--1分钟检查一次
DBMS_JOB.SUBMIT(:JOBNO, 'KILL_LOCKED_USER(90);',SYSDATE,'SYSDATE+(60/86400)',NULL);
COMMIT;
end;

select job,log_user,priv_user,last_date,next_date,interval from user_jobs;

begin
dbms_job.remove('2');

commit;
end;

停止一个JOB
SQL> exec dbms_job.broken(2,true)

SQL>commit //必须提交否则无效

启动作业
SQL> exec dbms_job.broken(2,false)

exec dbms_job.broken(2,false,next_day(sysdate,'monday')) //标记为非broken,指定执行时间

Simulating 11g Snapshot Standby Database feature on Oracle 10g?

As we all knew that the Oracle 11g improved the capabilities of standby database immensely, where a physical standby database can easily open in read-write mode, which can be ideally suitable for test and development environments. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.

What if you want to achieve the same on Oracle 10g? Well, I absolutely don’t have any clue about others, but, we have come across of such situation couple of days ago when our DR (Disaster Recovery Solution) team came to us with a request to test our standby database. They want the standby database in read write mode to do some real scenario tests and once the testing is done, they want the database to be back to standby mode.

We initially said, we can open the database in read only mode for their testing, but, the requirement demands the database to be in read write mode. We thought, we can break the standby database for their testing and once the testing is done, we can rebuild the standby database again. We know that this is very well possible with Oracle 11g but not with Oracle 10g. My colleague, Mr. Asif Momen, did some R&D come up with a solution where a Oracle 10g standby database can open in read write mode and can also be reverted back to standby mode.

The procedure as follows:

1. Set the following parameters on the standby database:

db_recovery_file_dest_size & db_recovery_file_dest

- Make sure the values are reflected.

2. Stop the media recovery process, if active.

3. When the standby is in MOUNT mode, Create a guaranteed restore point:

CREATE restore point before_rw guarantee flashback database;

3. Stop the log shipping on the primary database. (for safer side)

alter system archive log current;

alter system set log_archive_dest_state_2=DEFER;

4. Failover the standby database using the following command:

ALTER DATABASE ACTIVATE STANDBY DATABASE;

-Make sure the media recovery process is turned off

-Minimize the protection mode to MAXIMUM PERFORMANCE, if the mode is set other than the MAXIMUM PERFORMANCE.

5. Open the database (read write mode).

AT THIS POINT, YOU CAN USE THIS DATABASE AS NORMAL READ WRITE DATABASE.

Reverting the database back to standby mode:

• Shutdown the database
• Startup database in mount mode
• Flash back database to restore point using the following:

FLASH BACK DATABASE TO RESTORE POINT before_rw;

• Convert the database back to standby mode using the following:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

• Shutdown the standby database and remove the previously set parameters.
• Start the standby database in mount state and drop the restored point.
• Enable the Media Recovery on the Standby database.
• Activate log shipping on the primary using the following:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENALE;

Since, we have done very little changes in the database, after converting to read write mode, the time which took to revert the database back to standby mode took few minutes only. Well, it is definitely need to be seen the time that take during the conversion to standby mode after huge changes in the read write database.

The 11g OCP certification should be available this year. You can register for the production exam 1Z0-050 New Features on Prometrics and pass the exam on Mon Feb 18th or later, the OCA exams 1Z1-051 SQL Fundamentals and 1Z1-052 Admin I are in beta and the OCP exam 1Z1-053 Admin II is planned for early 2008 (well, the OCA exams are planned for late 2007, so do not rely on this timeframe).
Check the official page :
Oracle Education 11g certification

the password is not longer displayed in dba_users.password in 11g

By reading Pete Finnigan’s Oracle security weblog today, I discovered that the password is no longer displayed in DBA_USERS in 11g.

select username,password
from dba_users
where username='SCOTT';
USERNAME PASSWORD
-------- ------------------------------
SCOTT

select name,password
from sys.user$
where name='SCOTT';
NAME  PASSWORD
----- ------------------------------
SCOTT F894844C34402B67

on the one hand, it is good for the security.

On the other hand, it is a huge change which is not documented (I immediately sent comments to the Security and Reference book authors) and it will make a lot of script failing (scripts that use to change the password to log in and change it back to the original value afterwards).

Protecting the hash is extremely important, check your scripts for 11g compatibility!

Create edition

In 2006, I blogged about 11g new features part II. At that time 11g was in alpha or early beta stage.

One of the new feature announced at OpenWorld 2006 was multiversioning. Like you have multiple versions of your packages running at the same time.

Unfortunately, the feature has not been implemented in 11g Release 1.

As you can see, the CREATE EDITION command does not work :

SQL> create edition v1;
create edition v1
*
ERROR at line 1:
ORA-00901: invalid CREATE command

SQL> alter session set edition=v1;
ERROR:
ORA-38802: edition does not exist

The error messages ORA-38801 to ORA-38816 refer to EDITIONS.

SQL> select * from dba_editions;
EDITION_NAME PARENT_EDITION_NAME USABLE
------------ ------------------- ------
ORA$BASE YES

SQL> alter session set edition=ora$base;

Session altered.


Apparently the feature is implemented, but not ready to be used. Be patient …

backup bigfile tablespace

One may pretend bigfile tablespace is bad, because you cannot backup/restore in parallel.



SQL> create bigfile tablespace big datafile size 1g;

Tablespace created.

RMAN> configure device type disk parallelism 4;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE
TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE
TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> backup tablespace big section size 100m;

Starting backup at 30.11.2007 07:23:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=42 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=41 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=40 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 1 through 12800
channel ORA_DISK_1: starting piece 1 at 30.11.2007 07:23:37
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 12801 through 25600
channel ORA_DISK_2: starting piece 2 at 30.11.2007 07:23:41
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 25601 through 38400
channel ORA_DISK_3: starting piece 3 at 30.11.2007 07:23:45
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 38401 through 51200
channel ORA_DISK_4: starting piece 4 at 30.11.2007 07:23:48
channel ORA_DISK_1: finished piece 1 at 30.11.2007 07:23:49
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_1_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 51201 through 64000
channel ORA_DISK_1: starting piece 5 at 30.11.2007 07:23:53
channel ORA_DISK_2: finished piece 2 at 30.11.2007 07:23:53
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_2_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 64001 through 76800
channel ORA_DISK_2: starting piece 6 at 30.11.2007 07:23:57
channel ORA_DISK_3: finished piece 3 at 30.11.2007 07:23:57
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_3_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 76801 through 89600
channel ORA_DISK_3: starting piece 7 at 30.11.2007 07:24:01
channel ORA_DISK_1: finished piece 5 at 30.11.2007 07:24:01
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_5_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 89601 through 102400
channel ORA_DISK_1: starting piece 8 at 30.11.2007 07:24:05
channel ORA_DISK_2: finished piece 6 at 30.11.2007 07:24:05
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_6_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 102401 through 115200
channel ORA_DISK_2: starting piece 9 at 30.11.2007 07:24:09
channel ORA_DISK_4: finished piece 4 at 30.11.2007 07:24:09
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_4_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:21
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 115201 through 128000
channel ORA_DISK_4: starting piece 10 at 30.11.2007 07:24:13
channel ORA_DISK_1: finished piece 8 at 30.11.2007 07:24:13
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_8_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 128001 through 131072
channel ORA_DISK_1: starting piece 11 at 30.11.2007 07:24:18
channel ORA_DISK_2: finished piece 9 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_9_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_3: finished piece 7 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_7_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:17
channel ORA_DISK_4: finished piece 10 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_10_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_1: finished piece 11 at 30.11.2007 07:24:19
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_11_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30.11.2007 07:24:19

This works fine

Oracle 11g Hot patching

Online Patching : you can apply or roll back online patches while the RDBMS instance is running

1) download an interim patch for 11g, f.ex. dummy patch 6198642
2) unzip p6198642_111060_LINUX.zip
3) cd 6198642
4) $ORACLE_HOME/OPatch/opatch apply -silent -connectString LSC08 -runSql

Invoking OPatch 11.1.0.6.0

Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation. All rights reserved.

Oracle Home : /opt/oracle/product/11/db_4
Central Inventory : /opt/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.0
OUI version : 11.1.0.6.0
OUI location : /opt/oracle/product/11/db_4/oui
Log file location : /opt/oracle/product/11/db_4/cfgtoollogs/
opatch/opatch2007-12-05_21-23-42PM.log

RollbackSession rolling back interim patch ‘6198642′ from OH
‘/opt/oracle/product/11/db_4′

Running prerequisite checks…

OPatch detected non-cluster Oracle Home from the inventory
and will patch the local system only.

Please shutdown Oracle instances running out of this
ORACLE_HOME on the local system.
(Oracle Home = ‘/opt/oracle/product/11/db_4′)

Is the local system ready for patching? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
Backing up files affected by the patch ‘6198642′ for restore.
This might take a while…
Execution of ’sh /opt/oracle/product/11/db_4/.patch_storage/
6198642_May_07_2007_00_50_36/original_patch/custom/
scripts/pre -rollback 6198642 ‘:

Return Code = 0

Patching component oracle.rdbms, 11.1.0.6.0…
Copying file to “/opt/oracle/product/11/db_4/cpu/CPUDummy2007/
catcpu.sql”
RollbackSession removing interim patch ‘6198642′ from inventory

—————————————————————
This is a dummy patch for testing only
—————————————————————
Execution of ’sh /opt/oracle/product/11/db_4/.patch_storage/
6198642_May_07_2007_00_50_36/original_patch/custom/scripts/
post -rollback 6198642 ‘:

Return Code = 0

Running the “apply” sql script “/opt/oracle/product/11/db_4/cpu/
CPUDummy2007/catcpu.sql” with reference to ‘patchmd.xml’ file
for the patch “6198642″…

The local system has been patched and can be restarted.

OPatch succeeded.

OPatch did run the necessary script (catcpu for Dummy2007) on the various instances (LSC08). It needed only one step and 34 seconds on my notebook. This patch is a dummy patch, let’s wait for CPU January to see if it is online applicable ;-)