Disable ADDM In 10g

Of course, versions 10 and were normal this support. but there are still plenty of them in production environments. Also many of these are migrated to version 11 or 12, but other many remain as historical. One feature I am observing that is becoming common and depending on license political to customer with Oracle is relative to ADDM. I have received requests to disable this feature / tool.

If you read the manual, it says to put off the ADDM must configure the STATISTICS_LEVEL to BASIC. Which is correct, but it is less clear or does not say that putting this parameter to BASIC, puts disable many other features of tuning that are free. This is put sticks in the wheels.

The solution specific to this version, the following versions this changes, is using a hidden parameter;

ALTER SYSTEM SET “_ADDM_AUTO_ENABLE”=FALSE;

HTH – Antonio NAVARRO

Advertisements

Questions Coffee Break

A typical question in the hour of coffee is that files can be backed up using RMAN and which are not. To simplify this post we will discuss which one can not do backup. We talked hereand version of 12c to have knowledge updated daily. Currently you can not backup the online redo and neither can make backup of pfiles. The remaining files can be done by everyone. Although there are others such as trace files, which could not, at least directly. Accordingly, they could charge as tables and save. Or from the ADRCI make management thereof.

HTH – Antonio NAVARRO

Datafile Headers Aren’t Update When Tablespace Is In Backup Mode

Today I had a discussion with a coworker about its implications for the files associated with a tablespaace when this is put into backup mode. The commented that the data files are not written during or while the tablespace is in backup mode. I believed remember that this was not implying the backup mode. Although I have a Oracle OCP and this is usually a typical exam question not remember exactly the question itself. I‘m getting old too fast. So nothing like reading the manual to refresh the memory (RTFM) .

According to the documentation in this case header data files are not updated until the backup mode is not removed, as is the tablespace or database. This is also to keep track of what you would have to implement recovery in case there was a problem.

HTH – Antonio NAVARRO

Upgrade RMAN Catalog

When I connected to rman catalog for the first time from a new database, I get the next error;

	
RMAN> connect rcvcat rman/rmanpass@mycatalog

connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.03 in RCVCAT database is not current
PL/SQL package RMAN.DBMS_RCVMAN version 11.02.00.03 in RCVCAT database is not current

The problem here is very usual, the catalog version is lower than this new database. You need execute “UPGRADE CATALOG” command, twice times (the second time for confirm). It resolve the issue.

	
RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 11.02.00.04
DBMS_RCVMAN package upgraded to version 11.02.00.04
DBMS_RCVCAT package upgraded to version 11.02.00.04

RMAN> exit

HTH – Antonio NAVARRO

ORA-01078 And LRM-00109 Starting Database (On 12c)

When starting a database, which is in version 12c, aperece the following error;

SQL> startup
ORA-01078: failure in processing system parameters 
LRM-00109: could not open parameter file ‘C:\ORACLE\DB_1\DATABASE\INITSITO.ORA’

It seems that missed init.ora. Fortunately or rather a rule I usually have contfolfile autobackup, so this case is simple bastantante solve the problem. We must connect to RMAN and execute the following commands.

 rman> STARTUP NOMOUNT
 rman> RESTORE SPFILE FROM AUTOBACKUP

HTH – Antonio NAVARRO

Delete Permission On All Schemes

Today I asked from the development team needs a user that already exists can do delete from other tables schemes. Until here all right. Suffice to give you a grant to delete the table you need to delete. the issue is that the tables where they need to delete are created and deleted dynamically, so if today we give permission to delete a table this table morning probably do not exist. And if a new table is created tomorrow, we will have to give the grant of delete to this new entity.

There are several solutions to this problem. So would be the most interesting;

  • i) patch the code that creates the table so dynamic and that of the “grant on new_tab to delete user”.
  • ii) create a job / scheduler every so often to check for new tables that give no permission to delete this user.
  • iii) give permission “delete any table”, however this does not meet the ISO 9075 / ANSI SQL standard, and can be a risk of high security. Since you can delete any schema.

Finally, although I am not quite agree with the solution, has chosen the “Delete Any Table”.

HTH – Antonio NAVARRO

A Way To See The Performance Improvement

I recently migrated a database to another more powerful machine. the issue is that I have a scheduler that reconstructs two indexes every so often, and looking at runtime (in seconds) is a dramatical change. I remains to wait upcoming executions
to see what is trending.

 index_name              When     				Time
----------------------  ---------------------  -----

SUPPLY2_EST_EXECUT_I    04/NOV/2014 10:00:43   4164
SUPPLY_CODEOPERATION_I  04/NOV/2014 10:01:22   3904
SUPPLY2_EST_EXECUT_I    11/NOV/2014 10:00:39   3811
SUPPLY_CODEOPERATION_I  11/NOV/2014 10:01:20   4047
SUPPLY2_EST_EXECUT_I    18/NOV/2014 10:00:40   3869
SUPPLY_CODEOPERATION_I  18/NOV/2014 10:01:23   4290
SUPPLY2_EST_EXECUT_I    25/NOV/2014 10:00:48   4738
SUPPLY_CODEOPERATION_I  25/NOV/2014 10:01:33   4423
SUPPLY2_EST_EXECUT_I    06/ENE/2015 10:00:01     73
SUPPLY_CODEOPERATION_I  06/ENE/2015 10:00:01     29

HTH – Antonio NAVARRO