用ipcs管理oracleunix共享内存-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

用ipcs管理oracleunix共享内存

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:03 PM

unixsharedMemorymanage

UNIX and Linux Shared Memory and Oracle

The UNIX and LINUX operating systems allocate memory based on an interprocess communication model (IPC) to manage memory segments for Oracle database environments.

To obtain details on shared memory for UNIX and LINUX, make use of the ipcs command. Details on syntax are illustrated below from within the UNIX and LINUX man pages:

NAME

ipcs : provide information on ipc facilities

SYNOPSIS

ipcs [ -asmq ] [ -tclup ]

ipcs [ -smq ] -i id

ipcs -h

DESCRIPTION

ipcs provides information on the ipc facilities for which the calling process has read access

The -i option allows a specific resource ID to be specified. Only information on this id will be printed.

Resources may be specified as follows:

-m shared memory segments

-q message queues

Determine what Oracle database has a semaphore set

As we have noted, when an Oracle database hangs, you may have leftover background processes, held RAM memory segment and held semaphore sets. When you have multiple instances on a UNIX server and need to release a semaphore set for an Oracle database, you must first determine which semaphore set belongs to your crippled instance.

Unfortunately, you cannot tell with the ipcs ?sa command which semaphore set belongs to each Oracle database. Here is the Oracle procedure for determining the semaphore set number for an individual Oracle database so that you can remove the semaphores with the ipcs command.

SQL> oradebug setmypid

SQL> oradebug ipc

SQL> !vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_13358.trc

*** 2014-09-20 17:35:30.830
Processing Oradebug command 'ipc'
Dump of unix-generic skgm context
areaflags 000000f7
realmflags 0000000f
mapsize 00000800
protectsize 00001000
lcmsize 00001000
seglen 00400000
largestsize 000000007ead3000
smallestsize 0000000000400000
stacklimit 0xbda06894
stackdir -1
mode 660
magic acc01ade
Handle: 0x1b0058 `/u01/app/oracle/product/11.2.0/db_1orcl'
Dump of unix-generic realm handle `/u01/app/oracle/product/11.2.0/db_1orcl', flags = 00000000
Area #0 `Fixed Size' containing Subareas 0-0
Total size 00000000001476d4 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
0 0 3080200 0x00000020000000 0x00000020000000
Subarea size Segment size
0000000000148000 000000003ec00000
Area #1 `Variable Size' containing Subareas 4-4
Total size 000000003e000000 Minimum Subarea size 00400000
Area Subarea Shmid Stable Addr Actual Addr
1 4 3080200 0x00000020800000 0x00000020800000
Subarea size Segment size
000000003e000000 000000003ec00000
Area #2 `Redo Buffers' containing Subareas 1-1
Total size 000000000047a000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
2 1 3080200 0x00000020148000 0x00000020148000
Subarea size Segment size
000000000047a000 000000003ec00000
Area #3 `Base Allocator Control' containing Subareas 3-3
Total size 0000000000002000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
3 3 3080200 0x000000207fe000 0x000000207fe000
Subarea size Segment size
0000000000002000 000000003ec00000
Area #4 `Slab Allocator Control' containing Subareas 2-2
Total size 000000000023c000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
4 2 3080200 0x000000205c2000 0x000000205c2000
Subarea size Segment size
000000000023c000 000000003ec00000
Area #5 `skgm overhead' containing Subareas 5-5
Total size 0000000000003000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
5 5 3080200 0x0000005e800000 0x0000005e800000
Subarea size Segment size
0000000000003000 000000003ec00000
Dump of Linux-specific skgm context
sharedmmu 00000001
shareddec 0
used region 0: start 0000000012000000 length 0000000000400000
used region 1: start 0000000020000000 length 000000003ec00000
used region 2: start 00000000af800000 length 0000000010800000
Maximum processes: = 150
Number of semaphores per set: = 154
Semaphores key overhead per set: = 4
User Semaphores per set: = 150
Number of semaphore sets: = 1
Semaphore identifiers: = 1
Semaphore List=
131073
-------------- system semaphore information -------------
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 2883587 root 644 52 2
0x00000000 2916357 root 644 16384 2
0x00000000 2949126 root 644 268 2
0xd3ac6c80 3080200 oracle 660 4096 0
------ Semaphore Arrays --------
key semid owner perms nsems
0x5c23a1bc 131073 oracle 660 154
------ Message Queues --------
key msqid owner perms used-bytes messages

Since we cannot get the semaphore set number for the crippled database, we must determine the semaphore set using the process of elimination. We issues the above commands for each live database on the server, and the un-claimed semaphore set will belong to the crippled instance.

Once identified, you can use the following procedure for removing the semaphore set.

Removing a semaphore set for Oracle

We start by issuing the ipcs ?sb command to display details of the semaphore set.

root> ipcs
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x7403d635 1769472 root 600 4 0
0x00000001 1933313 root 600 655360 2
0x7403d60a 2523138 root 600 4 0
0x00000000 2883587 root 644 52 2
0x7403d609 2490372 root 600 4 0
0x00000000 2916357 root 644 16384 2
0x00000000 2949126 root 644 268 2
0x00000000 3014663 gdm 600 393216 2 dest
0xd3ac6c80 3080200 oracle 660 4096 0

------ Semaphore Arrays --------
key semid owner perms nsems
0x000000a7 0 root 600 1
0x5c23a1bc 131073 oracle 660 154

Now, we can remove the semaphores from the locked-up database. In this example, we assume that set 131073is the one for the crippled database.

root> ipcrm -s 131073

Now we can confirm that the semaphores are deleted.

root> ipcs -sb|grep oracle

s 67 0x00000000 --ra-r----- oracle dba 400
s 334 0x00000000 --ra-r----- oracle dba 300

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

How does MySQL handle data replication?Apr 28, 2025 am 12:25 AM

MySQL processes data replication through three modes: asynchronous, semi-synchronous and group replication. 1) Asynchronous replication performance is high but data may be lost. 2) Semi-synchronous replication improves data security but increases latency. 3) Group replication supports multi-master replication and failover, suitable for high availability requirements.

How can you use the EXPLAIN statement to analyze query performance?Apr 28, 2025 am 12:24 AM

The EXPLAIN statement can be used to analyze and improve SQL query performance. 1. Execute the EXPLAIN statement to view the query plan. 2. Analyze the output results, pay attention to access type, index usage and JOIN order. 3. Create or adjust indexes based on the analysis results, optimize JOIN operations, and avoid full table scanning to improve query efficiency.

How do you back up and restore a MySQL database?Apr 28, 2025 am 12:23 AM

Using mysqldump for logical backup and MySQLEnterpriseBackup for hot backup are effective ways to back up MySQL databases. 1. Use mysqldump to back up the database: mysqldump-uroot-pmydatabase>mydatabase_backup.sql. 2. Use MySQLEnterpriseBackup for hot backup: mysqlbackup--user=root-password=password--backup-dir=/path/to/backupbackup. When recovering, use the corresponding life

What are some common causes of slow queries in MySQL?Apr 28, 2025 am 12:18 AM

The main reasons for slow MySQL query include missing or improper use of indexes, query complexity, excessive data volume and insufficient hardware resources. Optimization suggestions include: 1. Create appropriate indexes; 2. Optimize query statements; 3. Use table partitioning technology; 4. Appropriately upgrade hardware.

What are views in MySQL?Apr 28, 2025 am 12:04 AM

MySQL view is a virtual table based on SQL query results and does not store data. 1) Views simplify complex queries, 2) Enhance data security, and 3) Maintain data consistency. Views are stored queries in databases that can be used like tables, but data is generated dynamically.

What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

See all articles