Information on Statistics collection on 11gR2

In 11gR2 statistics collection is automatic by default.The statistics will be collected by using an auto job, if the statistics found to be stale for an object or if any DDL command used against the object.

Please see the below for the job details.

select client_name,status,consumer_group,attributes from DBA_AUTOTASK_CLIENT
 
CLIENT_NAME                    STATUS    CONSUMER_GROUP     
------------                   ---------  --------------
auto optimzer stat collection ENABLED ORA$AUTOTASK_STATS_GROUP


The stale percent is determined based on the number of DML operations occured on the table ie., the percentage of number of rows updated or inserted or deleted on the table. By default the threshold limit for the stale percentage is 10%.

You can check this values by using the below query.

select dbms_stats.GET_PREFS ('STALE_PERCENT','OWNER','TABLENAME') from dual.

To check the objects considered for auto stats collection we can check the parameter AUTOSTATS_TARGET.The value of this parameter controls the objects considered for stats collection.

The values:
   'ALL' - Statistics collected for all objects in system
   'ORACLE' - Statistics collected for all Oracle owned objects
   'AUTO' - Oracle decides on which objects to collect statistics

We can check this by using the below query

select dbms_stats.GET_PREFS ('AUTOSTATS_TARGET') from dual;
DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET')                                      
-------------------------------------------
AUTO 

To check the percentage of rows modified or inserted or deleted on the tables you can use the below query

select * from DBA_TAB_MODIFICATIONS where table_owner='SCOTT'
If you need to check the information about stats collection on your tables, you can use the below query

select * from DBA_TAB_STATS_HISTORY where owner='SCOTT'
select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB
where client_name='%stats%';
To enable or disable the Auto stats job please use the below procs.

ENABLE:

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/

DISABLE:

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/

Other useful queries

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%';

SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%';

Select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='auto optimizer stats collection';

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%';


Regards,
Navaneeth

Starting windows services starts database automaticaly and maintain services on Windows

Sometimes starting the Windows Oracle services starts the database automatically. You can check this in Windows registry as mentioned below.

Open the registry with regedit.   

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\oracle_home_name.

There will be a key called:

ORA_SID_AUTOSTART.  SID is your database SID.

This key should be set to TRUE.  If you want to start the database automatically once the service is started.  

If you want to manually start the database set ORA_SID_AUTOSTART to FALSE.  The service will start but not the database.  

There is also an ORA_SID_SHUTDOWN which you want to be TRUE so that if the server is shut down the service will shutdown the database automatically.

For Deleting a service

you can use 

> oradim or sc delete

 

Regards,

Navaneeth

Incremental backup size of 4th week is Huge

This is tested in production environment. There was an issue in the incremental backup of a 10.2.0.3 database server. The every months 4th week cumulative incremental backup size is huge(comes around 400 Gigs).

Database size - 335 GB.

Backup scenario:

> 1st sunday of every month - Level 0 Incremental backup

    script => Backup incremental level = 0 database tag='Complete_Backup';

> Every Sunday of the month - Level 1 Cumulative Incremental backup

   script => Backup incremental level = 1 cumulative database tag='Cumulative_Backup';

Once the backup is completed the backup's will be moved from the local drive to tape drive(ie: once in a week)

The issue was because of the Incremental backups are moved from its original location to tape drive. It was proved by the below query.

select recid, file#, to_char(creation_change#) create#, incremental_level lvl, to_char(incremental_change#) inc#,
to_char(checkpoint_change#) ckp#, datafile_blocks BLKS, block_size blksz, blocks_read READ,
round((blocks_read/datafile_blocks) * 100,2)  READpc,
blocks WRTN, round((blocks/datafile_blocks)*100,2) WRTNpc,
completion_time, used_change_tracking bct
from v$backup_datafile where
completion_time > to_date('17-Jun-11 12:32:00', 'dd-mon-rr hh24:mi:ss ')
and file# > 0;

 

 If we see the query output we can find that for several Datafiles the 'incremental_change#' is 0 or the same as the Datafile Creation SCN. This means that the RMAN Incremental Backup cannot find a corresponding Level 0 Backup and takes a full Backup of these Datafiles. Therefore the Size of the Backup is growing that much.

So to avoid this we have to ensure that Level 0 Backup from Week 1 for all Datafiles is available for all 4 Weeks (until the next Level 0 Backup is taken).Even do not move this Backup outside RMAN - RMAN must be able to access the Level 0 Backup at its cataloged Location.

We tested by not moving the Incremental Level 0 backup until the 4th weeks backup was taken from its original location. After this the 4th week backup was fine and was creating the backup size of normal.

Hope this helps !

Regards,

Navaneeth

Rename/Move a datafile in the primary and propagate the changes in the standby database.

Rename a datafile in the primary database and manually propagate the changes to the standby database.

 in PRIMARY

1. Set STANDBY_FILE_MANAGEMENT=MANUAL on both Primary and Standby Database.

SQL> show parameter standby_file_manag

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      AUTO

SQL>  select name,value from v$parameter where name like 'db_unique_name';

NAME                VALUE
-----------------  ---------------
db_unique_name      orcl11g

SQL>
SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL>  show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      MANUAL

  

in standby

SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL> select name,value from v$parameter where name like 'db_unique_name';

NAME                VALUE
------------------ ------------
db_unique_name      11gstdby

SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      MANUAL

 

in PRIMARY

2. Take the Tablespace offline on the Primary Database:

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         2 UNDOTBS1                       YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 FDB                            YES NO  YES

6 rows selected.


SQL> alter tablespace users offline;

Tablespace altered.

3. Rename Datafile on Primary Site:

   copy the file using OS command to the new location


4. Rename the Datafile in the Primary Database.

SQL> alter tablespace users rename datafile 'D:\app\nvcb\oradata\orcl11g\USERS01.DBF' TO 'D:\app\nvcb\oradata\orcl11g_1\USERS01.DBF';

Tablespace altered.

5. Bring the Tablespace back online:

SQL> ALTER TABLESPACE USERS ONLINE;

Tablespace altered.

in standby

1. Stop Redo Apply on Standby Database.

SQL> recover managed standby database cancel;
Media recovery complete.

2. Shutdown the Standby Database.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

3. Move the Datafile to the new location at the Standby site.

Copy the file using OS command to the new location.

4. Start and mount the Standby Database.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             209715664 bytes
Database Buffers          318767104 bytes
Redo Buffers                5804032 bytes
Database mounted.

5.Rename the Datafile in the Standby Database control file.

SQL> alter database rename file 'D:\app\nvcb\oradata\11gstdby\USERS01.DBF' to 'D:\app\nvcb\oradata\11gstdby_1\USERS01.DBF';

Database altered.

6. On the Standby Database, restart Redo Apply:

SQL> recover managed standby database disconnect from session;
Media recovery complete.


7. Set STANDBY_FILE_MANAGEMENT=AUTO on both Primary and Standby Database.


SQL> alter system set standby_file_management=auto;

System altered.

SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      AUTO

SQL>  select file#,name from v$datafile where name like '%USERS%';

     FILE#   NAME

----------  -----------------------------------------------
          4 D:\APP\nvcb\ORADATA\11GSTDBY_1\USERS01.DBF

in Primary

SQL> alter system set standby_file_management=auto;

System altered.

SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      AUTO


SQL>  select file#,name from v$datafile where name like '%USERS%';

     FILE# NAME
---------- --------------------------------------

         4 D:\APP\NAV6COB\ORADATA\ORCL11G_1\USERS01.DBF

Regards,

Navaneeth

Moving DATAFILE(S) with the Database OPEN in 10.2.0.3

A datafile has been created in a wrong location by mistake, so it has to be moved to a valid location without down time. Its a 10.2.0.3 production environment.

Renaming or Moving DATAFILE(S) with the Database OPEN
  
1. Check how many datafiles are associated with the tablespace.

  
SQL>SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TS_BCH_DE';  

FILE_NAME                                  STATUS
----------------------------------------------------------------------------

F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_01.DBF                                               AVAILABLE
F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_02.DBF                                               AVAILABLE
F:\ORACLE10G\STT\ORADATA\TS_BCH_DE_03.DBF                                                      AVAILABLE

2. Make sure that all datafiles returned have the status AVAILABLE.

3. Make the tablespace is read only.

     
SQL> ALTER TABLESPACE TS_BCH_DE READ ONLY;

Tablespace altered.


4. Make sure that the tablespace is defined as read only in the data dictionary.
 
SQL>  SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TS_BCH_DE';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TS_BCH_DE                    READ ONLY

SQL>


5. Copy the datafile(s) to the new location using the operating system copy command.Once the datafile(s) have been copied to the new location compare the sizes of the datafiles.Make sure that the sizes match.

6. Once the datafiles have been copied to the new location alter the tablespace offline.


SQL> ALTER TABLESPACE TS_BCH_DE OFFLINE;

Tablespace altered.


7. Once the tablespace is offline you will need to rename the datafile(s) to the new location.This updates the entry for the datafile(s) in the controlfile.

     
SQL> ALTER DATABASE RENAME FILE
  2                   'F:\ORACLE10G\STT\ORADATA\TS_BCH_DE_03.DBF'
  3                   TO
  4                   'F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_03.DBF';

Database altered.


8. Once the alter database statement has been processed for the datafile(s) you can bring the tablespace online.

      
SQL> ALTER TABLESPACE TS_BCH_DE ONLINE;

Tablespace altered.


9. After you bring the tablespace back online you can make the tablespace read/write again.

      
SQL> ALTER TABLESPACE TS_BCH_DE READ WRITE;

Tablespace altered.

10. You can check the status of the tablespace to make sure it is read/write.You can also verify that the controlfile has been updated by doing the following:

        > ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

the above command creates the readable format of the content of the controlfile, check whether the datafile location is updated

         or

Optionally, we can query V$DATAFILE, which gets information from the controlfile as well.


SQL> select file#,status,enabled,name from v$datafile where name like '%TS_BCH_DE%';

     FILE# STATUS  ENABLED    NAME
---------- ------- ---------- ---------------------------------------------------------------------
        28 ONLINE  READ WRITE F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_01.DBF
        85 ONLINE  READ WRITE F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_02.DBF
        90 ONLINE  READ WRITE F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_03.DBF



11. Remove the datafile(s) from the old location at the O/S level.

Regards,

Navaneeth

Grant privilege on V$ tables to other users

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

SQL> select owner,object_name,object_type from dba_objects where object_name = 'V$SESSION';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
PUBLIC                         V$SESSION                      SYNONYM

SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = 'V$SESSION';

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$SESSION

SQL> grant select on v_$session to scott;

Grant succeeded.

All the V$ views are synonyms, we can use the above method to query and find the corresponding tables and grant access to other users.

Regards,

Navaneeth

TNS Entry on 2 node RAC database and Listener Entry

************ Tnsnames.ora ********

INSPT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
    )
  )

DB2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
      (INSTANCE_NAME = DB2)
    )
  )

DB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
      (INSTANCE_NAME = DB1)
    )
  )

LISTENERS_DB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
  )

CLUSTER1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 173.243.1.235)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pedb)
    )
  )

DB_REPORT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
    )
  )




 ************ Listener db1 **************


# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (SID_NAME = DB1)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
  )