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

Archive for February, 2012

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

Advertisements

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 »