How to monitor DROP USER statement is hung or running?

Hi All,

We were doing a refresh activity in one of our data warehouse environments and we had to drop the existing schema in that database and recreate that user and fill with the latest dump.

So as usually i issued the DROP USER command and it had been running after 30-40 minutes. I worried and was trying to check whether that’s running, hung or any other anomaly was happening…

I didn’t see anything in the v$session_longops even but i didn’t see the record corresponding to this drop operation.

Then i used another method. Of course it doesn’t looks elegant but i liked it very much.

I opened another session and was just issuing

select count(0) from dba_objects where owner=<OWNER>;

And i was keep on issuing it…see the result below

10:20:16 SYS@CLDTRNG2 > select count(0) from dba_objects where owner=<OWNER>;

  COUNT(0)
———-
    129656

10:20:50 SYS@CLDTRNG2 > /

  COUNT(0)
———-
    129647

10:20:53 SYS@CLDTRNG2 > /

  COUNT(0)
———-
    129643

10:20:55 SYS@CLDTRNG2 > /

  COUNT(0)
———-
    126252

10:27:15 SYS@CLDTRNG2 > /

  COUNT(0)
———-
    103607

10:33:11 SYS@CLDTRNG2 > /

  COUNT(0)
———-
    103607

10:33:13 SYS@CLDTRNG2 > /

  COUNT(0)
———-
     95615

So, when it becomes 0, we can say the user is dropped. The other session too reports that “USER DROPPED”.

This just help us to realize that DROP USER command is working perfectly in the background and will save us from standing on toes 🙂

Hope this helps.

I’m welcome to your feedback and suggestions.

CSM

Backup optimization in RMAN

I came across a parameter BACKUP OPTIMIZATION in RMAN when i listed all of them down in my environment through SHOW ALL command.

The documentation just says it enables backup optimization when we set it to ON  and disables when it’s OFF. A very unsatisfying answer 🙂

So I researched a bit  more on this to find out what’s it. Some people said it’s same as BCT (I have some good articles written about BCT already Artcle1, Article2 and Aritcle3). I said “I don’t think so!!” and thanks to Google/Search Engines(These days they became interchangeable words) – Otherwise we would have listened/forced to listen to every misconceptions expressed by every idiot as getting proofs about their false hypothesis would be very difficult – NO OFFENSE 🙂

So here is the answer

Backup Optimization Algorithm

Datafile

With a recovery window-based retention policy:

For backups to tape, RMAN takes another backup of a file, even if a backup of an identical file exists, if the most recent backup is older than the configured recovery window. This is done to allow media to be recycled after the media expires.

For backups to disk, RMAN skips taking the backup if an identical file is available from a backup on disk, even if that backup is older than the beginning of the recovery window. The retention policy causes RMAN to retain the old backup for as long as it is needed.

With a redundancy-based retention policy:

RMAN sets r=1 by default and searches for values of n in this order of precedence (that is, values higher on the list override values lower on the list):

  1. If CONFIGURE RETENTION POLICY TO REDUNDANCY r is enabled, then RMAN only skips datafiles when n=r+1 backups exist.

  2. BACKUP ... COPIES n

  3. SET BACKUP COPIES n

  4. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE ... TO n

RMAN skips backup only if at least n backups of an identical file exist on the specified device. If RMAN does not skip the backup, then it makes the backup exactly as specified.

Archived log

By default, n=1. RMAN searches for values of n in this order of precedence (that is, values higher on the list override values lower on the list):

  1. BACKUP ... COPIES n

  2. SET BACKUP COPIES n

  3. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE ... TO n

RMAN skips backup only if at least n backups of an identical file exist on the specified device. If RMAN does not skip the backup, then it makes the backup exactly as specified.

Backup set

By default, n=1. RMAN searches for other values of n in this order of precedence (that is, values higher on the list override values lower on the list):

  1. BACKUP ... COPIES n

  2. SET BACKUP COPIES n

RMAN skips backup only if at least n backups of an identical file exist on the specified device. If RMAN does not skip the backup, then it makes the backup exactly as specified.

How to enable and disable backup optimization??

By default, backup optimization is configured to OFF. To enable backup optimization, run the following command:

CONFIGURE BACKUP OPTIMIZATION ON;

To disable backup optimization, run the following command:

CONFIGURE BACKUP OPTIMIZATION OFF;

To clear the current backup optimization setting, that is, return backup optimization to its default setting of OFF, run this command:

CONFIGURE BACKUP OPTIMIZATION CLEAR;

How to check the current status?

You can use SHOW BACKUP OPTIMIZATION to view the current settings of backup optimization as configured with the CONFIGURE BACKUP OPTIMIZATION command. After connecting to the target database and recovery catalog (if you use one), issue the SHOWBACKUP OPTIMIZATION command. For example, enter:

SHOW BACKUP OPTIMIZATION;

Sample output for SHOW BACKUP OPTIMIZATION follows:

RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION ON;

How to grant privileges on V$ tables??

Hi Data lovers,

I was testing something in my 11.2.0.3 database today and as part of that i created a user and he needs select on some of the v$views.

i tried granting privileges directly through the below command

SQL> grant select on v$archived_log to csm;
grant select on v$archived_log to csm
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

The error message looks so weird for me. The “V$” are not views really?

After going through the documentation i found a fact that they’re called as views while referring to them but they’re actually synonyms. So the error message is legitimate but not weird as i thought.

So i accomplished my purpose by doing this

SQL> grant select on v_$archived_log to csm;

Grant succeeded.

What’s this “V_$” thing? The actual V$ views have the names like V_$ARCHIVED_LOG, V_$SESSION etc…the V$ representation is just a synonym of the original view.

So grants on the original view solved the problem.

p.s. I’m still not sure why Oracle has synonyms with these names instead of just leaving the view names intact and publishing them tot the world…Then we might be calling them as V_$ views instead of v$ views…Any historical reason, probably 🙂

CSM

Can we start the Database with both the spfile and pfile lost??

Hi Data lovers,

Hope you guys are having a good time with your databases.

A scenario i came across this week while playing with one of my test Databases is, i lost both my spfile and pfiles.

so what to do in this case to bring my database up? i already had a blog post on recovering some of the parameters, at least, in such scenarios here.

Now i found another option to start my database, through RMAN.

Yes, RMAN can be used for starting the Oracle instances when we lost all the parameter files. It use default parameters and startup the instance.

Please see the exercise below.

[DB11203-11.2.0 @ mymachine : /home/oracle] sql

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 12 19:27:13 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /11.2.0.3/dbs/spfileDB11203.or
                                                 a
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now we’re renaming the spfile and trying to start the database.

[DB11203-11.2.0 @ mymachine : /home/oracle] home
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3] cd dbs
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3/dbs] ls
hc_DB11203.dat  init.ora  lkDB11203  orapwDB11203  spfileDB11203.ora
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3/dbs] mv spfileDB11203.ora spfileDB11203.ora_

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3/dbs] sql

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 12 19:27:55 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/11.2.0.3/dbs/initDB11203.ora’
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now try with RMAN

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3/dbs] rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Wed Jun 12 19:28:20 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/11.2.0.3/dbs/initDB11203.ora’

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 06/12/2013 19:29:46
ORA-00205: error in identifying control file, check alert log for more info

This error is because, as per default settings the Oracle instance expects control files to be under $ORACLE_HOME/dbs location. Now i identified the location of the control files and copied them to this location.

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3/dbs] cd /u01/app/oracle/database/DB11203/
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203]
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] ls
control01.ctl  control02.ctl  example01.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] cp control01.ctl control02.ctl /u01/app/oracle/product/11.2.0/11.2.0.3/dbs
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] sql

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 12 19:33:00 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now try it again with RMAN

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Wed Jun 12 19:33:09 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup

Oracle instance started
database mounted
database opened

Total System Global Area     939495424 bytes

Fixed Size                     2233960 bytes
Variable Size                268437912 bytes
Database Buffers             662700032 bytes
Redo Buffers                   6123520 bytes

RMAN> exit

Please feel free to comment/provide your feedback.

CSM