"Teach Oracle Learn Oracle" -- Taj Contact me for support at askdbataj@gmail.com This blog is dedicated to all ORACLE Professionals and Learning Students.
Search This Blog
Sunday, September 25, 2011
HOW TO configure vnc server and client on linux machine
Saturday, September 17, 2011
Enable auditing for SYSDBA (sys) user in 11gr2
Sunday, December 5, 2010
Bug : DATABASE CRASH AND FAILS TO OPEN WITH ORA-600 [KCRATR_NAB_LESS_THAN_ODR]
check on metalink and found this is a BUG and solution didn't publish.

If you receive error like this then kindly submit logfiles to support for further action.
Error message:
ORA-600: internal error code, arguments: [kcratr_nab_less_than_odr],[1],[1838],[180473],[180496],[]
Friday, June 5, 2009
ORA-27001 on Database Startup
Database: 10gr1 10.1.0.5.0
OS: Windows 2003 sp1 standard edition
When trying to start the database after enable PAE getting below error
ORA-27001 on Database Startup
fact: Oracle Server - Enterprise Edition
fact: MS Windows Platform
symptom: Database startup fails
symptom: ORA-27100: shared memory realm already exists
symptom: DB_BLOCK_BUFFERS parameter is not set too high for the machine resourcescause: Operating system has a duplicate Oracle memory segment still in memory.
fix:
Stop and start the database service. If this does not help, then a re-boot of the server will flush the memory
Metalink Note: 208285.1
Ora-27100 even After Restarting the Windows Service
Metalink Note: 370469.1The parameter sga_max_size was changed from 2GB to 3GB through OEM Or Command prompt
Cause
Changing the parameter from OEM will make the change in the spfile.
ORA-27100 is received as a result of changes made to parameters affecting the SGA size.The service was created to start with spfile .When the service is restarted , it will use the spfile and the error occurs due to SGA parameter change.Solution: try to start db with pfile instead of SPFILE.
How to enable PAE on windows platform
1. open boot.ini file (it is hidden system file)
2. add '/PAE /3GB' in the following format
[boot loader]timeout=30default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS[operating systems]multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /noexecute=optout /fastdetect /PAE /3GB
3. restart WINDOWS machine
4. check the system properties for PAE addition confirmation as above picture.
Sunday, February 11, 2007
Oracle Database Windows/UNIX Differences
On UNIX
several files and scripts in different directories are used to start an instance automatically. Other scripts are run on computer shutdown, allowing applications such as Oracle Database to shut down cleanly.
on Windows
set registry parameter ORA_SID_AUTOSTART to true
To start the listener automatically, set services startup type to automatic
Background Processing and Batch Jobs
UNIX provides sophisticated control mechanisms for background processing and batch jobs
Windows, use the AT command or a GUI version in the Microsoft Resource Kit
Diagnostic and Tuning Utilities
On UNIX
utilities such as sar and vmstat are used to monitor Oracle Database background and shadow processes
on Windows
Oracle Counters for Windows Performance Monitor, Task Manager, Control Panel, Event Viewer, User Manager, and Microsoft Management Console (included only with Windows 2000).
Task Manager on Windows displays currently running processes and their resource usage, similar to the UNIX ps -ef command or HP OpenVMS SHOW SYSTEM. But Task Manager is easier to interpret and the columns can be customized
Dynamic Link Libraries (DLLs)
Shared libraries on UNIX are similar to shared DLLs on Windows. Object files and archive libraries are linked to generate Oracle Database executables. Relinking is necessary after certain operations, such as installation of a patch.
On Windows, Oracle Database DLLs form part of the executable at run time and are therefore smaller. DLLs can be shared between multiple executables. Relinking by the user is not supported, but executable images can be modified using ORASTACK.
Initialization Parameters: Multiple Database Writers
On UNIX, you can specify more than one database writer process with initialization parameter DB_WRITERS. Multiple database writers can help, for example, when a UNIX port does not support asynchronous I/O.
DB_WRITERS is supported but typically unnecessary on Windows, which has its own asynchronous I/O capabilities.
Oracle Net Services Configuration on Windows
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services contains subkeys that correspond to services
OracleHOME_NAMEClientCache
OracleHOME_NAMECMAdmin
OracleHOME_NAMECMan
OracleHOME_NAMETNSListener
Listener Requirements
In Oracle Database 10g Release 1 (10.1), the listener is set to start automatically at system restart.
Single listener can SUPPORT MULTIPLE DATABASES.
or
If we want to use separate listener for each database then make sure listener configured to listen on different TCP/IP port numbers.
If some IP address or PORT are used then second or subsequent listeners would fails.
If we have more than one ORACLE_HOME with different Oracle Version then higher Oracle Version capable to listen lower version. we should configure higher version listener or stop lower version listener.
example : Oracle 10gr2 or Oracle 9ir2 on some machine.just configure oracle 10gr2 listener to listen both connection 10g or 9i. or stop oracle 9i listener services.
Understanding Optional Configuration Parameters
Oracle Net Service first checks for the parameters as environment variables, and uses the values defined. If environment variables are not defined, it searches for these parameters in the registry
LOCAL
You can use parameter LOCAL to connect to Oracle Database without specifying a connect identifier in the connect string.
connect identifier should be "NET SERVICE NAME"
Oracle Net checks if LOCAL is defined as an environment variable or as a parameter in the registry, and uses finance as the service name. If it exists, Oracle Net connects
TNS_ADMIN
You can add parameter TNS_ADMIN to change the directory path of Oracle Net Services configuration files from the default location of ORACLE_HOME\network\admin.
USE_SHARED_SOCKET
You can set parameter USE_SHARED_SOCKET to true to enable use of shared sockets. If this parameter is set to true, the network listener passes the socket descriptor for client connections to the database thread. As a result, the client does not need to establish a new connection to the database thread and database connection time improves. Also, all database connections share the port number used by the network listener, which can be useful if you are setting up third-party proxy servers.
This parameter only works in dedicated server mode in a TCP/IP environment
Configuration Parameters and the Registry
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOME_NAME
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
Oracle Database Specifications for Windows
Oracle Database provides an annotated sample initialization parameter file with alternative values for initialization parameters.
SGA_MAX_SIZE Parameter
Parameter SGA_MAX_SIZE holds the maximum size that System Global Area (SGA) can reach for a particular instance.
Beginning with Oracle9i release 1 (9.0.1), Oracle Database can change its SGA configuration while the instance is running
If no SGA_MAX_SIZE value is specified, then Oracle Database selects a default
value that is the sum of all components specified or defaulted at initialization time. If SGA_MAX_SIZE specified in the initialization parameter file is less than the sum of all components specified or defaulted to at initialization time, then the setting of SGA_MAX_SIZE in the initialization parameter file is ignored.
Initialization Parameters Without Windows-Specific Values
AUDIT_FILE_DEST
Not supported on Windows and should not be added to the initialization parameter file
DB_WRITER_PROCESSES
Supported, but typically unnecessary due to Windows asynchronous I/O capabilities
COMPATIBLE_NO_RECOVERY
Uses default value set in Oracle Database kernel (no Windows-specific value)
BACKGROUND_CORE_DUMP
Specifies whether Oracle Database includes SGA in core file for Oracle Database background processes
SHADOW_CORE_DUMP
Specifies whether Oracle Database includes SGA in core file for foreground (client) processes
CORE_DUMP_DEST
Specifies directory where Oracle Database dumps core files
CPU_COUNT
Oracle Database automatically sets value to number of processors available for your Oracle Database instance
HI_SHARED_MEMORY_ADDRESS
Not applicable to Windows
SHARED_MEMORY_ADDRESS
Not applicable to Windows
LARGE_POOL_SIZE
Uses maximum value limited by available memory
LOG_BUFFER
Starter database uses value set in Oracle Database kernel (no Windows-specific value). The Custom database creation option of Database Configuration Assistant enables you to customize the value for this parameter.
SPIN_COUNT
Uses default value set in Oracle Database kernel (no Windows-specific value)
Uneditable Database Initialization Parameters
They cannot be modified after you have created the database.
CHARACTER SET
Specifies database Globalization Support character set to use. This parameter can be set only when you create the database.
DB_BLOCK_SIZE
Specifies size in bytes of standard Oracle Database blocks.
DB_NAME
Specifies name of the database to be created. Database name is a string of eight characters or less. You cannot change the name of a database.
Calculating Database Limits
Maximum File Sizes
Maximum file size for a FAT file 4 GB
Maximum file size in NTFS 16 Exabytes (EB)
Maximum database size 65,536 * 64 GB equals approximately 4 Petabytes (PB)
Maximum control file size 20,000 blocks
Authenticating Database Users with Windows
Enabling users to connect to Oracle Database without supplying a username or password
Current user database links are not supported with Windows native authentication
Windows Authentication Protocols
The Windows native authentication adapter works with Windows authentication
protocols to enable access to Oracle Database.
Kerberos is the default authentication protocol for Windows 2000.
The only Oracle Database requirement is to ensure that parameter
SQLNET.AUTHENTICATION_SERVICES contains nts in the following file on both the client and database server:
ORACLE_BASE\ORACLE_HOME\network\admin\sqlnet.ora
Operating System Authentication Enabled at Installation
When you install Oracle Database, a special Windows local group called ORA_DBA is created. Members of local group ORA_DBA automatically receive the SYSDBA privilege.
Membership in ORA_DBA enables you to
Connect to local Oracle Database servers without a password with the command
CONNECT / AS SYSDBA
Connect to remote Oracle Database servers without a password with the command
CONNECT /@net_service_name AS SYSDBA
Monitoring a Database on Windows
Oracle Counters for Windows Performance Monitor
Monitor database objects, such as CPU usage, buffer cache, or a background process.
Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Oracle Counters for Windows Performance Monitor
Event Viewer
Monitor database events
Start > Programs > Administrative Tools > Event Viewer
Trace Files
Record occurrences and exceptions of database operations
BACKGROUND_DUMP_DEST
Alert Files
Record important information about error messages and exceptions during database operations
BACKGROUND_DUMP_DEST
Oracle Enterprise Manager
Monitor and tune using tools with real-time graphical performance information
Oracle Administration Assistant for Windows
View information on or terminate any Oracle thread.
Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows
Administering a Database on Windows
1.Control Panel
start > run > services.msc
2.Command Prompt
c:\>net start/stop OracleSerivce
3.Oracle Administration Assistant for Windows
Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows
Setting Registry Parameters
To start or stop Oracle Database through Oracle Database Services
ORA_SID_AUTOSTART [TRUE/FALSE]
Viewing and Hiding the Password File
1. Command Prompt
C:\oracle\product\10.1.0\Db_1\database>attrib +H PWDorcl.ora
C:\oracle\product\10.1.0\Db_1\database>attrib +H PWDdb01.ora
C:\oracle\product\10.1.0\Db_1\database>attrib
A C:\oracle\product\10.1.0\Db_1\database\hc_db01.dat
A C:\oracle\product\10.1.0\Db_1\database\hc_orcl.dat
A C:\oracle\product\10.1.0\Db_1\database\INITorcl.ORA
A C:\oracle\product\10.1.0\Db_1\database\oradba.exe
A H C:\oracle\product\10.1.0\Db_1\database\PWDdb01.ora
A H C:\oracle\product\10.1.0\Db_1\database\PWDorcl.ora
A C:\oracle\product\10.1.0\Db_1\database\SPFILEDB01.ORA
A C:\oracle\product\10.1.0\Db_1\database\SPFILEORCL.ORA
2. Windows Explorer
Connecting Remotely to the Database as SYS
With Oracle Database, the password used to verify a remote database connection is automatically encrypted. Whenever a user attempts a remote login,
Set the ORA_ENCRYPT_LOGIN environment variable to TRUE on the client machine.
Set the DBLINK_ENCRYPT_LOGIN server initialization parameter to TRUE
Changing Archive Mode to ARCHIVELOG
shutdown immediate;
startup mount;
archive log list
alter database archivelog;
archive log list
alter database open;
LOG_ARCHIVE_FORMAT
%%ORACLE_SID%%%T.ARC specifies the thread number
%%ORACLE_SID%%%S.ARC specifies the log sequence number
%%ORACLE_SID%%%t.ARC specifies the thread number. The number is not padded
%%ORACLE_SID%%%s.ARC specifies the log sequence number. The number is not padded
Database Tools on Windows
Create a database SQL*Plus Worksheet/DBCA
Delete database services ORADIM/DBCA
Start a database OEM/SQL*PLUS/SQL Worksheet
Shut down a database OEM/SQL*PLUS/SQL WS/Ctrl panel
Change database passwords ORADIM/ORAPWD
Migrate database DBUA
Upgrade a database DBUA/SQL*PLUS
Export data EXP/EXPDP
Import data IMP/IMPDP
Load data SQL*LOADER/OEM
Backup database RMAN/OCOPY/OEM

