How Are The Statisctis Configured?

Many times, where I arrived to a new installation or client, one the first question that I try discovered is about the statistics, if it is working, if it is configured on defaults, so on… In this post I like put the basic query to get in a fast view the minimal information about statistics configuration;

 

SELECT DBMS_STATS.GET_PARAM('CASCADE') FROM DUAL;
SELECT DBMS_STATS.GET_PARAM('DEGREE') FROM DUAL;
SELECT DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') FROM DUAL;
SELECT DBMS_STATS.GET_PARAM('METHOD_OPT') FROM DUAL;
SELECT DBMS_STATS.GET_PARAM('NO_INVALIDATE') FROM DUAL;
SELECT DBMS_STATS.GET_PARAM('GRANULARITY') FROM DUAL;

 

HTH – Antonio NAVARRO

Advertisements

Log_buffer Differs From The Value Set In The SPFILE

Yesterday someone ask about the difference between spfile (in init.ora occurs too) and memory value for Log_buffer parameter. In memory it greater than set in spfile. The problem, of course, it isn’t a problem, is like Oracle works, because of granules. Oracle take chuncks of memory round to the granule, a value depends on operating system. In this case it is round up to the next granule multiple. This is an expected behaviour.

In general, all memory pools in Oracle are round to granule (remember that granule is depends on operating system).

HTH – Antonio NAVARRO

 

 

The Official Way To Know If Spfile Or Init.ora Was Used To Start

The official way is as easy as look at the paremeters, in this case the instance started from a spfile;

 

 
SQL> show parameter ifile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ifile                                file
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\TEMPHOME\112\DBHOME_1\DATAB
                                                 ASE\SPFILETEMPHOME.ORA
SQL>

Remember, the instance follow the next order for startup, if spfile exist will be used as first option, if spfile not exist but init.ora exist then init.ora will be the choiced.

HTH – Antonio NAVARRO

 

How To Delete Service In Windows

Today I like to show a simple “how to” to delete a Windows service. In this case I need delete it because was created with a wrong name (Is a Oracle Listener), after delete it I create again;

First of all, we query the actually status of listener. It is better stop the service before delete;

 
C:\Users\bbdd> query OracleOraDb11g_home1TNSListenerListener_gecae

SERVICE_NAME: OracleOraDb11g_home1TNSListenerListener_sogecae
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 1  STOPPED
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

With the next command and parameter we delete the service OracleOraDb11g_home1TNSListenerListener_gecae;

 
C:\Users\bbdd> delete OracleOraDb11g_home1TNSListenerListener_gecae
[SC] DeleteService SUCCESS

Check status again from sc command and get the next reply;

 
C:\Users\bbdd> query OracleOraDb11g_home1TNSListenerListener_sogecae
[SC] EnumQueryServicesStatus:OpenService FAILED 1060:

The specified service does not exist as an installed service.

HTH – Antonio NAVARRO.

ORA-19905 Error

Today a  coworker ask me about the next error;

ORA-19905: log_archive_format must contain %s, %t and %r

In this case, He was installing a new database, version 11g, the error has been raised when he has started the database with the new parameters for archive. I told him, “please use RTFM”, after a fast look to the doc he has can see the mandatory sets for log_archive_format in 11g. From Oracle Documentation;

Archive log file names must contain each of the elements %s (sequence), %t (thread), and %r (resetlogs ID) to ensure that all archive log file names are unique. If the LOG_ARCHIVE_FORMAT initialization parameter is set in the parameter file, then make sure the parameter value contains the %s, %t, and %r elements.

HTH – Antonio NAVARRO

 

Ora-00600

 

Today I have been reported from the next error in a Oracle 10.2.0.5 version;

ORA-00600: código de error interno  argumentos: [17280]  [1]  [0x570A6ED78]  []  []  []  []  []

Looking at MOS, I have discovered that it is the bug 8449495. The error is raised when client side process die or break the connection, when execute a fetch from SQL which uses pipelines, the server side report this ora-00600.

This bug has been fixed in 11.1.0.6 Release. You can too apply a oneoff using patch 844945. However in this case you can safely ignore the problem because it since report an error when the connection is been closing.

HTH – Antonio NAVARRO

 

Order Used When Importing Objects

In this post I like write some lines about how to import order for differents objects is performed. In the next list I show the order for 10g version. This order has changed sometimes depend on Oracle version. The list is make attend to simplified or reduce the possible errors when execute the import;

1. Tablespaces
2. Profiles
3. Users
4. Roles
5. System Privilege Grants
6. Role Grants
7. Default Roles
8. Tablespace Quotas
9. Resource Costs
10. Rollback Segments
11. Database Links
12. Sequences
13. Snapshots
14. Snapshot Logs
15. Job Queues
16. Refresh Groups
17. Cluster Definitions
18. Tables (also grants,comments, indexes, constraints, auditing)
19. Referential Integrity
20. POSTTABLES actions
21. Synonyms
22. Views
23. Stored Procedures
24. Triggers, Defaults and Auditing

HTH – Antonio NAVARRO