Tuesday, September 28, 2010

Configure EM DBcontrol in 11gR2

[11gr2@rh5 grid]$ emca -config dbcontrol db -repos create

STARTED EMCA at Sep 28, 2010 12:27:55 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /u11gr2/app/oracle/product/11.2.0/grid ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): luodonghua@gmail.com
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /u11gr2/app/oracle/product/11.2.0/grid ]:
ASM SID [ +ASM ]:
ASM port [ 1521 ]:
ASM username [ ASMSNMP ]:
ASM user password:
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u11gr2/app/oracle/product/11.2.0/dbhome_1

Local hostname ................ rh5.lab.dbaglobe.com
Listener ORACLE_HOME ................ /u11gr2/app/oracle/product/11.2.0/grid
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ............... luodonghua@gmail.com
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /u11gr2/app/oracle/product/11.2.0/grid
ASM SID ................ +ASM
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ ASMSNMP

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Sep 28, 2010 12:28:31 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u11gr2/app/oracle/cfgtoollogs/emca/orcl/emca_2010_09_28_00_27_55.log.
Sep 28, 2010 12:28:33 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Sep 28, 2010 12:37:03 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Sep 28, 2010 12:37:10 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Sep 28, 2010 12:38:56 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Sep 28, 2010 12:38:59 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Sep 28, 2010 12:38:59 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Sep 28, 2010 12:39:33 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Sep 28, 2010 12:39:33 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Sep 28, 2010 12:40:00 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Sep 28, 2010 12:40:00 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Sep 28, 2010 12:42:12 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Sep 28, 2010 12:42:14 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://rh5.lab.dbaglobe.com:1158/em <<<<<<<<<<<
Sep 28, 2010 12:42:22 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u11gr2/app/oracle/product/11.2.0/dbhome_1/rh5.lab.dbaglobe.com_orcl/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 28, 2010 12:42:22 AM

Sunday, September 19, 2010

Identify the Current Version of SQL Server 2000

To identify the installed version of SQL Server 2000 Database Components

Execute one of the following queries against an instance of the database engine by using isql, osql, or Query Analyzer.


SELECT SERVERPROPERTY('ProductLevel')
SELECT @@VERSION
SELECT SERVERPROPERTY('ProductVersion')


Find out your version of Database Components by means of the following table. SQL Server 2000 Version and Level @@VERSION Product Level

>>> SQL Server 2000 Original Release 8.00.194 RTM
>>> Database Components SP1 8.00.384 SP1
>>> Database Components SP2 8.00.534 SP2
>>> Database Components SP3, SP3a or MSDE 2000 Release A. 8.00.760 SP3
>>> Database Components SP4 8.00.2039 SP4


Note Your product version may be different than these values if you applied a hotfix after installing the product or after installing a previous service pack. For example, @@VERSION returns a value of 8.00.818 after you apply the security fix MS03-031 to SQL Server 2000 SP3a.

(Optional) If you are not sure whether you are running an edition of the SQL Server 2000 database engine or MSDE 2000, use isql, osql or Query Analyzer to execute the following query against the instance in question.
SELECT SERVERPROPERTY('Edition')
If this query returns "desktop engine," you are running an instance of MSDE 2000; otherwise, you are running an instance of SQL Server 2000 database engine.

Saturday, September 18, 2010

How flashback database can saves your time

============ Part 1, crash the database ============

SQL> conn / as sysdba

SQL> delete from sys.user$;

41 rows deleted.

SQL> commit;

SQL> select * from dba_users;

no rows selected

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 348128992 bytes
Database Buffers 167772160 bytes
Redo Buffers 5869568 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [],
[], [], [], [], []
Process ID: 3366
Session ID: 125 Serial number: 5


============ Part 2, flashback the database ============

SQL> startup mount
ORACLE instance started.

Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 348128992 bytes
Database Buffers 167772160 bytes
Redo Buffers 5869568 bytes
Database mounted.


SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE
-------------------- -------------------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
680531 2010-SEP-05 06:35:35 1440 75988992
95232000


SQL> flashback database to timestamp to_timestamp('2010-SEP-05 20:30:00','YYYY-MON-DD HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from sys.user$;

COUNT(*)
----------
41

Sunday, September 5, 2010

How to turn-on ASO for network encryption

### SERVER SIDE CONFIGURATION ###
#
SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(MD5)
SQLNET.ENCRYPTION_TYPES_SERVER=(DES40,RC4_40)
SQLNET.CRYPTO_SEED="Between Ten and Seventy Random Characters"

### CLIENT SIDE CONFIGURATION ###
#
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(MD5)
SQLNET.ENCRYPTION_TYPES_CLIENT=(DES40,RC4_40)


How to verify whether ASO Network Encryption is working or not:

SQL> select a.sid,a.username,b.network_service_banner
2 from v$session a,v$session_connect_info b
3 where a.sid=b.sid
4 and a.serial#=b.serial#
5 /

SID USERNAME NETWORK_SERVICE_BANNER
---------- -------------------- ----------------------------------------------------------------------------------------------------
124 TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
124 Oracle Advanced Security: encryption service for Linux: Version 11.1.0.7.0 - Production
124 Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.1.0.7.0 - Production
126 BANKING TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
126 BANKING Oracle Advanced Security: encryption service for Linux: Version 11.1.0.7.0 - Production
126 BANKING Oracle Advanced Security: DES40 encryption service adapter for Linux: Version 11.1.0.7.0 - Producti
126 BANKING Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.1.0.7.0 - Production
126 BANKING Oracle Advanced Security: MD5 crypto-checksumming service adapter
129 SYS
146 SYS

10 rows selected.

SQL>