Month: March 2012

Enable or Disable GATHER_STATS_JOB

Enable GATHER_STATS_JOB:

SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);
PL/SQL procedure successfully completed.

Check job status:

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

Disable GATHER_STATS_JOB:

SQL> exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);
PL/SQL procedure successfully completed.

Check job status:

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

You can run the job manually via DBMS_SCHEDULER:

BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'GATHER_STATS_JOB'
);
END;
/

References:

DISK_ASYNCH_IO

DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans).  If your platform supports asynchronous I/O to disk, Oracle recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.

  • For optimum performance make sure you use asynchronous I/Os.
  • TRUE is the default parameter value for the majority of platforms.

SQL>SHOW PARAMETER disk_asynch_io
NAME TYPE VALUE
------------------------------------ ----------- ------
disk_asynch_io boolean TRUE
SELECT * FROM v$parameter WHERE lower(name) = 'disk_asynch_io' ;

These parameters enable or disable the operating system’s asynchronous I/O facility. They allow query server processes to overlap I/O requests with processing when performing table scans.

Asynchronous operations are currently supported for parallel table scans, hash joins, sorts, and serial table scans. However, this feature can require operating system specific configuration and may not be supported on all platforms.

Source/Reference Links:

Determine OS Block Size

1. Windows Machine
If you use ntfs file system you can use fsutil fsinfo ntfsinfo drivename:  to get information of block size.

c:>fsutil fsinfo ntfsinfo f:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:Documents and SettingsAdministrator>fsutil fsinfo ntfsinfo D:
NTFS Volume Serial Number : 0xc054aec854aec090
Version : 3.1
Number Sectors : 0x000000000c34f28c
Total Clusters : 0x0000000001869e51
Free Clusters : 0x00000000015f0419
Total Reserved : 0x0000000000000000
Bytes Per Sector : 512
Bytes Per Cluster : 4096
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x0000000003528000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x0000000000c34f28
Mft Zone Start : 0x00000000000c3520
Mft Zone End : 0x00000000003cd3e0
C:Documents and SettingsAdministrator>

Plan Oracle Backup Strategy

This article focus on general guidelines that can help to decide:

  • when to perform database backups
  • which parts of a database you should back up
  • what tools Oracle provides for those backups
  • how to configure your database to improve its robustness and make backup and recovery easier

The specifics of the strategy must balance the needs of your restore strategy with questions of cost, resources, personnel and other factors.

1.Protecting the redundancy set

  •  The redundancy set should contain:
    1.  Last backup of the control file and all the datafiles
    2. All archived redo logs generated after the last backup was taken
    3. Duplicates of the current control file and online redo log files, generated by Oracle database multiplexing, operating system mirroring, or both
    4. Copies of configuration files such as the server parameter file, tnsnames.ora, and listener.ora
  • Do not store the redundancy set on the same disk that contains the datafiles, online logs and control files of the database.
  • Make use of a flash recovery area, on a separate device from the working set files and keep it as the ideal location to store backup files and mirrored files.
  • Multiplex the online redo log files and current control file at the database level and the multiplexed files should be on different disks mounted under different disk controllers. Also mirror the online redo logs and current control file at the operating system or hardware level although this is not a substitute for multiplexing at the database level.
  • Archive the redo logs to multiple locations, ideally on different disks if database is running in Archivelog mode.
  • Use operating system or hardware mirroring for the control file.
  • Use operating system or hardware mirroring for the primary datafiles if possible, to avoid having to perform media recovery for simple disk failures.
  • Keep at least one copy of the entire redundancy set including the most recent backup on disk.
  • If the target database is stored on a RAID device, then store the redundancy set on a set of disks that are not in the same RAID device.
  • If you store the redundancy set on tape, then maintain at least two copies of the data to protect against the risk of tape failure.

2. Flash Recovery Area

  • It is recommended that you take advantage of the flash recovery area to store as many backup and recovery -related files as as possible, including disk backups and archived redo logs.
  • Obsolete files no longer needed to meet the recoverability goals and files backed up to tape or other media become eligible for deletion and are deleted when space is needed.

3. ARCHIVELOG and NOARCHIVELOG Mode

  • Database running in ARCHIVELOG mode is preferable to running in NOARCHIVELOG mode because  it provides more flexible recovery options after a data loss, such as point-in-time recovery of the database or some tablespaces.
  • In NOARCHIVELOG mode, one can’t perform online backups of the database and have to shut database down cleanly before backup is taken. Also any data recovery techniques that require the archived redo logs can’t be used.
  • The stored archived redo logs must be managed such that limited disk space is alloted for archived redo logs and older logs which are no longer needed to meet recoverability goals are deleted.

4. Oracle Flashback Features and Restore Points

  • Using the flashback features of Oracle improves the availability of the database when repairing the effects of unwanted database changes. The logical-level flashback features allow the objects not affected to remain available, and Flashback Database allows for faster rewind of the entire database than point-in-time recovery.
  • Incorporating Flashback Database or guaranteed restore points into the strategy requires to enable a flash recovery area, as well as creating guaranteed restore points at the right points in time, or configuring flashback logging.
5. Backup Retention Policy
  • RMAN automates the implementation of a backup retention policy, using the following commands:
    1. CONFIGURE RETENTION POLICY command lets to set the retention policy that will apply to all of your database files by default.
    2. REPORT OBSOLETE command lets to list backups currently on disk that are obsolete under the retention policy.
    3. DELETE OBSOLETE command deletes the files which REPORT OBSOLETE lists as obsolete.
    4. CHANGE… KEEP lets to set a separate retention policy for specific backups, such as long-term backups kept for archival purposes.
  • Redundancy based retention policy
    1. In a redundancy-based retention policy, you a number n is specified such that you always keep at least n distinct backups of each file in the database.
    2. A redundancy-based backup retention policy determines whether a backup is obsolete based on how many backups of a file are currently on disk.
  • Recovery window-based retention policy
    1. In a recovery window-based retention policy, a time interval is specified in the past and keep all backups required to let you perform point-in-time recovery to any point during that window.
    2. A recovery window-based retention policy lets you guarantee that you can perform point-in-time recovery to any point in the past, up to a number of days that you specify.

6. Archiving Older Backups

  • An older backup of datafiles and archived logs is necessary for performing point-in-time recovery to a time before your most recent backup.
  • If your most recent backup is corrupt, you can still recover your database using an older backup and the complete set of archived logs since that older backup.
  • Keep a copy of the database for archival purposes.

7. Frequency of backups

  • Base the frequency of backups on the rate or frequency of database changes such as:
    1. Addition and deletion of tables
    2. Insertions and deletions of rows in existing tables
    3. Updates to data within tables
  • The more frequently your database is updated, the more often you should perform database backups.
  • If database updates are relatively infrequent, then you can make whole database backups infrequently and supplement them with incremental backups.

8.  Backups before and after structural changes

  • If you make any of the following structural changes, then perform a backup of the appropriate portion of your database immediately before and after completing the following changes:
    1. Create or drop a tablespace.
    2. Add or rename a datafile in an existing tablespace.
    3. Add, rename, or drop an online redo log group or member.
  • If you are in NOARCHIVELOG mode, then you must shut down the database and perform a consistent whole database backup after any such change.
  • If you are running in ARCHIVELOG mode, then you must make a control file backup after any such change, using either RMAN’s BACKUP CONTROLFILE command or the SQL ALTER DATABASE BACKUP CONTROLFILE statement.

9. Scheduling Backups for Frequently-Updated Tablespaces

  • If you run in ARCHIVELOG mode, then you can back up an individual tablespace or even a single datafile. You might want to do this for one or more tablespaces that are updated much more often than the rest of your database, as is sometimes the case for the SYSTEM tablespace and automatic undo tablespaces.

10. Backing Up after NOLOGGING Operations

  • When a direct path load is performed to populate a database, no redo data is logged for those database changes. You cannot recover these changes after a restore from backup using conventional media recovery.
  • Likewise, when tables and indexes are created as NOLOGGING, the database does not log redo data for these objects, which means that you cannot recover these objects from existing backups. Therefore, you should back up your datafiles after operations for which no redo data is logged.

11. Exporting Data for Added Protection and Flexibility

  • Oracle database import and export utilities are used to export database objects (tables, stored procedures, and so forth) from databases to be stored as files, and re-import objects from those files.
  • An export provides a logical-level snapshot of the exported objects at the time of the export, as a binary file that can be imported back into the source database or some other database.

12. Preventing the Backup of Online Redo Logs

  • Online redo logs, unlike archived logs, should never be backed up. The chief danger associated by having backups of online redo logs is that you may accidentally restore those backups without meaning to, and corrupt your database.
  • The best method for protecting the online logs against media failure is to multiplex them, with multiple log members in each group, on different disks attached to different disk controllers.

13. Keeping Records of the Hardware and Software Configuration of the Server

  • You should have the following documentation about the hardware configuration:
    1. The name, make, and model of the machine that hosts the database
    2. The version and patch of the operating system
    3. The number of disks and disk controllers
    4. The disk capacity and free space
    5. The names of all datafiles
    6. The name and version of the media management software (if you use a third-party media manager)
  • You should also keep the following documentation about the software configuration:
  1. The name of the database instance (SID)
  2. The database identifier (DBID)
  3. The version and patch release of the Oracle database server
  4. The version and patch release of the networking software
  5. The method (RMAN or user-managed) and frequency of database backups
  6. The method of restore and recovery (RMAN or user-managed
Article based on the Oracle Doc – Planning Backup Strategy

Network Exports/Imports using Network_Link

The NETWORK_LINK parameter identifies a database link to be used as the source for a network export/import. The following database link will be used to demonstrate its use.

CONN / AS SYSDBA
GRANT CREATE DATABASE LINK TO test;
CONN test/test
CREATE DATABASE LINK remote_scott
                    CONNECT TO scott IDENTIFIED BY tiger USING 'DEV';

In the case of exports, the NETWORK_LINK parameter identifies the database link pointing to the source server. The objects are exported from the source server in the normal manner, but written to a directory object on the local server, rather than one on the source server. Both the local and remote users require the EXP_FULL_DATABASE role granted to them.

expdp test/test@db10g tables=SCOTT.EMP network_link=REMOTE_SCOTT
        directory=TEST_DIR dumpfile=EMP.dmp logfile=expdpEMP.log

For imports, the NETWORK_LINK parameter also identifies the database link pointing to the source server. The difference here is the objects are imported directly from the source into the local server without being written to a dump file. Although there is no need for a DUMPFILE parameter, a directory object is still required for the logs associated with the operation. Both the local and remote users require the IMP_FULL_DATABASE role granted to them.

impdp test/test@db10g tables=SCOTT.EMP network_link=REMOTE_SCOTT
     directory=TEST_DIR logfile=impdpSCOTT.log
    remap_schema=SCOTT:TEST
  • Ensure that the exporting user at the source database has the EXP_FULL_DATABASE role.This user must be specified when you create the database link.
  • Ensure that the importing user at the destination database has the IMP_FULL_DATABASE role.
  • Create and test a database link between the source and destination databases.
  • Run the following command, where import_user is the username for the importing user, and db_link is the name of the database link owned by the exporting user: 
    IMPDP import_user/password NETWORK_LINK=db_link FULL=Y;
  • A log file for the import operation is written to the DATA_PUMP_DIR directory. You can discover the location of this directory by running the following command: 
    SQL> select * from dba_directories where DIRECTORY_NAME like 'DATA_PUMP_DIR';

Source/Reference Links:

Switch to ASMM in 10g

Oracle Automatic Shared Memory Management is enabled by setting:

  1. A spfile used to specify init.ora values
  2. sga_target parameter is set to a non-zero value
  3. statistics_level parameter set to to TYPICAL (the default) or ALL
  4. shared_pool_size must be set to a non-zero value

Check different component sizes from v$parameter

select name,value
 from v$parameter
 where name in('shared_pool_size','large_pool_size',
 'java_pool_size','db_cache_size','sga_target');

Switch to ASMM:

$ sqlplus / as sysdba
SQL> alter system set sga_max_size=1G scope=spfile;
System altered.

SQL> alter system set sga_target=500m scope=both;
System altered.
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
System altered.
SQL> ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
System altered.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
System altered.
SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 0;
System altered.
SQL> ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1223540 bytes
Variable Size 738198668 bytes
Database Buffers 327155712 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.

SQL> show parameter sga_target
NAME                TYPE                     VALUE
----------------  ------------------  -------------
sga_target       big integer           500M

SQL> show parameter sga_max_size
NAME                   TYPE                    VALUE
------------------ ------------------ -------------
sga_max_size   big integer         1G

Disable ASSM:

Disabling ASSM can be done by chaning SGA_TARGET parameter to zero value. But we need to set the below parameter with proper sizing when we disable ASMM

1. shared_pool_size
2. large_pool_size
3. java_pool_size
4. db_cache_size

SQL> alter system set sga_target=0 scope=both;
System altered.

We may need to disable ASMM if there is significant amount of memory resizing activity that cause the performance issue in database. This frequent memory resizing might happen when the environment is mixed of DSS and OLTP environment.

Source / Reference Links:

Manage Files On Cloud

Just searched for Amazon S3 clients and found two good file management solutions compared to other such apps.

1. S3 Fox – Mozilla Addon – S3Fox Organizer helps you organize/manage/store your files on Amazon S3. It is easy to install and use as it is integrated into the browser.

2. CloudBerry S3 Explorer – CloudBerry Explorer makes managing files in Amazon S3 storage easy. By providing a user interface to Amazon S3 accounts, files, and buckets, CloudBerry lets you manage your files on cloud just as you would on your own local computer.

Other alternatives:

Reference Links: