Wednesday, May 30, 2012

Oracle 10g:Put Database in Archive Log Mode.



1.Set the parameters.

SQL> alter system set log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;

SQL> alter system set log_archive_dest_1='location=C:\archive' SCOPE=spfile;

SQL>alter system set log_archive_start=true scope=spfile;

2.Restart the database.

SQL>SHUT IMMEDIATE

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.
Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             171969412 bytes
Database Buffers          432013312 bytes
Redo Buffers                7135232 bytes
Database mounted.

3.Start Archive log Mode.

SQL> alter database archivelog;

Database altered.

4.Open Database.

SQL> alter database open;

Database altered.


SQL> alter system switch logfile;

END;

Tuesday, May 29, 2012

Microsoft SQL Server:Add Table/Columns Description



Use sys.sp_addextendedproperty to add Description to the tables and columns.

E.g.

For Table Description,


EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'Put your Table Comments Here ',
@level0type = N'SCHEMA', @level0name = 'dbo',
@level1type = N'TABLE',  @level1name = 'TableName';
GO


**dbo is database schema.you may your own schema where objects reside.

For Columns Description,

EXEC sys.sp_addextendedproperty
@name=N'MS_Description',
@value=N'Put your Comment here' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TableName',
@level2type=N'COLUMN',@level2name=N'ColumnName1'
GO

EXEC sys.sp_addextendedproperty
@name=N'MS_Description',
@value=N'Put your Comment here' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TableName',
@level2type=N'COLUMN',@level2name=N'ColumnName2'
GO

Wednesday, May 23, 2012

Oracle 10g:Change Tablespace to all objects



1.Use this script to change Index's Tablespace.

SQL>spool C:\Change_Index_tablespace.sql

SQL>select 'ALTER INDEX '||index_name||' REBUILD TABLESPACE BHAVESH_INDX    ;' from user_indexes;
/

ALTER INDEX SYS_C007391 REBUILD TABLESPACE BHAVESH_INDX    ;
ALTER INDEX TLMYIELDFORECAS_IDX$$_28180009 REBUILD TABLESPACE BHAVESH_INDX;
ALTER INDEX TLMSEGMENTFLIGHTMASTER_PK REBUILD TABLESPACE BHAVESH_INDX;
-----
-------
----------

SQL>Spool off


SQL>@C:\Change_Index_tablespace.sql

2.Use this script to change table's tablespace

SQL>spool C:\Change_Index_tablespace.sql

SQL>select 'ALTER TABLE '||table_name||' MOVE TABLESPACE tablespace_name;'  from user_tables;
/
ALTER TABLE MLOG$_MVDCPRLOADYIELD MOVE TABLESPACE tablespace_name;
ALTER TABLE MLOG$_MVFCSTFLIGHTMAPPINGD MOVE TABLESPACE tablespace_name;
ALTER TABLE MLOG$_MVFLIGHTSCHEDULE MOVE TABLESPACE tablespace_name;
ALTER TABLE MLOG$_MVIEW_REFRESH_LOG MOVE TABLESPACE tablespace_name;

------------------
----------------------
------------------------

SQL>Spool off


Tuesday, May 22, 2012

Oracle 10gR2:Reconfigure Data Control by EMCA



1.Set oracle parameter

Set oracle_sid=blogdb
Set Oracle_Home=F:\oracle\product\10.2.0\db_1

2.De configure and delete old entries.

emca -deconfig all db -repos drop




3.Configure Data Control.

emca -config all db -repos create

Output
May 22, 2012 12:07:44 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at F:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\blogdb\emca_2012-05-22_12-06-05-PM.log.
May 22, 2012 12:07:47 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
May 22, 2012 12:07:47 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Error creating the repository
May 22, 2012 12:07:47 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Refer to the log file at F:\oracle\product\10.2.0\db_1\cfgtoollogs\emca\bl
ogdb\emca_repos_create_<date>.log for more details.
May 22, 2012 12:07:47 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error creating the repository
Refer to the log file at F:\oracle\product\10.2.0\db_1\cfgtoollogs\emca\blogdb\e
mca_2012-05-22_12-06-05-PM.log for more details.
Could not complete the configuration. Refer to the log file at F:\oracle\product
\10.2.0\db_1\cfgtoollogs\emca\blogdb\emca_2012-05-22_12-06-05-PM.log for more de
tails.


in log

CONFIG: ORA-20001: SYSMAN already exists..
ORA-06512: at line 17

oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-20001: SYSMAN already exists..
ORA-06512: at line 17


    at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1467)
    at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeScript(SQLEngine.java:841)
    at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:265)
    at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:306)
    at oracle.sysman.emcp.EMReposConfig.createRepository(EMReposConfig.java:360)
    at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:182)
    at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:124)
    at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:142)
    at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:479)
    at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1123)
    at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:463)
    at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:412)
May 22, 2012 12:07:47 PM oracle.sysman.emcp.EMReposConfig invoke


oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-01921: role name 'MGMT_USER' conflicts with another user or role name

    at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1467)
    at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeScript(SQLEngine.java:841)
    at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:265)
    at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:306)
    at oracle.sysman.emcp.EMReposConfig.createRepository(EMReposConfig.java:360)
    at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:182)
    at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:124)
    at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:142)
    at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:479)
    at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1123)
    at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:463)
    at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:412)
May 22, 2012 12:14:08 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Error creating the repository


Solution. 

SQL> DROP USER sysman CASCADE;

User dropped.

SQL> DROP PUBLIC SYNONYM setemviewusercontext;

Synonym dropped.

SQL> DROP ROLE mgmt_user;

Role dropped.

SQL> DROP PUBLIC SYNONYM mgmt_target_blackouts;

Synonym dropped.

SQL> DROP USER mgmt_view;
       

User dropped.

SQL>



Again,issue command

emca -deconfig all db -repos drop 


 4.Start Configure it.it will take time.

Check Successful Message



Restart Services.

Start DB Console.




Monday, May 14, 2012

Oracle 10g:Create New Database Manually.



1.Create New Service

Go to Command Prompt.

oradim -new -sid blogdb -intpwd blogdb

2.Create Parameter File

Sample Pfile

*.__db_cache_size=444596224
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__shared_pool_size=150994944
*.__streams_pool_size=0
*.compatible='11.1.0'
*.control_files='D:\Database\control01.ctl','D:\Database\control02.ctl','D:\Database\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='blogdb'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='blogdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=blogdbXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384

3.Start Database in NoMount mode with pfile 

startup nomount pfile=D:\oracle\product\10.2.0\db_1\database\INITblogdb.ora

4. Create Database 

Sample Script

create database blogdb
  logfile   group 1 ('D:\Database\redo1.log') size 50M,
            group 2 ('D:\Database\redo2.log') size 50M,
            group 3 ('D:\Database\redo3.log') size 50M
  character set          WE8ISO8859P1
  national character set utf8
  datafile 'D:\Database\system.dbf'
            size 100M
            autoextend on
            next 50M maxsize unlimited
            extent management local
  sysaux datafile 'D:\Database\sysaux.dbf'
            size 100M
            autoextend on
            next 50M
            maxsize unlimited
  undo tablespace undo
            datafile 'D:\Database\undo.dbf'
            size 100M
  default temporary tablespace temp
            tempfile 'D:\Database\temp.dbf'
            size 100M;

Check Message

Database created.

SQL>
SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

5.Execute Database Scripts

@?\RDBMS\ADMIN\catalog.sql

@?\RDBMS\ADMIN\cataproc.sql

@?\RDBMS\ADMIN\utlrp.sql

MIcrosoft SQL Server:Create New Database User for Login.

** Execute this Query **

USE [master]
GO
 

CREATE LOGIN [LoginName] WITH PASSWORD=N'Password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
 

USE [DatabaseName]
GO
 

CREATE USER [DatabaseUserName] FOR LOGIN [LoginName]
GO
 

USE [DatabaseName]
GO
 

EXEC sp_addrolemember N'db_datareader', N'DatabaseUserName'
GO

Oracle 10g:Get All Query output values in Comma Separated in One Single row

Replace ColumnName to Column that need out in Comma Separated.

Replace TableName with your Table 

SELECT SUBSTR (c, 2) concatenated
  FROM (SELECT     SYS_CONNECT_BY_PATH ( ColumnName, ',') c, r
              FROM (SELECT   ROWNUM ID, ColumnName,
                             RANK () OVER (ORDER BY ROWID DESC) r
                        FROM TableName
                        WHERE ColumnName='Value'
                    ORDER BY ColumnName)
        START WITH ID = 1
        CONNECT BY PRIOR ID = ID - 1)
 WHERE r = 1;

XType values in Sys.sysobjects

**select *from sys.sysobjects where xtype=? **

Question mark replace by below values.

    C = CHECK constraint
    D = Default or DEFAULT constraint
    F = FOREIGN KEY constraint
    L = Log
    P = Stored procedure
    PK = PRIMARY KEY constraint (type is K)
    RF = Replication filter stored procedure
    S = System table
    TR = Trigger
    U = User table
    UQ = UNIQUE constraint (type is K)
    V = View
    X = Extended stored procedure

Kill Session in Microsoft SQL Server.

DECLARE @ID INT

DECLARE @MSG CHAR(8)

While 1 = 1

BEGIN
    Set RowCount 1

 SELECT
  @id =
  spid
 FROM
  Master..Sysprocesses P,
  Master..Sysdatabases D
 WHERE
  D.Name='DatabaseName'
 AND
  D.dbid = P.dbid
  --and P.blocked<>0 --if you want to kill blocked session.


    IF @@rowcount = 0
    break

    SELECT @msg = 'KILL ' + convert(char(8) ,@ID)
    Print @msg
    EXEC( @msg)
    --break
END

GO

Wednesday, May 9, 2012

Blog for Oracle to MS SQL DBA Migration Methods

Oracle TO SQL Server DBA Migration






SQL Server Connection from Oracle SQL Developer

Connection from SQL Developer to MS SQL.
1.Download jtds-1.2.5-dist
2.Extract Files
3.Go to SQL Developer's TOOL Menu-->Preferences-->Search like Third Party JDBC



4.Click Add Entry -->Add jtds-1.2.5.jar File.
5.Search file ntlmauth.dll into @@jtds-1.2.5-dist\x86\SSO\
and  put into @@\Java\jre1.6.0_12\bin
6.Restart SQL Developer.
7.Go to New Connection and Tab of SQL Server.


8.Give username,password,hostname(instance name),TCP/IP Port.

Eg.


10.Test and Check Status as Success.