rman backup on disk script oracle database

Following is the content of offshore_rman.sh for taking rman level 0 backup on sever disk.


mkdir -p /software/rmantemp/offshore_`date +%d-%m-%y`
chmod g+w /software/rmantemp/offshore_`date +%d-%m-%y`

export ORACLE_HOME=/oracle/app/product/11.2.0/db_1
export ORACLE_SID=offshore

$ORACLE_HOME/bin/rman target / <<!

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/software/rmantemp/offshore_`date +%d-%m-%y`/OFFSHORE_DB_CTL_%F'; 
RUN { 
SQL 'ALTER SYSTEM SWITCH LOGFILE'; 
BACKUP INCREMENTAL LEVEL 0 DATABASE FORMAT '/software/rmantemp/offshore_`date +%d-%m-%y`/OFFSHORE_DB_DATAFILE_%u_%s_%p.DBF' TAG 'DATAFILE_BACKUP'; 
BACKUP ARCHIVELOG ALL FORMAT '/software/rmantemp/offshore_`date +%d-%m-%y`/OFFSHORE_DB_ARCHIVELOG_%u_%s_%p.DBF' TAG 'ARCHIVELOG_BACKUP'; 
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE -5'; 
} 
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;


Thanks
A Rahim Khan

Changing Oracle Snapshot Interval and Retention

to check out snapshot settings


select * from dba_hist_wr_control;
--------------------------------------------
DBID:           1225231090
SNAP_INTERVAL:  +00 01:00:00.000000
RETENTION:      +35 00:00:00.000000
TOPNSQL:        DEFAULT


to change snapshot interval and retention period


execute dbms_workload_repository.modify_snapshot_settings( interval => 60, retention => 50400);


to determine appropriate size sysaux table space


/oracle/app/11g/db_1/rdbms/admin/utlsyxsz.sql

Thanks
A Rahim Khan

Temporarily disable data guard using data guard broker

Those are some handy dgmgrl Commands to stop Log Transport in Primary Database and Log Apply in Standby Database.


bash-3.2$ dgmgrl
DGMGRL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration

Configuration - TESTADG

  Protection Mode: MaxPerformance
  Databases:
    prim - Primary database
    stnd - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Continue reading

Data File Rename/Move in Oracle Active Data Guard Environment

Data File rename or move activity is not applied automatically to standby Database even if STANDBY_FILE_ MANAGEMENT is set to AUTO. When STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, use of the following SQL statements is not allowed:


ALTER DATABASE RENAME .. 
ALTER DATABASE ADD/DROP LOGFILE ..
ALTER DATABASE ADD/DROP STANDBY LOGFILE MEMBER ..
ALTER DATABASE CREATE DATAFILE AS ..

Continue reading

Enable Password Never Expire feature in Oracle

First of all, detect profile name of the target user to set password never expire.


SQL> select profile from DBA_USERS where upper(username) = upper('rman');

PROFILE
------------------------------
DEFAULT

Modify target user profile using this .


SQL> alter profile DEFAULT limit password_life_time UNLIMITED;

Profile altered.

Now, check out user expiry_date.


SQL> select username,expiry_date,account_status from dba_users where upper(username) = upper('rman');

USERNAME                       EXPIRY_DA ACCOUNT_STATUS
------------------------------ --------- --------------------------------
RMAN                                     OPEN


Thanks
A Rahim Khan

Automated Rebuild Indexes, Analyze Objects and Gather Statistics | Oracle

Here, I am going to share a way to automate Index Rebuild, Analyze Objects and Gather Statistics jobs.
Stored Procedure to Rebuild All indexes in a specific Schema:


CREATE OR REPLACE PROCEDURE SYS.REBUILD_TABLE_INDEX (SCHEMANAME VARCHAR2)
AS
SQL_STR VARCHAR2(1000);
BEGIN

    FOR CUROSR_INDEX IN (SELECT OWNER,SEGMENT_NAME from SYS.DBA_SEGMENTS
        WHERE SEGMENT_TYPE='INDEX'
        AND OWNER = SCHEMANAME
        ORDER BY SEGMENT_NAME ) LOOP

        BEGIN
        SQL_STR:='ALTER INDEX '||CUROSR_INDEX.OWNER||'.'||CUROSR_INDEX.SEGMENT_NAME ||' REBUILD ONLINE NOLOGGING PARALLEL 8';        
        EXECUTE IMMEDIATE SQL_STR;
        DBMS_OUTPUT.PUT_LINE('SUCCESS : '|| CUROSR_INDEX.OWNER||'.'||CUROSR_INDEX.SEGMENT_NAME);
        EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR : '||CUROSR_INDEX.OWNER||'.'||CUROSR_INDEX.SEGMENT_NAME||':'||SQLERRM);
        END;
 
END LOOP;
END;

Continue reading

DBA_AUTOTASK in Oracle Database

You can query the DBA_AUTOTASK_OPERATION to find out the names and status of the automatic tasks in your database as shown in the following example:


SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED 
auto space advisor                                               ENABLED 
sql tuning advisor                                               ENABLED 

3 rows selected.

Continue reading