Multiplexing redo log files.

[oracle@DB ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 30 17:59:02 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SQL> SELECT GROUP#,TYPE, MEMBER FROM GV$LOGFILE;

    GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
         3 ONLINE
+DG_REDO/ERPDB/ONLINELOG/group_3.263.911340093

         2 ONLINE
+DG_REDO/ERPDB/ONLINELOG/group_2.262.911340093

         1 ONLINE
+DG_REDO/ERPDB/ONLINELOG/group_1.261.911340093


SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1      39979        100 NO  ACTIVE
         2          1      39980        100 NO  ACTIVE
         3          1      39981        100 NO  CURRENT

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 ('+DG_DATA/ERPDB/ONLINELOG/redo-05-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-05-02.log') SIZE 1G,
GROUP 6 ('+DG_DATA/ERPDB/ONLINELOG/redo-06-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-06-02.log') SIZE 1G;
  2    3

Database altered.

SQL> SQL>
SQL>
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1      39979        100 NO  ACTIVE
         2          1      39980        100 NO  ACTIVE
         3          1      39981        100 NO  CURRENT
         5          1          0       1024 YES UNUSED
         6          1          0       1024 YES UNUSED

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE ;

System altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1      39979        100 NO  INACTIVE
         2          1      39980        100 NO  INACTIVE
         3          1      39981        100 NO  INACTIVE
         5          1      39982       1024 NO  ACTIVE
         6          1      39983       1024 NO  CURRENT

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 1 ('+DG_DATA/ERPDB/ONLINELOG/redo-01-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-01-02.log') SIZE 1G,
GROUP 2 ('+DG_DATA/ERPDB/ONLINELOG/redo-02-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-02-02.log') SIZE 1G;
  2    3

Database altered.

SQL> SQL>
SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 3 ('+DG_DATA/ERPDB/ONLINELOG/redo-03-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-03-02.log') SIZE 1G,
GROUP 4 ('+DG_DATA/ERPDB/ONLINELOG/redo-04-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-04-02.log') SIZE 1G;  2    3

Database altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1          0       1024 YES UNUSED
         2          1          0       1024 YES UNUSED
         3          1          0       1024 YES UNUSED
         4          1          0       1024 YES UNUSED
         5          1      39982       1024 NO  ACTIVE
         6          1      39983       1024 NO  CURRENT

6 rows selected.

SQL>

Warning: log write elapsed time 1288ms, size 78KB

This warning message will be generated only if the log write time is more than 500 ms. this is a storage performance issue, either the storage has to get faster, or the application has to commit less. So check the performance of storage and analyze the commit frequency of application.

erpdcdb02$ cat erpdcdb2_lgwr_5046526.trc
Trace file /u01/app/oracle/diag/rdbms/erpdcdb/erpdcdb2/trace/erpdcdb2_lgwr_5046526.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing option
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    AIX
Node name:      erpdcdb02
Release:        1
Version:        7
Machine:        00F8FB754C00
Instance name: erpdcdb2
Redo thread mounted by this instance: 2
Oracle process number: 23
Unix process pid: 5046526, image: oracle@erpdcdb02 (LGWR)


*** 2016-10-27 13:47:38.631
*** SESSION ID:(10811.1) 2016-10-27 13:47:38.631
*** CLIENT ID:() 2016-10-27 13:47:38.631
*** SERVICE NAME:(SYS$BACKGROUND) 2016-10-27 13:47:38.631
*** MODULE NAME:() 2016-10-27 13:47:38.631
*** ACTION NAME:() 2016-10-27 13:47:38.631

Warning: log write elapsed time 1288ms, size 78KB
kcrfw_update_adaptive_sync_mode: post->poll long#=20 sync#=49 sync=299 poll=10019 rw=416 ack=13 min_sleep=10019

*** 2016-10-28 06:50:44.380
Log file sync switching to polling
Current scheduling delay is 1 usec
Current approximate redo synch write rate is 16 per sec
kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=187 switch_sched_delay=1 current_sync_count_delta=0 switch_sync_count_delta=49

*** 2016-10-28 07:12:28.017
Log file sync switching to post/wait
Current approximate redo synch write rate is 0 per sec
kcrfw_update_adaptive_sync_mode: post->poll long#=6 sync#=16 sync=172 poll=10019 rw=779 ack=0 min_sleep=10019

*** 2016-10-29 01:39:22.838
Log file sync switching to polling
Current scheduling delay is 250 usec
Current approximate redo synch write rate is 5 per sec
kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=0 switch_sched_delay=250 current_sync_count_delta=0 switch_sync_count_delta=16

*** 2016-10-29 01:52:14.212
Log file sync switching to post/wait
Current approximate redo synch write rate is 0 per sec

Opatch version check failed in New environment.

opatch version is success but failed to apply patch due to Oracle bug. Resolve this bug by create .patch_storage directory in the GI Home manually as root user:

bash-4.2# opatch auto /u02/stage/psu_oct_2016/24436346 -ocmrf /u02/stage/psu_oct_2016/ocm.rsp
Executing /u01/app/11.2.0/grid/perl/bin/perl /u01/app/oracle/product/11.2.0/db_1/OPatch/crs/patch11203.pl -patchdir /u02/stage/psu_oct_2016 -patchn 24436346 -ocmrf /u02/stage/psu_oct_2016/ocm.rsp -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatchauto2016-10-29_04-19-53.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatchauto2016-10-29_04-19-53.report.log

2016-10-29 04:19:53: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
The opatch minimum version  check for patch /u02/stage/psu_oct_2016/24436346/24006111 failed  for /u01/app/11.2.0/grid
The opatch minimum version  check for patch /u02/stage/psu_oct_2016/24436346/23054319 failed  for /u01/app/11.2.0/grid
The opatch minimum version  check for patch /u02/stage/psu_oct_2016/24436346/22502505 failed  for /u01/app/11.2.0/grid
Opatch version check failed for oracle home  /u01/app/11.2.0/grid
Opatch version  check failed
ERROR: update the opatch version for the failed homes and retry

opatch auto failed.
bash-4.2#
bash-4.2# opatch version
OPatch Version: 11.2.0.3.15

OPatch succeeded.
bash-4.2# cd /u01/app/11.2.0/grid/
bash-4.2#
bash-4.2# pwd
/u01/app/11.2.0/grid
bash-4.2#
bash-4.2# whoami
root
bash-4.2# mkdir .patch_storage
bash-4.2# chown grid:oinstall .patch_storage
bash-4.2#

How to find device information of ASM Disks.

1. Find out the asm Disk list using oralceasm listdisks command.
2. Find the device mapping information using oracleasm  querydisk command.
3. Find out the device using ls -l command.

[root@sbdbdr01 ~]# oracleasm listdisks
DB_ORCL_DATA_ASM_01
DB_ORCL_DATA_ASM_02
DB_ORCL_DATA_ASM_03
DB_ORCL_DATA_ASM_04
DB_ORCL_DATA_ASM_05
DB_ORCL_FRA_ASM_01
DB_ORCL_FRA_ASM_02
DB_ORCL_FRA_ASM_03
DB_ORCL_FRA_ASM_04
DB_ORCL_FRA_ASM_05
DB_ORCL_FRA_ASM_06
[root@sbdbdr01 ~]#
[root@sbdbdr01 ~]# oracleasm querydisk -d DB_ORCL_DATA_ASM_05
Disk "DB_ORCL_DATA_ASM_05" is a valid ASM disk on device [8, 97]
[root@sbdbdr01 ~]#
[root@sbdbdr01 ~]#
[root@sbdbdr01 ~]# ls -l /dev/* | grep 8, | grep 97
brw-rw----  1 root disk      8,  97 Oct 23 13:49 /dev/sdg1
[root@sbdbdr01 ~]#

How to drop ASM Disk.

[grid@sbdbdr01 ~]$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 23 12:47:18 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option

SQL> SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN disk_group_name        FORMAT a25           HEAD 'Disk Group Name'
COLUMN disk_file_path         FORMAT a30           HEAD 'Path'
COLUMN disk_file_name         FORMAT a20           HEAD 'File Name'
COLUMN disk_file_fail_group   FORMAT a20           HEAD 'Fail Group'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

BREAK ON report ON disk_group_name SKIP 1

COMPUTE sum LABEL ""              OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report

SELECT
    NVL(a.name, '[CANDIDATE]')                       disk_group_name
  , b.name                                           disk_file_name
  , b.failgroup                                      disk_file_fail_group
  , b.total_mb                                       total_mb
  , (b.total_mb - b.free_mb)                         used_mb
FROM
    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
    a.name;
/SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> columns cleared
SQL> breaks cleared
SQL> computes cleared
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10
Disk Group Name           File Name            Fail Group           File Size (MB) Used Size (MB)
------------------------- -------------------- -------------------- -------------- --------------
DATA                      DB_ORCL_DATA_ASM_05  DB_ORCL_DATA_ASM_05          49,151         23,281
                          DB_ORCL_DATA_ASM_04  DB_ORCL_DATA_ASM_04          49,151         23,280
                          DB_ORCL_DATA_ASM_03  DB_ORCL_DATA_ASM_03          49,151         23,283
                          DB_ORCL_DATA_ASM_02  DB_ORCL_DATA_ASM_02          49,151         23,284
                          DB_ORCL_DATA_ASM_01  DB_ORCL_DATA_ASM_01          49,151         23,282
*************************                                           -------------- --------------
                                                                           245,755        116,410

FRA                       DB_ORCL_FRA_ASM_04   DB_ORCL_FRA_ASM_04           23,551          7,071
                          DB_ORCL_FRA_ASM_03   DB_ORCL_FRA_ASM_03           23,551          7,073
                          DB_ORCL_FRA_ASM_02   DB_ORCL_FRA_ASM_02           23,551          7,074
                          DB_ORCL_FRA_ASM_01   DB_ORCL_FRA_ASM_01           23,551          7,077
*************************                                           -------------- --------------
                                                                            94,204         28,295

RMAN_BKP                  DB_ORCL_FRA_ASM_05   DB_ORCL_FRA_ASM_05           23,551             59
*************************                                           -------------- --------------
                                                                            23,551             59

[CANDIDATE]                                                                      0              0
*************************                                           -------------- --------------
                                                                                 0              0

                                                                    -------------- --------------
Grand Total:                                                               363,510        144,764

11 rows selected.
SQL> select * from v$asm_operation ;

no rows selected

SQL> ALTER DISKGROUP DATA DROP DISK DB_ORCL_DATA_ASM_05;

Diskgroup altered.

SQL> select * from v$asm_operation ;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           1 REBAL RUN           1          1        340      33291       5431           6

SQL>

ORA-20005: object statistics are locked

Cause: Gather statistics on table that has statistics locked.

Solution: Find out the table of index and unlock the Gather statistics.


SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS ('CBS_USER','IDX_ACCOUNTS_ACC_NUM');
BEGIN DBMS_STATS.GATHER_INDEX_STATS ('CBS_USER','IDX_ACCOUNTS_ACC_NUM'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 18379
ORA-06512: at "SYS.DBMS_STATS", line 18402
ORA-06512: at line 1


SQL>  SELECT TABLE_OWNER,TABLE_NAME,STATTYPE_LOCKED
 FROM ALL_IND_STATISTICS
 WHERE INDEX_NAME='IDX_ACCOUNTS_ACC_NUM';  2    3

TABLE_OWNER                    TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
CBS_USER                  ACCOUNTS                       ALL

SQL> SELECT OWNER, TABLE_NAME, STATTYPE_LOCKED
FROM ALL_TAB_STATISTICS
WHERE TABLE_NAME='ACCOUNTS';  2    3

OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
CBS_USER                  ACCOUNTS                       ALL

SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS ('CBS_USER','ACCOUNTS','ALL');

PL/SQL procedure successfully completed.

SQL>  EXEC DBMS_STATS.GATHER_INDEX_STATS ('CBS_USER','IDX_ACCOUNTS_ACC_NUM');

PL/SQL procedure successfully completed.

SQL>

Enable/Disable Automatic Startup Oracle HAS

[grid@OEL564ASMN1 ~]$ crsctl check has
CRS-4638: Oracle High Availability Services is online
[grid@OEL564ASMN1 ~]$
[grid@OEL564ASMN1 ~]$
[grid@OEL564ASMN1 ~]$ crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
[grid@OEL564ASMN1 ~]$
[grid@OEL564ASMN1 ~]$ crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.
[grid@OEL564ASMN1 ~]$ crsctl config has
CRS-4621: Oracle High Availability Services autostart is disabled.
[grid@OEL564ASMN1 ~]$ crsctl enable has
CRS-4622: Oracle High Availability Services autostart is enabled.
[grid@OEL564ASMN1 ~]$ crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
[grid@OEL564ASMN1 ~]$

How to collect all SQL statement executed by Schema.

1. Set AUDIT_TRAIL parameter value db (Default) so that audit information will written into database (SYS.AUD$).
2. Create Schema and define audit policy.
3. Create and Enable fine grained auditing.
4. Collect all SQL Statement from sys.FGA_LOG$ table.
5. Purge all Audit information.
6. Drop fine grained auditing.
7. Scripts for enable fine grained auditing for all objects of schema.

1. 
SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB
SQL>

2.
GRANT CONNECT, RESOURCE, DBA TO AUDIT_USER IDENTIFIED BY AUDIT_USER;

AUDIT ALL BY AUDIT_USER BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY AUDIT_USER BY ACCESS;
AUDIT EXECUTE PROCEDURE BY AUDIT_USER BY ACCESS;
3.
--- Create Fine Grained Auditing
BEGIN
  DBMS_FGA.ADD_POLICY(
    OBJECT_SCHEMA   => 'AUDIT_USER',
    OBJECT_NAME     => 'TEST_DATA',
    POLICY_NAME     => 'AUDIT_USER_P',
    AUDIT_CONDITION => NULL,
    AUDIT_COLUMN    => NULL,
    STATEMENT_TYPES => 'SELECT,INSERT,UPDATE,DELETE');
END;
--- Enable Fine Grained Auditing
BEGIN

DBMS_FGA.ENABLE_POLICY (
OBJECT_SCHEMA    =>  'AUDIT_USER',
OBJECT_NAME      =>  'TEST_DATA',
POLICY_NAME      =>  'AUDIT_USER_P',
ENABLE           =>   TRUE);

END;

4.
--- Find all SQL Statement using the following Query. You can also store all SQL Statement in your own table using the Second query.

SELECT LSQLTEXT, LSQLBIND,NTIMESTAMP#
FROM FGA_LOG$;

CREATE TABLE SQL_STATEMENT
AS
SELECT LSQLTEXT, LSQLBIND,NTIMESTAMP#
FROM FGA_LOG$;

SELECT *
FROM FGA_LOG$;

5. 
--- Auditing information can be purge by truncate SYS.AUD$ and sys.FGA_LOG$ table. This is manual purge. Automated purge policy can define so that Oracle will automatically purge all audit information by using scheduler.

TRUNCATE TABLE SYS.AUD$ ;
TRUNCATE TABLE sys.FGA_LOG$;

6. 
BEGIN
  DBMS_FGA.drop_policy(
    object_schema   => 'AUDIT_USER',
    object_name     => 'TEST_DATA',
    policy_name     => 'AUDIT_USER_P');
END;

7. 
CREATE OR REPLACE PROCEDURE SP_AUDIT_SCHEMA_ACTIVITY (
   P_IN_SCHEMA_NAME IN VARCHAR2)
IS
   W_POLICY_NAME   VARCHAR2 (100);

   CURSOR CUR_TABLE_LIST
   IS
      SELECT OWNER, TABLE_NAME
        FROM ALL_TABLES
       WHERE OWNER = P_IN_SCHEMA_NAME;

BEGIN
   FOR IDX IN CUR_TABLE_LIST
   LOOP
      W_POLICY_NAME := IDX.OWNER || '_' || IDX.TABLE_NAME;
   
      IF LENGTH(W_POLICY_NAME)>30 THEN
       W_POLICY_NAME:=IDX.TABLE_NAME;
      END IF;

      BEGIN
         DBMS_FGA.ADD_POLICY (
            OBJECT_SCHEMA     => IDX.OWNER,
            OBJECT_NAME       => IDX.TABLE_NAME,
            POLICY_NAME       => W_POLICY_NAME,
            AUDIT_CONDITION   => NULL,
            AUDIT_COLUMN      => NULL,
            STATEMENT_TYPES   => 'SELECT,INSERT,UPDATE,DELETE');
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE (W_POLICY_NAME||' >> '||SQLERRM);
      END;


      BEGIN
         DBMS_FGA.ENABLE_POLICY (OBJECT_SCHEMA   => IDX.OWNER,
                                 OBJECT_NAME     => IDX.TABLE_NAME,
                                 POLICY_NAME     => W_POLICY_NAME,
                                 ENABLE          => TRUE);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE (W_POLICY_NAME||' >> '||SQLERRM);
      END;
   END LOOP;
END;

CREATE OR REPLACE PROCEDURE SP_DROP_AUDIT_POLICY (
   P_IN_SCHEMA_NAME IN VARCHAR2)
IS
   W_POLICY_NAME   VARCHAR2 (100);

   CURSOR CUR_POLICY_LIST
   IS
      SELECT OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME
        FROM DBA_AUDIT_POLICIES
       WHERE OBJECT_SCHEMA = P_IN_SCHEMA_NAME;

BEGIN
   FOR IDX IN CUR_POLICY_LIST
   LOOP
      W_POLICY_NAME := IDX.POLICY_NAME;

      BEGIN
         DBMS_FGA.drop_policy (object_schema   => IDX.OBJECT_SCHEMA,
                               object_name     => IDX.OBJECT_NAME,
                               policy_name     => W_POLICY_NAME);
      END;
   END LOOP;
END;


EXEC SP_AUDIT_SCHEMA_ACTIVITY('SCHEMA_NAME');

EXEC SP_DROP_AUDIT_POLICY('SCHEMA_NAME');

Reference

https://oracle-base.com/articles/10g/auditing-10gr2

https://oracle-base.com/articles/11g/auditing-enhancements-11gr2

RMAN Compress Backup Archivelog and Full Database

Archivelog Backup:

backup AS COMPRESSED BACKUPSET  archivelog all delete all input tag ORCL_ARCHIVE format '/u01/backup/rman/%d_%T_%s_%p_ARCHIVE';

Full Database and Archivelog Backup:

RUN
{
configure controlfile autobackup on;
set command id to 'ORCLOnlineBackupFull';
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/u01/backup/rman/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
backup AS COMPRESSED BACKUPSET  archivelog all delete all input tag ORCL_ARCHIVE format '/u01/backup/rman/%d_%T_%s_%p_ARCHIVE';
backup tag ORCL_CONTROL current controlfile format '/u01/backup/rman/%d_%T_%s_%p_CONTROL';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

[oracle@OEL564ASMN1 rman]$ rman target/

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 18 13:48:20 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ASMDB (DBID=624768357)

RMAN> backup AS COMPRESSED BACKUPSET  archivelog all delete all input tag ORCL_ARCHIVE format '/u01/backup/rman/%d_%T_%s_%p_ARCHIVE';

Starting backup at 18-OCT-16
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=37 RECID=35 STAMP=925050761
input archived log thread=1 sequence=38 RECID=36 STAMP=925566466
input archived log thread=1 sequence=39 RECID=37 STAMP=925566511
channel ORA_DISK_1: starting piece 1 at 18-OCT-16
channel ORA_DISK_1: finished piece 1 at 18-OCT-16
piece handle=/u01/backup/rman/ASMDB_20161018_24_1_ARCHIVE tag=ORCL_ARCHIVE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+FRA/asmdb/archivelog/2016_10_12/thread_1_seq_37.362.925050753 RECID=35 STAMP=925050761
archived log file name=+FRA/asmdb/archivelog/2016_10_18/thread_1_seq_38.358.925566461 RECID=36 STAMP=925566466
archived log file name=+FRA/asmdb/archivelog/2016_10_18/thread_1_seq_39.354.925566507 RECID=37 STAMP=925566511
Finished backup at 18-OCT-16

Starting Control File and SPFILE Autobackup at 18-OCT-16
piece handle=+FRA/asmdb/autobackup/2016_10_18/s_925566523.354.925566523 comment=NONE
Finished Control File and SPFILE Autobackup at 18-OCT-16

RMAN> exit


Recovery Manager complete.
[oracle@OEL564ASMN1 rman]$

Modify the default Scheduler Window

resmgr:cpu quantum : This wait event indicate that The session is waiting to be allocated a quantum of cpu. This event occurs when the resource manager is enabled and is throttling CPU consumption. 

Solution : Check the status of Resource manager if it is running stop and configure Scheduler Window as per your idle database time.

SELECT WINDOW_NAME,

 RESOURCE_PLAN,

 ENABLED,

 CAST (LAST_START_DATE AS TIMESTAMP) LAST_START_DATE,

 CAST (NEXT_START_DATE AS TIMESTAMP) NEXT_START_TIME,

   EXTRACT (DAY FROM DURATION) * 24 * 60

 + EXTRACT (HOUR FROM DURATION) * 60

 + EXTRACT (MINUTE FROM DURATION) DURATION,

 ACTIVE,

 COMMENTS

FROM ALL_SCHEDULER_WINDOWS

ORDER BY NEXT_START_DATE;

The output of this query :

It is configured based on US holiday standard but is some different country like Bangladesh holiday is Friday for that we have to modify this default configuration.

To change the default configuration of each window we have to perform the following steps:

1. Disable the window.

2. Set attribute for window.

3. Enable The window.

Example of Sunday Window Change :

BEGIN

BEGIN

DBMS_SCHEDULER.DISABLE(

name=>'"SYS"."SUNDAY_WINDOW"',

force=>TRUE);

END;

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name=>'"SYS"."SUNDAY_WINDOW"',

attribute=>'DURATION',

value=>numtodsinterval(240, 'minute'));

END;

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name=>'"SYS"."SUNDAY_WINDOW"',

attribute=>'REPEAT_INTERVAL',

value=>'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=1;BYMINUTE=0;BYSECOND=0');

END;

BEGIN

DBMS_SCHEDULER.ENABLE(

name=>'"SYS"."SUNDAY_WINDOW"');

END;

END;

Result after change the default configuration of Sunday window :

Oracle RMAN backup compression full and incremental

Full backup compression scripts :

backup AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 database tag ORCL_FULL format '+RMAN_BACKUP/rman_bkp_df2_%d_%t_%s_%p_%U';

Incremental backup compression scripts :

backup AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 database tag ORCL_FULL format '+RMAN_BACKUP/rman_bkp_inc_%d_%t_%s_%p_%U';

Log:


[oracle@OEL564ASMN1 scripts]$ rman target/

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 12 14:50:09 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ASMDB (DBID=624768357)

RMAN> backup AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 database tag ORCL_FULL format '+RMAN_BACKUP/rman_bkp_df2_%d_%t_%s_%p_%U';

Starting backup at 12-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/asmdb/datafile/system.256.880569463
input datafile file number=00002 name=+DATA/asmdb/datafile/sysaux.257.880569467
input datafile file number=00005 name=+DATA/asmdb/datafile/example.265.880569629
input datafile file number=00006 name=+DATA/asmdb/datafile/test_tbs.267.881349853
input datafile file number=00003 name=+DATA/asmdb/datafile/undotbs1.258.880569467
input datafile file number=00004 name=+DATA/asmdb/datafile/users.259.880569471
input datafile file number=00007 name=+DATA/asmdb/datafile/tbs_user_data_1.268.882462007
input datafile file number=00008 name=+DATA/asmdb/datafile/tbs_user_data_1.269.882458361
channel ORA_DISK_1: starting piece 1 at 12-OCT-16
channel ORA_DISK_1: finished piece 1 at 12-OCT-16
piece handle=+RMAN_BACKUP/rman_bkp_df2_asmdb_925051814_18_1_0iri6at6_1_1 tag=ORCL_FULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 12-OCT-16

Starting Control File and SPFILE Autobackup at 12-OCT-16
piece handle=+FRA/asmdb/autobackup/2016_10_12/s_925051911.360.925051913 comment=NONE
Finished Control File and SPFILE Autobackup at 12-OCT-16

RMAN>
RMAN> backup AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 database tag ORCL_FULL format '+RMAN_BACKUP/rman_bkp_inc_%d_%t_%s_%p_%U';

Starting backup at 12-OCT-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/asmdb/datafile/system.256.880569463
input datafile file number=00002 name=+DATA/asmdb/datafile/sysaux.257.880569467
input datafile file number=00005 name=+DATA/asmdb/datafile/example.265.880569629
input datafile file number=00006 name=+DATA/asmdb/datafile/test_tbs.267.881349853
input datafile file number=00003 name=+DATA/asmdb/datafile/undotbs1.258.880569467
input datafile file number=00004 name=+DATA/asmdb/datafile/users.259.880569471
input datafile file number=00007 name=+DATA/asmdb/datafile/tbs_user_data_1.268.882462007
input datafile file number=00008 name=+DATA/asmdb/datafile/tbs_user_data_1.269.882458361
channel ORA_DISK_1: starting piece 1 at 12-OCT-16
channel ORA_DISK_1: finished piece 1 at 12-OCT-16
piece handle=+RMAN_BACKUP/rman_bkp_inc_asmdb_925052081_22_1_0mri6b5h_1_1 tag=ORCL_FULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 12-OCT-16

Starting Control File and SPFILE Autobackup at 12-OCT-16
piece handle=+FRA/asmdb/autobackup/2016_10_12/s_925052147.359.925052147 comment=NONE
Finished Control File and SPFILE Autobackup at 12-OCT-16

RMAN> exit


Recovery Manager complete.

[oracle@OEL564ASMN1 scripts]$