Reference
http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1252556_tax306964,00.html
1) Always test upgrade procedure and post-upgrade installation on a test environment
2) Use separate ORACLE_HOME and install existing version temporarily (with temporarily modification of oracleinventory directory loc in oraInst.loc file). With a brief outage change point the running db to this new home. Now install back the binaries in original home & edit back the oraInst.loc. While the db still running using another home, install the patch on this binary. Take a brief outage start the database with this upgraded installation
3) When going for OS upgrade, if the Oracle version is not supported on the upgraded OS, first upgrade Oracle to that supported version. Then upgrade OS. Ensure the binary links are recreated.
4) Set AUDIT_TRAIL = DB
5) Create separate user for client install.
Tuesday, March 18, 2008
Monday, March 17, 2008
RMAN commands - Examples
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE ARCHIVELOG DELETION POLICY TO
BACKED UP 2 TIMES
TO DEVICE TYPE sbt;
The following DELETE command deletes all archived redo logs on disk if they are not needed to meet the configured deletion policy, which specifies that logs must be backed up twice to tape (sample output included):
RMAN> DELETE ARCHIVELOG ALL;
---------
This example deletes backups and copies that are not needed to recover the database to an arbitrary SCN within the last week. RMAN also deletes archived redo logs that are no longer needed.
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
---------
This example uses a configured sbt channel to check the media manager for expired backups of the tablespace users that are more than one month old and removes their recovery catalog records.
CROSSCHECK BACKUPSET OF TABLESPACE users
DEVICE TYPE sbt COMPLETED BEFORE 'SYSDATE-31';
DELETE NOPROMPT EXPIRED BACKUPSET OF TABLESPACE users
DEVICE TYPE sbt COMPLETED BEFORE 'SYSDATE-31';
---------
The following example attempts to delete the backup set copy with tag weekly_bkup:
RMAN> DELETE NOPROMPT BACKUPSET TAG weekly_bkup;
RMAN displays a warning because the repository shows the backup set as available, but the object is not actually available on the media:
The following command forces RMAN to delete the backup set:
RMAN> DELETE FORCE NOPROMPT BACKUPSET TAG weekly_bkup;
CONFIGURE ARCHIVELOG DELETION POLICY TO
BACKED UP 2 TIMES
TO DEVICE TYPE sbt;
The following DELETE command deletes all archived redo logs on disk if they are not needed to meet the configured deletion policy, which specifies that logs must be backed up twice to tape (sample output included):
RMAN> DELETE ARCHIVELOG ALL;
---------
This example deletes backups and copies that are not needed to recover the database to an arbitrary SCN within the last week. RMAN also deletes archived redo logs that are no longer needed.
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
---------
This example uses a configured sbt channel to check the media manager for expired backups of the tablespace users that are more than one month old and removes their recovery catalog records.
CROSSCHECK BACKUPSET OF TABLESPACE users
DEVICE TYPE sbt COMPLETED BEFORE 'SYSDATE-31';
DELETE NOPROMPT EXPIRED BACKUPSET OF TABLESPACE users
DEVICE TYPE sbt COMPLETED BEFORE 'SYSDATE-31';
---------
The following example attempts to delete the backup set copy with tag weekly_bkup:
RMAN> DELETE NOPROMPT BACKUPSET TAG weekly_bkup;
RMAN displays a warning because the repository shows the backup set as available, but the object is not actually available on the media:
The following command forces RMAN to delete the backup set:
RMAN> DELETE FORCE NOPROMPT BACKUPSET TAG weekly_bkup;
RMAN Scripts - Creating and running from Catalog Database
CONNECT TARGET SYS/password@prod
CONNECT CATALOG rman/password@catdb
CREATE SCRIPT backup_whole
COMMENT "backup whole database and archived redo logs"
{
BACKUP
INCREMENTAL LEVEL 0 TAG backup_whole
FORMAT "/disk2/backup/%U"
DATABASE PLUS ARCHIVELOG;
}
RUN { EXECUTE SCRIPT backup_whole; }
For scripts using substitution varialbles:
CREATE SCRIPT backup_df { BACKUP DATAFILE &1 TAG &2.1 FORMAT '/disk1/&3_%U'; }
RUN { EXECUTE SCRIPT backup_df USING 3 test_backup df3; }
-------------
RMAN> LIST SCRIPT NAMES;
List of Stored Scripts in Recovery Catalog
RMAN> DELETE GLOBAL SCRIPT global_backup_db;
CONNECT CATALOG rman/password@catdb
CREATE SCRIPT backup_whole
COMMENT "backup whole database and archived redo logs"
{
BACKUP
INCREMENTAL LEVEL 0 TAG backup_whole
FORMAT "/disk2/backup/%U"
DATABASE PLUS ARCHIVELOG;
}
RUN { EXECUTE SCRIPT backup_whole; }
For scripts using substitution varialbles:
CREATE SCRIPT backup_df { BACKUP DATAFILE &1 TAG &2.1 FORMAT '/disk1/&3_%U'; }
RUN { EXECUTE SCRIPT backup_df USING 3 test_backup df3; }
-------------
RMAN> LIST SCRIPT NAMES;
List of Stored Scripts in Recovery Catalog
RMAN> DELETE GLOBAL SCRIPT global_backup_db;
RMAN Recovery Catalog Database creation
SQL> CONNECT SYS/password@catdb AS SYSDBA
SQL> CREATE USER catowner IDENTIFIED BY oracle
2 DEFAULT TABLESPACE cattbs
3 QUOTA UNLIMITED ON cattbs;
SQL> GRANT recovery_catalog_owner TO catowner;
SQL> EXIT
RMAN> CONNECT CATALOG catowner/password@catdb
RMAN> CREATE CATALOG;
RMAN> CONNECT TARGET SYS/password@prod1
RMAN> REGISTER DATABASE; ---this will sync all already existing RMAN data in Target db's control files with the catalog database
RMAN> EXIT
SQL> CREATE USER catowner IDENTIFIED BY oracle
2 DEFAULT TABLESPACE cattbs
3 QUOTA UNLIMITED ON cattbs;
SQL> GRANT recovery_catalog_owner TO catowner;
SQL> EXIT
RMAN> CONNECT CATALOG catowner/password@catdb
RMAN> CREATE CATALOG;
RMAN> CONNECT TARGET SYS/password@prod1
RMAN> REGISTER DATABASE; ---this will sync all already existing RMAN data in Target db's control files with the catalog database
RMAN> EXIT
Dataguard - Physical Standby Role Transitions : Switchover & Failover
Refer to previous posting creating-dataguard-physical-standby-in.html
Now for conducting switchover to secondary so that some maintenance may be carriedout on Primary follow the following steps
1) Ensure temporary files exist on the standby database that match the temporary files on the primary database
2) Chech the switchover status of both Primary and Standby using SELECT SWITCHOVER_STATUS FROM V$DATABASE;
3)Check the lags on all standbys using SELECT * FROM V$DATAGUARD_STATS; and choose the one which will require the minimum time for transition
4) If a standby database currently running in maximum protection mode will be involved in the failover, first place it in maximum performance mode by issuing the following statement on the standby database:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
5) Primary : ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
6) Primary : SHUTDOWN IMMEDIATE;
7) Primary : STARTUP MOUNT; ---Now the former primary has become a standby
8) Standby : ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
9) Standby : ALTER DATABASE OPEN; if it was ever opened in readonly mode then SHUTDOWN IMMEDIATE & STARTUP
10)Check the log transport and apply by ALTER SYSTEM SWITCH LOGFILE; in new primary
The following steps are to be used for failover in case of problem in Primary database
1)check the archive log gaps SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
2)The archive logs missing in the standby may be physically copied in standby file system and are to be registered using ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1'; Repeat until the above query returns no rows.
3)Initiate failover in standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
4)Finish failover using ALTER DATABASE OPEN; if it was ever opened in readonly mode then SHUTDOWN IMMEDIATE & STARTUP
The target physical standby database has now undergone a transition to the primary database role while the previous Primary is no more a participant of Dataguard. It needs to be recreated from the current primary.
Now for conducting switchover to secondary so that some maintenance may be carriedout on Primary follow the following steps
1) Ensure temporary files exist on the standby database that match the temporary files on the primary database
2) Chech the switchover status of both Primary and Standby using SELECT SWITCHOVER_STATUS FROM V$DATABASE;
3)Check the lags on all standbys using SELECT * FROM V$DATAGUARD_STATS; and choose the one which will require the minimum time for transition
4) If a standby database currently running in maximum protection mode will be involved in the failover, first place it in maximum performance mode by issuing the following statement on the standby database:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
5) Primary : ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
6) Primary : SHUTDOWN IMMEDIATE;
7) Primary : STARTUP MOUNT; ---Now the former primary has become a standby
8) Standby : ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
9) Standby : ALTER DATABASE OPEN; if it was ever opened in readonly mode then SHUTDOWN IMMEDIATE & STARTUP
10)Check the log transport and apply by ALTER SYSTEM SWITCH LOGFILE; in new primary
The following steps are to be used for failover in case of problem in Primary database
1)check the archive log gaps SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
2)The archive logs missing in the standby may be physically copied in standby file system and are to be registered using ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1'; Repeat until the above query returns no rows.
3)Initiate failover in standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
4)Finish failover using ALTER DATABASE OPEN; if it was ever opened in readonly mode then SHUTDOWN IMMEDIATE & STARTUP
The target physical standby database has now undergone a transition to the primary database role while the previous Primary is no more a participant of Dataguard. It needs to be recreated from the current primary.
Thursday, March 13, 2008
Renaming / Moving Data Files, Control Files, and Online Redo Logs
http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_35.shtml
Only applies to datafiles whose tablespaces do not include SYSTEM, ROLLBACK or TEMPORARY segments
For renaming controlfiles,
1) Edit the control_files parameter in the init.ora file (create pfile from spfile; if running in spfile mode)
or : alter system set control_files='/oracle/control1.ctl','/oracle2/control2.ctl' scope=spfile;
2) Shutdown instance
3) OS move of controlfiles as required
4) Startup using pfile
STARTUP PFILE=C:\Oracle\Admin\SID\PFile\init.ora;
Only applies to datafiles whose tablespaces do not include SYSTEM, ROLLBACK or TEMPORARY segments
SQL> shutdown immediate
or SQL> alter tablespace INDX offline;
SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf
SQL> startup mount - if shutdown immediate was done
For both datafiles and redolog files:
SQL> alter database rename file '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';
Do not disconnect after this step. Stay logged in
and proceed to open the database!
SQL> alter database open;
SQL> exit
For renaming controlfiles,
1) Edit the control_files parameter in the init.ora file (create pfile from spfile; if running in spfile mode)
or : alter system set control_files='/oracle/control1.ctl','/oracle2/control2.ctl' scope=spfile;
2) Shutdown instance
3) OS move of controlfiles as required
4) Startup using pfile
STARTUP PFILE=C:\Oracle\Admin\SID\PFile\init.ora;
Tuesday, March 11, 2008
ROWID format
ROWID format: block ID, row in block, file ID.
ROWIDs must be entered as formatted hexadecimal strings using only numbers and the characters A through F. A typical ROWID format is '000001F8.0001.0006'.
ROWIDs must be entered as formatted hexadecimal strings using only numbers and the characters A through F. A typical ROWID format is '000001F8.0001.0006'.
Monday, March 10, 2008
Unix Commands - Syntax
Some basic unix command usage examples
#!/bin/ksh
$ find /u02/ora/archives -type f -mtime +7 -name \*.dbf -print -exec rm {} \;
Example usages link
To delete ALL files owned by a user:
$ find . -user USERID awk '{print "rm "$NF}' ksh
'find' command links
To Copy files matching certain criteria to another directory (just to practice grep, printf and awk features...)
$ ls -altr grep "Jul 3" grep -v ^d awk '{printf("cp %s ./new_dir/%s\n",$9,$9)}' sh
$ for mystatement in `cat myfile.dat awk '{print $1}'`; <<EOF!
do sqlplus / as sysadmin mystatement ;
EOF!
$ chown oracle:dba * : to change owner and group of all files in current dir
$ ps -ef grep 234 awk '{print $2}' xargs kill
$ ps -ef egrep 'pmonsmonlgwrdbwarc' grep -v grep
$ ls -altr
$ ptrace [processid]
$ pfiles [processid]
$ ptree [processid]
$ truss -p [processid]
$ top
$ sort -t: +2n -3 /etc/passwd
-t : separator
+2n : begin - nth occurance of separator
-3 : end
$ who sort +4n
$ vmstat interval 6
$ mpstat - for multiprocessor
$ sar
$ iostat
$ uname -a
$ at 8:15
$ uucp
$ du -h : Disk usage
$ bdf . : to find the disk free space on a particular mount volume (in %)
$ df : to find the disk free space on a particular mount volume
$ tail -100 filename.ext
$ tnsping INSTNAME
$ which top
$ bc : calculating arithmatics from command line
$ id -a : user id and group id
$ su - : switch to root user i.e. superuser
$ tar -cvvf tarfile.tar *.* for creating tarfile
$ tar -xvvf tarfile.tar for extracting from existing tarfile
$ tar -xvvzf tarfile.tar.gz for uncompress
$ rcp -r user@sourcehostname:sourcefileordir user@desthostname:destfileordir
$ gzip filename for compressing the file replaces the file with filename.gz
$ gzip -dvf zipfile.gz for uncompressing a .gz file
Useful link : Shell Programming
To delete files (with extension .dbf) in a folder which are older than 7 days. This may be put inside a shell script for executing as a cron job
#!/bin/ksh
$ find /u02/ora/archives -type f -mtime +7 -name \*.dbf -print -exec rm {} \;
Example usages link
To delete ALL files owned by a user:
$ find . -user USERID awk '{print "rm "$NF}' ksh
'find' command links
To Copy files matching certain criteria to another directory (just to practice grep, printf and awk features...)
$ ls -altr grep "Jul 3" grep -v ^d awk '{printf("cp %s ./new_dir/%s\n",$9,$9)}' sh
$ for mystatement in `cat myfile.dat awk '{print $1}'`; <<EOF!
do sqlplus / as sysadmin mystatement ;
EOF!
$ chown oracle:dba * : to change owner and group of all files in current dir
$ ps -ef grep 234 awk '{print $2}' xargs kill
$ ps -ef egrep 'pmonsmonlgwrdbwarc' grep -v grep
$ ls -altr
$ ptrace [processid]
$ pfiles [processid]
$ ptree [processid]
$ truss -p [processid]
$ top
$ sort -t: +2n -3 /etc/passwd
-t : separator
+2n : begin - nth occurance of separator
-3 : end
$ who sort +4n
$ vmstat interval 6
$ mpstat - for multiprocessor
$ sar
$ iostat
$ uname -a
$ at 8:15
$ uucp
$ du -h : Disk usage
$ bdf . : to find the disk free space on a particular mount volume (in %)
$ df : to find the disk free space on a particular mount volume
$ tail -100 filename.ext
$ tnsping INSTNAME
$ which top
$ bc : calculating arithmatics from command line
$ id -a : user id and group id
$ su - : switch to root user i.e. superuser
$ tar -cvvf tarfile.tar *.* for creating tarfile
$ tar -xvvf tarfile.tar for extracting from existing tarfile
$ tar -xvvzf tarfile.tar.gz for uncompress
$ rcp -r user@sourcehostname:sourcefileordir user@desthostname:destfileordir
$ gzip filename for compressing the file replaces the file with filename.gz
$ gzip -dvf zipfile.gz for uncompressing a .gz file
Useful link : Shell Programming
Saturday, March 8, 2008
DATAGUARD - Physical Standby on same machine
Creating Dataguard Physical Standby (in the same machine)
1) Primary - Enable Force Logging
2) Primary - Ensure Archivelog mode
3) Primary - Make pfile from spfile
4) Primary - Incorporate Changes in pfile
5) Primary - Create Standby Redo file Group & files (check sufficient MAXLOGFILES and MAXLOGMEMBERS values)
6) Primary - Create password file (if not there)
7) listener.ora - Add entries for additional listener
8) tnsnames.ora - Add entries for additional services
9) sqlnet.ora - Include SQLNET.EXPIRE_TIME=2 DEFAULT_SDU_SIZE=32767
10) Primary - Shutdown immediate (for backup) or use RMAN online backup copy
11) Primary - Backup & Copy all datafiles to Standby file system location
12) Primary - Create a Backup Controlfile for use with the standby and copy the file to standby
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
13) Primary - Open database
SQL> ALTER DATABASE OPEN;
14) Standby - Copy the password file (or create a new one with same sys password as in Primary) and pfile from primary
15) Standby - Edit the pfile and change db_unique_name, control_files, log_archive_dest_i, db_filename_convert, log_filename_convert, FAL_server, FAL_Client
16) Standby - Startup mount
17)
Recovery Steps
Fault Tolerencing
http://www.itk.ilstu.edu/docs/oracle/server.101/b10726/appconfig.htm#g635861
1) Primary - Enable Force Logging
2) Primary - Ensure Archivelog mode
3) Primary - Make pfile from spfile
4) Primary - Incorporate Changes in pfile
5) Primary - Create Standby Redo file Group & files (check sufficient MAXLOGFILES and MAXLOGMEMBERS values)
6) Primary - Create password file (if not there)
7) listener.ora - Add entries for additional listener
8) tnsnames.ora - Add entries for additional services
9) sqlnet.ora - Include SQLNET.EXPIRE_TIME=2 DEFAULT_SDU_SIZE=32767
10) Primary - Shutdown immediate (for backup) or use RMAN online backup copy
11) Primary - Backup & Copy all datafiles to Standby file system location
12) Primary - Create a Backup Controlfile for use with the standby and copy the file to standby
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
13) Primary - Open database
SQL> ALTER DATABASE OPEN;
14) Standby - Copy the password file (or create a new one with same sys password as in Primary) and pfile from primary
15) Standby - Edit the pfile and change db_unique_name, control_files, log_archive_dest_i, db_filename_convert, log_filename_convert, FAL_server, FAL_Client
16) Standby - Startup mount
17)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
--
Check the log archiving and transfer by running the following on both Primary & Standby after executing ALTER SYSTEM SWITCH LOGFILE; in Primary
--
SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;
--
SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
--
SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#
FROM V$ARCHIVE_DEST_STATUS
WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';
--
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)
LOCAL WHERE
LOCAL.SEQUENCE# NOT IN
(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
THREAD# = LOCAL.THREAD#);
Recovery Steps
Fault Tolerencing
http://www.itk.ilstu.edu/docs/oracle/server.101/b10726/appconfig.htm#g635861
Wednesday, March 5, 2008
Compressing and splitting large exports in Unix
Mention in the parfile for the export as (pipe)
file=compress_pipe
Create pipes, one for compression and another for splitting :
rm –f compress_pipe
rm –f spilt_pipe
mknod compress_pipe p
mknod split_pipe p
chmod g+w compress_pipe
chmod g+w split_pipe
split –b500m < split_pipe > /tmp/exp_tab &
compress < compress_pipe > split_pipe &
exp parfile=export_tab.par file=compress_pipe > exp_tab.list 2>&1 &
Execute the reverse for import :
cat xaa xab xac xad > join_pipe &
uncompress –c join_pipe > uncompress_pipe &
imp file=uncompress_pipe
file=compress_pipe
Create pipes, one for compression and another for splitting :
rm –f compress_pipe
rm –f spilt_pipe
mknod compress_pipe p
mknod split_pipe p
chmod g+w compress_pipe
chmod g+w split_pipe
split –b500m < split_pipe > /tmp/exp_tab &
compress < compress_pipe > split_pipe &
exp parfile=export_tab.par file=compress_pipe > exp_tab.list 2>&1 &
Execute the reverse for import :
cat xaa xab xac xad > join_pipe &
uncompress –c join_pipe > uncompress_pipe &
imp file=uncompress_pipe
Migration from one OS Platform to another (with different endian formats)
A transportable tablespace allows you to quickly move a subset of an Oracle database from one Oracle database to another. Beginning with the Oracle10g database, a tablespace can always be transported to a database with the same or higher compatibility setting, whether the
target database is on the same or a different platform.
First check whether the tablespace is self contained i.e. all dependancies are within the ts itself.
Find the Oracle internal name and endian for the host & target OS using
Copy the converted datafiles (binary FTP) research_file.dbf to the target machine
Use the Export utility to create the file of metadata information after making the tablespace ReadOnly
The metadata information file should be moved from its location and the converted datafiles in directory as in FORMAT to their respective target directories on the destination
Then plug the tablespace(s) into the new database with the Import utility
If the busy OLTP source database cannot spare processor overhead for the conversion, the conversion may be carriedout at the Target database using the following:
Note: CONVERT DATABASE may be used on read only database as target only if the endian of the destination database and the source are the same.
Using External Tables, in 10g fast transport of selective column data (to Datawarehouses) is possible
The files generated are readable across platform (like export dump)
Further readings on Transportable tablespaces
http://www.rampant-books.com/art_otn_transportable_tablespace_tricks.htm
target database is on the same or a different platform.
First check whether the tablespace is self contained i.e. all dependancies are within the ts itself.
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('research, another_ts', TRUE);
SQL> SELECT * FROM transport_set_violations;
Find the Oracle internal name and endian for the host & target OS using
SQL> select name, platform_id,platform_name from v$database;
SQL> SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM;
The following CONVERT to PLATFORM is required only if the Host & target OS are of different ENDIANS
% rman TARGET /
RMAN> CONVERT TABLESPACE research
2> TO PLATFORM 'Microsoft Windows NT'
3> FORMAT='/tmp/oracle/transport_windows/%U'
4> PARALLELISM = 4;
To preserve the filenames from host use the following instead of format:
db_file_name_convert '/usr/oradata/dw10/dw10','/home/oracle/rman_bkups'
Copy the converted datafiles (binary FTP) research_file.dbf to the target machine
Use the Export utility to create the file of metadata information after making the tablespace ReadOnly
alter tablespace research read only;
exp tablespaces=research transport_tablespace=y file=exp_ts_research.dmp
: if DATAPUMP is to be used
expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_ts_research.dmp \
LOGFILE=exp_ts_research.log TRANSPORT_TABLESPACES=research, another_ts \
TRANSPORT_FULL_CHECK=Y
The metadata information file should be moved from its location and the converted datafiles in directory as in FORMAT to their respective target directories on the destination
Then plug the tablespace(s) into the new database with the Import utility
imp tablespaces=research transport_tablespace=y file=exp_ts_research.dmp datafiles='research_file.dbf'
: importing Using datapump
impdp system/manager DIRECTORY=my_dir2 DUMPFILE=exp_ts_research.dmp
LOGFILE=imp_tbsp.log TRANSPORT_DATAFILES=\('research_file.dbf',
'another_ts_file.dbf'\) REMAP_SCHEMA=\(np:np\)
TRANSPORT_FULL_CHECK=Y
If the busy OLTP source database cannot spare processor overhead for the conversion, the conversion may be carriedout at the Target database using the following:
RMAN> convertYou can only use CONVERT DATAFILE when connected as TARGET to the destination database and converting datafile copies on the destination platform
2> datafile '/usr/oradata/dw10/dw10/users01.dbf'
3> format '/home/oracle/rman_bkups/%N_%f';
Note: CONVERT DATABASE may be used on read only database as target only if the endian of the destination database and the source are the same.
Using External Tables, in 10g fast transport of selective column data (to Datawarehouses) is possible
create table trans_dump
organization external
(
type oracle_datapump
default directory dump_dir
location ('trans_dump_1.dmp','trans_dump_2.dmp')
)
parallel 4
as
select * from trans
/
The files generated are readable across platform (like export dump)
Further readings on Transportable tablespaces
http://www.rampant-books.com/art_otn_transportable_tablespace_tricks.htm
Index becoming invalid
Whenever a DBA task shifts the ROWID values
1) Table partition maintenance - Alter commands (move, split or truncate partition) will shift ROWID's, making the index invalid and unusable.
2) CTAS maintenance - Table reorganization with "alter table move" or an online table reorganization (using the dbms_redefinition package) will shift ROWIDs, creating unusable indexes.
3) Oracle imports - An Oracle import (imp utility) with the skip_unusable_indexes=y parameter
4) SQL*Loader (sqlldr utility) - Using direct path loads (e.g. skip_index_maintenance) will cause invalid and unusable indexes.
ALTER INDEX myindex REBUILD ONLINE TABLESPACE newtablespace;
1) Table partition maintenance - Alter commands (move, split or truncate partition) will shift ROWID's, making the index invalid and unusable.
2) CTAS maintenance - Table reorganization with "alter table move" or an online table reorganization (using the dbms_redefinition package) will shift ROWIDs, creating unusable indexes.
3) Oracle imports - An Oracle import (imp utility) with the skip_unusable_indexes=y parameter
4) SQL*Loader (sqlldr utility) - Using direct path loads (e.g. skip_index_maintenance) will cause invalid and unusable indexes.
ALTER INDEX myindex REBUILD ONLINE TABLESPACE newtablespace;
Thursday, February 28, 2008
SQL Execution Plan
Optimizer creates the execution plan as per statistics available on database objects
Different types of internal joins
- nested loop joins (Both Small tables & Optimizer mode: First Rows)
- hash joins (Big Tables, Join type: Equality, Optimizer mode: All Rows)
- sort merge join (Big Tables, Join type: Inequality, Optimizer mode: All Rows)
- star transformation joins
The statements with the maximum level will be first executed and returned for the next level statement to work on it.
DBMS_STATS for CBO - replaced ANALYZE
Nice article explaining the transition to DBMS_STATS for CBO from old ANALYZE
http://www.dba-oracle.com/oracle_tips_dbms_stats1.htm
DBMS_STATS.GATHER_TABLE_STATS
Options: (for gather_schema_stats)
gather —Reanalyzes the whole schema
gather empty —Only analyzes tables that have no existing statistics
gather stale —Only reanalyzes tables with more than 10% modifications (inserts, updates, deletes).
gather auto —Reanalyzes objects which currently have no statistics and objects with stale statistics
method_opt:
'for all indexed columns size skewonly' - examines the distribution of values for every column within every index for histogram
'for all columns size repeat' - only reanalyze indexes with existing histograms
'for columns size auto' - used when invoked using alter table xxx monitoring; data distribution and the access manner / load on column
DBMS_STATS.GATHER_SYSTEM_STATS
execute dbms_stats.gather_system_stats('Start');
-- one hour delay during high workload
execute dbms_stats.gather_system_stats('Stop');
The statistics collected are (sys.aux_stats$)
No Workload (NW) stats:
CPUSPEEDNW - CPU speed
IOSEEKTIM - The I/O seek time in milliseconds
IOTFRSPEED - I/O transfer speed in milliseconds
Workload-related stats:
SREADTIM - Single block read time in milliseconds
MREADTIM - Multiblock read time in ms
CPUSPEED - CPU speed
MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
MAXTHR - Maximum I/O throughput (for OPQ only)
SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)
Statistics collected during different workloads may be stored and used for influencing the optimizer plan as per load times
-------------------------------------------------------------------
http://www.dba-oracle.com/oracle_tips_dbms_stats1.htm
DBMS_STATS.GATHER_TABLE_STATS
exec dbms_stats.gather_table_stats( -
ownname => 'PERFSTAT', -
tabname => ’STATS$SNAPSHOT’ -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size skewonly', -
cascade => true, -
degree => 7 -
)
Options: (for gather_schema_stats)
gather —Reanalyzes the whole schema
gather empty —Only analyzes tables that have no existing statistics
gather stale —Only reanalyzes tables with more than 10% modifications (inserts, updates, deletes).
gather auto —Reanalyzes objects which currently have no statistics and objects with stale statistics
method_opt:
'for all indexed columns size skewonly' - examines the distribution of values for every column within every index for histogram
'for all columns size repeat' - only reanalyze indexes with existing histograms
'for columns size auto' - used when invoked using alter table xxx monitoring; data distribution and the access manner / load on column
DBMS_STATS.GATHER_SYSTEM_STATS
execute dbms_stats.gather_system_stats('Start');
-- one hour delay during high workload
execute dbms_stats.gather_system_stats('Stop');
The statistics collected are (sys.aux_stats$)
No Workload (NW) stats:
CPUSPEEDNW - CPU speed
IOSEEKTIM - The I/O seek time in milliseconds
IOTFRSPEED - I/O transfer speed in milliseconds
Workload-related stats:
SREADTIM - Single block read time in milliseconds
MREADTIM - Multiblock read time in ms
CPUSPEED - CPU speed
MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
MAXTHR - Maximum I/O throughput (for OPQ only)
SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)
Statistics collected during different workloads may be stored and used for influencing the optimizer plan as per load times
/* e.g. activate the DAY statistics each day at 7:00 am */
DECLARE
I NUMBER;
BEGIN
DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s
tatown => ''SYSTEM'', statid => ''DAY'');', trunc(sysdate) + 1 + 7/24, 'sysdate + 1');
END;
/
/* e.g. activate the NIGHT statistics each day at 9:00 pm */
DECLARE
I NUMBER;
BEGIN
DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s
tatown => ''SYSTEM'', statid => ''NIGHT'');', trunc(sysdate) + 1 + 21/24, 'sysdate + 1');
END;
/
*** ********************************************************
*** Initialize the OLTP System Statistics for the CBO
*** ********************************************************
1. Delete any existing system statistics from dictionary:
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS;
PL/SQL procedure successfully completed.
2. Transfer the OLTP statistics from OLTP_STATS table to the dictionary tables:
SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => 'OLTP_stats', statid => 'OLTP', statown => 'SYS');
PL/SQL procedure successfully completed.
3. All system statistics are now visible in the data dictionary table:
SQL> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- ------------------ ---------- --------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-09-2001 16:40
SYSSTATS_INFO DSTOP 08-09-2001 16:42
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN SREADTIM 7.581
SYSSTATS_MAIN MREADTIM 56.842
SYSSTATS_MAIN CPUSPEED 117
SYSSTATS_MAIN MBRC 9
where
=> sreadtim : wait time to read single block, in milliseconds
=> mreadtim : wait time to read a multiblock, in milliseconds
=> cpuspeed : cycles per second, in millions
-------------------------------------------------------------------
SQL> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> select * from t1 where owner='PUBLIC';
OWNER OBJECT_NAME
------------------------------ ------------------------------
PUBLIC DUAL
PUBLIC SYSTEM_PRIVILEGE_MAP
PUBLIC TABLE_PRIVILEGE_MAP
PUBLIC STMT_AUDIT_OPTION_MAP
PUBLIC MAP_OBJECT
PUBLIC DBMS_STANDARD
PUBLIC V$MAP_LIBRARY
...
2765 rows selected.
SQL> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> select /*+index(t2 inx_t2) */ * from t2 where owner='PUBLIC';
OWNER OBJECT_NAME
------------------------------ ------------------------------
PUBLIC DUAL
PUBLIC SYSTEM_PRIVILEGE_MAP
PUBLIC TABLE_PRIVILEGE_MAP
PUBLIC STMT_AUDIT_OPTION_MAP
PUBLIC MAP_OBJECT
PUBLIC DBMS_STANDARD
PUBLIC V$MAP_LIBRARY
...
2765 rows selected.
SQL> exec runStats_pkg.rs_stop;
Run1 ran in 304 hsecs
Run2 ran in 331 hsecs
run 1 ran in 91,84% of the time
Name Run1 Run2 Diff
STAT...table scans (short tabl 1 0 -1
LATCH.resmgr:schema config 0 1 1
LATCH.job_queue_processes para 0 1 1
LATCH.undo global data 7 6 -1
LATCH.resmgr:actses active lis 0 1 1
STAT...shared hash latch upgra 0 1 1
STAT...session cursor cache co 1 0 -1
STAT...heap block compress 6 7 1
STAT...index scans kdiixs1 0 1 1
LATCH.In memory undo latch 0 2 2
LATCH.library cache lock 4 6 2
LATCH.redo allocation 13 16 3
STAT...CPU used by this sessio 15 12 -3
STAT...calls to get snapshot s 5 2 -3
STAT...active txn count during 4 8 4
STAT...cleanout - number of kt 4 8 4
STAT...redo entries 9 13 4
STAT...calls to kcmgcs 4 8 4
LATCH.messages 18 22 4
STAT...consistent gets - exami 4 9 5
LATCH.library cache pin 38 44 6
STAT...recursive cpu usage 13 7 -6
LATCH.library cache 48 55 7
LATCH.active service list 2 10 8
STAT...db block gets 17 29 12
STAT...db block gets from cach 17 29 12
STAT...consistent changes 17 29 12
STAT...db block changes 26 42 16
STAT...bytes received via SQL* 3,188 3,209 21
STAT...DB time 36 15 -21
STAT...CPU used when call star 34 12 -22
STAT...Elapsed Time 311 335 24
LATCH.simulator hash latch 11 44 33
LATCH.simulator lru latch 11 44 33
LATCH.JS queue state obj latch 0 36 36
LATCH.enqueues 2 78 76
LATCH.enqueue hash chains 2 79 77
STAT...no work - consistent re 206 397 191
STAT...consistent gets 218 415 197
STAT...consistent gets from ca 218 415 197
STAT...table scan blocks gotte 206 0 -206
STAT...session logical reads 235 444 209
STAT...undo change vector size 2,132 2,420 288
LATCH.cache buffers chains 550 933 383
STAT...buffer is not pinned co 0 576 576
STAT...redo size 2,780 3,400 620
STAT...table fetch by rowid 0 2,765 2,765
STAT...buffer is pinned count 0 5,139 5,139
STAT...table scan rows gotten 51,831 0 -51,831
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,290 1,962 672 65.75%
PL/SQL procedure successfully completed.
Wednesday, February 27, 2008
ASH - Active Session History
Unlike AWR, the collection of session performance statistics is in-memory every second.
Can be queried from V$ACTIVE_SESSION_HISTORY view
MMON flushes the ASH buffer to AWR DBA_HIST_ACTIVE_SESS_HISTORY
To find out how many sessions waited for some event,
Can be queried from V$ACTIVE_SESSION_HISTORY view
MMON flushes the ASH buffer to AWR DBA_HIST_ACTIVE_SESS_HISTORY
To find out how many sessions waited for some event,
select session_id||','||session_serial# SID, n.name, wait_time, time_waited
from v$active_session_history a, v$event_name n
where n.event# = a.event#
- CURRENT_OBJ#, can then be joined with DBA_OBJECTS to get the segments in question
- SQL_ID, can be joined with V$SQL to get SQL Statement that caused the wait
- CLIENT_ID, which was set for any client application session using DBMS_SESSION.SET_IDENTIFIER may be used to identify the client.
ADDM - Automatic Data Diagnostics Monitor
ADDM’s goal is to improve the value of db_time
Prerequisite :Exec dbms_advisor.set_default_task_parameter(’ADDM’,’DBIO_EXPECTED’, 20000);(response time expected by Oracle from the disk I/O system, defaults to 10 milliseconds)
@addmrpt.sql - for detailed report
Another article - http://www.rampant-books.com/art_floss_addm.htm
Prerequisite :Exec dbms_advisor.set_default_task_parameter(’ADDM’,’DBIO_EXPECTED’, 20000);(response time expected by Oracle from the disk I/O system, defaults to 10 milliseconds)
select sum(value) "DB time" from v$sess_time_modelAnalysis findings are ranked and catagorised into
where stat_name='DB time';
- PROBLEM
- SYMPTOM
- INFORMATION
Set pages 1000
Set lines 75
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message : 'b.message MESSAGE,
'Command To correct: 'c.command COMMAND,
'Action Message : 'c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,
Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%' and a.status='COMPLETED'
Order by b.impact, d.rank;
@addmrpt.sql - for detailed report
Another article - http://www.rampant-books.com/art_floss_addm.htm
AWR - Automatic Workload Repository reports
statistics_level parameter should be TYPICAL or ALL - for Automatic collection
BASIC - for manual snapshots
dba_hist_snapshot - for existing snapshots
dba_hist_baseline - for viewing baseline settings
awrrpt.sql - for Statspack style report (either text or html format)
awrrpti.sql - for single instance in RAC
Following views gets affected by AWR snapshots
v$active_session_history - Displays the active session history (ASH) sampled every second.
v$metric - Displays metric information.
v$metricname - Displays the metrics associated with each metric group.
v$metric_history - Displays historical metrics.
v$metricgroup - Displays all metrics groups.
dba_hist_active_sess_history - Displays the history contents of the active session history.
dba_hist_baseline - Displays baseline information.
dba_hist_database_instance - Displays database environment information.
dba_hist_snapshot - Displays snapshot information.
dba_hist_sql_plan - Displays SQL execution plans.
dba_hist_wr_control - Displays AWR settings.
Another interesting article on AWR, Time Model, Active Session History, Baseline, Snapshots is in http://www.rampant-books.com/art_nanda_awr.htm
BASIC - for manual snapshots
dba_hist_snapshot - for existing snapshots
dba_hist_baseline - for viewing baseline settings
awrrpt.sql - for Statspack style report (either text or html format)
awrrpti.sql - for single instance in RAC
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);
END;
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
Setting baselines:
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline (
baseline_name => 'batch baseline',
cascade => FALSE); -- Deletes associated snapshots if TRUE.
END;
This example creates a baseline (named 'batch baseline') between snapshots 105 and 107 for the local database:
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 105,
end_snap_id => 107,
baseline_name => 'batch baseline');
Following views gets affected by AWR snapshots
v$active_session_history - Displays the active session history (ASH) sampled every second.
v$metric - Displays metric information.
v$metricname - Displays the metrics associated with each metric group.
v$metric_history - Displays historical metrics.
v$metricgroup - Displays all metrics groups.
dba_hist_active_sess_history - Displays the history contents of the active session history.
dba_hist_baseline - Displays baseline information.
dba_hist_database_instance - Displays database environment information.
dba_hist_snapshot - Displays snapshot information.
dba_hist_sql_plan - Displays SQL execution plans.
dba_hist_wr_control - Displays AWR settings.
Another interesting article on AWR, Time Model, Active Session History, Baseline, Snapshots is in http://www.rampant-books.com/art_nanda_awr.htm
Statspack - How to
SQL> CONN perfstat/perfstat
Connected.
SQL> EXEC STATSPACK.snap;
PL/SQL procedure successfully completed.
@spauto.sql < for taking snapshot automatically every hour
@sppurge.sql < for deleting unwanted snaps
@spreport < for report generation
Data Pump - Export Import
Found this spool useful as a onestop reference to actual export import done using DataPump utility
http://www.databasejournal.com/img/jsc_DataPump_Listing_1.html#List0101
http://www.databasejournal.com/img/jsc_DataPump_Listing_1.html#List0101
Tuesday, February 26, 2008
X11 X-Windows Forwarding through SSH
Xming is a display server which can be used to display forwarded XWindow applications through SSH.
Assume, Unix server is having SSH service enabled. To bring up the display window of Unix in your local MS Windows PC; you need two softwares - one is a SSH client - putty and another is a X11 display server.
Download & install both from
http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
http://www.straightrunning.com/XmingNotes/#head-121
Xming setting
Create a windows shortcut
"C:\Program Files\Xming\Xming.exe" :0 -clipboard -multiwindow
Execute the same
Putty setting
connection>SSH>X11>Check - Enable X11 forwarding > X Display location : localhost:0.0
Login to the Unix.
$ export DISPLAY=localhost:10.0
To check whether X11 forwarding is functioning
$ /usr/openwin/bin/xclock
Assume, Unix server is having SSH service enabled. To bring up the display window of Unix in your local MS Windows PC; you need two softwares - one is a SSH client - putty and another is a X11 display server.
Download & install both from
http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
http://www.straightrunning.com/XmingNotes/#head-121
Xming setting
Create a windows shortcut
"C:\Program Files\Xming\Xming.exe" :0 -clipboard -multiwindow
Execute the same
Putty setting
connection>SSH>X11>Check - Enable X11 forwarding > X Display location : localhost:0.0
Login to the Unix.
$ export DISPLAY=localhost:10.0
To check whether X11 forwarding is functioning
$ /usr/openwin/bin/xclock
Subscribe to:
Posts (Atom)