Pages

Wednesday, June 19, 2013

Oracle Datapump



Oracle Data Pump replaced the older ‘exp’ and ‘imp’ utilities.

Create Data Pump Directory in the source DB host specifying the OS directory where the export will unload the data into a .dmp file

You can skip this step, and use an existing Directory.

SQL> CREATE DIRECTORY schema_refresh AS '/staging/tmp2/ccdw';
Directory created.

SQL> SELECT directory_name, directory_path from all_directories;


SCHEMA_REFRESH
/staging/tmp2/ccdw



I prefer to use a parameter file with expdp.
Create a parameter file to use with expdp; there a number of options to choose from, below basic options:

$ touch schema_refresh.par
$ vi  schema_refresh.par
" schema_refresh.par" [New file]
DUMPFILE= schema_refresh.dmp
DIRECTORY= SCHEMA_REFRESH
SCHEMAS=warehouse
EXCLUDE=TABLE:"IN ('USAGE_CONCURRENT_REQUESTS')"

 Run expdp command using the parameter file created in the step above. Preferably, and as per Oracle, do not run it ‘AS SYSDBA’.


$ expdp parfile= schema_refresh.par
…..
Export: Release 11.2.0.3.0 - Production on Mon Jan 21 15:31:13 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: warehouse
Password: *****
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
…….
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
….

While expdp is running, CTRL+C will allow one to make a number of changes to the job, such as:

Export> STATUS                                                    {To check status of the job}
Export> KILL_JOB                                                 {To kill the job}
Export> PARALLEL=16                                          {Assign number of workers to the job}
Export> EXIT_CLIENT                                          {Exit the client but job continues to run}


From another session, one can attach to a running expdp job:

$ expdp ATTACH=SYS_EXPORT_SCHEMA_01
….
Export: Release 11.2.0.3.0 - Production on Mon Jan 21 16:09:05 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: warehouse
Password: *****
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: SYS_EXPORT_SCHEMA_01





Transfter sqlserver logins.



--------------------------- execute on server (SOURCE)

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO



------------------- execute  above procedure using following sp. (on source)
EXEC sp_help_revlogin

----------------------- run output of above on (target) server where you want to transfer your logins. 
output look likes following
/*
-- Login: NT AUTHORITY\SYSTEM
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

-- Login: NT SERVICE\SQLAgent$VMSQLSERVER
CREATE LOGIN [NT SERVICE\SQLAgent$VMSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

-- Login: test
CREATE LOGIN [test] WITH PASSWORD = 0x0200B133F8DD3324234232348ED337002F32250E4E6586D5042B2BE244E0234022FD68B3ED4DB32ACCFDB1C33F94455449CDCE225BFF6E742642A0B09EB8A27B1 HASHED, SID = 0xF74403A934346CB8241891B145DB8882C4A, DEFAULT_DATABASE = [mydb], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
*/


Check SQL Server version information




1.
SELECT @@VERSION


2.
EXEC xp_msver

3.
SELECT
SERVERPROPERTY('productversion') AS ProductVersion,
SERVERPROPERTY ('productlevel') AS ProductLevel,
SERVERPROPERTY ('edition') AS ProductEdition



Thursday, June 13, 2013

DBMS_SQLTUNE | SQL Tuning Task using SQL_ID



--- SQL Tuning Task using SQL_ID

--- first capture Highest SQL using following Query for last 60 minutes.


select *
  from (select s.BUFFER_GETS, s.DISK_READS ,nvl(s.sql_id,'null') as sql_id , Nvl(S.sql_text, 'NULL') AS SQL_text,
               round(COUNT(*) / 60, 2) DB_TIME,
               ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER(), 2) AS PCT_LOAD
          FROM V$active_Session_History A, v$SQL S
         WHERE A.SQL_ID = S.SQL_ID
           AND SAMPLE_TIME > SYSDATE - 60 / 24 / 60  
           AND SESSION_TYPE <> 'BACKGROUND'
         GROUP BY s.sql_id, s.SQL_text, s.BUFFER_GETS,s.DISK_READS
         ORDER BY COUNT(*) DESC)
 where rownum <= 1;



 --- notedown sql_id from above command. e.g 3hww0a8at1tnv



 --- now execute following  plsql block for sql tuning using sql_id and place your sql_id in sql_id parameter

declare
stmt_task VARCHAR2(100);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '3hww0a8at1tnv');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end


--- notedown  task_id from above plsql block. e.g TASK_18790




--- task is created, now execute task using following.

begin  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_18790'); end;


--- queries related to TASK.

select * from v$advisor_progress x  ;
SELECT task_name, status FROM DBA_ADVISOR_LOG;



-- generate report using following
set long 100000
set longchecksize 10000
set pages 0
set lines 250
SELECT DBMS_SQLTUNE. REPORT_TUNING_TASK('TASK_18789') AS recommendations FROM dual;


as always its better to follow ORACLE documentation for complete understandby refer following link.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sqltun.htm



feeling trouble???????????? dont hasitate to contact me @ adnan.rafi@gmail.com