DBConsole recreate

Устанавливаем переменные окружения:

$export ORACLE_HOSTNAME=dbsrv.oracle.com
$export ORACLE_HOME=/app/oracle/product/10.2.0/db_1
$export ORACLE_SID=test1
$


Ручное удаление:

Останавливаем dbconsole.

$ORACLE_HOME/bin/emctl stop dbconsole

$ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9
$
$ ps -ef|grep emagent
$ ps -ef|grep dbsnmp
$
$ ps -fu oracle

SQL> alter user sys identified by XXX;
SQL> alter user sysman identified by XXX;
SQL> alter user dbsnmp identified by XXX;

$$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager dbsrv.oracle.com 1521 test1 -action drop

Enter SYS user's password :
Enter repository user name : sysman
Enter repository user password :
Getting temporary tablespace from database...
Found temporary tablespace: TEMP
Checking SYS Credentials ... OK.
Dropping the repository..
Quiescing DB ... Done.
Checking for Repos User ... Exists.
Repos User exists..
Clearing EM Contexts ... OK.
Dropping EM users ...
Done.
Dropping Repos User ... Done.
Dropping Roles/Synonymns/Tablespaces ... Done.
Unquiescing DB ... Done.
Dropped Repository Successfully.


Автоматическое создание:

Останавливаем dbconsole.

$ORACLE_HOME/bin/emctl stop dbconsole

$ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9
$
$ ps -ef|grep emagent
$ ps -ef|grep dbsnmp
$
$ ps -fu oracle


$emca -config dbcontrol db -repos create

STARTED EMCA at Feb 10, 2010 9:53:30 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: test1
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /app/oracle/product/10.2.0/db_1

Database hostname ................ dbsrv.oracle.com
Listener port number ................ 1521
Database SID ................ test1
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Feb 10, 2010 9:54:20 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test1/emca_2010-02-10_09-53-30-AM.log.
Feb 10, 2010 9:54:22 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Feb 10, 2010 9:57:38 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Feb 10, 2010 9:57:43 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://dbsrv.oracle.com.lan:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 10, 2010 9:59:25 AM
$




(ОПЦИОНАЛЬНО)

Можно попробовать еще раз пересоздать dbconsole, 
но теперь уже все автоматически.


Автоматическое удаление:

Останавливаем dbconsole.

$ORACLE_HOME/bin/emctl stop dbconsole

$ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9
$
$ ps -ef|grep emagent
$ ps -ef|grep dbsnmp
$
$ ps -fu oracle



$emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Feb 10, 2010 9:50:18 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: test1
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
Feb 10, 2010 9:50:52 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test1/emca_2010-02-10_09-50-18-AM.log.
Feb 10, 2010 9:50:53 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Feb 10, 2010 9:51:01 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Feb 10, 2010 9:53:15 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 10, 2010 9:53:15 AM
$


Автоматическое создание:

Останавливаем dbconsole.

$ORACLE_HOME/bin/emctl stop dbconsole

$ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9
$
$ ps -ef|grep emagent
$ ps -ef|grep dbsnmp
$
$ ps -fu oracle


test1:dbsrvt:~$emca -config dbcontrol db -repos create

STARTED EMCA at Feb 10, 2010 9:53:30 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: test1
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /app/oracle/product/10.2.0/db_1

Database hostname ................ dbsrv.oracle.com
Listener port number ................ 1521
Database SID ................ test1
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Feb 10, 2010 9:54:20 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test1/emca_2010-02-10_09-53-30-AM.log.
Feb 10, 2010 9:54:22 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Feb 10, 2010 9:57:38 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Feb 10, 2010 9:57:43 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://dbsrv.oracle.com.lan:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 10, 2010 9:59:25 AM
$

Oracle XML Database Recreate

SQL> select comp_name, status, version from DBA_REGISTRY;

COMP_NAME                               STATUS      VERSION
--------------------------------------- ----------- ------------------------------
Oracle Database Catalog Views           VALID       10.2.0.3.0
Oracle Database Packages and Types      VALID       10.2.0.3.0
Oracle Workspace Manager                VALID       10.2.0.1.0
JServer JAVA Virtual Machine            VALID       10.2.0.3.0
Oracle XDK                              VALID       10.2.0.3.0
Oracle Database Java Packages           VALID       10.2.0.3.0
Oracle Expression Filter                VALID       10.2.0.3.0
Oracle Enterprise Manager               VALID       10.2.0.3.0
Oracle XML Database                     INVALID     10.2.0.3.0

9 rows selected.

SQL>


vi /app/oracle/product/10.2.0/db_1/dbs/inittest1.ora

java_pool_size=150M

или так:
ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;

star


REMOVAL STEPS

1. Shutdown and restart the database

2. Connect as sysdba and run the catnoqm.sql script
SQL> set echo on
SQL> spool /export/home/oracle/xdb_removal.log
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;

3.minumums
shared_pool_size =150 MB
java_pool_size =150 MB
and
XDB tablespace must have 150m

5. Shutdown the database immediate, and startup the database normal
change_on_install


INSTALL STEPS

1. Connect as sysdba and run the catqm.sql script

SQL>set echo on
SQL>spool /export/home/oracle/xdb_install.log
SQL>@?/rdbms/admin/catqm.sql XDB SYSAUX TEMP
SQL>spool off;


SQL>@?/rdbms/admin/catxdbj.sql


SQL>select comp_name, status, version from DBA_REGISTRY 
    where comp_name= 'Oracle XML Database'

SQL>select count(*) from dba_objects where owner='XDB' and status='INVALID';


6. Restart database and listener to enable Oracle XML DB protocol access
dispatchers="(PROTOCOL=TCP) (SERVICE=XDB)"

@/app/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql;

Oracle Upgrade 10.2.0.3 -> 10.2.0.4

Pre_upgrade

export ORACLE_SID=test1
export ORACLE_HOME=/app/oracle/product/10.2.0/db_1

SQL>sqlplus "/ as sysdba"
SQL>shutdown immediate
SQL>STARTUP UPGRADE
SQL>SPOOL /export/home/oracle/upgrade_info.log 
SQL>@/app/oracle/product/10.2.0/db_2/rdbms/admin/utlu102i.sql
SQL>SPOOL OFF

SQL>@/app/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql

SQL>shutdown immediate;
SQL>spool off
SQL>exit

Upgrade


export PATH=/bin:/usr/bin:/usr/sbin:/etc:/opt/bin:/usr/ccs/bin:/usr/ucb/bi:/usr/local/bin:/app/oracle/product/10.2.0/db_2/bin
export ORACLE_HOME=/app/oracle/product/10.2.0/db_2
$env

Смотрим чтобы все переменные указывали на db_2
и в путях и в oracle_home


sqlplus "/ as sysdba"
STARTUP UPGRADE
SPOOL /export/home/oracle/upgrade_db.log
@/app/oracle/product/10.2.0/db_2/rdbms/admin/catupgrd.sql
shutdown immediate;
startup
SPOOL OFF
exit
EOF

sqlplus "/ as sysdba"
SPOOL /tmp/invalid_test1.log
@/app/oracle/product/10.2.0/db_2/rdbms/admin/utlrp.sql;
spool off
exit
EOF


$export ORACLE_HOSTNAME=dbsrv.oracle.com
$export ORACLE_HOME=/app/oracle/product/10.2.0/db_2
$export ORACLE_SID=test1
$

emca -upgrade db



Более подробная информация:

Run the Pre-Upgrade Information Tool
If you are upgrading database manually, then you should analyze it by running 
the Pre-Upgrade Information Tool.
The Pre-Upgrade Information Tool is a SQL script that ships with the new 
Oracle Database 10g release, and must be copied to and run from the environment
of the database being upgraded. In upgrading from 9.2.0.X to 10.2.0.4, run
the Pre-Upgrade-Information Tool in the 9.2.0.X environment. In upgrading a 
database from 10.2.0.X to 10.2.0.4, run the 10.2.0.4 version of the 
utlu102i.sql script in the environment of the previous 10.2.0.X release, and 
not in the 10.2.0.4 database.
Complete the following procedure to run the Pre-Upgrade Information Tool:
1. Start the database in the UPGRADE mode:
2. SQL> STARTUP UPGRADE
3. Set the system to spool results to a log file for later analysis:
4. SQL> SPOOL upgrade_info.log 
5. Run the Pre-Upgrade Information Tool:
6. SQL> @?/rdbms/admin/utlu102i.sql
7. Turn off the spooling of script results to the log file:
8. SQL> SPOOL OFF
Check the output of the Pre-Upgrade Information Tool in the upgrade_info.log 
file. The following is an example of the output generated by the Pre-Upgrade Information Tool:
Oracle Database 10.2 Upgrade Information Utility    02-04-2008 11:48:11
.
**********************************************************************
Database:
**********************************************************************
--> name:       X102040
--> version:    10.2.0.1.0
--> compatible: 10.2.0.1
--> blocksize:  8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 505 MB
.... AUTOEXTEND additional space required: 15 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 401 MB
.... AUTOEXTEND additional space required: 376 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 265 MB
.... AUTOEXTEND additional space required: 15 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "shared_pool_size" needs to be increased to at least 167772160
WARNING: --> "java_pool_size" needs to be increased to at least 67108864
.
**********************************************************************
Components: [The following database components will be upgraded orinstalled]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> Oracle Data Mining           [upgrade]  VALID
--> Messaging Gateway            [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Oracle Ultra Search          [upgrade]  VALID
--> Oracle Label Security        [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
PL/SQL procedure successfully completed.


Upgrading a Release 10.2 Database

After you install the patch set, you must perform the following steps
on every database associated with the upgraded Oracle home:
Note:
If you do not run the catupgrd.sql script as described in this section and 
you start up a database for normal operation, then ORA-01092: 
ORACLE instance terminated. Disconnection forced errors will occur and the 
error ORA-39700: 
database must be opened with UPGRADE option will be in the alert log.

1.Log in as the Oracle software owner user.

2.For Oracle RAC installations, start listener on each node of the cluster
as follows:
  $ srvctl start listener -n node

3.If you are using Automatic Storage Management, start the Automatic 
Storage Management instance.

4.For single-instance installations, start the listener as follows:
  $ lsnrctl start

5.For single-instance installations, use SQL*Plus to log in to the database 
as the SYS user with SYSDBA privileges:
  $ sqlplus /nolog 
  SQL> CONNECT SYS AS SYSDBA
  Enter password:SYS_password
  Users of single-instance installations now proceed to step 7.
   
6.For Oracle RAC installations:
  1.Use SQL*Plus to log in to the database as the SYS user with SYSDBA 
privileges:
    $ sqlplus /nolog 
    SQL> CONNECT SYS AS SYSDBA
    Enter password: SYS_password
    SQL> STARTUP NOMOUNT
  2.Set the CLUSTER_DATABASE initialization parameter to FALSE:
    SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile; 
  3.Shut down the database:
    SQL> SHUTDOWN

7.Enter the following SQL*Plus commands:
  SQL> STARTUP UPGRADE
  SQL> SPOOL patch.log
  SQL> @?/rdbms/admin/catupgrd.sql
  SQL> SPOOL OFF

8.Review the patch.log file for errors and inspect the list of components 
that is displayed at the end of catupgrd.sql script.
  This list provides the version and status of each SERVER component in the  database.

9.If necessary, rerun the catupgrd.sql script after correcting any problems.

10. Restart the database:
      SQL> SHUTDOWN IMMEDIATE
      SQL> STARTUP

11.Run the utlrp.sql script to recompile all invalid PL/SQL packages now 
instead of when the packages are accessed for the first time. 
   This step is optional but recommended.
   SQL> @?/rdbms/admin/utlrp.sql
   Note:
   When the 10.2.0.4 patch set is applied to an Oracle Database 10g 
Standard Edition database, there may be 54 invalid objects after the utlrp.sql 
   script runs. These objects belong to the unsupported components and do
not affect the database operation.
   Ignore any messages indicating that the database contains invalid recycle 
bin objects similar to the following:
   BIN$4lzljWIt9gfgMFeM2hVSoA==$0

12.Run the following command to check the status of all the components after
the upgrade:
   SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
   In the output of the preceding command, the status of all the components 
should be VALID for a successful upgrade.

13.If you are using the Oracle Recovery Manager catalog, enter the following 
   command:
   $ rman catalog username/password@alias 
   RMAN> UPGRADE CATALOG;

14.For Oracle RAC installations:
   1.Set the CLUSTER_DATABASE initialization parameter to TRUE:
     SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile; 
   2.Restart the database:
     SQL> SHUTDOWN IMMEDIATE
     SQL> STARTUP
   3.Start any database services that you want to use:
     $ srvctl start service -d db_name -s service_name

15.To configure and secure Enterprise Manager follow these steps:
   Ensure the database and Listener are operational.
   In the case of a single instance, execute
   emca -upgrade db
   In the case of Oracle Real Application Clusters (RAC), execute

   emca -upgrade db -cluster

Solaris 10 Process Cheat Sheet

svcs - report service status

svcs -shows all enabled services
svcs -a - shows all services
svcs -l cron - shows detail information about the service
svcs -xv - shows explanations for services states that are
enabled but not running or preventing other services from running

svcadm - administration program for manipulating service instances.

svcadm enable cron - starts the cron daemon
svcadm enable -r cron - starts the cron daemon and any dependencies
svcadm disable cron - stops the cron daemon
svcadm restart cron - stops and then restarts the cron daemon
svcadm refresh cron - rereads the configuration files (like kill -HUP)
svcadm clear cron - clear the maintenance stat of the cron daemon

inetadm - administration program for manipulating inetd-controlled services

inetadm - lists inetd-controlled services
inetadm -l - lists the properties of the service
inetadm -e - enables the service
inetadm -d - disables the service

psrinfo - displays information on processes

psradm - change processor operational status

psradm -f - take a process offline
psradm -n - bring a process back online

mpstat - report per process statistics

prstat - report active process statistics

prstat -a - show processes and users totals
prstat-P cpu - show process statistics of a cpu

pmap - displays address space info on a process
pargs - prints process arguments

pargs -e - prints process environment variables

ptree - prints process tree

pfiles - prints open files and ports for a process 

Tracking undo generated by session

select
   s.sid, 
   s.username,
   r.name       "RBS name", 
   t.start_time,
   t.used_ublk  "Undo blocks",
   t.used_urec  "Undo recs"
 from
   v$session s,
   v$transaction t,
   v$rollname r
 where
   t.addr = s.taddr and
   r.usn  = t.xidusn;

Solaris 10 : Project for oracle

project.max-shm-ids:        shmmni    Maximum shared memory IDs for a project.
project.max-sem-ids:        semmni    Maximum semaphore IDs for a project.
project.max-msg-ids:        msgmni    Maximum message queue IDs for a project.
project.max-shm-memory:     shmmax    Total amount of shared memory allowed for a project.
process.max-sem-nsems:      semmsl    Maximum number of semaphores allowed per semaphore set.
process.max-sem-ops:        semopm    Maximum number of semaphore operations allowed per semop.
process.max-msg-qbytes:     msgmnb    Maximum number of bytes of messages on a message queue.
process.max-msg-messages:   msgtql    Maximum number of messages on a message queue.


project.max-shm-memory         shmmax  ***
process.max-stack-size
process.max-file-descriptor
process.max-sem-nsems          semmsl  1024   256
project.max-sem-ids            semmni  512    100
project.max-shm-ids            shmmni  512    100

==========================================================================
#projects -l
#projdel group.dba
#projadd group.dba

#usermod -K project=group.dba oradb
#usermod -K project=group.dba oragrid
#projmod -sK "project.max-shm-memory=(priv,70gb,deny)"  group.dba
#projmod -sK "process.max-stack-size=(privileged,33554432,deny)" group.dba
#projmod -sK "process.max-file-descriptor=(privileged,65536,deny)" group.dba
#projmod -sK "process.max-sem-nsems=(priv,256,deny)" group.dba
#projmod -sK "project.max-sem-ids=(priv,100,deny)" group.dba
#projmod -sK "project.max-shm-ids=(priv,100,deny)" group.dba


*  Added to fix switchover problem

set shmsys:shminfo_shmmax=171798691840
set semsys:seminfo_semmsl=256
set semsys:seminfo_semmni=100
set shmsys:shminfo_shmmni=100

==========================================================================
#projects -l
#projdel group.dba
#projadd group.dba

#usermod -K project=group.dba oradb
#usermod -K project=group.dba oragrid
#projmod -sK "project.max-shm-memory=(priv,150gb,deny)"  group.dba
#projmod -sK "process.max-stack-size=(privileged,33554432,deny)" group.dba
#projmod -sK "process.max-file-descriptor=(privileged,65536,deny)" group.dba
#projmod -sK "process.max-sem-nsems=(priv,1024,deny)" group.dba
#projmod -sK "project.max-sem-ids=(priv,512,deny)" group.dba
#projmod -sK "project.max-shm-ids=(priv,512,deny)" group.dba


# Oracle:
set shmsys:shminfo_shmmax=75161927680
set semsys:seminfo_semmsl=1024
set semsys:seminfo_semmni=512
set shmsys:shminfo_shmmni=512

set noexec_user_stack=1

==========================================================================

$ulimit -n

256

$prctl -n process.max-file-descriptor -v 20480 -r -i process $$

$

$ulimit -n

20480

$

$ulimit -s

8192

$prctl -n process.max-stack-size -v 9223372036854775807 -r -i process $$

$

$ulimit -s

unlimited

$

==========================================================================

Установить значения параметров:

prctl -n project.max-shm-memory      -v 150gb                 -r -i process $$
prctl -n process.max-stack-size      -v 9223372036854775807   -r -i process $$
prctl -n process.max-file-descriptor -v 20480                 -r -i process $$
prctl -n process.max-sem-nsems       -v 1024                  -r -i process $$
prctl -n project.max-sem-ids         -v 512                   -r -i process $$
prctl -n project.max-shm-ids         -v 512                   -r -i process $$

Просмотр текущих значений параметров:

prctl -n project.max-shm-memory       -i process $$
prctl -n process.max-stack-size       -i process $$
prctl -n process.max-file-descriptor  -i process $$
prctl -n process.max-sem-nsems        -i process $$
prctl -n project.max-sem-ids          -i process $$
prctl -n project.max-shm-ids          -i process $$

Чтобы после перезагрузки их не потерять создадим проект:

projdel user.oradb

projadd -U oradb -K "project.max-shm-memory=(priv,150gb,deny)"  user.oradb
projmod -sK "process.max-stack-size=(privileged,9223372036854775807,deny)" user.oradb
projmod -sK "process.max-file-descriptor=(privileged,20480,deny)" user.oradb
projmod -sK "process.max-sem-nsems=(priv,1024,deny)" user.oradb
projmod -sK "project.max-sem-ids=(priv,512,deny)" user.oradb
projmod -sK "project.max-shm-ids=(priv,512,deny)" user.oradb


Или так:

# projadd group.dba
# usermod -K project=group.dba oradb
# usermod -K project=group.dba oragrid

# more /etc/project

system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
group.dba:100::::

# projmod -sK "project.max-shm-memory=(priv,150gb,deny)"  group.dba
# projmod -sK "process.max-stack-size=(privileged,9223372036854775807,deny)" group.dba
# projmod -sK "process.max-file-descriptor=(privileged,20480,deny)" group.dba
# projmod -sK "process.max-sem-nsems=(priv,1024,deny)" group.dba
# projmod -sK "project.max-sem-ids=(priv,512,deny)" group.dba
# projmod -sK "project.max-shm-ids=(priv,512,deny)" group.dba


Проверяем так:
# prctl -i project group.dba

# vi /etc/system
# Oracle:
set noexec_user_stack=1

Подготовка RHEL6/7 Для Oracle 12c

Note 1961997.1

Установка пакетов:

yum -y install binutils.x86_64 \
compat-libcap1.x86_64 \
compat-libstdc++-33.i686 \
compat-libstdc++-33.x86_64 \
gcc.x86_64 \
gcc-c++.x86_64 \
glibc.i686 \
glibc.x86_64 \
glibc-devel.i686 \
glibc-devel.x86_64 \
ksh.x86_64 \
libaio.i686 \
libaio.x86_64 \
libaio-devel.i686 \
libaio-devel.x86_64 \
libgcc.i686 \
libgcc.x86_64 \
libstdc++.i686 \
libstdc++.x86_64 \
libstdc++-devel.i686 \
libstdc++-devel.x86_64 \
libXi.i686 \
libXi.x86_64 \
libXtst.i686 \
libXtst.x86_64 \
make.x86_64 \
sysstat.x86_64 \



Для устранения ошибки Bug 21151912 -> Doc ID 2062336.1

Installation of 12.1.0.2 Oracle Database Server/Client on RHEL 7/OL7
reports pre-requisite package 'compat-libstdc++' missing. (Doc ID 2062336.1)


yum -y install compat-libstdc++-33.x86_64 \
compat-libstdc++-33.i686

ALERT: Setting RemoveIPC=yes on Redhat 7.2 Crashes ASM and Database Instances

as Well as Any Application That Uses a Shared Memory Segment (SHM) or Semaphores (SEM) (Doc ID 2081410.1)

Set RemoveIPC=no in /etc/systemd/logind.conf

Set RemoveIPC=no in /etc/systemd/logind.conf
Reboot the server or restart systemd-logind as follows:

$ su - root
$ systemctl daemon-reload
$ systemctl restart systemd-logind

Смотрим размеры физической памяти и Swap

# grep MemTotal /proc/meminfo
# grep SwapTotal /proc/meminfo

Установка параметров ядра

Общий объём доступной разделяемой памяти (в страницах)

kernel.shmall

Max shared memory = shmall * page_size (так будет в байтах)

Размер страницы:

$ getconf PAGE_SIZE
4096

Например, мы хотим под SGA выделить 10GB

10*1024*1024*1024=107374182240 (10Gb in bytes)
107374182240/4096=2621440
kernel.shmall = 2621440 

kernel.shmall рекомендуется выставлять в максимальное значение
(т.е. равным размеру физической памяти в системе в страницах)
Минимальное рекомендованное значение kernel.shmall = 6291456


Например, в системе 64GB RAM, выделяем всю под shared memory
Если размер страницы равен 4096 Bytes, то kernel.shmall вычисляется так:

64 Gb
64 * 262144 = 16777216

65536 Mb
65536 * 256 = 16777216

67108864 Kb
67108864 / 4 = 16777216.0

68719476736 Bytes
68719476736 / 4096 = 16777216.0

kernel.shmall = 16777216

Закомментируем существующий параметр:
sed -ie 's/^kernel.shmall/#kernel.shmall/g' /etc/sysctl.conf

Вычисляем и устанавливаем новое значение:
MemTotal Kb / 4
echo "kernel.shmall = $(awk '$1 == "MemTotal:"{tmp=$2/4; printf"%0.0f\n", tmp}' /proc/meminfo)" >> /etc/sysctl.conf

Применить параметры ядра

$ sysctl -p

Максимальный размер сегмента разделяемой памяти (в байтах)

kernel.shmmax 

Рекомендуется выставлять 1/2 от размера физической памяти в системе (в байтах)

К примеру для 64 Гб это будет:

64*1024*1024*1024=68719476736 (64Gb in bytes)
68719476736/2=34359738368
kernel.shmmax = 34359738368


64 Gb
64 * 536870912  = 34359738368

65536 Mb
65536 * 524288  = 34359738368

67108864 Kb
67108864 * 512  = 34359738368

68719476736 Bytes
68719476736 / 2 = 34359738368
                 

kernel.shmmax  = 34359738368

Закомментируем существующий параметр:
sed -ie 's/^kernel.shmmax/#kernel.shmmax/g' /etc/sysctl.conf

Вычисляем и устанавливаем новое значение:
MemTotal Kb * 512
echo "kernel.shmmax = $(awk '$1 == "MemTotal:"{tmp=$2*512; printf"%0.0f\n", tmp}' /proc/meminfo)" >> /etc/sysctl.conf

Применить параметры ядра

$ sysctl -p

Максимальное число сегментов разделяемой памяти для всей системы

kernel.shmmni 

Рекомендуется установить kernel.shmmni  =  4096

Применить параметры ядра

$ sysctl -p

Рекомендуемые значения других параметров ядра следующие:

kernel.sem = 250 32000 100 128
fs.file-max = 6815744
fs.aio-max-nr = 1048576
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 = 1048576
kernel.panic_on_oops = 1


Устанавливаем

cat << EOF >> /etc/sysctl.conf
kernel.shmmni = 4096
net.ipv4.ip_local_port_range = 9000 65535
kernel.sem = 250 32000 100 128
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048576
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.panic_on_oops = 1
EOF


Применить параметры ядра

$ sysctl -p

Сетевые параметры:

Устранить обрывы связи для долго работающих запросов
при использовании фаерволов между БД и клиентом

Добавить в /etc/sysctl.conf

cat >> /etc/sysctl.conf << EOF
###########################################
### For CheckPoint Timeout ################
###########################################
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 2700
###########################################
EOF


Применить параметры ядра

$ sysctl -p

Настройка Shell Limits:

Убедитесь, что последняя версия PAM загружена в
/etc/pam.d/login file

cat > /etc/pam.d/login << EOF
#+-------------------------------+
#| Setting parameters for Oracle |
#+-------------------------------+
session required pam_limits.so
EOF


Set Shell limits for oracle users:

cat > /etc/security/limits.d/70-oracle.conf << EOF
#+-------------------------------+
#| Setting parameters for Oracle |
#+-------------------------------+
oracle soft nofile 131072
oracle hard nofile 131072
oracle soft nproc 131072
oracle hard nproc 131072
# For 64 Gb Installed : 67108864=64*1024*1024 (размер в Kb)
oracle soft memlock 67108864
oracle hard memlock 67108864
#
EOF


Set Shell limits for root users:


cat > /etc/security/limits.d/70-root.conf << EOF
#+-------------------------------------+
#| Setting limits root [for Oracle DB] |
#+-------------------------------------+
root soft nofile 131072
root hard nofile 131072
root soft nproc 131072
root hard nproc 131072
# For 64 Gb Installed : 67108864=64*1024*1024 (размер в Kb)
root soft memlock 67108864
root hard memlock 67108864
#
EOF



Проверяем:

su - oracle -c 'id; ulimit -a'
su - root -c 'id; ulimit -a'

Настройка /dev/shm

Для сервера с RAM=64 GB

su - root
df -h /dev/shm/
umount /dev/shm
mount -t tmpfs shmfs -o size=64g /dev/shm

Добавить строчку в /etc/fstab следующего содержания:

cat >> /etc/fstab <
tmpfs /dev/shm tmpfs size=64g 0 0
EOF

Требование к размеру файловых систем

/tmp -> требуется минимум 1 GB

Установить точное время

ntpdate omega.local
Настройка NTP daemon
Настроить запуск демона с параметром -x :
ntpd -u ntp:ntp -p /var/run/ntpd.pid -x

Настройка разрешения имен

В файле /etc/resolv.conf добавить domain omega.local

Installing the cvuqdisk Package for Linux (RAC only)

rpm -qi cvuqdisk
rpm -iv cvuqdisk-1.0.9-1.rpm

Disabling Transparent HugePages

Необходимо выбрать наиболее удобный вариант

Настройка загрузчика

в /boot/grub/grub.conf -> добавить опцию transparent_hugepage=never

kernel /vmlinuz-2.6.32-504.1.3.el6.x86_64 ro root=LABEL=/ transparent_hugepage=never



Скрипт автозапуска

В файле /etc/rc.local добавить скрипт отключение Transparent HugePages

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
    echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
    echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi


Начинася с Oracle Linux 7 в ядер UEK, transparent hugepages отключены по умолчанию,
никаких действий принимать не надо!

Настройка Hugepages

Oracle может использовать Hugepages для размещения SGA.
Hugepages не вытесняются в swap.
Процессор уменьшит число трансляций виртуальных адресов памяти в физические.
Кеш CPU Translation Lookaside Buffers (TLB) будет реже устаревать.

При использовании больших страниц памяти shared memory для СУБД настраивать не нужно.
Но если используется ASM, то для его экземпляра зарезервируем 1024 МБ shared memory,
поэтому размер /dev/shm быть не менее  1024 МБ

 $ sqlplus / as sysasm

Для версии 11.2.0.3 и ниже (Note 265633.1)  минимально необходимые параметры:
alter system set memory_target    = 320M scope=spfile sid='*';
alter system set shared_pool_size = 128M scope=spfile sid='*';
alter system set large_pool_size  =  64M scope=spfile sid='*';

Для версии 11.2.0.4 и выше (Note 1536039.1 и Note 1982132.1):
alter system set memory_target    = 1024M scope=spfile sid='*';
alter system set shared_pool_size =  128M scope=spfile sid='*';
alter system set large_pool_size  =   64M scope=spfile sid='*';

Для высоконагруженных многопроцессорных систем (Note 437924.1) 
Для версии 11.2.0.3 и выше:
alter system set memory_max_target= 2048M  scope=spfile sid='*';
alter system set memory_target    = 1536M  scope=spfile sid='*';
alter system set shared_pool_size = 128M   scope=spfile sid='*';
alter system set large_pool_size  = 64M    scope=spfile sid='*';



Необходимо отключить автоматическое управление памятью AMM
alter system set memory_target=0

Количество больших страниц памяти задаётся параметром ядра vm.nr_hugepages.
Можно динамически изменять количество hugepages, но лучше резервировать их при загрузке ОС.


Информацию об использовании hugepages можно узнать из /proc/meminfo:

bash-4.2$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
bash-4.2$


Покажет
- сколько всего выделено страниц
- сколько свободных страниц
- сколько зарезервированных страниц
- размер одной страницы памяти



Рассчитаем необходимое количество страниц:

На сервере 64 Gb
Параметры экземпляра такие:

memory_max_target    = 61440M
memory_target        = 61440M
sga_max_size         = 36864M
sga_target           = 36864M
pga_aggregate_target = 24576M


Если размер страницы равен 2 Mb (2048 Kb)
то количество страниц расчитываем так:


для sga_max_size = 36 Gb

36 Gb
36 * 512               = 18432

36864 Mb
36864 * 0.5            = 18432

37748736 Kb
37748736 / 2048        = 18432

38654705664 Bytes
38654705664 / 2097152  = 18432


Необходимо выделить небольшой запас свободных страниц hugepages

18432 увеличим до 18450

vm.nr_hugepages = 18450

Проверьте настройку лимитов

/etc/security/limits.conf

Пользователю, от которого запускается СУБД разрешено блокировать память такого размера

oracle soft memlock 67108864
oracle hard memlock 67108864

$ ulimit -l

Отключите AMM

SQL> alter system set memory_target=0;
System altered.

SQL> alter system set memory_max_target=0;
System altered.

Установите рассчитанное значение параметру  vm.nr_hugepages

Под root

# vi /etc/sysctl.conf
vm.nr_hugepages = 18450


# sysctl -p
# sysctl vm.nr_hugepages=18450

shutdown immediate;

# reboot


Проверяем:

# grep ^Huge /proc/meminfo

Примечание:

Есть такой bash-скрипт  (MOS) note 401749.1
Который рассчитывает рекомендуемое значение vm.nr_hugepages.


#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk {'print $2'}`
# Start from 1 pages to be on the safe side and guarantee 1 free HugePage
NUM_PG=1
# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | awk {'print $5'} | grep "[0-9][0-9]*"`
do
   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
   if [ $MIN_PG -gt 0 ]; then
      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
   fi
done
# Finish with results
case $KERN in
   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
   '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
esac
# End 



$ chmod u+x ./hugepages_setting.sh 
$ ./hugepages_settings.sh
.
.
.
Recommended setting: vm.nr_hugepages = nnnn