Oracle DBA: November 2010

Monday, November 29, 2010

Scripts

(1) LOCK ACCOUNT
$ vi lockAccount.sql
"lockAccount.sql" [Read only] 12 lines, 358 characters

set echo on
spool /u01/app/oracle/admin/scripts/lockAccount.log
BEGIN
FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
'SYS','SYSTEM') )
LOOP
dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
execute immediate 'alter user ' || item.USERNAME || ' password expire account lock' ;
END LOOP;
END;
/
spool off

(2) CloneRmanRestore
$ vi CloneRmanRestore.sql
"CloneRmanRestore.sql" [Read only] 5 lines, 248 characters

connect "SYS"/"password" as SYSDBA
set echo on
spool /u01/app/oracle/admin/PXXX/scripts/CloneRmanRestore.log
startup nomount pfile="/u01/app/oracle/admin/PXXX/scripts/init.ora";
@/u01/app/oracle/admin/PXXX/scripts/rmanRestoreDatafiles.sql;
~

(3) RMAN - RestoreDatafiles
$ vi rmanRestoreDatafiles.sql
"rmanRestoreDatafiles.sql" [Read only] 29 lines, 1308 characters

set echo off;
set serveroutput on;
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;
variable devicename varchar2(255);
declare
omfname varchar2(512) := NULL;
done boolean;
begin
    dbms_output.put_line(' ');
    dbms_output.put_line(' Allocating device.... ');
    dbms_output.put_line(' Specifying datafiles... ');
       :devicename := dbms_backup_restore.deviceAllocate;
    dbms_output.put_line(' Specifing datafiles... ');
    dbms_backup_restore.restoreSetDataFile;
      dbms_backup_restore.restoreDataFileTo(1, '/u10/oradata/PXXX/system01.dbf', 0, 'SYSTEM');
      dbms_backup_restore.restoreDataFileTo(2, '/u10/oradata/PXXX/sysaux01.dbf', 0, 'SYSAUX');
      dbms_backup_restore.restoreDataFileTo(3, '/u13/oradata/PXXX/undotbs01.dbf', 0, 'UNDOTBS1');
      dbms_backup_restore.restoreDataFileTo(4, '/u10/oradata/PXXX/users01.dbf', 0, 'USERS');
    dbms_output.put_line(' Restoring ... ');
    dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.1.0/db_1/assistants/dbca/templates/Seed_Database.dfb', done);
    if done then
        dbms_output.put_line(' Restore done.');
    else
        dbms_output.put_line(' ORA-XXXX: Restore failed ');
    end if;
    dbms_backup_restore.deviceDeallocate;
end;
/
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;

(4) postDBCreation

$ vi postDBCreation.sql
"postDBCreation.sql" [Read only] 24 lines, 1491 characters

connect "SYS"/"password" as SYSDBA
set echo on
spool /u01/app/oracle/admin/PXXX/scripts/postDBCreation.log
shutdown immediate;
connect "SYS"/"Password" as SYSDBA
startup mount pfile="/u01/app/oracle/admin/PXXX/scripts/init.ora";
alter database archivelog;
alter database open;
connect "SYS"/"Password" as SYSDBA
set echo on
create spfile='/u01/app/oracle/product/11.1.0/db_1/dbs/spfilePXXX.ora' FROM pfile='/u01/app/oracle/admin/PXXX/scripts/init.ora';
shutdown immediate;
connect "SYS"/"Password" as SYSDBA
startup ;
alter user SYSMAN identified by "Password" account unlock;
alter user DBSNMP identified by "Password" account unlock;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
commit;
host /u01/app/oracle/product/11.1.0/db_1/bin/emca -config dbcontrol db -silent -DB_UNIQUE_NAME PXXX -PORT 1521 -EM_HOME /u01/app/oracle/product/11.1.0/db_1 -
LISTENER LISTENER -SERVICE_NAME Pxxx -SYS_PWD password -SID PXXX -ORACLE_HOME /u01/app/oracle/product/11.1.0/db_1 -DBSNMP_PWD Password -HOST server_name -LISTENER_OH /u01/app/oracle/product/11.1.0/db_1 -LOG_FILE /u01/app/oracle/admin/PXXX/scripts/emConfig.log -SYSMAN_PWD Password;
connect "SYS"/"Password" as SYSDBA
spool /u01/app/oracle/admin/PXXX/scripts/postDBCreation.log

(5)
$ vi pxxx-xx5-PXXX-backup.rcv
"pxxx-xx5-PXXX-backup.rcv" 24 lines, 1000 characters

connect target rman_dba/password@PXXX
connect catalog rman_ctlg/password@PYYY1
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 35 DAYS;
configure device type sbt parallelism 1;
allocate channel for maintenance device type 'SBT_TAPE' send
#'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=pxx-xx5,NSR_GROUP=DB_Oracle2_VTL)';
'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=listenerVIP,NSR_GROUP=DB_Oracle2_VTL)';
crosscheck backup;
CROSSCHECK COPY;
delete NOPROMPT obsolete device type SBT_TAPE;
release channel;
run
{
allocate channel t1 type 'SBT_TAPE' send
#'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=pxxx-xx5,NSR_GROUP=DB_Oracle2_VTL)';
'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=listenerVIP,NSR_GROUP=DB_Oracle2_VTL)';
sql "alter system archive log current";
backup current controlfile format '/ctl_%d_%s_%U/';
backup full format '/db_%d_%s_%U/' (database);
backup archivelog all format '/arc_%d_%s_%U/' delete input;
release channel t1;
}
exit

(6)

$ vi postScripts.sql
"postScripts.sql" [Read only] 14 lines, 557 characters

connect "SYS"/"Password" as SYSDBA
set echo on
spool /u01/app/oracle/admin/PXXX/scripts/postScripts.log
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/dbmssml.sql;
execute dbms_datapump_utl.replace_default_dir;
commit;
connect "SYS"/"Password" as SYSDBA
alter session set current_schema=ORDSYS;
@/u01/app/oracle/product/11.1.0/db_1/ord/im/admin/ordlib.sql;
alter session set current_schema=SYS;
connect "SYS"/"Password" as SYSDBA
connect "SYS"/"Password" as SYSDBA
execute ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
spool off

(7)
$ vi pxxx-xx5-PXXX5-cumulative1.rcv
"pxxx-xx5-PXXX5-cumulative1.rcv" 12 lines, 676 characters
connect target rman_dba/password@pxxx5
connect catalog rman_ctlg/password@pyyy1
configure controlfile autobackup on;
#configure channel device type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=pxx-xx5,NSR_GROUP=DB_Oracle2_VTL)';
configure channel device type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=listenerSibVIP,NSR_GROUP=DB_Oracle2_VTL)';
configure device type sbt parallelism 1;
configure default device type to sbt;
#backup incremental level 1 database;
backup incremental level 1 format '/db_%d_%s_%U/' (database);
backup current controlfile format '/ctl_%d_%s_%U/';
backup archivelog all format '/arc_%d_%s_%U/' delete input;
exit;

(8)

Tuesday, November 23, 2010

CronTab

Hi,
Please open a terminal and type crontab -e and press enter. The vi editor will open in command mode, press the I key to enter INSERT mode, Type the following line, substituting the full path for the one shown below, into the vi editor:

00 17 * * * /usr/bin/ksh run.ksh

Press the ESC key to exit INSERT mode and return to command mode, Type :wq to save and exit the file.
You can also specify a user that you want to setup a crontab entry for by doing the following command:

#crontab -e -u <user>

You'll want to replace the <user> with the username which you wish to set the crontab entry for.

Monday, November 22, 2010

Shell Tutorials

http://www.tech-recipes.com/category/unix/unix-shell/korn-shell/

Friday, November 19, 2010

OEM basics

http://onlineappsdba.com/index.php/2009/06/18/oracle-enterprise-manager-oem-basics/

Thursday, November 18, 2010

SQL-FAQ

http://www.orafaq.com/wiki/SQL_FAQ#What_are_the_difference_between_DDL.2C_DML_and_DCL_commands.3F

wait events

The following are the most common causes of wait events:

DB File Scattered Read: This wait event is usually caused by large full table scans. This is normal for DSS systems but is critical for OLTP systems. The DBA should consider the caching of small tables to eliminate file reads. Also, in the OLTP environment, consideration should be given to tuning SQL statements as well.

DB File Sequential Read: A high number of waits for this event indicates possible problems with join operations of SQLs or invocation of non-selective indexes. This wait is caused by a large number of single block reads.

Buffer Busy: This wait is caused by concurrent access to buffers in the buffer cache. This statistic should be correlated with the Buffer Waits section of the AWR report.

Free Buffer: This wait event indicates that Oracle waited many times for a free buffer in the buffer cache. This could be caused by the small size of buffer cache, or a large number of reads which populated the buffer cache with unnecessary data. In this case, the SQLs and buffer contents should be examined. Also, slow work by the Database Writer (DBWR) process could cause such wait event.

Log Buffer Space: This wait event shows that the Log Writer (LGWR) process is not fast enough to free log cache for new blocks. This could be caused by slow log switches, slow disks serving redo logs, or a small size of the redo log buffer.

Latch Free: This wait event is often caused by not using bind variables in SQL statements. This fact is indicated by the library cache latch in the Latches section of the AWR report. There are other latches that can cause this wait event to be high: redo allocation latch , cache buffers LRU chain, cache buffers chain, etc.

The wait events for background processes are separated in the Background Wait Events section. In most cases, Oracle background processes place very little overhead on the system. However, it makes sense to monitor the database’s wait events activity in order to see how they operate.