Oracle DBA ,RAC 11g/12c/18c/19c: December 2014

Thursday 11 December 2014

significance of MAXLOGFILES, MAXLOGMEMBERS,MAXINSTANCES and MAXDATAFILES in controlfile

what does the terms MAXLOGFILES, MAXLOGMEMBERS,MAXINSTANCES and MAXDATAFILES refers to?
(or)
How to understand control file?

CREATE DATABASE database_1           ---------> indicates the database name
USER SYS IDENTIFIED BY welcome123    ---------> setting the SYS user password
USER SYSTEM IDENTIFIED BY welcome123 ---------> setting the SYSTEM user password
   LOGFILE GROUP 1 ('/u01/database_1/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/database_1/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/database_1/redo03.log') SIZE 100M ---------> creating redolog groups and members init
   MAXLOGFILES 5           ---------> indicates the maximum number of redo log file groups that can ever be created for the database
   MAXLOGMEMBERS 5         ---------> indicates the maximum number of members for a redo log file group
   MAXINSTANCES 1         ---------> indicates the maximum number of instances that can simultaneously have this database mounted and open.
   MAXDATAFILES 100        ---------> indiactes the maximun nuber of datafiles we can create for database. this value can be changed
   CHARACTER SET US7ASCII ---------> indicates the character set the database uses to store data
   NATIONAL CHARACTER SET AL16UTF16 ---------> Specify the national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2
   EXTENT MANAGEMENT LOCAL ---------> Metadata regarding extent allocation and unallocated extents are stored in the tablespace header
   DATAFILE '/u01/database_1/system01.dbf' SIZE 325M         ---------> creation of system datafile
   SYSAUX DATAFILE '/u01/database_1/sysaux01.dbf' SIZE 325M ---------> creation of sysaux datafile
   DEFAULT TABLESPACE users
      DATAFILE '/u01/database_1/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED        ---------> creation of users tablespace and associated datafile with auto extend on option
   DEFAULT TEMPORARY TABLESPACE tempts1                      ---------> creation of temp tablespace and associated temfile
      TEMPFILE '/u01/database_1/temp01.dbf'
      SIZE 20M
   UNDO TABLESPACE undotbs                                   ---------> creation of undo tablespace and associated datafile with auto extend on option
      DATAFILE '/u01/database_1/undotbs01.dbf'
      SIZE 200M AUTOEXTEND ON MAXSIZE UNLIMITED;
/

cheers....!!!!

Wednesday 10 December 2014

What is control file and what is use of control file?

What is control file and what is use of control file?

Control file(small binary file) contains database name,CRD files locations(database structure),DB creation time-stamp, check point information and RMAN recent backup details. It is also used in database recovery.

When an oracle instance is started control file will be read during mount stage.

SQL> SHOW PARAMETERS control_files

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_files string +DATA/pavandb/controlfile/control01.ctl

It is suggested to multiplex the control file in different locations

Controls file multiplexing:

sql>Shut immediate

Set the new location in pfile

control_files='+DATA1/PRODDB/CONTROLFILE/control01.ctl', '+DATA2/pavandb/controlfile/control02.ctl'

sql>alter database mount

sql>alter database open;

SQL> SHOW PARAMETERS control_files

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_files string +DATA1/pavandb/controlfile/control01.ctl, +DATA2/pavandb/controlfile/control02.ctl

We can keep maximum of 8 controls files for a database. We can’t multiplex further.

We can read the control file after saving it in a location. This will be in human readable text format.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control_trace.sql'

click here to understand control file parameters using database creation script.

Tuesday 9 December 2014

Types of backups in Oracle

which types of backups we can take in Oracle?

1.Logical backup(export & import, expdp & impdp)
2.Physical backup(RMAN backup,HOT backup, COLD backup)

what is logical backup?
http://orcl11gdba.blogspot.in/2015/06/what-is-logical-backup.html

what is hot backup?
http://orcl11gdba.blogspot.in/2014/03/taking-hot-bakcup-database-in-begin.html

how to take cold backup?
http://orcl11gdba.blogspot.in/search/label/Recovery%20using%20COLD%20backup

what is rman backup?
http://orcl11gdba.blogspot.in/2014/05/taking-database-backup-using-rman-method.html

Tuesday 2 December 2014

create ASM disks using asmca(configuration assistant)

How to create ASM disks using asmca(configuration assistant) ?

Xterm &