Oracle Database 12c Released

Finally Oracle Database 12c Launched.

http://www.pcworld.com/article/2042977/oracle-database-12c-now-available-for-download.html


http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

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

main url: http://oracledbatipsntricks.blogspot.com/2013/01/oracle-data-pump-export-utility.html

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

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