Pages

Monday, December 30, 2013

What happens when DATAFILE RESIZE?

There is a need to understand what will be the impact when resizing some 8000 plus datafiles consecutively.  After some search did some test like enabled 10046 trace and captured what will happen when there is a datafile resize.

Used tkprof to format and provided the output to customer explaining the behavior.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"
SQL> oradebug setmypid
Statement processed.
SQL> alter session set tracefile_identifier='mytrace_10046';

Session altered.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\TESTDRP1.DBF' resize 150m;

Database altered.

SQL> ALTER SESSION SET EVENTS='10046 trace name context off';

Session altered.

SQL> oradebug tracefile_name
D:\ORACLE_DB\diag\rdbms\testdb\testdb\trace\testdb_ora_556_mytrace_10046.trc
SQL>

TKPROF Output.

alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\TESTDRP1.DBF' resize 150m

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.01       1.96          0          0          3           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       1.99          0          0          3           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  control file sequential read                   31        0.03          0.10
  db file sequential read                         2        0.02          0.04
  Disk file operations I/O                        3        0.01          0.04
  Data file init write                            1        0.00          0.00
  db file single write                            1        0.00          0.00
  control file parallel write                     3        0.00          0.00
  rdbms ipc reply                                 1        0.21          0.21
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       10.41         10.41

********************************************************************************

Recommendation given ,
-- See the above test, when there is a resize it will go through all this events
-- This test is done on my local machine where there is a very minimal load.
-- As it took 1.99s for a single file there will be more time considering your server.
-- Considering other factors like OS/ storage performance, it will take more time
-- Do it in a small batches and in a sequential manner. So that it will not harm database performance. 
-- Also it is best to test a small batch to estimate run  time in non business hours and proceed with the activity.

Thanks for reading, post your suggestions / comments on this. Happy reading.


how to filter records with symbol ~ (tilt)

There is one requirement for customer to select rows which has tilt at the beginning and at the end. After some searching found REGEXP_LIKE New Operator in 10G. Did some test case and seems so simple. Thought to post as I didn't get much detailed info on google.

Rows begins with ~
18:06:37 SQL> select * from test where REGEXP_LIKE (ID,'^~');

ID
--------------------
~test
~1~
~11~
~12~
~32~

Rows begin with ~ and number and ~
18:09:55 SQL> select * from test where REGEXP_LIKE (ID,'^~[0-9]~$');

ID
--------------------
~1~

Rows begin with ~ and any no. of digits and ends with ~
18:11:06 SQL> select * from test where REGEXP_LIKE (ID,'^~[0-9]*~$');

ID
--------------------
~1~
~11~
~12~
~32~

{m} Matches exactly m times
Bracket expression for specifying a matching list that should match any one of the expressions represented in the list. A nonmatching list expression begins with a circumflex (^) and specifies a list that matches any character except for the expressions represented in the list.
^-- beginning of line
$ End of line.

Rows begin with ~ and then 2 numbers and ends with ~
18:11:42 SQL> select * from test where REGEXP_LIKE (ID,'^~[0-9]{2}~$');

ID
--------------------
~11~
~12~
~32~

Thanks for reading, Please comment on post. Happy reading.


Reference:

REGEXP_LIKE New Operator in 10G (Doc ID 255953.1)
http://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions007.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_posix001.htm#i690819
http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_posix002.htm#i690823

Tuesday, October 29, 2013

Now 11g OCP certified

Friends,

I have upgraded certification from 10g OCP to 11g OCP ...


Saturday, September 7, 2013

12c Cannot create service/ sid using ORADIM (DIM-00019: create service error O/S-Error: (OS 5) Access is denied.)

Creating new sid in windows using ORADIM throws the below error. After spending some research found the simple solution. From 12.1 oracle introduced owner for oracle home. See the below description from Doc. When calling oradim it will prompt for that OH owner name.

C:\Users\mmeerha>oradim -new -sid testdb12c
Enter password for Oracle service user:
DIM-00019: create service error
O/S-Error: (OS 5) Access is denied.

Starting with Oracle Database 12c Release 1 (12.1), Oracle Database supports the use of Oracle home user, specified at the time of installation. Oracle home user is the owner of Oracle services that run from Oracle home and cannot be changed post installation. On a system, different Oracle homes can share the same Oracle home user or use different Oracle home user names.

Oracle home user can be a Windows built-in account or a standard Windows user account (not an Administrator account). This account is used for running the Windows services for the Oracle home. For a database server installation, Oracle recommends that you use a standard Windows user account (instead of a Windows built-in account) as the Oracle home user for enhanced security.

For Oracle RAC Database, the Oracle home user must be a Windows domain user account and must be an existing Windows account.

Even when providing the correct password it throws the same error.

Then I realised to run cmd prompt as administrator.

Also Oracle Doc mentioned it
  • Windows Vista, Windows Server 2008, and Windows 7 require Administrator privileges to run ORADIM.



So what if I forget Oracle home user password, the sid will be created but it will not be started. See the below snaps



So how can I find who owns the Oracle home, and how to modify the password of it.

From windows registry HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraDB12Home1


As other windows users, reset password on it and run as administrator to create Sid.

Note :
In 11g We will get different error in oradim when not run cmd prompt as administrator.



Happy reading :), post your comments if anything to improve.

Reference:

Monday, August 26, 2013

Orakill similar to kill -9


C:\Users\mmeerha>orakill

Usage:  orakill sid thread

  where sid    = the Oracle instance to target  --- > (sid is not session id it is instance name)
        thread = the thread id of the thread to kill

  The thread id should be retrieved from the spid column of a query such as:

        select spid, osuser, s.program from
        v$process p, v$session s where p.addr=s.paddr

C:\Users\mmeerha>orakill 5 8824

Could not attach to Oracle instance 5: err = 203

C:\Users\mmeerha>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 26 00:29:55 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select     s.username
  2  ,  s.sid
  3  ,  s.serial#
  4  ,  p.spid
  5  ,  last_call_et
  6  ,  status,to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
  7  from       V$SESSION s
  8  ,  V$PROCESS p
  9  where      s.PADDR = p.ADDR
 10  and        s.sid=5
 11  /

USERNAME                              SID    SERIAL# SPID
------------------------------ ---------- ---------- ------------------------
LAST_CALL_ET STATUS   LOGIN_TIME
------------ -------- --------------
A                                       5          5 8824
        4549 ACTIVE   22:56 25/08/13


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\mmeerha>orakill testdb 8824

Kill of thread id 8824 in instance testdb successfully signalled.

Sunday, August 25, 2013

Tail -f in windows

     We have inconvenience in looking alert log when it grows in windows machines as we do it in linux machines with tail -f. Oracle ADRCI utility provide this feature and is very useful. We can also use it via Notepad++ but what we have to switch window to see the last update.

Remember it is 11g feature.

Steps followed:

  1. Set proper environment variables
  2. login to adrci
  3. set sid with set home as below
  4. SHOW ALERT -TAIL -F

C:\Users\mmeerha>adrci

ADRCI: Release 11.2.0.2.0 - Production on Sun Aug 25 23:31:40 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "D:\ORACLE_DB"
adrci> show homes
ADR Homes:
diag\clients\user_mmeerha\host_813904677_80
diag\clients\user_SYSTEM\host_813904677_80
diag\rdbms\clonedb\clonedb
diag\rdbms\orcl\orcl
diag\rdbms\testdb\testdb
diag\tnslsnr\MMEERHA-IN\listener
adrci> set home diag\rdbms\testdb\testdb
adrci> SHOW ALERT -TAIL -F
2013-08-25 23:31:37.488000 +05:30
Archived Log entry 3579 added for thread 1 sequence 3672 ID 0x9a702651 dest 1:
2013-08-25 23:31:42.026000 +05:30
Thread 1 cannot allocate new log, sequence 3675
Checkpoint not complete
  Current log# 1 seq# 3674 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO01.LOG
2013-08-25 23:31:44.418000 +05:30
Thread 1 advanced to log sequence 3675 (LGWR switch)
  Current log# 2 seq# 3675 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO02.LOG
Archived Log entry 3580 added for thread 1 sequence 3673 ID 0x9a702651 dest 1:
2013-08-25 23:31:51.635000 +05:30
Archived Log entry 3581 added for thread 1 sequence 3674 ID 0x9a702651 dest 1:
2013-08-25 23:31:58.339000 +05:30
Thread 1 cannot allocate new log, sequence 3676
Checkpoint not complete
  Current log# 2 seq# 3675 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO02.LOG
2013-08-25 23:31:59.473000 +05:30
Thread 1 advanced to log sequence 3676 (LGWR switch)
  Current log# 3 seq# 3676 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO03.LOG
2013-08-25 23:32:07.646000 +05:30
Thread 1 advanced to log sequence 3677 (LGWR switch)
  Current log# 1 seq# 3677 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO01.LOG
2013-08-25 23:32:16.794000 +05:30
Thread 1 cannot allocate new log, sequence 3678
Checkpoint not complete
  Current log# 1 seq# 3677 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO01.LOG
2013-08-25 23:32:18.185000 +05:30
Archived Log entry 3582 added for thread 1 sequence 3675 ID 0x9a702651 dest 1:
2013-08-25 23:32:19.720000 +05:30
Thread 1 advanced to log sequence 3678 (LGWR switch)
  Current log# 2 seq# 3678 mem# 0: D:\ORACLE_DB\TESTDB\TESTDB\REDO02.LOG
^C
C:\Users\mmeerha>

Happy reading, leave your comments/suggestion.

Reference:

Sunday, August 11, 2013

Why CBD_DATA_FILES and V$DATAFILE differs?

Though it is well documented in the Oracle 12c documentation, just to demo I wrote this post.

http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn009.htm#REFRN009

CDB_* Views

For every DBA_* view, a CDB_* view is defined. In the root of a multitenant container database (CDB), CDB_* views can be used to obtain information about tables, tablespaces, users, privileges, parameters, and so on contained in the root and in pluggable databases (PDBs).
CDB_* views are container data objects. When a user connected to the root queries a CDB_* view, the query results will depend on the CONTAINER_DATA attribute for users for the view. The CONTAINER_DATA clause of the SQL ALTER USER statement is used to set and modify users' CONTAINER_DATA attribute.
The CDB_* views are owned by SYS, regardless of who owns the underlying DBA_* view.
By default, a user connected to the root will only see data pertaining to the root.

In a PDB, the CDB_* views only show objects visible through a corresponding DBA_* view.
In addition to all the columns found in a given DBA_* view, the corresponding CDB_* view also contains the CON_ID column, which identifies a container whose data a given CDB_* row represents. In a non-CDB, the value of a CON_ID column will be 0.
Data returned by these views depends on whether a given PDB is open at the time the query is issued.

In particular, in an Oracle RAC environment, data returned by these view may vary according to the instance to which a session is connected.

List of action performed

  1. Logged into CDB
  2. Queried  both cbd_data_files and v$datafile , CDB_ view has only 7 rows
  3. Queried V$PDBS to know status off PDBs
  4. One PDB is in Mount state
  5. Opened that PDB in read write mode.
  6. Then we could see all the files.

SQL> set time on echo on
22:27:54 SQL> select name,cdb from v$database;

NAME      CDB                                                                  
--------- ---                                                                  
ORCL12C   YES                                                                  

22:28:14 SQL> sho con_name

CON_NAME                                                                       
------------------------------                                                 
CDB$ROOT                                                                       
22:28:20 SQL> set pages 20
22:28:54 SQL> select name from v$datafile;

NAME                                                                           
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF                                   
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF                                     
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSTEM01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSAUX01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF               
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\EXAMPLE01.DBF                           

10 rows selected.

22:29:03 SQL> select file_name from cdb_data_files;

FILE_NAME                                                                      
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF                                     
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF                                   
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF                            

6 rows selected.

22:29:24 SQL> select con_id,name,open_mode from v$pdbs order by 1;

    CON_ID NAME                           OPEN_MODE                            
---------- ------------------------------ ----------                           
         2 PDB$SEED                       READ ONLY                            
         3 PDBORCL                        MOUNTED                              


22:31:08 SQL> alter pluggable database PDBORCL open read write;

Pluggable database altered.

22:31:25 SQL> select con_id,name,open_mode from v$pdbs order by 1;

    CON_ID NAME                           OPEN_MODE                            
---------- ------------------------------ ----------                           
         2 PDB$SEED                       READ ONLY                            
         3 PDBORCL                        READ WRITE                           

22:31:32 SQL> select file_name from cdb_data_files;

FILE_NAME                                                                      
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF                                     
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF                                   
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSTEM01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSAUX01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF               
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\EXAMPLE01.DBF                           

10 rows selected.

22:31:45 SQL> spool off

Please provide your valuable suggestion/ corrections to improve
Thanks , Happy reading...

Sunday, August 4, 2013

Oracle 12c Database install on Windows 7

Today I have installed 12c database on my laptop to test one issue, just thought to share the steps. Will post all my experiments in this blog soon. Happy learning. :)

1. Configure Security updates.

2. Mention whether software updates needed
3. Install options, select 

4. Install class Desktop or server class

5. Grid installation option
6. Install type
7. Specify Oracle Home user
8. Select home path, provide OS admin user password, whether container/ pluggable DB need to be created
9 & 10 Pre requisite check and Summary Screen ( Forgot to take Snap) sorry

11. Installing database, will invoke DBCA and will create CDB and PDB
12. Finish
Test login


Windows x64 Hardware Requirements
RequirementValue
System Architecture
Processor: AMD64 and Intel EM64T
Physical memory (RAM)
2 GB minimum
Virtual memory (swap)
  • If physical memory is between 2 GB and 16 GB, then set virtual memory to 1 times the size of the RAM
  • If physical memory is more than 16 GB, then set virtual memory to 16 GB
Disk space
  • Typical Install Type total: 10 GB
  • Advanced Install Types total: 10 GB
See Table 2-2 for details.
Video adapter
256 colors
Screen Resolution
1024 X 768 minimum


Reference:

Oracle® Database Installation Guide 12c Release 1 (12.1) for Microsoft Windows
Oracle 12c Download

Sunday, May 12, 2013


DBCA error Java.lang.OutOfMemoryError: Java Heap Space" Error


We can increase the default 128m memory value in dbca file and start dbca again

Windows :
1. Navigate to ORACLE_HOME/BIN (Take a backup dbca.bat)
2. Find and edit file dbca.bat with the below 
"D:\Oracle_DB\product\11.2.0\dbhome_1\jdk\jre\BIN\JAVA" -DORACLE_HOME="%OH%" -Doracle.installer.not_bootstrap=true -DJDBC_PROTOCOL=thin -mx128m oracle.sysman.assistants.dbca.Dbca %*
3. Modify "-mx128m" value to a higher value like -mx512m

Linux:
1. cd $ORACLE_HOME/bin
2. ls -ltr dbca
3. vi dbca and edit the value -mx128m
JRE_OPTIONS="${JRE_OPTIONS} -DSET_LAF=${SET_LAF} -Dsun.java2d.font.DisableAlgorithmicStyles=true -Dice.pilots.html4.ignoreNonGenericFonts=true -DDISPLAY=${DISPLAY} -DJDBC_PROTOCOL=thin -mx128m"

Thursday, May 9, 2013


How to find a column has space:

There may be several other ways to find space in a column, this is another simple way

SQL> sho user
USER is "SYS"
SQL> conn a/a
Connected.
SQL> create table space (space varchar2(10));

Table created.

SQL> insert into space values ('test1');

1 row created.

SQL> insert into space values ('test2 ');

1 row created.

SQL> insert into space values ('test3  ');

1 row created.

SQL> insert into space values ('test4   ');

1 row created.

SQL> insert into space values ('test5    ');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from space;

SPACE
----------
test1
test2
test3
test4
test5

SQL> select length(space) from space;

LENGTH(SPACE)
-------------
            5
            6
            7
            8
            9

SQL> select space|| ';' from space;

SPACE||';'
-----------
test1;
test2 ;
test3  ;
test4   ;
test5    ;

SQL> select length(space) "length",space|| ';' value from space;

    length VALUE
---------- -----------
         5 test1;
         6 test2 ;
         7 test3  ;
         8 test4   ;
         9 test5    ;

Tuesday, February 19, 2013

Compare AWR reports easily.


This is my new post exactly after '5 months, 3 weeks, 6 days' long break. I have joined most prestigious company for any DBA. New office, city, house... day by day new learning and unique by its nature. I will try to post my new learning here.

I have seen feature in OEM to compare two awr reports and today found a sql and functions for it AWR_DIFF_REPORT_HTML/ AWR_DIFF_REPORT_TEXT.

I ran in a windows 7, oracle 11.2.0.2 database.

The script is @?/rdbms/admin/awrddrpt. It will confirm two dbids and 4 snaps. easier to compare two reports.

Snap of a sample report.


Reference:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm#BACEHJJI