Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Install Oracle VM VirtualBox 4.2.14

Posted by Hendry chinnapparaj on July 1, 2013

Install Oracle VM VirtualBox 4.2.14

Advertisements

Posted in Oracle VirtualBox | Tagged: | Leave a Comment »

Quick oracle10g Database Copying/Cloning from Win2003 to Win2008

Posted by Hendry chinnapparaj on September 21, 2012

QUICK DATABASE COPYING / CLONING

Platform: from Windows 2003 to Windows 2008
A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be used to quickly migrate a system from one windows server to another.  It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old Windows 2003  system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “TEST77” NORESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘F:\ORACLE\ORADATA\TEST77\REDO01.LOG’  SIZE 50M,

GROUP 2 ‘F:\ORACLE\ORADATA\TEST77\REDO02.LOG’  SIZE 50M,

GROUP 3 ‘F:\ORACLE\ORADATA\TEST77\REDO03.LOG’  SIZE 50M

— STANDBY LOGFILE

DATAFILE

‘F:\ORACLE\ORADATA\TEST77\SYSTEM01.DBF’,

‘F:\ORACLE\ORADATA\TEST77\UNDOTBS01.DBF’,

‘F:\ORACLE\ORADATA\TEST77\SYSAUX01.DBF’,

‘F:\ORACLE\ORADATA\TEST77\USERS01.DBF’

CHARACTER SET WE8MSWIN1252

;

Customize the create controlfile script as below and save as cc.sql

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “TEST77” NORESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\REDO01.LOG’  SIZE 50M,

GROUP 2 ‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\REDO02.LOG’  SIZE 50M,

GROUP 3 ‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\REDO03.LOG’  SIZE 50M

— STANDBY LOGFILE

DATAFILE

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSTEM01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\UNDOTBS01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSAUX01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\USERS01.DBF’

CHARACTER SET WE8MSWIN1252

;

STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

–          Do not copy controlfiles and redolog files

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

CREATE CONTROLFILE REUSE DATABASE “TEST77” NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE “TEST77” RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 6: Re-names of the data files names that have changed.

Save as cc.sql.

Old:

DATAFILE
‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSTEM01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\UNDOTBS01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSAUX01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\USERS01.DBF’

New:

DATAFILE
‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSTEM01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\UNDOTBS01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSAUX01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\USERS01.DBF’

STEP 7: Create the bdump, udump and cdump and FRA directories

cd E:\oracle\product\10.2.0\db_1\admin
mkdir TEST77
cd TEST77
mkdir bdump
mkdir udump
mkdir cdump
mkdir adump

cd E:\oracle\product\10.2.0\db_1\oradata\TEST77

mkdir oraflash

cd oraflash

mkdir TEST77
STEP 8: Copy-over the old init.ora file

INITtest77.ORA

STEP 9: Create new Instance in Windows 2008 Server

E:\oracle\product\10.2.0\db_1\database>oradim -NEW -SID TEST77 -SYSPWD sys -STARTMODE manual -PFILE INITtest77.ORA

Instance created.

This will  also create the password file PWDTEST77.ORA

 

STEP 9:  Create the Controlfile

E:\oracle\product\10.2.0\db_1\database>set oracle_sid=test77

E:\oracle\product\10.2.0\db_1\database>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Sep 21 01:34:17 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> @cc.sql

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  2163680 bytes

Variable Size             136117280 bytes

Database Buffers          171966464 bytes

Redo Buffers                4325376 bytes

Control file created.

SQL> select open_mode from v$database;

OPEN_MODE

———-

MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_name                              string      TEST77

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

STEP 10: Create spfile

SQL> create spfile from pfile;

File created.

STEP 11: Shutdown and startup with Spfile

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  2163680 bytes

Variable Size             136117280 bytes

Database Buffers          171966464 bytes

Redo Buffers                4325376 bytes

Database mounted.

Database opened.

SQL> show parameter db_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_name                              string      TEST77

 

STEP 11: Change the Archive destination

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL> alter system set log_archive_dest_1=’location=E:\oracle\product\10.2.0\db_1\oradata\TEST77\arch’ scope=both;

System altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            E:\oracle\product\10.2.0\db_1\oradata\TEST77\arch

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

STEP 12: create temp datafile

ALTER TABLESPACE TEMP ADD TEMPFILE E:\ORACLE\PRODUCT\10.2.0\DB_1\ORADATA\TEST77\TEMP01.DBF’

SIZE 2G  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 5G;

Enjoy !

Shalom

Hendry

Posted in Uncategorized | Leave a Comment »

Thread 1 cannot allocate new log, sequence 36286 Checkpoint not complete while doing import

Posted by Hendry chinnapparaj on June 14, 2012

Problem:- Oracle10g Import is slow and see the warnings in the Oracle Alert Log

Thread 1 advanced to log sequence 36285 (LGWR switch)
Current log# 2 seq# 36285 mem# 0: F:\ORACLE\ORADATA\MZSTWCD1\MZSTWCD1_REDO02A.LOG
Current log# 2 seq# 36285 mem# 1: G:\ORACLE\ORADATA\MZSTWCD1\MZSTWCD1_REDO02B.LOG
Thread 1 cannot allocate new log, sequence 36286
Checkpoint not complete

Cause:- 

The “checkpoint not complete” messages are generated because the logs are switching so fast that the checkpoint associated with the log switch isn’t complete.

During that time, Oracle’s LGWR process has filled up the other redo log groups and is now waiting for the first checkpoint to successfully execute.

Remember, Oracle will stop processing until the checkpoint completes successfully!

Solution:- It’ll take a while to complete. Examine your redologs size, increase the Size so that it can keep up with completing the checkpoint without waiting. Increase the redolog size so that you can have like 4 switches in an hour and add additional one /more redo logs if required.

Shalom

Hendry

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »

UDE-00008: operation generated ORACLE error 31626

Posted by Hendry chinnapparaj on June 12, 2012

Problem:- During Oracle Datapump export in oracle 10.2.0.4 encounters the errors below

. . exported “MZS_OWNER”.”READING”                       3.269 GB 12277747 rows

 

UDE-00008: operation generated ORACLE error 31626

ORA-31626: job does not exist

ORA-39086: cannot retrieve job information

ORA-06512: at “SYS.DBMS_DATAPUMP”, line 2772

ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3886

ORA-06512: at line 1

 

Solution:– it’s mentioned in Oracle MOS.

DataPump Export (EXPDP) Client Gets UDE-8 ORA-31626 ORA-39086 [ID 549781.1]

check the expdp logfile first, if it’s successfully completed as below, then no issue

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
H:\ORACLE\ORAEXP\MZSTWCP\DPDUMP\PROD_MZS_SCHEMAS_20120612.DMP
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 14:12:39

——————–

However, reviewing the log file shows that the “job successfully completed”

Cause

This issue has been discussed in Bug 5969934 EXPDP CLIENT GETS UDE-00008 ORA-31626 WHILE THE SERVER SIDE EXPORT IS OK

Solution

The expdp client makes calls to DBMS_DATAPUMP package to start and monitor export job. Once the export job is underway, the client just monitors the job status by issuing DBMS_DATAPUMP.GET_STAUS. Therefore, if the export logfile says “job successfully completed”, the dump file generated by the job should be fine.

You can simply ignore the errors, since the dump file is still valid for an import.

In the 10.2.0.2 release, there were a number of problems that caused the expdp and impdp clients to exit prematurely, interpreting a nonfatal error as a fatal one, giving the appearance that the job had failed when it hadn’t. In fact, inspection of the log file, if one was specified for the job, showed that the job ran successfully to completion. Often a trace file written by one of the Data Pump processes would provide more detail on the error that had been misinterpreted as a fatal one. Many of these errors involved the queues used for communication between the Data Pump processes, but there were other issues as well.

With each subsequent release, these problems have been addressed, and the client has become more robust and rarely, if ever, runs into situations like this. However, this is the result of many bug fixes in subsequent releases, some in Data Pump and some in supporting layers. It’s impossible to know, at this point, what combination of bug fixes would address this specific failure, and even if that was possible, it wouldn’t address other possible failures that look very similar on the client side.

Posted in Datapump10g | Leave a Comment »

Another version of Microsoft Visual studio 2008 has been detected on this system.

Posted by Hendry chinnapparaj on February 23, 2012

Thursday, 23 February 2012

 

Another version of Microsoft Visual studio 2008 has been detected on this system

 

Problem:- I was trying to install the Client components for sql server 2008 R2, when i encountered the error below

 

“Another version of Microsoft Visual studio 2008 has been detected on this system that must be updated to SP1. please update all the visual studio 2008 installations to SP1 level, by visiting Microsoft Update.

 

Solution:- Try to remove any prior version of Visual studio 2008 and also modify the registry to be able to run the installation.

 

Open Regedit and update the Key value “SP” from “0” to “1” under the path here

 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VS\Servicing\9.0

 

Rerun the Installation

 

Shalom

H

Posted in SQL SERVER 2008 R2, SQL SERVER DATABASE | Leave a Comment »

CREATING AN ORACLE11GR2 DATABASE WITH FEW COMMANDS IN WINDOWS

Posted by Hendry chinnapparaj on February 14, 2012

G:\Hendry Scripts>oradim -new -sid HENDRY
Instance created.

G:\Hendry Scripts>edit initHENDRY.ora

db_name=HENDRY

G:\HENDRY~1>set ORACLE_SID=HENDRY

G:\HENDRY~1>sqlplus “/ as sysdba”

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 14 14:45:52 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=initHENDRY.ora
ORACLE instance started.

Total System Global Area 146472960 bytes
Fixed Size 1373152 bytes
Variable Size 92277792 bytes
Database Buffers 50331648 bytes
Redo Buffers 2490368 bytes
SQL> create database;

Database created.

SQL> select ts#, name from v$tablespace;

TS# NAME
———- ——————————
0 SYSTEM
1 SYSAUX
2 SYS_UNDOTS

SQL> column name format a60
SQL> select ts#, name from v$datafile;

TS# NAME
———- ————————————————————
0 G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\DBS1HENDRY.ORA

1 G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SYX1HENDRY.ORA

2 G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\UND1HENDRY.ORA
SQL> column member format a60
SQL> select group#, member from v$logfile;

GROUP# MEMBER
———- ————————————————————
1 G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1HENDRY.ORA

2 G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2HENDRY.ORA
SQL> column name format a60
SQL> select name from v$controlfile;

NAME
————————————————————
G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\CTL1HENDRY.ORA
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive restrict pfile=initHENDRY.ora
ORACLE instance started.

Total System Global Area 146472960 bytes
Fixed Size 1373152 bytes
Variable Size 92277792 bytes
Database Buffers 50331648 bytes
Redo Buffers 2490368 bytes
Database mounted.
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Release 11.2.0.1.0 – Production
SQL> exit

G:\HENDRY~1>oradim -delete -sid HENDRY
Instance deleted.

Shalom

H

Posted in Database11g | Leave a Comment »

Clone Oracle11gR2 Database on Same Windows Server (without using RMAN)

Posted by Hendry chinnapparaj on February 14, 2012

Source Database : oracle11gr2
Clone New Database: clone1
Mode:- No Archive Mode
Login to Source Database – oracle11gr2
—————————-
SQL> create pfile from spfile;
File created.

SQL> alter database backup controlfile to trace;
Database altered.

mkdir G:\app\oracle\admin\clone1
mkdir G:\app\oracle\admin\clone1\adump
mkdir G:\app\oracle\oradata\clone1 — copy all the datafiles, redo log files here from the source.

Location of spfile and pfile
G:\app\oracle\product\11.2.0\dbhome_1\database
Copy INIToracle11gr2.ora as INITclone1.ora
INITclone1.ora
SPFILECLONE1.ORA
Edit INTclone1.ora and make the following changes and more if required.
*.audit_file_dest=’G:\app\oracle\admin\clone1\adump’
*.control_files=’G:\app\oracle\oradata\clone1\control01.ctl’,’G:\app\oracle\recovery_area\clone1\control02.ctl’
*.db_name=’clone1′
*.db_unique_name=’clone1′

create the script for Controlfile creation from the trace file generated in trace location
G:\app\oracle\diag\rdbms\oracle11gr2\oracle11gr2\trace\oracle11gr2_dbrm_4028.trc

vi createcon.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE “CLONE1” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘G:\APP\ORACLE\ORADATA\CLONE1\REDO01.LOG’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘G:\APP\ORACLE\ORADATA\CLONE1\REDO02.LOG’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘G:\APP\ORACLE\ORADATA\CLONE1\REDO03.LOG’ SIZE 50M BLOCKSIZE 512
DATAFILE
‘G:\APP\ORACLE\ORADATA\CLONE1\SYSTEM01.DBF’,
‘G:\APP\ORACLE\ORADATA\CLONE1\SYSAUX01.DBF’,
‘G:\APP\ORACLE\ORADATA\CLONE1\UNDOTBS01.DBF’,
‘G:\APP\ORACLE\ORADATA\CLONE1\USERS01.DBF’,
‘G:\APP\ORACLE\ORADATA\CLONE1\EXAMPLE01.DBF’
CHARACTER SET WE8MSWIN1252
;

RECOVER DATABASE USING BACKUP CONTROLFILE;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘G:\APP\ORACLE\ORADATA\CLONE1\TEMP01.DBF’
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

😡
Create the Listener Database Service, Tns entry and also the NEW Service

G:\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN
vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = clone1)
(ORACLE_HOME = G:\app\oracle\product\11.2.0\dbhome_1)
(SID_NAME = clone1)
)
)

😡
G:\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN
vi tnsnames.ora
CLONE1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.131)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clone1)
)
)

😡

G:\>oradim -NEW -SID CLONE1 -SRVC OracleServiceCLONE1 -STARTMODE manual -PFILE G:\app\oracle\product\11.2.0\dbhome_1\database\initclone1.ora
Instance created.

Go to the services and check if the New Oracle Service is running.
OracleServiceCLONE1
OracleJobSchedulerCLONE1
Oracle CLONE1 VSS Writer Service
Shutdown and Copy the Database files from the Source Oracle11gr2 database.

Create the Controlfile and startup the database

Open command prompt in Windows
set oracle_sid=clone1
sqlplus / as sysdba
SQL> @createcon.sql

ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1375032 bytes
Variable Size 318768328 bytes
Database Buffers 96468992 bytes
Redo Buffers 6057984 bytes

Control file created.

Then open the database.

If you have copied the database files when the source database was running, then you will get error like

ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘G:\APP\ORACLE\ORADATA\CLONE1\SYSTEM01.DBF’
Try the recovery steps below

copy also the online redo logfiles from the source database to the new clone1 database directory and specify the full path during recovery.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1060604 generated at 02/14/2012 11:02:25 needed for thread 1
ORA-00289: suggestion :
G:\APP\ORACLE\RECOVERY_AREA\CLONE1\ARCHIVELOG\2012_02_14\O1_MF_1_7_%U_.ARC
ORA-00280: change 1060604 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
G:\app\oracle\oradata\clone1\REDO01.LOG
Log applied.
Media recovery complete.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL>

Create the Temporary tablespace

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘G:\APP\ORACLE\ORADATA\CLONE1\TEMP01.DBF

2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.
SQL> select name,created, open_mode from v$database;

NAME CREATED OPEN_MODE
——— ——— ——————–
CLONE1 14-FEB-12 READ WRITE
==================================

SQL to generate the copy script

Select ‘COPY DATAFILE ‘ ||FILE_ID||’ TO “G:\app\oracle\oradata\clone1\’||SUBSTR(FILE_NAME,38,50)|| ‘”;’ from dba_data_files;
‘COPYDATAFILE’||FILE_ID||’TO”G:\APP\ORACLE\ORADATA\CLONE1\’||SUBSTR(FILE_NAME,38
——————————————————————————–
COPY DATAFILE 4 TO “G:\app\oracle\oradata\clone1\RS01.DBF”;
COPY DATAFILE 3 TO “G:\app\oracle\oradata\clone1\OTBS01.DBF”;
COPY DATAFILE 2 TO “G:\app\oracle\oradata\clone1\AUX01.DBF”;
COPY DATAFILE 1 TO “G:\app\oracle\oradata\clone1\TEM01.DBF”;
COPY DATAFILE 5 TO “G:\app\oracle\oradata\clone1\MPLE01.DBF”;

5 rows selected.

Shalom

H

Posted in Clone11g DB or Duplicate | Leave a Comment »

MAXIMUM NUMBER OF USER CONNECTIONS REACHED

Posted by Hendry chinnapparaj on February 10, 2012

Problem:- you get the Following error in sql server 2008 database
2012-02-10 09:17:24.45 Logon Could not connect because the maximum number of ’10’ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]

Diagnose:-
Open a SQL Query session in MS SQL Server Manageent Studio
Execute sp_configure with no parameters displays all configuration options
use KOJ_Marketing_new;
go
RECONFIGURE;
EXEC sp_configure;
otherwise go specific for this parameter
use KOJ_Marketing_new;
go
sp_configure “user connections”
go

name minimum maximum config_value run_value
user connections 0 32767 10 10
It means that SQL Server is configured to accept only 10 connections. This can be changed using sp_configure ‘user connections’, 0. By default SQL will have that option set as 0 hence it accepts unlimited connections. Here someone has changed the default vaul, hence the error in this case.

Solution:-

Exit the SQL Query session and then start it in SINGLE user mode ( can do this using the studio under the properties and options tab) and try to establish a connection at the earliest and then change the options using sp_configure then Restart the Instance in MULTI-USER mode.
use KOJ_Marketing_new;
go

reconfigure;

go

sp_configure ‘user connections’, 0

go

The SQL above will update the config_value immediately, but the run_value will not change until the instance is bounced (as the parameter is not dynamic).
For any dynamic parameter, running reconfigure can dynamically change the run value.

Bouce the SQL Instance now and then query

Open a SQL Query session

use KOJ_Marketing_new;
go
sp_configure “user connections”
go

name minimum maximum config_value run_value
user connections 0 32767 0 0
Shalom
H

Posted in SQL SERVER 2008 R2 | Leave a Comment »

Error in writing to File “orancrypt11.dll” and “oravsn11.dll” during oracle11gr2 install on Windows

Posted by Hendry chinnapparaj on January 30, 2012

Problem:Error in writing to File “orancrypt11.dll” during oracle11gr2 install (copying files stage) on Windows 2003. The process cannot access the file because it’s being used by another process.
Solution: just rename the file to orancrypt11.dll.old and oravsn11.dll.old and click retry.

Posted in Install and Configure | Leave a Comment »

em.ear file not found error during oracle11gr2 installation on windows

Posted by Hendry chinnapparaj on January 30, 2012

Problem: For one of the Projects i was trying to install oracle11gR2 on a windows 2003 server and encountered the error “em.ear” file not found.

Solution:- when you unzip the software files win32_11gR2_database-1of2 and win32_11gR2_database-2of2, unzip them on the same folder.

Shalom

H

Posted in Install and Configure | Leave a Comment »