вторник, 16 февраля 2010 г.

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 



понедельник, 15 февраля 2010 г.

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;