Tuesday, July 30, 2013

0 Oracle Backup /Restore (1)

In this article, I am going to demo the basic Oracle Backup/Restore.

 

Find all the database files:

select name from v$datafile;
select name from v$tempfile;
select name from v$controlfile;
select member from v$logfile;

image

Offline backup/Cold backup

Shutdown the database server and use the operation command to copy.

 

Online Backup/Warm backup

-- Begin the backup for all datafile
alter database begin backup;
-- Begin backup for single data file
alter tablespace example begin backup;
-- Check if the data file is in backup mode
select * from v$backup;

image

  • Shutdown the database server and use the operation command to copy.
  • Also need to backup the parameter file.
  • Backup the log file, archive the current log and backup the log use os command
ALTER SYSTEM ARCHIVE LOG current;
  • Backup the control file
alter database backup controlfile to '/u01/app/oracle/control_back.ctl';

image

Database Recovery

-- shutdown the database and listener
-- startup listener
-- start database mount
-- recover database
-- alter database open

 

User Tablespace recovery

alter tablespace example offline immediate;
recover tablespace example;
alter tablespace example online;

image

image

image

 

image

 

DataFILE recovery

alter database datafile '/u01/app/oracle/oradata/oracledb1/system01.dbf' online;
recover datafile '/u01/app/oracle/oradata/oracledb1/system01.dbf';
alter database open;

image

Tuesday, July 23, 2013

0 Learning Note: SQL Server VS Oracle–Instance architecture

This is my learning note base on the “SQL Server Essentials for Oracle DBAs Jump Start” . Many of the screenshots are from MSFT.

 

Memory Allocation

Oracle:  PGA + SGA = Total server usage. AMM is Oracle way to simplify the memory configuration. Even so, We have much more  granularity control of different memory setting in Oracle.

image

SQL Server does not have granularity memory configuration like Oracle.  As you can see from below screenshot, we only can configure MAX/MIN Server memory, Index creation memory and Minimum memory per query.

image

select * from sys.dm_os_memory_clerks

image

select * from sys.dm_os_sys_info
select * from sys.dm_os_sys_memory
select * from sys.dm_os_process_memory
select * from sys.dm_os_memory_nodes

image

image

SQLOS is equivalent of the PMON process in Oracle.

image

 

CPU/Thread allocation

Oracle has 2 connection mode: See here more detail.

  • Dedicate Server mode
  • Share Server mode

image

SQL Server always use share server mode.

image

Background processes

  • SQL Server use thread model and we can configure to use fiber optionally.
  • Oracle use process model on Linux but use thread model on windows.

Below is the screenshot of the SQL Server threads.

 image

Below is the screenshot of the Oracle 12c threads on Windows Server 2012.

image

Below is the screenshot of the Oracle 11g process on the Linux

image

Here is the comparison processes between Oracle and SQL server.

image

image

To see background process from SQL Server. Session_id < 50 are the system processes.

select session_id,status,command from sys.dm_exec_requests

image

image

Sunday, July 21, 2013

47 Oracle 12c in Windows Server 2012

 

prerequisite

There are 2 files download from Oracle. We have to extract them and put into the same directory.

image

Installation

Installation steps are mostly the same as previous version. The only major difference is the pluggable database name setting.

Double click the setup.exe to start the installation.

image

image

image

image

image

image

image

The plugin database in the one of the new features in 12c.

image

image

image

image

image

image

image

SQLplus

image

Oracle Enterprise Express 12c

OEM express 12c replace is the light weight web front tool that replace the traditional enterprise manager. However, somehow I can not make it work on my Windows Server 2012.

I didn’t spend too much time on trouble shooting it . Maybe if when I have need to run the Oracle on Windows, I will  Winking smile 

select dbms_xdb_config.gethttpsport () from dual;
show parameter dispatcher ;

0 Install Windows Server 2012

 

 

image

1 Learning Note: SQL Server VS Oracle–Database architecture

This is my learning note base on the “SQL Server Essentials for Oracle DBAs Jump Start” .

 

Data block/Extend and Segment

image

  • Oracle use Extend allocation map to track extend. Extend is continually blocks. Each Extend is always part of one segment. The object is created on the segment.
  • MSSQL use GAM/SGAM to track the page usages. The objects can be uniform extend or in mix extend depends on the object size. Trace 1118 turn on would force the SQL server always use uniform extend.
  • SQL Server is always 8K per page and 64K per extend ( 8 pages X 8K), Oracle has various block size hence has various extend size. Oracle can also has uniform extend size and other various extend size base on how  “Extend management” setting within the table space. ( see here).

 

Tablespaces and system databases

Because there is always one database per oracle instances so most of the system database mapping to Oracle is tablespace.

  • Model DB: Because there is no need to create the separate user db in the Oracle. The close thing to the SQL Server model db is “database Template” which we can use to define the characteristic of database and we can use it to create the database on another instance.
  • Tempdb: Oracle can have multiple temporary table space. SQL server’s tempdb is shared among entire servers. If we turn on the RCSI for the SQL Server database, the version store is also store in the Tempdb versus Oracle store the version in the UNDO tablespace.  DBA_TEMP_FILES list the temporary table space files. For SQL Server, sp_helpdb tempdb.
  • Log file: SQL Server log files are split internally as multiple VLFs and contain both UNDO and REDO logs. ( Except the tempdb log files , it only has UNDO). Oracle log files only has REDO. UNDO log store in the UNDO table space. In SQL Server, each database has its own log files and the log file within the same db are used sequentially. In Oracle, the log files are being divided as log group, each groups can have multiple log files.

image

 

System level INFORMATION

Oracle store the system level information in the SYSTEM tablespace under the schema SYS.

SQL Server store in the master database.

  Oracle SQL Server Master database
Users DBA_USERS syslogins
Objects DBA_OBJECTS sys.objects
Tables DBA_TABLES sys.tables
DataFiles DBA_DATAFILES sys.databases

 

In SQL Server, we can use sp_help to find out the basic information of the object. In Oracle, we use DESC . See here .

v$datafiles and V$logfile are the system level view which allow as to see the information within oracle even the database is not open.

List all process

Oracle: v$sessoins

MSSQL: sys.dm_exec_requests or sp_who2

 

server configuration

For SQL Server, a lot of configuration values are defined as advance, therefore , we have to

EXEC sp_configure 'show advanced option', '0';
reconfigure

 

In SQL Server 2008R2, the basic configuration option is 16. All options including advance are 70.

For Oracle, show parameter would display all the options. there are many advance options but most of time, we don’t really need to change it unless it is asked by Oracle support.

 

  Oracle MSSQL
List all parameters show parameter sp_configure
List single parameter show parameter X sp_configure ‘X’
list parameter with keyword match show paramter XXX N/A

 

Oracle:  please refer here for detail.

alter system set PARAMETER = XX scope=[MEMORY|SPFILE|BOTH]

MSSQL :See here for configuration option.

sp_configure 'XXX'.value;
reconfigure;

 

Resource

http://www.pythian.com/blog/analyzing-sql-server-data-file-anatomy/

Friday, July 19, 2013

0 Partition Index for MSSQL

Recently, I got confuse about the partition index, so I decided to do some testing about it.

Create the sample database

use master
CREATE DATABASE [PARTITION_DEMO] ON  PRIMARY 
( NAME = N'PARTITION2011_DATA', FILENAME = N'C:\DATA\PARTITION2011_DATA.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP FG2012
(NAME = 'PARTITION2012_DATA',FILENAME ='C:\DATA\PARTITION2012_DATA.ndf',SIZE =4096KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB )
 LOG ON 
( NAME = N'PARTITION_LOG', FILENAME = N'C:\DATA\PARTITION_LOG.ldf' , SIZE = 2048KB  , FILEGROWTH = 10%);

 

Create partition function and scheme

CREATE PARTITION FUNCTION partiton_demo_f (datetime)
AS RANGE RIGHT FOR VALUES ('9/01/2011','10/01/2011', '11/01/2011', '12/01/2011',
               '1/01/2012', '2/01/2012', '3/01/2012', '4/01/2012',
               '5/01/2012', '6/01/2012', '7/01/2012', '8/01/2012');
GO
 
CREATE PARTITION SCHEME partiton_demo_s
AS PARTITION partiton_demo_f
TO ([PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [FG2012], [FG2012]
, [FG2012], [FG2012], [FG2012]
, [FG2012], [FG2012], [FG2012]
, [FG2012], [FG2012]);
GO

 

create the test table and insert some data

Create Table dbo.orders
(
      order_id  int Identity(1,1)   Not Null
    ,  product_name char(1000) Not Null 
    , orderDate datetime            Not Null
    , orderData smalldatetime       Not Null
 
) On partiton_demo_s(orderDate);
Go
 
 
Set NoCount On;
 
Declare @endDate datetime = '2011-08-01';
Declare @randomString varchar(255);
 
While @endDate < '2012-09-01'
Begin
    SELECT @randomString = CONVERT(varchar(255), NEWID());
    Insert Into dbo.orders 
    Select @randomString,@endDate, @endDate
 
    Set @endDate = DATEADD(minute, 3, @endDate);
 
End;

 

Check the data distribution

use PARTITION_DEMO:
 
SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('dbo.orders');

 

image

Select i.name
    , i.index_id
    , p.partition_number
    , p.rows
From sys.partitions As p
Join sys.indexes As i
    On p.object_id = i.object_id 
   And p.index_id = i.index_id
Where p.object_id = object_id('orders')
Order By i.index_id, p.partition_number;

Because there are no indexes, so the index_id is 0 , meaning it is heap.

image

Create the partition index

 

CREATE NONCLUSTERED INDEX ix_orders_partitioned
    ON dbo.orders(order_id)
     ON partiton_demo_s(orderDate);

 

Now check the index size again

SELECT i.name
    , i.index_id
   ,p.rows
   ,p.partition_id
   ,a.used_pages*8 AS 'Used space(KB)'
FROM sys.partitions AS p
JOIN sys.indexes AS i
    ON p.object_id = i.object_id 
   AND p.index_id = i.index_id
   JOIN sys.allocation_units a ON p.partition_id=a.container_id
WHERE p.object_id = OBJECT_ID('orders')
-- group by i.index_id,i.name
ORDER BY i.index_id

image

Create non partition index

CREATE NONCLUSTERED INDEX ix_orders_unpartitioned
    ON dbo.orders(order_id)
    ON [PRIMARY];

The index id 3 is non partition index which contains all the rows.

image

 

Compare the performance between partition index and non partition index

Case1 with specific query

Select order_id, orderDate
From dbo.orders With (Index(ix_orders_partitioned))
Where order_id = 300;
 
Select order_id, orderDate
From dbo.orders With (Index(ix_orders_unpartitioned))
Where order_id = 300;

 

With specific query, the partition index is more expensive than the non partitioned index.

image

 

Case 2 with Range

Select order_id, orderDate
From dbo.orders With (Index(ix_orders_partitioned))
where orderdate between '2011-12-27' and '2012-01-11'
 
Select order_id, orderDate
From dbo.orders With (Index(ix_orders_unpartitioned))
where orderdate between '2011-12-27' and '2012-01-11'

Partition index is better than non partition index in this case, because the query is searching for particular date therefore the query optimizer can utilize the partition elimination.

 

image

Check the index fragmentation

SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'dbo.orders'), NULL , NULL, NULL)
where object_id=OBJECT_ID(N'dbo.orders')
GO

image

Rebuild the partition index

ALTER INDEX ix_orders_partitioned
ON dbo.orders
REBUILD Partition = 5;
 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates