Showing posts with label 11g R2. Show all posts
Showing posts with label 11g R2. Show all posts

18 October 2014

Background Media Recovery terminated with ORA-1274 after adding a Datafile

Symptom : Recently I got error in alert log of DR database like :-

Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file /ora/visprod/home/product/11.2.0/log/diag/rdbms/visprodr/VISPRODR/trace/VISPRODR_pr00_25255.trc:
ORA-01111: name for data file 286 is unknown - rename to correct file
ORA-01110: data file 286: '/ora/visprod/home/product/11.2.0/dbs/UNNAMED00286'
ORA-01157: cannot identify/lock data file 286 - see DBWR trace file
ORA-01111: name for data file 286 is unknown - rename to correct file
ORA-01110: data file 286: '/ora/visprod/home/product/11.2.0/dbs/UNNAMED00286'
Slave exiting with ORA-1111 exception
Errors in file /ora/visprod/home/product/11.2.0/log/diag/rdbms/visprodr/VISPRODR/trace/VISPRODR_pr00_25255.trc:
ORA-01111: name for data file 286 is unknown - rename to correct file
ORA-01110: data file 286: '/ora/visprod/home/product/11.2.0/dbs/UNNAMED00286'
ORA-01157: cannot identify/lock data file 286 - see DBWR trace file
ORA-01111: name for data file 286 is unknown - rename to correct file
ORA-01110: data file 286: '/ora/visprod/home/product/11.2.0/dbs/UNNAMED00286'
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (VISPRODR)

Reason :- Datafile was added on Prod on one mount point, on DR site there was no space left out so newly added datafile went to $ORACLE_HOME/dbs/UNNAMED00286 name. This stop archivelog to apply on DR database and if we try to start MRP process, it failed to start.

Solution: - 
1. Shutdown DR database.
2. Change pfile parameter "Standy_file_management" and set to MANUAL.
3. Mount DR with pfile.
4.  alter database create datafile '/ora/visprod/home/product/11.2.0/dbs/UNNAMED00286' as 'New FileName';
5.Shutdown DR database. 
6. Change again "Standy_file_management" to Auto
7. Start DR on Mount. 
8. Start MRP process. 

Now Archive will start applying.

8 August 2014

How to compile invalid objects


UTL_RECOMP

This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects. Although invalid objects are recompiled automatically on use, it is useful to run this script prior to operation because this will either eliminate or minimize subsequent latencies due to on-demand automatic recompilation at runtime.

Parallel recompilation can exploit multiple CPUs to reduce the time taken to recompile invalid objects. The degree of parallelism is specified by the first argument to RECOMP_PARALLEL Procedure.
In general, a parallelism setting of one thread for each available CPU provides a good initial setting. However, please note that the process of recompiling an invalid object writes a significant amount of data to system tables and is fairly I/O intensive. A slow disk system may be a significant bottleneck and limit speedups available from a higher degree of parallelism.

Examples

Recompile all objects sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL();
 
Recompile objects in schema SCOTT sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL('SCOTT');
 
Recompile all objects using 4 parallel threads:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4);
 
Recompile objects in schema JOE using the number of threads specified in the parameter JOB_QUEUE_PROCESSES:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(NULL, 'JOE');

Another simple method is to use utlrp.sql command located in $ORACLE_HOME/rdbms/admin
 
sqlplus "/as sysdba"
sqlplus >@$ORACLE_HOME/rdbms/admin/utlrp.sql;

6 July 2014

Enable/Disable Archive log mode in RAC 11gR2

Enable Archivelog

1)  Login to one of the nodes , verify the archive log mode

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 200
Current log sequence 201

SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
PUB NOARCHIVELOG

2) Disable the cluster instance parameter by setting cluster_database to FALSE from the current instance:
sqlplus "/ as sysdba"

SQL> alter system set cluster_database=false scope=spfile sid='orcl1;


3) Shutdown all instances accessing the clustered database:

  srvctl stop database -d orcl

4)Using the local instance, MOUNT the database:

sqlplus "/ as sysdba"
SQL> startup mount 

5) Enable archiving:

SQL> alter database archivelog;

6) Re-enable support for clustering by modifying the instance parameter cluster_database to TRUE from the current instance:

SQL> alter system set cluster_database=true scope=spfile sid='orcl1;

7) Shutdown the local instance:

SQL> shutdown immediate 

8) Bring all instance back up using srvctl:

srvctl start database -d orcl

9)Login to the local instance and verify Archive Log Mode is enabled:

sqlplus "/ as sysdba"

SQL> archive log list
Database log mode            Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     200
Next log sequence to archive  201

After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs.


Disable Archivelog
1) export oracle_sid=orcl1

SQL> alter system set cluster_database=false scope=spfile sid='orcl1';
System altered.

2) srvctl stop database -d orcl -o immediate

3) SQL> startup mount

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Next log sequence to archive 19
Current log sequence 19

SQL> alter database noarchivelog;
Database altered.

SQL> alter system set cluster_database=true scope=spfile sid='orcl1';
System altered.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

4) srvctl start database -d orcl

5) export oracle_sid=orcl1
sqlplus / as sysdba

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Current log sequence 19
SQL>

29 March 2014

Step by Step Upgrade to 11.2.0.3 from 11.2.0.1 on Linux


Step by Step Upgrade to 11.2.0.3 from 11.2.0.1 on Linux

SQL> select version from v$instance;
VERSION
-----------------
11.2.0.1.0
SQL>
  • Download patch p10404530_112030_Linux-x86-64_1of7.zip & p10404530_112030_Linux-x86-64_2of7.zip and unzip it. This will create a directory name “database”.
  • Create one directory 11.3.0 to create new oracle home
cd /u01/app/oracle/product
mkdir 11.3.0

export ORACLE_SID=<Oracle SID>
export ORACLE_HOME=/u01/app/oracle/product/11.3.0
export PATH=$ORACLE_HOME/bin:$PATH

  • Install the oracle software only on this new oracle home.
  • Check correct oracle sid should point in  /etc/oratab and there should not be multiple ORACLE_HOME for same SID. i.e RAC1:/u01/app/oracle/product/11.3.0:N
  • Copy init.ora/spfile and password file (orapw<sid>.ora) from 11.2.0.1 $ORACLE_HOME/dbs to 11.2.0.3 $ORACLE_HOME/dbs
  • Copy listener and tnsnames.ora file from 11.2.0.1 home to 11.2.0.3 home and modify it, if needed.
  • Download pre-upgrade utility script utlu112i_5.sql. MOS document ID 884522.1 and correct if any issue.
  •  Purge DBA_RECYCLEBIN if this contains any objects.
  • Gather dictionary status.
  • Go to New ORACLE_HOME .. > /u01/app/oracle/product/11.3.0/bin and run dbua command 
 
          ./dbua 

Follow the  screen for options.
your database is upgraded to 11.2.0.3. Change .bash_profle for new ORACLE_HOME. 

28 March 2014

Step by Step Installation of 11g R2 on RHEL 5 , 64 bit using VMWARE


Linux Operating system installation

Kindly refer my previous post to install Linux 64 bit using Vmware.

System Requirements check :
  • We need Minimum 1gb of ram memory for the Linux machine.
  • The swap space should be Minimum 1.5 times the ram size(for ram size b/n 1gb to 2 gb).
  • The /tmp mount should have Minimum 1gb of space available.
  • Check the free space of the mount points where we are installing oracle software and the database files.
  • Check the Linux kernel version, the kernel version for installing oracle 11gR2 should be equal to or greater than 2.6.18. 
 Command to check linux kernel version 
[root@dev1 ~]# uname -r
2.6.18-128.el5
[root@dev1 ~]#

Prerequisites for installing oracle software.
·         Add an entry of ip address,fully qualified name and machine name in /etc/hosts
[root@dev1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
192.168.152.131 dev1.localdomain.com dev1
127.0.0.1               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
[root@dev1 ~]#

·         Add the following lines in /etc/sysctl.conf

Some entries may already existing in this linux kernel configuration file. If the given value is greater than the existing value for any parameter, we have to adjust it to the greater value otherwise we can ignore it.

# Oracle settings
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
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.tcp_rmem = 4194304 4194304 4194304

To make effect the kernel parameters changes immediately
[root@dev1 ~]# sysctl –p

·         Create OS user (oracle software owner) and groups needed to install oracle software.
[root@dev1 ~]# groupadd dba
[root@dev1 ~]# groupadd oinstall
[root@dev1 ~]# groupadd oper
[root@dev1 ~]# useradd -g oinstall -G dba,oper oracle
[root@dev1 ~]# passwd oracle
Changing password for user oracle.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@dev1 ~]#
 
·         Create a directory where oracle software will be installed and provide the necessary ownership and privileges.
[root@ dev1 ~]# mkdir -p /u01/app/oracle/product/11.2.0/db_1
[root@ dev1~]# chown -R oracle:oinstall /u01
[root@ dev1~]# chmod -R 775 /u01
[root@ dev1~]#

·         Set the shell limits in /etc/security/limits.conf for the oracle owner
oracle           soft    nproc   2047
oracle           hard    nproc   16384
oracle           soft    nofile  1024
oracle           hard    nofile  65536

·         Add the following line to /etc/pam.d/login if the same is not there
session    required     pam_limits.so

·         Check the /etc/selinux/config to see whether the selinux parameter is disabled.
SELINUX=disabled

·         Verify that the required rpm packages are installed for oracle
Mount the RHEL5 dvd go to /media/RHEL_5.7 i386 DVD/Server directory and give the below commands. By using "rpm -Uvh <package name>" command, the packages will be installed if it is not previously installed

rpm -Uvh binutils-2*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh elfutils-libelf-0*
rpm -Uvh elfutils-libelf-devel-0*
rpm -Uvh elfutils-libelf-devel-static-0*
rpm -Uvh gcc-4*
rpm -Uvh gcc-c++-4*
rpm -Uvh glibc-2*`uname -p`*
rpm -Uvh glibc-common-2*
rpm -Uvh glibc-devel-2*
rpm -Uvh glibc-headers-2*
rpm -Uvh kernel-headers-2*
rpm -Uvh ksh-20*
rpm -Uvh libaio-0*
rpm -Uvh libaio-devel-0* 
rpm -Uvh libgcc-4*
rpm -Uvh libgomp-4*
rpm -Uvh libstdc++-4* 
rpm -Uvh libstdc++-devel-4*
rpm -Uvh make-3*
rpm -Uvh numactl-devel-0*
rpm -Uvh sysstat-7*
rpm -Uvh unixODBC-2*
rpm -Uvh unixODBC-devel-2*
rpm -Uvh compat-libstdc++-33*

·         Add the following lines to .bash_profile  file.
switch to oracle user and add the following entries.

# Oracle settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR


ORACLE_HOSTNAME=dev1.localdomain.com; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=ora11g; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH

if [ $USER = "oracle" ]; then
   if [ $SHELL = "/bin/ksh" ]; then
     ulimit -p 16384
     ulimit -n 65536
   else
     ulimit -u 16384 -n 65536
   fi
fi

·         Run the following command to make the given changes effective.

[oracle@dev1 ~]$ source ~/.bash_profile 
[oracle@dev1 ~]$ 

·         Install the oracle software and create a database using DBCA.
Now the system is ready to install oracle software.

Login as oracle user, download/copy the oracle 11gR2 software and unzip the media file.A folder named "database" will be created, go to the database folder and execute the "runInstaller" script to start oracle universal installer.

[oracle@dev1 database]$ ll
total 36
drwxr-xr-x 12 oracle oinstall 4096 Mar 28 13:22 doc
drwxr-xr-x  4 oracle oinstall 4096 Mar 28 13:22 install
drwxr-xr-x  2 oracle oinstall 4096 Mar 28 13:22 response
drwxr-xr-x  2 oracle oinstall 4096 Mar 28 13:22 rpm
-rwxr-xr-x  1 oracle oinstall 3226 Mar 28 13:22 runInstaller
drwxr-xr-x  2 oracle oinstall 4096 Mar 28 13:22 sshsetup
drwxr-xr-x 14 oracle oinstall 4096 Mar 28 13:26 stage
-rwxr-xr-x  1 oracle oinstall 5402 Mar 28 13:26 welcome.html

Please follow the screenshot for database installation.











 Your database installation is complete.

[oracle@dev1 database]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 28 17:50:15 2014

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


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

SQL> select instance_name,version from  v$instance;

INSTANCE_NAME    VERSION
---------------- -----------------
ora11g           11.2.0.1.0

SQL>