Kamran Agayev’s Oracle Blog

Kamran Agayev’s Oracle Blog

Archive for July, 2009

How to prevent DBAs from using dumps for passing Exams

Posted by Kamran Agayev A. on July 31, 2009

I’ve posted a new thread on OTN forum. Please share your opinions …

http://forums.oracle.com/forums/message.jspa?messageID=3661528

Advertisements

Posted in Administration | Leave a Comment »

User access to database within limits of time

Posted by Kamran Agayev A. on July 28, 2009

Today, my Turkish friend Emre Baransel asked a question in OTN Forum related to limiting access of a user to a database based on time interval. He want to prevent a specific user to access to a database between 08 and 22.  I’ve created a logon script and showed how it’s working:

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 14:56:58 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> create user kam identified by kam;
 
User created.
 
SQL> grant connect, resource to kam;
 
Grant succeeded.
 
SQL> conn kam/kam
Connected.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER limit_connection
  2     AFTER LOGON ON DATABASE
  3  BEGIN
  4     IF USER = 'KAM' THEN
  5        IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
  6        THEN
  7           RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');
  8        END IF;
  9     END IF;
10  END limit_connection;
11  / 
 
Trigger created.
 
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
23
 
SQL> conn kam/kam
Connected.
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
18
 
SQL> conn kam/kam
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998:  Dear user KAM! You can't login between 08 and 22
ORA-06512: at line 5
 
 
Warning: You are no longer connected to ORACLE.
SQL>

Posted in Administration, DBA scripts | 8 Comments »

Recovering Dropped tablespace using Flashback Database

Posted by Kamran Agayev A. on July 27, 2009

Today, in OTN forum, there was a question relating the recovery of dropped tablespace using Flashback Database. I did a little scenario where you can see the recovery process

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 1 14:20:34 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area  293601280 bytes
Fixed Size                  1248624 bytes
Variable Size              96469648 bytes
Database Buffers          192937984 bytes
Redo Buffers                2945024 bytes
Database mounted.
 
SQL> alter database archivelog;
 
Database altered.
 
SQL> alter database flashback on;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> create tablespace tb datafile 'c:\tb.df' size 1m;
 
Tablespace created.
 
SQL> create user tb identified by tb;
 
User created.
 
SQL> grant dba to tb;
 
Grant succeeded.
 
SQL> alter user tb default tablespace tb;
 
User altered.
 
SQL> create table tb (id number);
 
Table created.
 
SQL> select current_scn from v$database;
 
CURRENT_SCN
-----------
     547292
 
SQL> drop tablespace tb including contents and datafiles;
 
Tablespace dropped.
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area  293601280 bytes
Fixed Size                  1248624 bytes
Variable Size              96469648 bytes
Database Buffers          192937984 bytes
Redo Buffers                2945024 bytes
Database mounted.
 
SQL> flashback database to scn 547292;
flashback database to scn 547292
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'
 
 
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'
 
 
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
 
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005
 
SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' as 'c:\tb.dbf';
 
Database altered.
 
SQL> flashback database to scn 547292;
 
Flashback complete.
 
SQL> alter database open resetlogs;
 
Database altered.
 
SQL>
 
 
SQL> select * from tb;
 
no rows selected
 
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
 
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\USERS01.DBF
C:\TB.DBF
 
SQL> select name from v$tablespace;
 
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TB
 
6 rows selected.
 
SQL>
 

Posted in Administration | 13 Comments »

Comparison between Oracle and Others – Tom Kyte’s interesting discussion

Posted by Kamran Agayev A. on July 18, 2009

A very interesting discussion make by Tom Kyte on comparison between Oracle and other database systems

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1886476148373

Posted in Administration | Leave a Comment »

Send your favorite “Exclusive” Interview Questions and Interviewees name

Posted by Kamran Agayev A. on July 13, 2009

Hi all

I’m planning to take an “Exclusive Interview” from world-known Oracle Professional DBAs. So, I want you to send me your favourite Interviewees name and your favourite Interview questions you want to ask. Deadline is 20.07.2009

You can do it by adding a comment to this post or sending an e-mail to one of my email addresses written in my Profile

Posted in Administration | 12 Comments »

Using rlwrap to get command line history in SQL*Plus – Video Tutorial

Posted by Kamran Agayev A. on July 4, 2009

By viewing .bashrc file, it wasn’t recorded the “alias line” which I’ve added
The line is as below:

alias rlsqlplus=’rlwrap sqlplus’

 
Additionaly, you can download rlwrap software from below link:
 
P.S. Thanks for Tim for his article

Posted in Administration, Video Tutorials | 15 Comments »