Resizing Redo Log on Oracle 11g (No Archivelog Mode)

[root@mars ~]# su - oracle
[oracle@mars ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 15:12:23 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         2 CURRENT
         3 INACTIVE

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 (
  2  '/u01/app/oracle/oradata/itcsyariah2/redo01a.log',
  3  '/data04/redolog/itcsyariah2/redo01b.log',
  4  '/backup/redolog/itcsyariah2/redo01c.log') SIZE 250M REUSE;

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 CURRENT
         3 INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL> alter database drop logfile group 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 (
  2  '/u01/app/oracle/oradata/itcsyariah2/redo02a.log',
  3  '/data04/redolog/itcsyariah2/redo02b.log',
  4  '/backup/redolog/itcsyariah2/redo02c.log') SIZE 250M REUSE;

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 INACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 (
  2  '/u01/app/oracle/oradata/itcsyariah2/redo03a.log',
  3  '/data04/redolog/itcsyariah2/redo03b.log',
  4  '/backup/redolog/itcsyariah2/redo03c.log') SIZE 250M REUSE;

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL>
Advertisements

How to Backup SPFILE

CMIIW ^_^

  1. using SQLPLUS (1)

    SQL> create pfile=’/u01/app/oracle/backups/pools/pfiles/pfilesqlplus20140226.ora’ from spfile;

    File created.

  2. using SQLPLUS (2)

    SQL> create pfile=’/u01/app/oracle/backups/pools/pfiles/pfilememory20140226.ora’ from memory;

    File created.

  3. using ASMCMD

    ASMCMD> cp +DATA/ITCAPPS2/spfileitcapps2.ora /u01/app/oracle/backups/pools/pfiles/pfileasmcmd20140226.ora
    copying +DATA/ITCAPPS2/spfileitcapps2.ora -> /u01/app/oracle/backups/pools/pfiles/pfileasmcmd20140226.ora

  4. using RMAN

    RMAN> backup spfile to destination=’/u01/app/oracle/backups/pools/pfiles/rman’;

    Starting backup at 26-FEB-14
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=1385 device type=DISK
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    including current SPFILE in backup set
    channel ORA_DISK_1: starting piece 1 at 26-FEB-14
    channel ORA_DISK_1: finished piece 1 at 26-FEB-14
    piece handle=/u01/app/oracle/backups/pools/pfiles/rman/ITCAPPS2/backupset/2014_02_26/o1_mf_nnsnf_TAG20140226T153522_9jv9tbqw_.bkp tag=TAG20140226T153522 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    Finished backup at 26-FEB-14

ORA-01565: error in identifying file ‘?/dbs/spfile@.ora’

Usually I want to create pfile from spfile just run this command,

SQL> create pfile='/u01/app/oracle/backups/pools/pfiledb20120226.ora' from spfile;

File created.

But in ASM environment, the above command raises an error,

SQL> create pfile='/u01/app/oracle/backups/pools/pfileasm20120226.ora' from spfile;
create pfile='/u01/app/oracle/backups/pools/pfileasm20120226.ora' from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

Solution I found after googling,

SQL> create pfile='/u01/app/oracle/backups/pools/pfileasm20120226.ora' from memory;

File created.

RMAN Script Example

/home/oracle/.bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs
export BASHDP='BASHRC'
#PATH=$PATH:$HOME/bin

#export PATH
umask 022
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db1
ORACLE_SID=itcsyariah2
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH

/u01/app/oracle/backups/scrips/conf/bck.cfg

ORACLE_BASE=/u01/app/oracle

# how many days we want to keep backups on disk before RMAN deletes them
REDUNDANCY=2

# enable/disable automatic backup of spfile and controlfile
CONTROLFILE_AUTOBACKUP="ON"

# string with the destination of the automatic backup of the spfile and controlfile
CONTROLFILE_AUTOBACKUP_FORMAT="$ORACLE_BASE/backups/controlspfile/%F"

# Define parallelism degree by an integer (only usable on Enterprise Edition),
PARALLELISM=2
# and if the backupset is going to be compressed.
BACKUPSET_TYPE="COMPRESSED BACKUPSET"

# Set the destination of the snapshot control file to be kept on the backups.
SNAPSHOT_CONTROLFILE="$ORACLE_BASE/backups/controlspfile/snapcf_itcsyariah2.f"

DATAFILES_DEST="$ORACLE_BASE/backups/datafiles/%d_%T_s%s_s%p"
ARCHIVELOGS_DEST="$ORACLE_BASE/backups/archivelogs/%d_%T_s%s_s%p"

/u01/app/oracle/backups/scrips/bck.sh

#!/bin/bash

source ~/.bash_profile # $ORACLE_HOME and $ORACLE_SID should be exported here

source $ORACLE_BASE/backups/scripts/conf/bck.cfg

usage() {
echo `basename $0`: ERROR: $* 1>&2
echo usage: `basename $0` '[-[a(rchive log only) f(full)]]' 1>&2
exit 1
}

case "$1" in
-a) BACKUP_TYPE="archivelog all format '$ARCHIVELOGS_DEST' delete input";;
-f) BACKUP_TYPE="database plus archivelog";;
*) usage ;;
esac

rman target / nocatalog <<EOF
# how many days we want to keep backups on disk before RMAN deletes them
CONFIGURE RETENTION POLICY TO REDUNDANCY $REDUNDANCY;

# enable/disable automatic backup of spfile and controlfile
CONFIGURE CONTROLFILE AUTOBACKUP $CONTROLFILE_AUTOBACKUP;

# string with the destination of the automatic backup of the spfile and controlfile
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$CONTROLFILE_AUTOBACKUP_FORMAT';

# Define parallelism degree by an integer (only usable on Enterprise Edition), and if the backupset is going to be compressed.
CONFIGURE DEVICE TYPE DISK PARALLELISM $PARALLELISM BACKUP TYPE TO $BACKUPSET_TYPE;

# Set the destination of the snapshot control file to be kept on the backups.
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$SNAPSHOT_CONTROLFILE';

backup $BACKUP_TYPE;
exit;
EOF

exit