Logwriter basics

• Find out if database is in archive mode
• Increase the size of log files dynamically
• Are my log files multiplexed
• Get size, filename and groups information about the log file
• Under what conditions does LGWR get trigged

 

• Find out if database is in archive mode

The LGWR writes to both the files in circular manner. When one file is filled, it writes to second one. When second is filled, it writes to first one after the changes recorded in it have been written to datafiles if database is not in archived mode. If the database is in archived mode, then LGWR writes to first file after the changes recorded in it have been written to data files and the files have been archived.

To check if database is in archive mode:

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle11gR2/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     451
Current log sequence           452

• Increase the size of log files

Create two additional groups with desired size.
alter database add logfile group 3 ('+LOG1/log_1_8') size 10000M 
alter database add logfile group 4 ('+LOG2/log_1_9') size 10000M

Find out the status of all groups and drop the old inactive group

 select group#,thread#,status from v$log ;
alter database drop logfile group 2

Switch the logfile so you can drop the old active group as well

alter system switch logfile ;
alter database drop logfile group 1 ;

• Get size, filename and groups information about the log file

 Find out the size of the log file
SQL> select group#,thread#,bytes/(1024*1024) , blocksize ,members from v$log ;

Find out the filenames and the group to which they belong:

  sql>select group#,member,status from v$logfile;

• Are my log files multiplexed

Multiplexing of log files (having multiple identical copies) is implemented by creating groups of redo log file. A group consist of a redo log file and its multiplexed copies. Each identical copy is the member of the group.


IIssue the following commnd to find out how many groups you have. The output shows I have two groups and each group has one member ...Hence multiplexing is enabled.
SQL> select group#,member from v$logfile ;

    GROUP#                                           MEMBER
--------------------------------------------------------------------------------
         1                                                      +TPCC1/log_1_1

         2                                                         +TPCC1/log_1_2

• Under what conditions does LGWR gets triggered

When a transaction is committed, info in the redo log buffer is written to a Redo Log File. In addition to this, the following conditions will trigger LGWR to write the contents of the log buffer to disk:

• Whenever the log buffer is MIN(1/3 full, 1 MB) full; or
• Every 3 seconds; or
• When a DBWn process writes modified buffers to disk (checkpoint).

Queries to get info about SGA

•  Find the size of SGA and its components:
• select name, value/(1024*1024*1024) as sizeGB from v$sga;
• select sum(value)/(1024*1024*1024) as totalGB from v$sga;
• Detailed look into SGA:
• select name, bytes/(1024*1024*1024)) as sizeGB ,resizeable from v$sgainfo;
• Find the size and contents of buffer pool:
• select sum(current_size)/1024  as sizeGB from v$buffer_pool;
• select name, block_size, current_size/1024 as sizeGB  from v$buffer_pool ;
• Find  the size and contents of variable SGA size
• the sum of following two queries should add up to variable sga as reported by show sga:
• select name, bytes/(1024*1024*1024) as sizeGB from v$sgainfo where name='Free SGA Memory Available';
•  select sum(bytes)/(1024*1024*1024) as sizeGB from v$sgastat where pool in ('shared pool' , 'java pool' ,'large pool') ;
• The amount of SGA memory available for future dynamic SGA operations can be found by either one of the following query:
• select current_size/(1024*1024*1024)  as freeGB from v$sga_dynamic_free_memory;
• select name, bytes/(1024*1024*1024) as sizeGB from v$sgainfo where name='Free SGA Memory Available';
• Query to find out the current, min and max size of each dynamic component in SGA. This query also returns the number of type an grow or shrink operation was performed on each component.
• column component format a25
• select component, current_size/(1024*1024) as currentsizeMB, min_size/(1024*1024) as minMB,max_size/(1024*1024) as maxMB,user_specified_size/(1024*1024) as userspecifiedMB  ,oper_count from  v$sga_dynamic_components;

Oracle Memory Essentials

• There are five ways of managing memory in Oracle

• Enable automatic memory management where Oracle manages both SGA and PGA
• http://www.oracle-base.com/articles/11g/AutomaticMemoryManagement_11gR1.php
• http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/AMM1/Default.aspx
• Enable automatic SGA managment where Oracle manages SGA
• http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/memory004.htm#i1014121
• http://www.scribd.com/doc/2674966/Oracle-Automatic-Shared-Memory-Management-Asmm
• Enable automatic PGA management where Oracle manages PGA
• Manual SGA management
• Manual PGA management

• You can manage memory through EM as under:
• Under Home page -> Server-> Memory Advisor
• The following views provide information about dynamic resize operations:
  
  • V$MEMORY_CURRENT_RESIZE_OPS displays information about memory resize operations (both automatic and manual) which are currently in progress.
  • V$MEMORY_DYNAMIC_COMPONENTS displays information about the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.
  • V$MEMORY_RESIZE_OPS displays information about the last 800 completed memory resize operations (both automatic and manual). This does not include in-progress operations.
  • V$MEMORY_TARGET_ADVICE displays tuning advice for the MEMORY_TARGET initialization parameter.
  • V$SGA_CURRENT_RESIZE_OPS displays information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.
  • V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations. This does not include any operations currently in progress.
  • V$SGA_DYNAMIC_COMPONENTS displays information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations that occurred after startup.
  • V$SGA_DYNAMIC_FREE_MEMORY displays information about the amount of SGA memory available for future dynamic SGA resize operations.
• SGA Internals
• Relevant views: v$sga,v$sgainfo, v$sgastat
• You can display SGA related info by issuing show sga command
• SQL> show sga
• Total System Global Area 3.1277E+10 bytes
  Fixed Size                  2149312 bytes
  Variable Size            7247762496 bytes
  Database Buffers         2.3891E+10 bytes
  Redo Buffers              136527872 bytes
• The same info can be obtained as under:
• SQL> select name, value/(1024*1024*1024) as sizeGB from v$sga;
• NAME                     SIZEGB
  -------------------- ----------
  Fixed Size           .002001703
  Variable Size        6.75000483
  Database Buffers          22.25
  Redo Buffers         .127151489
  
  
  SQL> select sum(value)/(1024*1024*1024) as totalMB from v$sga;
  
     TOTALMB
  ----------
   29.129158
• Fixed Size
  Contains general information about the state of the database and the instance, which the background processes need to access. No user data is stored here. The size of the fixed portion is constant for a release and a plattform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters. For 11gR2, fixed SGA size is around 200MB
• Variable size
• The variable portion of SGA is called variable because its size (measured in bytes) can be changed.
  
  The variable portion consists of:
      Large Pool (large_pool_size)
      Shared Pool (shared_pool_size)
      Java Pool (java_pool_size)
  
  The size for the variable portion is roughly equal to the result of the following statement till Oracle 9i:
  
  SQL> select sum(bytes)/(1024*1024*1024) as sizeGB from v$sgastat where pool in ('shared pool' , 'java pool' ,'large pool') ;
  
      SIZEGB
  ----------
  6.75000446
• Post Oracle 9i, the variable component is calculated by adding free memory as shown by v$sgainfo to the above query

Variable Component(Show SGA) = Shared Pool + Large Pool + Java Pool + Overhead + free memory(9i onwards)

>  select name, bytes/(1024*1024*1024) as sizeGB from v$sgainfo;
Adding free sga memory available to the above query from v$sgastat should get you the variable SGA size as reported by show sga command.

• Database buffers consist of default buffer (db_cache_size), keep buffer (db_keep_cache_size) , recycle buffer (db_recycle_cache_size) and all non standard buffer (db_n_cache_size). Oracle has the following non standard buffer cache:
• DB_2k_cache_size
• DB_4K_cache_size
• DB_8k_cache_size
• db_16k_cache_size
• db_32k_cache_size.

 The default block size is determined by db_block_size parameter.Multiple buffer pools are only available for the standard block size. Non-standard block size caches have a single DEFAULT pool.

• Query v$buffer_pool to get the sizes of all database buffers configured for the instance.
  SQL> select name, block_size, current_size from v$buffer_pool ;
  NAME                 BLOCK_SIZE CURRENT_SIZE
  -------------------- ---------- ------------
  KEEP                       2048        10240
  RECYCLE                    2048         7168
  DEFAULT                    2048         2048
  DEFAULT                    8192          256
  DEFAULT                   16384         3072
  The above output indicates that the size of keep buffer is 10 GB, size of recycle buffer is 7GB and the size of default buffer is 2 GB. Also , an 8K buffer cache of size 256MB and a 16k buffer cache of size 3 gb is configured.
  The following query indicates that total buffer cache is 22GB as shown in the output of show sga command:
  SQL> select sum(current_size)/1024  as sizeGB from v$buffer_pool;
      SIZEGB
  ----------
       22.25

ASM Essentials

• Basic Commands
• Add disk to ASM
• Change the owner to oracle:oinstall
• chown oracle:oinstall /dev/rdsk/c1t0d2s*
• Change permission to 660
• chmod 660 /dev/rdsk/c1t0d2s*
• Create:

create diskgroup <test> external redundancy disk '/dev/rdsk' , '/dev/rdsk'  ;

• Alter diskgroup (add or drop disk; mount diskgroup):

alter diskgroup <test> add disk '/dev/rdsk/' , '/dev/'
alter diskgroup all mount ; to mount all diskgroups
alter diskgroup <test> drop disk < pathname as obtained below>

SQL> select group_number,name from v$asm_diskgroup;

GROUP_NUMBER NAME
—
1 DGNORMAL

SQL> select path,name from v$asm_disk where group_number=1;

PATH NAME

—

/dev/raw/raw5 DGNORMAL_0000

/dev/raw/raw6 DGNORMAL_0001

/dev/raw/raw7 DGNORMAL_0004

/dev/raw/raw8 DGNORMAL_0005

SQL> alter diskgroup dgnormal drop disk dgnormal_0005;

Diskgroup altered.

• Add greater than 2 TB disk on Oracle Database 12c

Creating diskgroup with slices with more than 2TB space works fine with the attribute:
ATTRIBUTE 'compatible.asm' = '12.1.0.2', 'compatible.rdbms' = '11.2.0.4'

• Drop diskgroup

drop diskgroup <test> including contents ;if disk is mounted
drop diskgroup <test> force including contents; if diskgroup is not mounted
Extreme case
if last diskgroup and you do not need to save asm parameter file
alter diskgroup <test>  dismount force
drop diskgroup <test> force including contents

• If you can dd on the disk and ASM still gives I/O error

 Asm Is not Discovering Disks On Solaris: ORA-15025 ORA-27041 SVR4 Error: 5: I/O error (Doc ID 1300393.1)'
If you are running ASM in local container, zero out the VTOC table in global container and reparition the disks. Then reboot the local container and recreate the diskgroup
First zero out the VTOC table so you can re-label as per the above document:
 dd if=/dev/zero of=/dev/rdsk/c2t8d10s2 count=10000 (then format ;select disk and label)

You cannot use the entire disk (s2) partition on Solaris as it contains sector 0 .....

• Zero the contents of disk header
• dd if=/dev/zero of=<diskpath> bs=4096 count=100
• See if any asm operation is currently active  (after modifying diskgroup)
• select * from v$asm_operation
• Find the list of disks in a disk group:
• Get the group number of diskgroup from v$ASM_DISKGROUP
• select group_number,name from V$ASM_DISKGROUP;
• Get the list of all the disk in that group number from v$ASM_disk
• select substr(name,1,10) name, substr(path,1,20) path from v$ASM_disk where group_number=x;
• Find the asm disk header status
• select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,NAME,PATH from v$asm_disk;
• FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
• UNKNOWN - ASM disk header has not been read
•  CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
•  INCOMPATIBLE - Version number in the disk header is not compatible with the ASM software version.
•  PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for ASM.
•  MEMBER (default) - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option
• · FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
•  CONFLICT - ASM disk was not mounted due to a conflict
•  FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles,logfiles, and OCR disks.
• Find which disk is not working in ASM diskgroup
• Look at the alert log to see which physical disks are allocated to diskgroup  when the disk group was created:
  SQL> CREATE DISKGROUP TPCC1 EXTERNAL REDUNDANCY  DISK '/dev/rdsk/c6t20030003BA4E
  8829d0s6' SIZE 378884M ,
  '/dev/rdsk/c6t20030003BA4E8829d1s6' SIZE 382877M ,
  '/dev/rdsk/c6t20030003BACCC8FAd0s6' SIZE 906256M ,
  '/dev/rdsk/c6t20030003BACCC902d0s6' SIZE 488460M ,
  '/dev/rdsk/c6t20030003BACCC902d1s6' SIZE 203777M ,
  '/dev/rdsk/c6t20030003BACCC902d2s6' SIZE 283654M  ATTRIBUTE 'compatible.asm'='11
  .2.0.0.0','au_size'='1M' /* ASMCA */
  NOTE: Assigning number (2,0) to disk (/dev/rdsk/c6t20030003BA4E8829d0s6)
  NOTE: Assigning number (2,1) to disk (/dev/rdsk/c6t20030003BA4E8829d1s6)
  NOTE: Assigning number (2,2) to disk (/dev/rdsk/c6t20030003BACCC8FAd0s6)
  NOTE: Assigning number (2,3) to disk (/dev/rdsk/c6t20030003BACCC902d0s6)
  NOTE: Assigning number (2,4) to disk (/dev/rdsk/c6t20030003BACCC902d1s6)
  NOTE: Assigning number (2,5) to disk (/dev/rdsk/c6t20030003BACCC902d2s6)
  Compare it with the alert log output when you issue the mount command to mount the diskgroup:
  SQL> alter diskgroup TPCC1 MOUNT /* ASMCA */
  NOTE: cache registered group TPCC1 number=2 incarn=0x6a1b1a64
  NOTE: cache began mount (first) of group TPCC1 number=2 incarn=0x6a1b1a64
  NOTE: Assigning number (2,5) to disk (/dev/rdsk/c6t20030003BACCC902d2s6)
  NOTE: Assigning number (2,4) to disk (/dev/rdsk/c6t20030003BACCC902d1s6)
  NOTE: Assigning number (2,3) to disk (/dev/rdsk/c6t20030003BACCC902d0s6)
  ERROR: no PST quorum in group: required 1, found 0

                            This means ASM was able to see disk 3,4 and 5 for diskgroup number 2 (ie TPCC1 diskgroup). It has problems seeing disk 0,1 and 2

• Additional queries:
• http://www.shutdownabort.com/dbaqueries/Administration_ASM.php

RMAN Essentials

• RMAN script to take database backup

Here is an RMAN script to take cold backup. The two input parameters are degree of parallelism and the diskgroup on which you want to take the backup :

setenv ORACLE_SID
rman target /
shutdown immediate ;
startup mount ;

show all ;
configure device type disk parallelism <degree of parallelism> backup type to  backupset
configure channel device type disk format  '<diskgroupname>';

backup database ;
alter database open;

• Set timestamp for RMAN backup

Before invoking RMAN, set the NLS_DATE_FORMAT and NLS_LANG environment variables. These variables determine the format used for the time parameters in RMAN commands such as RESTORE, RECOVER, and REPORT.

The following example shows typical language and date format settings:

• NLS_LANG=american
• NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'

If you are going to use RMAN to connect to an unmounted database and mount the database later while RMAN is still connected, then set the NLS_LANG environment variable so that it also specifies the character set used by the database.

A database that is not mounted assumes the default character set, which is US7ASCII. If your character set is different from the default, then RMAN returns errors after the database is mounted. For example, if the character set is WE8DEC, you can set the NLS_LANG parameter as follows:

• NLS_LANG=american_america.we8dec

NLS_LANG and NLS_DATE_FORMAT must be set for NLS_DATE_FORMAT to be used.

• Basic RMAN settings

Rman allows you to configure the following:

1) Device type on which to take the backup: the device type can be disk or tape.

• You can set  device type to be disk as under:
•  device type disk ;

2) Ability to compress the backup sets. 

• You can take compressed backup sets by using the following clause:
• as compressed backup

3) Backup tag: RMAN attaches a character string called tag to every backup it creates.

• You can either accept the default tag or specify your own tag with TAG paramter s under
• tag test_tag

4) Specify the type of backup (backup sets or image copies).

• This can be specified as
• as copy or as backupset clause

5) Location in the disk where you want to save backup: format 'filename'

• To save in ASM diskgroup
• configure channel device type disk format '+dgroup1';
• To save the backup on disk
• configure channel device type disk format '/backupP/bench/%U' ;
   (ensure that you have created /backupP/bench directory. %U will generate unique filenames)

6) Setting the environment:

• Show all command shows the default setting.
• You can change the default setting with the configure command. for eg, to take compressed backups on disk of type backupset :
• configure device type disk backup type to compressed backupset;

7) Log file:
Where does rman stores log file : no where unless you specify log

Just clarifying -- Oracle docs show the following:

"LOG = Specifies the file where Recovery Manager will record RMAN output, that is, the commands that were processed and their results. If you do not specify this argument, then RMAN writes its message log file to standard output. "

That being said, if you don't specify a log then it appears your standard output is the terminal. (So no log -- sorry.)

You can specify the log file as under:

rman target sys@mydbt log=d:\rman.log

Additional info :
http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmbckba.htm#i1006582

• Configuring channels in RMAN:

RMAN channel is a connection to a database server session. You can use configure channel command to configure options for disk or tape channels.

• Configure channel command sets setting for generic channels (all channels) 
•  allocate channel command sets setting for particular channel.

By default RMAN allocates one disk channel for all operations, you can configure channel parallelism for disk and tapes.You can specify channel parallelism by configure device command as under:

• configure device type disk parallelism 10 backup type to compressed backupset

• Configuring the fast recovery area:

Enable the fast recovery area by setting the following parameters:

• db_recovery_file_dest_size: <size of the fast recovery area>
• db_recovery_file_dest:<location of fast recovery area>

Leaving db_recovery_file_dest empty disables the flash recovery area. Here is an example of disabling the flash recovery area by resetting the db_recovery_file_dest parameter:

• alter system set db_recovery_file_dest=' ' scope=both;

• Set database in archive mode: 

 http://www.cuddletech.com/articles/oracle/node58.html

• To restore the database

rman target /
run{
startup mount
restore database;
recover database;
alter database open resetlogs;
}