Monday, 15 January 2018

Converting the DB to archive Log and Non Archive Log Mode

Archivelog is necessary in recovery of full database and complete backup, Database can be converted to archive log as below:
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /data/archive_sn
Oldest online log sequence     31
Current log sequence           33
SQL>
Change location of Archive Log List:
SQL> ALTER SYSTEM SET log_archive_dest_2='LOCATION=/u01/archivelog2' scope=both;
Enable Archive log mode
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /data/archive_sn
Oldest online log sequence     31
Next log sequence to archive   33
Current log sequence           33
/************************ Converting Archive Log to Non Archive Log *******************/
Below are steps:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size                  8620080 bytes
Variable Size             771753936 bytes
Database Buffers          419430400 bytes
Redo Buffers                8155136 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /data/archive_sn
Oldest online log sequence     31
Current log sequence           33
SQL>

Multiplexing the Controlfile

Controlfile is multiplexed to avoid single point of failure as Controlfille is brain of DB:

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string       u01/app/oracle/oradata/MYDB/control01.ctl',                                                      'u01/app/oracle/flash_recovery_area/MYDB/control02.ctl
                                                 control_management_pack_access                                                                                                     string      DIAGNOSTIC+TUNING
SQL>

Now we will set location for 3rd Controlfile by below command:


SQL> ALTER SYSTEM SET 
control_files='/u01/app/oracle/oradata/MYDB/control01.ctl','/u01/app/oracle/flash_recovery_area/MYDB/control02.ctl','/u01/multiplexing/control03.ctl' 
SCOPE=SPFILE;

Now manually/physically copy controlfile to that location:

cp /u01/app/oracle/oradata/MYDB/control01.ctl /u01/multiplexing/control03.ctl


SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string       u01/app/oracle/oradata/MYDB/control01.ctl',                                                      'u01/app/oracle/flash_recovery_area/MYDB/control02.ctl',
                                                '/u01/multiplexing/control03.ctl'
                                                 control_management_pack_access                                                                                                     string      DIAGNOSTIC+TUNING


Shutting Down Oracle Database

Oracle Database shutdown in 3 phases as below:

1- Close the DB- By close all new transaction acceptance are stopped, current transaction are complete depends upon command issue.

SQL> alter database close;
Database altered.
SQL>

2- Database Dismount

SQL> alter database dismount;
Database altered.
SQL> 

3- Shutdown Instance- As there is nothing running so we can abort it.

SQL> shutdown abort;
ORACLE instance shut down.
SQL>

Starting Up Oracle Database

Oracle Database starts in 3 below steps:

1- No Mount (where it read server parameter (spfile) file).

Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size                  8620080 bytes
Variable Size             771753936 bytes
Database Buffers          419430400 bytes
Redo Buffers                8155136 bytes

2- Mount (It reads controlfile)

SQL> alter database mount;
Database altered.

3- Open (Also know as read-write mode, where the Database is ready for performing operations)

SQL> alter database open;
Database altered.

SQL> select open_mode from v$database;

Installing Oracle 11g on Linux

Unpack Files
Unzip the files.
# 11.2.0.1
unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip
#11.2.0.2
unzip p10098816_112020_Linux-x86-64_1of7.zip
unzip p10098816_112020_Linux-x86-64_2of7.zip
#11.2.0.3
unzip p10404530_112030_Linux-x86-64_1of7.zip
unzip p10404530_112030_Linux-x86-64_2of7.zip
You should now have a single directory called “database” containing installation files.
Hosts File
The “/etc/hosts” file must contain a fully qualified name for the server.
<IP-address> <fully-qualified-machine-name> <machine-name>
For example.
127.0.0.1 localhost.localdomain localhost
192.168.1.20 tauseef.com
Oracle-Validated Setup
If you plan to use the “oracle-validated” package to perform all your prerequisite setup, follow the instructions at http://public-yum.oracle.com to setup the yum repository for OL, then perform the following command.
# yum install oracle-validated
All necessary prerequisites will be performed automatically.
Manual Setup
If you have not used the “oracle-validated” package to perform all prerequisites, you will need to manually perform the following setup tasks.
Oracle recommend the following minimum parameter settings.
fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
The current values can be tested using the following command.
/sbin/sysctl -a | grep <param-name>
Add or amend the following lines in the “/etc/sysctl.conf” file.
fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586
Run the following command to change the current kernel parameters.
/sbin/sysctl -p
Add the following lines to the “/etc/security/limits.conf” file.
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 4096
oracle hard nofile 65536
oracle soft stack 10240
Install the following packages if they are not already present.
# From Oracle Linux 5 DVD
cd /media/cdrom/Server
rpm -Uvh –force –nodeps binutils-2.*
rpm -Uvh –force –nodeps compat-libstdc++-33*
rpm -Uvh –force –nodeps compat-libstdc++-33*.i386.rpm
rpm -Uvh –force –nodeps elfutils-libelf*
rpm -Uvh –force –nodeps glibc-2.*
rpm -Uvh –force –nodeps glibc-common-2.*
rpm -Uvh –force –nodeps glibc-devel-2.*
rpm -Uvh –force –nodeps glibc-headers-2.*
rpm -Uvh –force –nodeps gcc-4.*
rpm -Uvh –force –nodeps gcc-c++-4.*
rpm -Uvh –force –nodeps ksh*
rpm -Uvh –force –nodeps libaio-0.*
rpm -Uvh –force –nodeps libaio-devel-0.*
rpm -Uvh –force –nodeps libgomp-4.*
rpm -Uvh –force –nodeps libgcc-4.*
rpm -Uvh –force –nodeps libstdc++-4.*
rpm -Uvh –force –nodeps libstdc++-devel-4.*
rpm -Uvh –force –nodeps make-3.*
rpm -Uvh –force –nodeps sysstat-7.*
rpm -Uvh –force –nodeps unixODBC-2.*
rpm -Uvh –force –nodeps unixODBC-devel-2.*
rpm -Uvh –force –nodeps numactl-devel-*
cd /
eject
Create the new groups and users.
groupadd oinstall
groupadd dba
groupadd oper
groupadd asmadmin
useradd -g oinstall -G dba,oper,asmadmin oracle
passwd oracle
Note. We are not going to use the “asmadmin” group, since this installation will not use ASM.
AdditionalSetup
The following setup tasks must be performed regardless of which setup method you used previously.
Disable secure linux by editing the “/etc/selinux/config” file, making sure the SELINUX flag is set as follows.
SELINUX=disabled
Alternatively, this alteration can be done using the GUI tool (Applications > System Settings > Security Level). Click on the SELinux tab and disable the feature. If SELinux is disabled after installation, the server will need a reboot for the change to take effect.
Create the directories in which the Oracle software will be installed.
mkdir -p /u01/app/oracle/product/11.2.0/db11g
chown -R oracle:oinstall /u01
chmod -R 775 /u01
Login as root and issue the following command.
xhost +<machine-name>
Login as the oracle user and add the following lines at the end of the “.bash_profile” file, remembering to adjust them for your specific installation.
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=tauseef.com; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db11g; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
Installation
Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.
DISPLAY=<machine-name>:0.0; export DISPLAY
Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.
./runInstaller
Proceed with the installation of your choice. You can see the type of installation I performed by clicking on the links below to see screen shots of each stage.
Configure Security Updates
Select Install Option
System Class
Node Selection
Select Install Type
Typical Install Configuration
Create Inventory
Perform Prerequisite Checks
Summary
Install Product
Database Configuration Assistant
Database Configuration Assistant 2
Execute Configuration Scripts
Finish
Post Installation
Edit the “/etc/oratab” file setting the restart flag for each instance to ‘Y’.
DB11G:/u01/app/oracle/product/11.2.0/db_1:Y

Installing Oracle 11g on Linux

Unpack Files Unzip the files. # 11.2.0.1 unzip linux.x64_11gR2_database_1of2.zip unzip linux.x64_11gR2_database_2of2.zip #11.2.0.2 u...