Oracle has introduced it new product: ODA (Oracle Database Appliance)
I was not able to join Oracle President Mark Hurd’s webcast where he supposed to announce a new Oracle product[It was at 1AM my time and I was sleeping], now viewing this webcast HERE
Kerry Osborne has shared information about this product, click link to see it, here you will see some good information about ODA.
Another new thing to talk about 🙂
Last week I saw a warning message at database startup time saying:
WARNING: EINVAL creating segment of size 0x000000000f0020xx fix shm parameters in /etc/system or equivalent
It is an Oracle 10204 database running on Solaris.
Searching MOS for exact meaning for this warning, it states that a new shared memory segment is getting created to accommodate SGA.
As the message indicated, I opened /etc/system file to verify the settings of SHMMAX parameter and found the SHMMAX value to be 4GB. I stopped at this point and closed the /etc/system file. Then the next thing to check is the number of oracle instances running on the server and the size of largest SGA.
There were two instances running on the server and the largest SGA was set to 1.8G and the other SGA size was 700M.
This setting shows that there is no need to create additional shared memory segment. Then I checked the /etc/system file again, but this time I used the following command :
<span style="font-size: small;"><span style="font-family: arial,helvetica,sans-serif;">$ cat /etc/system | grep shmmax</span></span> <span style="font-size: small;"><span style="font-family: arial,helvetica,sans-serif;">set shmsys:shminfo_shmmax=4000000000 ==>4GB</span></span> <span style="font-size: small;"><span style="font-family: arial,helvetica,sans-serif;">set shmsys:shminfo_shmmax=500000000 ==>500M</span></span><span style="font-size: small;"><span style="font-family: arial,helvetica,sans-serif;">
There were two different values set for SHMMAX parameter.
The cause of the above warning message came out to be:
As the files are read from bottom-to-top, server was taking SHMMAX value as 500M and ignoring the 4GB value.
After commenting SHMMAX value of 500M, the warning message disappeared.
You have been asked to schedule a shell script which need to connect to a particular user and perform some action? How do you pass the password to script without hardcoding it in script. If password is written in a script, isn’t it a security threat?
Well with 10gR2 , Oracle Wallet provides you with facility to store database credentials in client side Oracle Wallet. Once stored, you can connect to database using sqlplus /@connect_string
Let’s see how it works.
Create a Oracle Wallet
Syntax – mkstore -wrl -create
$mkstore -wrl /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet -create Enter wallet password:
Two files are created.
$ls -ltr total 8 -rw------- 1 oracle oinstall 3880 Sep 8 22:48 ewallet.p12 -rw------- 1 oracle oinstall 3957 Sep 8 22:48 cwallet.sso
If you schedule cron through oracle user, keep the privileges as such. Please note that if a user has a read permission on these files, it can login to database.So it’s like your House Key which you would like to keep safely with you 🙂
Next step is to add database credential to the wallet. Before this, create a tnsnames entry you will use to access the database
AMIT_TEST11R2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db11g)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test11r2)
)
)
Add user credential to Oracle Wallet. Syntax is
mkstore -wrl wallet_location -createCredential db_connect_string username password</span>
$mkstore -wrl /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet -createCredential amit_test11r2 amit amit Enter wallet password:
To confirm, if the credential has been added , use listCredential option
$mkstore -wrl /u02/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle -listCredential Oracle Secret Store Tool : Version 11.2.0.1.0 - Production Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 1: amit_test11r2 amit
Now add following entries in client sqlnet.ora file
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet) ) ) SQLNET.WALLET_OVERRIDE = TRUE
Ensure that auto-login is enabled for wallet.
Start Oracle Wallet manager
$owm
To enable auto login:
1. Select Wallet from the menu bar.
2.Select Auto Login. A message at the bottom of the window indicates that auto login is enabled.
Now let’s try connecting to database
[oracle@db11g admin]$ sqlplus /@amit_test11r2 SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 8 23:34:37 2009 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> show user USER is "AMIT"
We have been able to login without specifying a password. In case you change password for Database User, you will have to modify credentials .If you don’t, your DB login will fail with ORA-1017.
SQL> alter user amit identified by amitbansal; User altered. [oracle@db11g admin]$ sqlplus /@amit_test11r2 SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 8 23:35:34 2009 Copyright (c) 1982, 2009, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied
To modify credential you need to use modifyCredential option. Syntax for command is
mkstore -wrl <wallet_location> -modifyCredential <dbase_alias> <username> <password>
[oracle@db11g wallet]$ mkstore -wrl /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet/ -modifyCredential amit_test11r2 amit amitbansal Oracle Secret Store Tool : Version 11.2.0.1.0 - Production Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Modify credential Modify 1
To delete credentials use deleteCredential option with tnsalias
$mkstore -wrl /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet/ -deleteCredential amit_test11r2 Oracle Secret Store Tool : Version 11.2.0.1.0 - Production Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Delete credential Delete 1
You can add more users to these wallet , but you will have to use a separate TNSALIAS for it. Note that TNSALIAS is a unique identifier for each user to connect to database.
Oracle 11gR2 has been released for linux x86 and x86-64 platform. Software can be downloaded at http://www.oracle.com/technology/software/products/database/index.html
Guys start downloading..:) ..
Note: – As of now documentation link is not available on http://tahiti.oracle.com (13:30 G.M.T)
Update : – Thanks to H.Tonguç , documentation can be accesed at http://www.oracle.com/pls/db112/homepage?remark=tahiti
Cheers
Amit
There are situations when we see “temporary segments” in permanent tablespaces hanging around and not getting cleaned up.
These temporary segments in permanent tablespace can be created by DDL operations like CTAS and “alter index..rebuild” because
the new object is created as a temporary segment in the target tablespace and when the DDL action finishes it will be changed to permanent type.
These temporary segments take actual disk space when SMON fails to perform its assigned job to cleanup stray temporary segments.
Following query finds out these segments:
SQL > select tablespace_name, owner, sum(bytes/1024/1024) from dba_segments where segment_type = 'TEMPORARY' group by tablespace_name, owner; TABLESPACE_NAME OWNER SEGMENT_NAME SUM(BYTES/1024/1024) ------------------- ------------ ------------ -------------------- xxxx_DATA SYS 123.8365 137706 BDEPST_INDEX SYS 345.8756 8910 KMRPT_DATA SYS 345.87 25284.875 BILL_INDEX SYS .25 DSS_DATA SYS 798 MRKT_INDEX SYS 208 SPCT_DATA SYS 69642.25 SPCT_INDEX SYS 956.4375
Here we can see that tablespace KMRPT_DATA, SPCT_INDEX and SPCT_DATA have large temporary segments.
To know if any DDL is active which can create temporary segments we can use the following:
SQL> conn / as sysdba SQL> select owner FROM dba_segments WHERE segment_name='345.87'; SQL> select pid from v$process where username='owner from above query'; SQL> alter session set tracefile_identifier='TEMPORARY_SEGMENTS'; SQL> oradebug setorapid <pid obtained> SQL> oradebug dump errorstack 3 SQL > oradebug tracefile_name
It will give you the tracefile name, open that file and check for the “current sql”
If it is a DDL like CTAS or index rebuild, then wait for the operation to complete. If there is no pid
returned then these segments are “stray segements” and needs to cleaned up manually.
There are two ways to force the drop of temporary segments:
1. Using event DROP_SEGMENTS 2. Corrupting the segments and dropping these corrupted segments.
1. Using DROP_segments:
Find out the tablespace number (ts#) which contains temporary segments: SQL> select ts# from sys.ts$ where name = 'tablespace name'; Suppose it comes out to be 10, use the following command to cleanup temporary segments: SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 11'; level is ts#+1 i.e 10+1=11 in this case.
2. Corrupting temporary segments for drop:
For this following procedures are used:
– DBMS_SPACE_ADMIN.TABLESPACE_VERIFY
– DBMS_SPACE_ADMIN.SEGMENT_CORRUPT
– DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT
— Verify the tablespace that contains temporary segments (In this case it is KMRPT_DATA)
SQL>DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('KMRPT_DATA');
— Corrupt the temporary segments in tablespace KMRPT_DATA
SQL>DBMS_SPACE_ADMIN.SEGMENT_CORRUPT(' || chr(39) || tablespace_name || chr(39) || ',' || HEADER_FILE || ',' || HEADER_BLOCK || ');' from dba_segments where SEGMENT_TYPE like 'TEMP%' and tablespace_name = 'KMRPT_DATA';
— Drop the corrupted temporary segments
SQL> select 'exec DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT (' || chr(39) || tablespace_name || chr(39) || ',' || HEADER_FILE || ',' || HEADER_BLOCK || ');' from dba_segments where SEGMENT_TYPE like 'TEMP%' and tablespace_name = 'KMRPT_DATA';
— Verify the tablespace again to update the new dictionary information:
SQL>DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('KMRPT_DATA');
This will remove temporary segments from permanent tablespace.
Cheers!!!
Saurabh Sood
Few days back I got a request from development team to generate hourly export dumps of few schemas.
Following were the requirements:
1. Dumpfile name should contain current date and timestamp information.
2. The generated dumpfile should be moved to a specific location.
3. All users should have read privileges on the export dumpfile.
4. The export dump should be taken on hourly basis.
To accomplish this task I generated a shell script and scheduled it in crontab:
#!/bin/ksh #Script to Perform Datapump Export Every Hour ################################################################ #Change History #================ #DATE AUTHOR cHANGE #--------- ----------------------- ------------------- #23-jUN-2009 SAURABH SOOD New Script Created # # # ################################################################ export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db export ORACLE_SID=orcl export PATH=$PATH:$ORACLE_HOME/bin expdp username/password@orcl dumpfile=expdp-`date '+%d%m%Y_%H%M%S'`.dmp directory=DATA_PUMP_DIR logfile=expdp-`date '+%d%m%Y_%H%M%S'`.log schemas=SCHEMA_A,SCHEMA_B mv /tmp/expdp*.dmp /u01/backup/daily_export_orcl/ mv /tmp/expdp*.log /u01/backup/daily_export_orcl/ chmod o+r /s01/backup/daily_export_orcl/*
This script will do the following:
1. Set the ORACLE_HOME,ORACLE_SID and PATH in the environment settings.
2. Taken the datapump export to /tmp location as DATA_PUMP_DIR points to /tmp location.
3. Move the dump and log file to location /u01/backup/daily_export_orcl/
4. Change the permissions of the dumpfile so that any user can read the file.
The main thing here is to set the dumpfile name format. The following syntax is used for that:
DUMPFILE=expdp-`date ‘+%d%m%y_%H%M%S’`.dmp
The dumpfiles will be generated as expdp-23062009_090000.dmp, means that the export dump was taken on 23rd June 2009 at 9AM.
To schedule it on hourly basis crontab was modified as:
$ crontab -e
############################################################## #Script Used To Create Hourly Exports Of orcl database Schemas ############################################################### 00 09-18 * * 1-6 /u01/backup/daily_export_orcl/export.sh >/dev/null
It will taken the export at 9AM,10AM,11AM,12AM,13PM,14PM,15PM,16PM,17PM,18PM on everyday except sunday.
Cheers!!!
– Saurabh Sood
Recent Comments