There could be several bottlenecks which makes our backups slower.
Last month we had similar situation and after investigating the issue in the legato side, the number of allocated tape drives seemed us small. Because of that we had increased the number of tape drives devoted for the backup job from 2 to 4.
Interestingly, it does not make any sense.
After than, we increased the number of channels in RMAN script from 8 to 12 and in this way we managed to increase the utilization of tape drives. The real reason behind was the utilization of tape drives are dependent on utilization of disks for reading.
Further, in legato side, while monitoring the backup job, only 2 of allocated 4 tape drives are used. The minimum session limit for each tape drive was 8. That means 12 channels opened only utilizes 2 tape drives. After setting minimum session value per tape drive in legato side to 3, we are happy with the picture we got. 12 channels of backup work is evenly distibuted across the 4 tape drives.
As a result, our full backup job now lasts for 20 hours, which was 50 hours before the optimization.
Wednesday, October 26, 2011
Find Minimum size of datafiles for resize operations in TOAD
Last minute addition!
If you are not happy with the response times, you may try gathering dictionary statistics, it helped me a lot that response times decreased from minutes to seconds.
exec DBMS_STATS.GATHER_DICTIONARY_STATS;
If you had exhausted of space, and tried to find a quick way of resizing the tablespaces. You may want to use TOAD feature:
From Administer ->Tablespaces-> Select one tablespace and press F4. In the opened window click datafiles tab and double click the datafile which you want to resize. In this window there is a button "Minimum Size?", click it.
After some time of processing, it will find the minimum size of datafile it could be, it could be better than trial-error method for exact sizes.
[caption id="attachment_299" align="aligncenter" width="300" caption="Tablespace Menu Selection"]
[/caption]
[caption id="" align="aligncenter" width="300" caption="Datafile Definition Window"]
[/caption]
If you are not happy with the response times, you may try gathering dictionary statistics, it helped me a lot that response times decreased from minutes to seconds.
exec DBMS_STATS.GATHER_DICTIONARY_STATS;
If you had exhausted of space, and tried to find a quick way of resizing the tablespaces. You may want to use TOAD feature:
From Administer ->Tablespaces-> Select one tablespace and press F4. In the opened window click datafiles tab and double click the datafile which you want to resize. In this window there is a button "Minimum Size?", click it.
After some time of processing, it will find the minimum size of datafile it could be, it could be better than trial-error method for exact sizes.
[caption id="attachment_299" align="aligncenter" width="300" caption="Tablespace Menu Selection"]
[/caption][caption id="" align="aligncenter" width="300" caption="Datafile Definition Window"]
[/caption]
Spool SQL in TOAD
If you are using TOAD, and want to get the SQL in order automate tasks or simply wonder what happens behind. You may like to check this below:
In TOAD, from main menu select:
Database -> Spool SQL -> Spool SQL to Screen
[caption id="attachment_312" align="aligncenter" width="286" caption="Spool SQL Menu Selection"]
[/caption]And you will see every action is spooled in the below window.
[caption id="attachment_313" align="aligncenter" width="300" caption="Spool Output Window"]
[/caption]Hope this helps!
TNS Connection Failure Resolution
Many dbas has no single day in a week that not telling their database users about how to make TNS connection with the newly created accounts in their hands. I am providing below decision tree, to our users in our company for resolution of their TNS connection failures on their own.
Follow below decision tree for a sample TNS entry in$ORACLE_HOME/network/admin/tnsnames.ora file like below:
MYRAC=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))
Follow below decision tree for a sample TNS entry in$ORACLE_HOME/network/admin/tnsnames.ora file like below:
MYRAC=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))
- From cammand line issue: ping mydb.com
- If not work, WINS server client configuration problem !
- If works: tnsping MYRAC
i. If works, there must not be any problemJ
ii. If not work, from command line issue below command:
tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))
- If not work: Oracle client configuration/installation problem ! Or you are editing wrong tnsnames.ora file!
- If works: Check $ORACLE_HOME/network/admin/sqlnet.ora file. Note the
NAMES.DEFAULT_DOMAINparameter value if other than null and place it to your $ORACLE_HOME/network/admin/tnsnames.ora:
MYRAC.<names default domain>=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME= MYSERVICE)))
JDBC connect string
While trying to connect to Oracle via Oracle service name provided to you.
Then you could test the connection by:
tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYSERVICE)))
And then used this information int the connect string as below:
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYSERVICE)))
Note that, this is service name using issue, if you have chance to use SID name like INST1, old fashion connection string use will not be a problem, like below:
Then you could test the connection by:
tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYSERVICE)))
And then used this information int the connect string as below:
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYSERVICE)))
Note that, this is service name using issue, if you have chance to use SID name like INST1, old fashion connection string use will not be a problem, like below:
jdbc:oracle:thin:@mydb.com:1521:INST1
Tuesday, October 18, 2011
Which view for NLS Parameters
Sometimes it could be confusing, where to look at for NLS parameters.
There are 3 views for NLS parameters which could be used for achieving information about database characterset, language, territory and date parameters.
Characterset from NLS_DATABASE_PARAMETERS view, Language, territory, date from NLS_SESSION_PARAMETERS. I did not use NLS_INSTANCE_PARAMETERS view much, but may help while looking for RAC instance parameters.
There are 3 views for NLS parameters which could be used for achieving information about database characterset, language, territory and date parameters.
Characterset from NLS_DATABASE_PARAMETERS view, Language, territory, date from NLS_SESSION_PARAMETERS. I did not use NLS_INSTANCE_PARAMETERS view much, but may help while looking for RAC instance parameters.
SQL> select * from NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P9
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.1.0
20 rows selected.
SQL> select * from NLS_INSTANCE_PARAMETERS;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
SQL> select * from NLS_SESSION_PARAMETERS;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY TURKEY
NLS_CURRENCY TL
NLS_ISO_CURRENCY TURKEY
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-YYYY HH24:MI:SS
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD-MM-YYYY HH24:MI:SS.FF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RRRR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY YTL
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
Add and drop disks concurrently to a diskgroup in Oracle ASM, as to gain hours by one time rebalance
Adding a disk to a disk group needs rebalancing the data across newly formed diskgroup consisting newer disks. Dropping also needs similar rebalancing across diskgroup as you guess.
If you need to add and drop disks at the same time, then this trick is what you are looking for or bumped into.
As you could understood from below real world example SQL, the dgdata diskgroup is changed by adding 13 disks from new cabinet, and dropping 13 disks from old cabinet. And it worked smothly, thus we gained at least 15 hours from one time rebalancing instead of two times rebalancing. Also it worths noting that no intervention is needed, because you need to execute one SQL instead of 2.
If you need to add and drop disks at the same time, then this trick is what you are looking for or bumped into.
As you could understood from below real world example SQL, the dgdata diskgroup is changed by adding 13 disks from new cabinet, and dropping 13 disks from old cabinet. And it worked smothly, thus we gained at least 15 hours from one time rebalancing instead of two times rebalancing. Also it worths noting that no intervention is needed, because you need to execute one SQL instead of 2.
-- Concurrently execute add/drop disk in order gain from rebalance time.
ALTER DISKGROUP DATADG
ADD DISK
'/dev/ORACLE/ASMDISK/DATA7' NAME DATA_0007,
'/dev/ORACLE/ASMDISK/DATA8' NAME DATA_0008,
'/dev/ORACLE/ASMDISK/DATA9' NAME DATA_0009,
'/dev/ORACLE/ASMDISK/DATA10' NAME DATA_0010,
'/dev/ORACLE/ASMDISK/DATA11' NAME DATA_0011,
'/dev/ORACLE/ASMDISK/DATA12' NAME DATA_0012,
'/dev/ORACLE/ASMDISK/DATA13' NAME DATA_0013,
'/dev/ORACLE/ASMDISK/DATA14' NAME DATA_0014,
'/dev/ORACLE/ASMDISK/DATA15' NAME DATA_0015,
'/dev/ORACLE/ASMDISK/DATA16' NAME DATA_0016,
'/dev/ORACLE/ASMDISK/DATA17' NAME DATA_0017,
'/dev/ORACLE/ASMDISK/DATA18' NAME DATA_0018,
'/dev/ORACLE/ASMDISK/DATA19' NAME DATA_0019
DROP DISK
DATADG_0000,
DATADG_0001,
DATADG_0002,
DATADG_0003,
DATADG_0004,
DATADG_0005,
DATADG_0006,
DATADG_0007,
DATADG_0008,
DATADG_0010,
DATADG_0011,
DATADG_0012
REBALANCE POWER 5;
Subscribe to:
Posts (Atom)
Windows (powershell) counterparts of Linux commands
You may find Windows mostly powershell equivalent of frequently used Linux commands here. I will update this post, with newer ones by the ti...
-
Simple but very handy way of taking exports without knowing database password. expdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFIL...
-
Problem: I cannot see Oracle ODBC driver in datasource administrator in Windows 2003 64 bit. Cause: 64 bit 32 bit incompatibility issue for ...
-
What is consolidation? At first, it seems a process which enables making things with smaller number of resources. Below picture that depicts...
