Lokeshaggarwal's DBA

Live the life you love. Love the life you live

ORA-27154: post/wait create failed ORA-27300: OS system dependent operation:semget failed with status: 28


In the below problem, I was trying to startup my database, but the system throws an error ” ORA-27300, ORA-27300″.So, below are some of the steps.

[oracle@rac1 ~]$ sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 7 13:59:52 2012

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

Connected to an idle instance.

SQL> startup
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper
SQL> exit
Disconnected

Now, wen we and see the above error, we can easily understand that these error is basically realted to some OS error.

So, I had tried to execute df -kh, but the filesystem had sufficient space.

Next step I tried to find out the memory usage:

[oracle@rac1 ~]$ top

top – 14:01:43 up 11 min, 2 users, load average: 0.06, 0.45, 0.45
Tasks: 164 total, 2 running, 162 sleeping, 0 stopped, 0 zombie
Cpu(s): 50.0%us, 0.0%sy, 0.0%ni, 50.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 2075560k total, 1540896k used, 534664k free, 43604k buffers
Swap: 2096440k total, 0k used, 2096440k free, 687916k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7086 oracle 15 0 2196 1052 804 R 213.7 0.1 0:00.04 top
1 root 15 0 2064 628 536 S 0.0 0.0 0:00.55 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.06 migration/0
3 root 34 19 0 0 0 R 0.0 0.0 0:00.00 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root RT -5 0 0 0 S 0.0 0.0 0:00.09 migration/1
6 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/1
7 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/1
8 root 10 -5 0 0 0 S 0.0 0.0 0:00.02 events/0
9 root 10 -5 0 0 0 S 0.0 0.0 0:00.03 events/1
10 root 12 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
11 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
15 root 10 -5 0 0 0 S 0.0 0.0 0:00.01 kblockd/0
16 root 10 -5 0 0 0 S 0.0 0.0 0:00.11 kblockd/1
17 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid
186 root 13 -5 0 0 0 S 0.0 0.0 0:00.00 cqueue/0
187 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 cqueue/1
[oracle@rac1 ~]$ free
total used free shared buffers cached
Mem: 2075560 1540904 534656 0 43604 687916
-/+ buffers/cache: 809384 1266176

Swap: 2096440 0 2096440

[oracle@rac1 ~]$ vmstat 5 5
procs ———–memory———- —swap– —–io—- –system– —–cpu——
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 534496 43660 687928 0 0 575 49 596 977 2 5 83 10 0
0 0 0 534480 43668 687952 0 0 0 14 1025 1719 0 7 92 0 0
0 0 0 534248 43684 687956 0 0 1 20 1028 1844 0 2 98 0 0
0 0 0 534240 43684 687956 0 0 0 0 1021 1989 0 1 99 0 0
1 0 0 534240 43692 687956 0 0 1 11 1028 1888 0 2 98 0 0
[oracle@rac1 ~]$ cat /proc/swaps
Filename Type Size Used Priority
/dev/sda5 partition 2096440 0 -1

So, from the above command i.e top and vmstat we got to know that system was not lacking physical memory.

[oracle@rac1 ~]$ ipcs -la

—— Shared Memory Limits ——–
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 1073741824
min seg size (bytes) = 1

—— Semaphore Limits ——–
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 100
max ops per semop call = 32
semaphore max value = 32767

—— Messages: Limits ——–
max queues system wide = 16
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536

[oracle@rac1 ~]$ cat /etc/sysctl.conf
# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename
# Useful for debugging multi-threaded applications
kernel.core_uses_pid = 1

# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1

# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536

# Controls the default maxmimum size of a mesage queue
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 4294967295

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 268435456
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
kernel.sem = 250 100
[oracle@rac1 ~]$ vi /etc/sys
sysconfig/ sysctl.conf sysctl.confe syslog.conf
[oracle@rac1 ~]$ vi /etc/sysctl.conf
[oracle@rac1 ~]$ exit

logout

As per the Oracle ,the value for the semaphore should be set to :

kernel.sem = 250 32000 100 200

[root@rac1 raczone]# vi /etc/sysctl.conf
[root@rac1 raczone]# sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 4294967295
kernel.shmall = 268435456
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
kernel.sem = 250 32000 100 128
[root@rac1 raczone]# su – oracle
[oracle@rac1 ~]$ export ORACLE_BASE=/raczone/11.2.0
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base for ORACLE_HOME=/raczone/11.2.0/product/11.2.0/dbhome_1 is /raczone/11.2.0
[oracle@rac1 ~]$ cat /proc/swapinfo
cat: /proc/swapinfo: No such file or directory
[oracle@rac1 ~]$ sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 7 14:06:56 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 389189632 bytes
Fixed Size 1336736 bytes
Variable Size 268438112 bytes
Database Buffers 113246208 bytes
Redo Buffers 6168576 bytes
Database mounted.
Database opened.
SQL>

November 7, 2012 Posted by | Troubleshooting | Leave a comment

Oracle DBA Training


We provide Oracle DBA Training for the below mentioned Topics:

1) Oracle 10g RAC

2) Oracle 11g RAC

3) Oracle 9i/10g/11g Administration

4) Oracle Data Guard

5) Golden Gate

Anybody interested, they may contact at aggarwalokesh@gmail.com

August 28, 2012 Posted by | Training | 2 Comments

RMAN Backup Architecture (Diagram)


RMAN Backup Architecture

August 28, 2012 Posted by | Backup and Recovery | Leave a comment

WARNING OGG-01194 EXTRACT task LOAD2 abended : There is no trail to reposition to when doing direct load task


This problem arises because golden gate user doesn’t have proper privileges.

Problem can be solved by granting insert anytable privilege to the user i.e. grant insert any table to ggs_user.

August 8, 2012 Posted by | Golden Gate | Leave a comment

Node addition in 11gR2 RAC


Here, we will see a demonstration of adding a node in 11gR2.

[root@rac2 ~]# export DISPLAY=:0.0

[root@rac2 ~]# xhost +

access control disabled, clients can connect from any host

[root@rac2 ~]# su – oracle

[oracle@rac2 ~]$ cd /raczone/db_home1/oui/bin/

[oracle@rac2 bin]$ ./addNode.sh “CLUSTER_NEW_NODES={rac3}”

Starting Oracle Universal Installer…

Checking swap space: must be greater than 500 MB.   Actual 1599 MB    Passed

Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed

Oracle Universal Installer, Version 11.2.0.1.0 Production

Copyright (C) 1999, 2009, Oracle. All rights reserved.

Performing tests to see whether nodes rac1,rac3 are available

……………………………………………………… 100% Done.

.

—————————————————————————–

Cluster Node Addition Summary

Global Settings

Source: /raczone/db_home1

New Nodes

Space Requirements

New Nodes

rac3

/: Required 3.60GB : Available 28.41GB

Installed Products

Product Names

Oracle Database 11g 11.2.0.1.0

Sun JDK 1.5.0.17.0

Installer SDK Component 11.2.0.1.0

Oracle One-Off Patch Installer 11.2.0.0.2

Oracle Universal Installer 11.2.0.1.0

Oracle Configuration Manager Deconfiguration 10.3.1.0.0

Oracle DBCA Deconfiguration 11.2.0.1.0

Oracle RAC Deconfiguration 11.2.0.1.0

Oracle Database Deconfiguration 11.2.0.1.0

Oracle Configuration Manager 10.3.1.1.0

Oracle ODBC Driverfor Instant Client 11.2.0.1.0

LDAP Required Support Files 11.2.0.1.0

SSL Required Support Files for InstantClient 11.2.0.1.0

Bali Share 1.1.18.0.0

Oracle Extended Windowing Toolkit 3.4.47.0.0

Oracle JFC Extended Windowing Toolkit 4.2.36.0.0

Oracle Real Application Testing 11.2.0.1.0

Oracle Database Vault J2EE Application 11.2.0.1.0

Oracle Label Security 11.2.0.1.0

Oracle Data Mining RDBMS Files 11.2.0.1.0

Oracle OLAP RDBMS Files 11.2.0.1.0

Oracle OLAP API 11.2.0.1.0

Platform Required Support Files 11.2.0.1.0

Oracle Database Vault option 11.2.0.1.0

Oracle RAC Required Support Files-HAS 11.2.0.1.0

SQL*Plus Required Support Files 11.2.0.1.0

Oracle Display Fonts 9.0.2.0.0

Oracle Ice Browser 5.2.3.6.0

Oracle JDBC Server Support Package 11.2.0.1.0

Oracle SQL Developer 11.2.0.1.0

Oracle Application Express 11.2.0.1.0

XDK Required Support Files 11.2.0.1.0

RDBMS Required Support Files for Instant Client 11.2.0.1.0

SQLJ Runtime 11.2.0.1.0

Database Workspace Manager 11.2.0.1.0

RDBMS Required Support Files 11.2.0.1.0

Oracle Globalization Support 11.2.0.1.0

Exadata Storage Server 11.2.0.1.0

Provisioning Advisor Framework 10.2.0.4.2

Enterprise Manager Database Plugin — Repository Support 11.2.0.1.0

Enterprise Manager Repository Core Files 10.2.0.4.2

Enterprise Manager Database Plugin — Agent Support 11.2.0.1.0

Enterprise Manager Grid Control Core Files 10.2.0.4.2

Enterprise Manager Common Core Files 10.2.0.4.2

Enterprise Manager Agent Core Files 10.2.0.4.2

Agent Required Support Files 10.2.0.4.2

regexp 2.1.9.0.0

Parser Generator Required Support Files 11.2.0.1.0

Oracle 11g Warehouse Builder Required Files 11.2.0.1.0

Oracle Notification Service (eONS) 11.2.0.1.0

Oracle Text Required Support Files 11.2.0.1.0

Precompiler Required Support Files 11.2.0.1.0

Oracle Database 11g Multimedia Files 11.2.0.1.0

Oracle Multimedia Java Advanced Imaging 11.2.0.1.0

Oracle Multimedia Annotator 11.2.0.1.0

Oracle JDBC/OCI Instant Client 11.2.0.1.0

Oracle Multimedia Locator RDBMS Files 11.2.0.1.0

Oracle Core Required Support Files 11.2.0.1.0

Oracle Help For Java 4.2.9.0.0

Sample Schema Data 11.2.0.1.0

Oracle Starter Database 11.2.0.1.0

Oracle Message Gateway Common Files 11.2.0.1.0

Oracle XML Query 11.2.0.1.0

XML Parser for Oracle JVM 11.2.0.1.0

Expat libraries 2.0.1.0.1

Installation Plugin Files 11.2.0.1.0

Enterprise Manager Common Files 10.2.0.4.2

Perl Modules 5.10.0.0.1

Deinstallation Tool 11.2.0.1.0

Oracle Quality of Service Management (Client) 11.2.0.1.0

Perl Interpreter 5.10.0.0.1

JAccelerator (COMPANION) 11.2.0.1.0

Oracle Containers for Java 11.2.0.1.0

Oracle Code Editor 1.2.1.0.0I

Oracle Net Required Support Files 11.2.0.1.0

Secure Socket Layer 11.2.0.1.0

Oracle Universal Connection Pool 11.2.0.1.0

Oracle JDBC/THIN Interfaces 11.2.0.1.0

Oracle Multimedia Client Option 11.2.0.1.0

Oracle Java Client 11.2.0.1.0

Character Set Migration Utility 11.2.0.1.0

Oracle Locale Builder 11.2.0.1.0

PL/SQL Embedded Gateway 11.2.0.1.0

OLAP SQL Scripts 11.2.0.1.0

Database SQL Scripts 11.2.0.1.0

Oracle Globalization Support 11.2.0.1.0

Required Support Files 11.2.0.1.0

SQL*Plus Files for Instant Client 11.2.0.1.0

Oracle ODBC Driver 11.2.0.1.0

Oracle Database User Interface 2.2.13.0.0

Oracle Notification Service 11.2.0.0.0

Enterprise Manager Minimal Integration 11.2.0.1.0

XML Parser for Java 11.2.0.1.0

Oracle Security Developer Tools 11.2.0.1.0

Oracle Wallet Manager 11.2.0.1.0

Cluster Verification Utility Common Files 11.2.0.1.0

Oracle Clusterware RDBMS Files 11.2.0.1.0

Oracle UIX 2.2.24.5.0

Enterprise Manager plugin Common Files 11.2.0.1.0

HAS Common Files 11.2.0.1.0

Precompiler Common Files 11.2.0.1.0

Installation Common Files 11.2.0.1.0

Oracle Help for the  Web 2.0.14.0.0

Oracle LDAP administration 11.2.0.1.0

Buildtools Common Files 11.2.0.1.0

Assistant Common Files 11.2.0.1.0

Oracle Recovery Manager 11.2.0.1.0

PL/SQL 11.2.0.1.0

Generic Connectivity Common Files 11.2.0.1.0

Oracle Database Gateway for ODBC 11.2.0.1.0

Oracle Programmer 11.2.0.1.0

Oracle Database Utilities 11.2.0.1.0

Enterprise Manager Agent 10.2.0.4.2

Oracle Netca Client 11.2.0.1.0

SQL*Plus 11.2.0.1.0

Oracle Call Interface (OCI) 11.2.0.1.0

Oracle Multimedia Locator 11.2.0.1.0

Oracle Multimedia 11.2.0.1.0

Oracle Net 11.2.0.1.0

Database Configuration and Upgrade Assistants 11.2.0.1.0

Oracle XML Development Kit 11.2.0.1.0

Oracle JVM 11.2.0.1.0

Oracle Advanced Security 11.2.0.1.0

Oracle Internet Directory Client 11.2.0.1.0

HAS Files for DB 11.2.0.1.0

Oracle Enterprise Manager Console DB 11.2.0.1.0

Oracle Net Listener 11.2.0.1.0

Oracle Text 11.2.0.1.0

Oracle Net Services 11.2.0.1.0

Oracle Database 11g 11.2.0.1.0

Oracle OLAP 11.2.0.1.0

Oracle Spatial 11.2.0.1.0

Oracle Partitioning 11.2.0.1.0

Enterprise Edition Options 11.2.0.1.0

—————————————————————————–

Instantiating scripts for add node (Thursday, June 28, 2012 1:58:45 PM IST)

.                                                                 1% Done.

Instantiation of add node scripts complete

Copying to remote nodes (Thursday, June 28, 2012 1:58:55 PM IST)

…………………………………………………………………………………..                                 96% Done.

Home copied to new nodes

Saving inventory on nodes (Thursday, June 28, 2012 2:24:28 PM IST)

.                                                               100% Done.

Save inventory complete

WARNING:

The following configuration scripts need to be executed as the “root” user in each cluster node.

/raczone/db_home1/root.sh #On nodes rac3

To execute the configuration scripts:

1. Open a terminal window

2. Log in as “root”

3. Run the scripts in each cluster node

The Cluster Node Addition of /raczone/db_home1 was successful.

Please check ‘/tmp/silentInstall.log’ for more details.

[oracle@rac2 bin]$

Now, add an instance in a rac enviornment

srvctl add instance -d dev -i dev3 -n rac3

So, after successfully adding node, you can check the status of your database

[grid@rac3 ~]$ srvctl status database -d dev

Instance dev1 is running on node rac1

Instance dev2 is running on node rac2

Instance dev3 is running on node rac3

July 3, 2012 Posted by | RAC | Leave a comment

opmn failed to start a managed process after the maximum retry limit


$ opmnctl  startall

opmnctl: starting opmn and all managed processes...
================================================================================
opmn id=test:6201
    5 of 6 processes started.

ias-instance id=EnterpriseManager0.Test
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------------------------------------------------------------------------
ias-component/process-type/process-set:
    OC4J/OC4J_EM/default_island

Error
--> Process (pid=6900)
    failed to start a managed process after the maximum retry limit
    Log:
    /opt/oracle/product/oms10g/opmn/logs/OC4J~OC4J_EM~default_island~1

If “opmnctl startall” fails use “ias-component” and “process-type” to start individual components

$ opmnctl startproc ias-component=OC4J

opmnctl: starting opmn managed processes...
================================================================================
opmn id=Test:6201
    0 of 1 processes started.

ias-instance id=EnterpriseManager0.Test
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
    OC4J/OC4J_EM/default_island

Error
--> Process (pid=20563)
    failed to start a managed process after the maximum retry limit
    Log:
    /opt/oracle/product/oms10g/opmn/logs/OC4J~OC4J_EM~default_island~1

$ opmnctl status

Processes in Instance: EnterpriseManager0.Test
-------------------+--------------------+---------+---------
ias-component      | process-type       |     pid | status
-------------------+--------------------+---------+---------
DSA                | DSA                |     N/A | Down
LogLoader          | logloaderd         |     N/A | Down
HTTP_Server        | HTTP_Server        |    4750 | Alive
dcm-daemon         | dcm-daemon         |     N/A | Down
OC4J               | home               |    4752 | Alive
OC4J               | OC4J_EMPROV        |    4751 | Alive
OC4J               | OC4J_EM            |     N/A | Down
WebCache           | WebCache           |    4754 | Alive
WebCache           | WebCacheAdmin      |    4755 | Alive

To Start and stop individual ias components use :  opmnctl startproc ias-component=OC4J

To Start and stop individual processes use :  opmnctl startproc process-type=OC4J_EMPROV

In this case both options “ias-component” and “process-type”  failed to start OMS. We were not inclined to restart database but there were rogue processes and at the end we had to restart database to get OMS working.
$ /opt/oracle/product/oms10g/opmn/bin/opmnctl status
Processes in Instance: EnterpriseManager0.o2hp127
-------------------+--------------------+---------+---------
ias-component      | process-type       |     pid | status
-------------------+--------------------+---------+---------
DSA                | DSA                |     N/A | Down
LogLoader          | logloaderd         |     N/A | Down
HTTP_Server        | HTTP_Server        |   27225 | Alive
dcm-daemon         | dcm-daemon         |     N/A | Down
OC4J               | home               |   27230 | Alive
OC4J               | OC4J_EMPROV        |   27226 | Alive
OC4J               | OC4J_EM            |   27227 | Alive
WebCache           | WebCache           |   27228 | Alive
WebCache           | WebCacheAdmin      |   27229 | Alive

June 22, 2012 Posted by | Uncategorized | 1 Comment

error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory


While installing Grid Conrol 10.2.0.1 tyou might encountered an error “error while loading shared libraries: libdb.so.2

Solution:

ln -s   /usr/lib/libgdbm.so.2.0.0  /usr/lib/libdb.so.2

June 22, 2012 Posted by | Uncategorized | Leave a comment

Switchover from primary to standby database


Here, we are performing a switchover from tester(primary) database to standby database.

Primary Database (Tester)

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
——————–
SESSIONS ACTIVE

 

Session Active status means that you have to perform a switchover from WITH SESSION SHUTDOWN clause.It means that many users are still connecting to the database, so perform a session shutdown clause.

 

SQL> SELECT PROGRAM, TYPE FROM V$SESSION WHERE TYPE=’USER’;

PROGRAM                                                          TYPE
—————————————————————- ———-

 

SQL> SELECT PROGRAM, TYPE FROM V$SESSION WHERE TYPE=’USER’;

PROGRAM                                                          TYPE
—————————————————————- ———-
emagent.exe                                                      USER
ORACLE.EXE                                                       USER
sqlplus.exe                                                      USER

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
  2  WITH SESSION SHUTDOWN;

Database altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             218106756 bytes
Database Buffers          385875968 bytes
Redo Buffers                7135232 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

SQL> SHOW PARAMETER DB_NAME

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      tester
SQL> SELECT THREAD#, SEQUENCE#, APPLIED,to_char(COMPLETION_TIME,’DD-MON-YYYY:HH2
4:MI:SS’) FROM V$ARCHIVED_LOG;

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         29 NO  07-OCT-2011:20:49:17
         1         30 NO  10-OCT-2011:14:29:36
         1         31 NO  11-OCT-2011:21:41:22
         1         32 NO  11-OCT-2011:22:16:18
         1         32 NO  11-OCT-2011:22:16:19
         1         32 NO  11-OCT-2011:22:16:20
         1         33 NO  11-OCT-2011:22:47:50
         1         34 NO  12-OCT-2011:19:18:14
         1         34 NO  12-OCT-2011:19:18:15
         1         34 NO  12-OCT-2011:19:18:16
         1         35 NO  13-OCT-2011:21:06:59

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         36 NO  14-OCT-2011:09:55:03
         1         37 NO  14-OCT-2011:09:56:02
         1         38 NO  14-OCT-2011:09:56:23
         1         39 NO  14-OCT-2011:10:28:16
         1         39 NO  14-OCT-2011:10:28:17
         1         40 NO  14-OCT-2011:10:28:21
         1         41 NO  14-OCT-2011:21:02:05
         1         41 NO  14-OCT-2011:21:02:07
         1         42 NO  14-OCT-2011:21:02:10
         1         43 NO  14-OCT-2011:21:02:59
         1         44 NO  14-OCT-2011:22:08:59

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         44 NO  14-OCT-2011:22:09:00
         1         44 NO  14-OCT-2011:22:09:20
         1         41 NO  14-OCT-2011:22:09:40
         1         42 NO  14-OCT-2011:22:09:47
         1         43 NO  14-OCT-2011:22:09:54
         1         45 NO  14-OCT-2011:22:10:47
         1         45 YES 14-OCT-2011:22:11:21
         1         46 NO  15-OCT-2011:19:16:03
         1         46 NO  15-OCT-2011:19:16:04
         1         47 NO  15-OCT-2011:19:16:48
         1         48 NO  15-OCT-2011:19:17:04

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         49 NO  15-OCT-2011:19:19:13
         1         50 NO  15-OCT-2011:19:20:05
         1         51 NO  15-OCT-2011:19:20:08
         1         52 NO  15-OCT-2011:19:24:21
         1         53 NO  15-OCT-2011:19:24:59
         1         53 NO  15-OCT-2011:19:25:00
         1         46 NO  15-OCT-2011:19:25:11
         1         47 NO  15-OCT-2011:19:25:14
         1         48 NO  15-OCT-2011:19:25:14
         1         49 NO  15-OCT-2011:19:25:18
         1         50 NO  15-OCT-2011:19:25:18

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         51 NO  15-OCT-2011:19:25:23
         1         52 NO  15-OCT-2011:19:25:28
         1         54 YES 15-OCT-2011:19:26:33
         1         54 NO  15-OCT-2011:19:26:59
         1         55 YES 15-OCT-2011:19:32:00
         1         55 NO  15-OCT-2011:19:32:12
         1         56 YES 15-OCT-2011:19:49:15
         1         56 NO  15-OCT-2011:19:49:26
         1         57 NO  15-OCT-2011:20:05:05
         1         57 NO  15-OCT-2011:20:05:13
         1         58 NO  15-OCT-2011:20:06:58

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         58 NO  15-OCT-2011:20:06:59

56 rows selected.

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS “LAST_APPLIED_LOG”    FROM V$LOG_HISTORY
    GROUP BY
  2  THREAD#;

   THREAD# LAST_APPLIED_LOG
———- —————-
         1               57

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_S
TANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
——— ———— ———- ———- ———- ———-
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0

SQL>  alter database recover managed standby database disconnect from session;
 alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
SQL>  alter database recover managed standby database;
Previous standby
—————————-

 Here we are performing a switchover from standby to primary database.

 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
——————–
TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             180358020 bytes
Database Buffers          423624704 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> SHOW PARAMETER DB_NAME

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      TESTER
SQL> SHOW PARAMETER DB_UNI

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_unique_name                       string      STANDBY
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
——————–
SESSIONS ACTIVE

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> SELECT THREAD#, SEQUENCE#, APPLIED,to_char(COMPLETION_TIME,’DD-MON-YYYY:HH2
4:MI:SS’) FROM V$ARCHIVED_LOG;

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         44 YES 14-OCT-2011:22:09:37
         1         41 YES 14-OCT-2011:22:09:39
         1         42 YES 14-OCT-2011:22:09:46
         1         43 YES 14-OCT-2011:22:09:53
         1         45 YES 14-OCT-2011:22:11:23
         1         53 YES 15-OCT-2011:19:25:00
         1         46 YES 15-OCT-2011:19:25:10
         1         47 YES 15-OCT-2011:19:25:14
         1         48 YES 15-OCT-2011:19:25:14
         1         49 YES 15-OCT-2011:19:25:18
         1         50 YES 15-OCT-2011:19:25:18

   THREAD#  SEQUENCE# APP TO_CHAR(COMPLETION_T
———- ———- — ——————–
         1         51 YES 15-OCT-2011:19:25:22
         1         52 YES 15-OCT-2011:19:25:28
         1         54 YES 15-OCT-2011:19:26:46
         1         55 YES 15-OCT-2011:19:32:05
         1         56 YES 15-OCT-2011:19:49:18
         1         57 YES 15-OCT-2011:20:05:08
         1         58 YES 15-OCT-2011:20:06:57
         1         59 NO  15-OCT-2011:20:18:21

19 rows selected.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_S
TANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
——— ———— ———- ———- ———- ———-
ARCH      CLOSING               1         59          1        657
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
——————–
SESSIONS ACTIVE

Now, lets perform a switchover operation using DGMGRL(Data Guard Manager Role)

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>dgmgrl
DGMGRL for 32-bit Windows: Version 10.2.0.1.0 – Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/oracle@tester
Connected.
DGMGRL> show configuration

Configuration
  Name:                primary
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    tester  – Primary database
    standby – Physical standby database

Current status for “primary”:
SUCCESS

DGMGRL> switchover to standby
Performing switchover NOW, please wait…
Operation requires shutdown of instance “tester” on database “tester”
Shutting down instance “tester”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “standby” on database “standby”
Shutting down instance “standby”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “tester” on database “tester”
Starting instance “tester”…
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance “tester”
You must start instance “tester” manually
Operation requires startup of instance “standby” on database “standby”
You must start instance “standby” manually
Switchover succeeded, new primary is “standby”
DGMGRL> connect sys/oracle@standby
Connected.
DGMGRL> show configuration

Configuration
  Name:                primary
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    tester  – Physical standby database
    standby – Primary database

Current status for “primary”:
SUCCESS

DGMGRL>

 

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>set oracle_sid=tester

C:\Documents and Settings\Administrator>sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Oct 19 18:24:36 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
ERROR:
ORA-03113: end-of-file on communication channel
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – P
oduction
With the Partitioning, OLAP and Data Mining options (with complications)

C:\Documents and Settings\Administrator>sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Oct 19 18:35:31 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             197135236 bytes
Database Buffers          406847488 bytes
Redo Buffers                7135232 bytes
SQL> alter database mount standby database;

Database altered.

SQL> show parameter db_uniq

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_unique_name                       string      tester
SQL>

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

SQL>
Previous  Standby Database

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>set oracle_sid=standby

C:\Documents and Settings\Administrator>sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Oct 19 18:26:09 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             184552324 bytes
Database Buffers          419430400 bytes
Redo Buffers                7135232 bytes
SQL> alter database mount standby database;

Database altered.

SQL> startup
ORA-03113: end-of-file on communication channel
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             184552324 bytes
Database Buffers          419430400 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY

See, in the above we can see that Database Role here has been switched from Standby Role to Primary Role.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      TESTER
SQL>

 
 

 

October 19, 2011 Posted by | Data Guard | Leave a comment

Resolving Archive Gaps in Standby Database


I found that archive logs is not appling to the drsite i.e physical standby database, but luckily I had all the archive logs.

SQL> Select Low_Sequence#,High_Sequence# From V$Archive_Gap;

LOW_SEQUENCE# HIGH_SEQUENCE#
————- ————–
         1641           1646

If you are not having the archive logs at your standby database, the just copy or ftp the logs from primary to standby database.

SQL> alter database register logfile ‘/arch/icai/icai_1_1641.dbf’;

Database altered.

SQL> alter database register logfile ‘/arch/icai/icai_1_1642.dbf’;

Database altered.

SQL> alter database register logfile ‘/arch/icai/icai_1_1643.dbf’;

Database altered.

SQL> alter database register logfile ‘/arch/icai/icai_1_1644.dbf’;

Database altered.

SQL> alter database register logfile ‘/arch/icai/icai_1_1645.dbf’;

Database altered.

SQL> alter database register logfile ‘/arch/icai/icai_1_1646.dbf’;

Database altered.

After the redo logs have been registered on the physical standby database, the DBA can restart the managed recovery operations. For example, to put the physical standby database into automatic recovery managed mode:

SQL> alter database recover managed standby database disconnect from session;
 

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_S
TANDBY;
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
——— ———— ———- ———- ———- ———-
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      APPLYING_LOG          1       1641     307197     307198
RFS       RECEIVING             0          0          0          0

May 3, 2011 Posted by | Data Guard | Leave a comment

Restarting dead background process qmn0


Hi,

I was facing one issue at the time of log applying to physical standby database.I am getting a message in alert log file “restarting dead background process qmn0 “.MRP is not able to applying the specific log to the standby database.

Tue May  3 13:18:38 2011
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process
MRP0 started with pid=15, OS id=29345
MRP0: Background Managed Standby Recovery process started
Media Recovery Waiting for thread 1 seq# 1641
Fetching gap sequence for thread 1, gap sequence 1641-1646
Trying FAL server: prim
Tue May  3 13:18:44 2011
Completed: alter database recover managed standby database di
Tue May  3 13:18:59 2011
Failed to request gap sequence. Thread #: 1, gap sequence: 1641-1646
All FAL server has been attempted.
Tue May  3 13:22:51 2011
Restarting dead background process QMN0
QMN0 started with pid=14, OS id=29398
Tue May  3 13:28:54 2011
Restarting dead background process QMN0

QMN0 started with pid=14, OS id=29404
Tue May  3 13:34:57 2011
Restarting dead background process QMN0
QMN0 started with pid=14, OS id=29406
Tue May  3 13:41:00 2011
Restarting dead background process QMN0
QMN0 started with pid=14, OS id=29408
Tue May  3 13:47:03 2011
Restarting dead background process QMN0
QMN0 started with pid=14, OS id=29410
Tue May  3 13:53:04 2011
Restarting dead background process QMN0
QMN0 started with pid=14, OS id=29412
Tue May  3 13:59:07 2011
Restarting dead background process QMN0
QMN0 started with pid=14, OS id=29414
Tue May  3 14:05:10 2011
Restarting dead background process QMN0
QMN0 started with pid=14, OS id=29418
Tue May  3 14:11:13 2011
Restarting dead background process QMN0
QMN0 started with pid=14, OS id=29420
Tue May  3 14:17:16 2011
Restarting dead background process QMN0

Solution:

If you don’t use Advanced Queueing you can set it to 0:

alter system set aq_tm_processes=0 scope=both;

May 3, 2011 Posted by | Data Guard | Leave a comment