Upgrades and Patches - best practise

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.

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;

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;

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

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.

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

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;

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'.

Unix Commands - Syntax

Some basic unix command usage examples

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

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)

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

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

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.

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> convert
2> datafile '/usr/oradata/dw10/dw10/users01.dbf'
3> format '/home/oracle/rman_bkups/%N_%f';

You can only use CONVERT DATAFILE when connected as TARGET to the destination database and converting datafile copies on the destination platform

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;