Oracle RMAN Backup Script In Oracle Linux

This is rman.sh

ORACLE_SID=ITCAPPS; export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
rman target / log=/home/oracle/rman.log << EOF
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt expired archivelog all;
sql 'alter system switch logfile';
backup database format '/disk1/backup/full_%U.bkp';
delete noprompt obsolete;
EOF

And this is RMAN configuration,

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ITCAPPS are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/disk1/backup/controlfile_autobackup_%F.bkp';
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_ITCAPPS.f'; # default
Advertisements

Oracle Database in Oracle Linux Cannot Start / Open

Today I mess around with the Oracle database that can not start and open. I get some error messages from various places such as alert.log, sqlplus, rman.

These errors are,

ORA-03110: end-of-file on communication channel
ORA-03135: connection lost contact
ORA-00845: MEMORY_TARGET not supported on this system
ORA-16038: log 3 sequence# 400 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ITCAPPS/redo03.log'
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-00257: archiver error. Connect internal only, until freed.

I have do this step :
1. replace the  current spfile with the old one
2. startup with pfile
3. restore and recover database using rman

But that no help at all.

And the real solution is to enlarge the capacity of the folder where the archivelog is

Oracle Parameter optimizer_index_cost_adj Performance

From Oracle Documentation,

Default value 100
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values 1 to 10000

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

The default value 100 and 0 for OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING respectively are set for Data Warehouse System.

For OLTP system, setting 10 to OPTIMIZER_INDEX_COST_ADJ and 90 to OPTIMIZER_INDEX_CACHING will perform good result.

Oracle Parameter db_block_checksum Performance

From Oracle Documentation,

Default value TYPICAL
Modifiable ALTER SYSTEM
Range of values OFF | TYPICAL | FULL

DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read – only if this parameter is TYPICAL or FULL and the last write of the block stored a checksum. In FULL mode, Oracle also verifies the checksum before a change application from update/delete statements and recomputes it after the change is applied. In addition, Oracle gives every log block a checksum before writing it to the current log.

If this parameter is set to OFF, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces.

Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. If set to FULL, DB_BLOCK_CHECKSUM also catches in-memory corruptions and stops them from making it to the disk. Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead. In the FULL mode it causes 4% to 5% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL. For backward compatibility we preserve use of TRUE (implying TYPICAL) and FALSE (implying OFF) values.

Note: If your application is I/O intensive and you are short on CPU capacity, then you might want to disable it.

Cannot Compile Oracle Function ORA-04021

I was get this error when I alter and compile one function

ORA-04021: timeout occurred while waiting to lock object

First try, I use this query to find what cause this error

SELECT p.spid “Thread”,
s.sid “SID-Top Sessions”,
SUBSTR (s.osuser, 1, 15) “OS User”,
SUBSTR (s.program, 1, 25) “Program Running”,
s.username
FROM v$process p, v$session s
WHERE p.addr = s.paddr
ORDER BY SUBSTR (s.osuser, 1, 15)

Then I use

SELECT * FROM v$access where object = ‘[function_name]’

, the query took lonq execution. So, I use the simple query

SELECT * FROM v$access

then I find and now I can see who is locking the function. Just kill the user and I can compile the function again

alter function [function_name] compile;

How To See Oracle Database Parameter

You can run this sql to see Oracle Database Parameter :

SELECT name,
value,
isdefault default_value,
description,
isses_modifiable session_modifiable,
issys_modifiable system_modifiable,
isdeprecated deprecated,
update_comment
FROM v$parameter
ORDER BY name

ISDEFAULT Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)
ISSES_MODIFIABLE Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)
ISSYS_MODIFIABLE Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:
IMMEDIATE – Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.
DEFERRED – Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.
FALSE – Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.
ISINSTANCE_MODIFIABLE For parameters that can be changed with ALTER SYSTEM, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If the ISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.
ISMODIFIED Indicates whether the parameter has been modified after instance startup:
MODIFIED – Parameter has been modified with ALTER SESSION
SYSTEM_MOD – Parameter has been modified with ALTER SYSTEM (which causes all the currently logged in sessions’ values to be modified)
FALSE – Parameter has not been modified after instance startup
ISADJUSTED Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)
ISDEPRECATED Indicates whether the parameter has been deprecated (TRUE) or not (FALSE)

And this sql to compare Oracle Database Parameter between 2 database :

SELECT a.name mdn_name, b.name jkt_name, a.VALUE mdn_value, b.VALUE jkt_value
FROM v$parameter a, v$parameter@db2 b
WHERE a.name(+) = b.name
ORDER BY a.name

Create Oracle Schedule Backup Script on Oracle Linux :

  1. create backup directory for USERME

    CREATE OR REPLACE DIRECTORY BCK_DIR AS ‘/home/oracle/bck’;
    GRANT READ, WRITE ON DIRECTORY SYS.BCK_DIR TO EXP_FULL_DATABASE;
    GRANT READ, WRITE ON DIRECTORY SYS.BCK_DIR TO IMP_FULL_DATABASE;

  2. using vi to create script

    #!/bin/bash
    clear
    #echo “Hello, linux world from pandazen world.”
    echo “backup pandazen data with script on Oracle Linux”
    NOW=$(date +”%Y%m%d”)
    FILE=”pandazen$NOW”
    echo $FILE

    #export data pump with compression
    expdp USERME/<PASSWORD> dumpfile=$FILE.dmp logfile=$FILE.log directory=BCK_DIR compression=all

    #archive with tar
    tar -cf $FILE.tar $FILE.dmp $FILE.log

    #compress again with gzip
    gzip $FILE.tar
    [ESC]
    :wq

  3. using crontab to schedule it

    crontab -e
    0 22 * * * /home/oracle/script/bckmis.sh
    [ESC]
    :wq