Rman encryption and other techniques

Today I will demonstrate Recovery Manager features, especially encryption and some other techniques.

First of all let me note about RMAN backup encryption. Staring from Oracle 10g RMAN now creates encrypted backups that cannot be restored by unauthorized people.
There are 3 modes of backup encryption:

* Transparent encryption
* Password encryption
* Dual-mode encryption using either transparent or password encryption

All RMAN backups are not encrypted but you can encrypt any RMAN backup in the form of a backup set.
In this tutorial I will show you how to configure Password encryption.


Let`s finish talking and start to demonstrate.

check parameters with "SHOW ALL" command, by default encryption is OFF

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/oracle/product/10.2.0/db_1/dbs/snapcf_mydb.f'; # default

RMAN>


Here I choosed on tablespace for encryotion, for example : Users tablespace;
But you can encrypt entire database too: CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

 Now, take users tablespaces`s backup and restore it

So, now check this encryption while restore

For simulate we can drop datafile of tablespace or just take tablespace offline. In this example I will use second option.

Upssssssssss, exception, it is impossible!

there are several RMAN and ORA exceptions.

So, we need to decrypt it.

Yes, as you see, successfully restored.

Now, recover it and bring tablespace to online

Check tablespace`s status

An other tutorial is about Skipping Tablespaces when Backing Up a Database and Limiting the Size of Backup Pieces

I have created several tablespaces with seeral statuses. You can see it above screen shot.
We can exclude offline and readonly tablespaces while backing up.

To back up the database while skipping offline and read-only tablespaces, you can run the following command:

RMAN> BACKUP DATABASE
        SKIP READONLY
        SKIP OFFLINE;

Another way to persistently skip tablespaces across RMAN sessions is to issue the CONFIGURE EXCLUDE command for each tablespace that you always want to skip.
For example, you may always want to skip the example tablespace, which has been made read-only. You can then issue:

Then, whenever you run BACKUP DATABASE, RMAN skips this tablespace.
You do not have to specify a SKIP clause on the BACKUP command.
You can override this behavior and include the example tablespace as follows:

RMAN> BACKUP DATABASE NOEXCLUDE;

That`s all.


As source please refer to:
http://mohamedazar.com
http://download.oracle.com/docs/cd/B19306_01/server.102/b14214/chapter1.htm
http://download.oracle.com/docs/cd/B12037_01/server.101/b10734/rcmbackp.htm

Rebuild one partition of an index

Today one of the our developer asked me that, one query works very slowly. After investigation I have found out plan of the query is incorrect and query use substr function which incorrect place.

While using select I`ve received ORA-01502. What is the this error?

ORA-01502: index 'string.string' or partition of such index is in unusable state

Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation

Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition
   

I am going to check my partition indexes which is unusable state.


SQL> select * from DBA_IND_PARTITIONS

where index_name = 'MY_ARCH_TEST_DB_IDX2' and status <> 'USABLE'


Ups! Found. P2008_201010 partition is unusable state. Checked how many rows has in this partition:


SQL> select count(1) from my_arch_test partition (P2008_201010)

126339601 rows.


Let me note, my table`s size approximate 27 GB. Not big, not little :)

Partitioned in_date column, and has 2 partitioned indexes, in_date and subscriber_id.

Checked when table`s statistics was taken and are those indexes valid. Yesterday gathered statistic.


Now, let`s rebuild partition index.

SQL> alter index MY_ARCH_TEST_DB_IDX2 rebuild partition P2008_201010 online;

Remove old trace files or unnecessary files

As OS administrator, DBA should be check filesystem, clean up unnecessary and old trace files.
Below command will delete files older than 7 days.

find *.trc -mtime +7 -type f -local -exec rm  {} \;

DDL trigger

Sometimes someone creates/drop/modify db object(s). However it depends requester. To prevent this dislike events I suggest create DDL trigger. This trigger will fire every DDL action.

First of all we should determine which kind of DDL we have to store. So, create table:

CREATE TABLE SYS.DDL_WATCH_LOG
(
  ACTION             CHAR(6 BYTE),
  ORA_EVENT       VARCHAR2(20 BYTE),
  ORACLE_LOGIN_USER      VARCHAR2(30 BYTE),
  ORA_DATABASE_NAME   VARCHAR2(50 BYTE),
  ORA_OBJECT_TYPE   VARCHAR2(20 BYTE),
  ORA_OBJECT_NAME   VARCHAR2(30 BYTE),
  ORA_OBJECT_OWNER  VARCHAR2(30 BYTE),
  ORA_SESSIONUSER        VARCHAR2(30 BYTE),
  ORA_HOSTNAME           VARCHAR2(60 BYTE),
  ORA_IPADDRESS          VARCHAR2(30 BYTE),
  OSUSER             VARCHAR2(30 BYTE),
  DDL_TEXT           CLOB,
  DDL_DATE           TIMESTAMP(6) WITH LOCAL TIME ZONE
)

Now, create trigger:

CREATE OR REPLACE TRIGGER DDL_WATCH_AFTER
AFTER DDL ON DATABASE
BEGIN
IF ora_dict_obj_type IN ('TABLE','INDEX'/*an other object types*/) THEN /*You may exclude specific db users*/

INSERT INTO DDL_WATCH_LOG (ACTION, ORA_EVENT, ORACLE_LOGIN_USER, ORA_DATABASE_NAME, ORA_OBJECT_TYPE, ORA_OBJECT_NAME, ORADICT_OBJ_OWNER, ORA_SESSIONUSER, ORA_HOSTNAME, ORA_IPADDRESS, OSUSER, OSUSER, DDL_TEXT, DDL_DATE)
VALUES
('AFTER', ora_sysevent, ora_login_user, ora_database_name, ora_dict_obj_type, ora_dict_obj_name, sys_context('userenv','SESSION_USER'), sys_context('userenv','HOST'),
sys_context('userenv','IP_ADDRESS'), sys_context('userenv','OS_USER'),Dbms_Metadata.GET_DDL(ora_dict_obj_type,ora_dict_obj_name,ora_dict_obj_owner), SYSDATE);
END IF;
END;
/



Has another method, example using audit, but this trigger will behavior on development DBs where several database users changes existing db objects, create/remove etc.

RMAN Backed up file recoginize

Sometimes we get backup and did not use TAG or format clause via RMAN.
However RMAN will give unique name for backup. After some period we need to clarify backup name,
which of them controlfile name, which of them backup datafile 1 etc.
For clarify Oracle RMAN backups, you need to use : list backup by file;

Let`s to look at example
take backup of current controlfile and spfile

RMAN> backup current controlfile spfile;


take backup of 1 data file of database, SYSTEM01.dbf

RMAN> backup datafile 1;


take backup of whole database and archivelog

RMAN> backup database plus archivelog;

[oracle@localhost 2010_08_12]$ ls -lh

total 1.3G

-rw-r----- 1 oracle oinstall 283M Aug 12 11:50 o1_mf_annnn_TAG20100812T115003_6676dx1p_.bkp

-rw-r----- 1 oracle oinstall 17K Aug 12 11:51 o1_mf_annnn_TAG20100812T115147_6676j4h5_.bkp

-rw-r----- 1 oracle oinstall 6.8M Aug 12 11:38 o1_mf_ncnnf_TAG20100812T113851_6675qw1y_.bkp

-rw-r----- 1 oracle oinstall 6.9M Aug 12 11:48 o1_mf_ncsnf_TAG20100812T114718_667694kt_.bkp

-rw-r----- 1 oracle oinstall 6.9M Aug 12 11:51 o1_mf_ncsnf_TAG20100812T115040_6676j23l_.bkp

-rw-r----- 1 oracle oinstall 349M Aug 12 11:47 o1_mf_nnndf_TAG20100812T114718_66767pnz_.bkp

-rw-r----- 1 oracle oinstall 585M Aug 12 11:51 o1_mf_nnndf_TAG20100812T115040_6676g0r9_.bkp

-rw-r----- 1 oracle oinstall 96K Aug 12 11:38 o1_mf_nnsnf_TAG20100812T113851_6675qxbt_.bkp

As you see, on OS level our backup up files name like o1_mf_****_TAG20100812T11****_667***_.bkp
And we can not recoginize names. Thereby we can use by list clause on list backup

RMAN> list backup by file;


List of Datafile Backups========================

File Key     TY LV S Ckp SCN    Ckp Time  #Pieces #Copies Compressed Tag
---- ------- -  -- - ---------- --------- ------- ------- ---------- ---
1    6       B  F  A 531440     12-AUG-10 1       1       NO         TAG20100812T115040
     3       B  F  A 531208     12-AUG-10 1       1       NO         TAG20100812T114718
2    6       B  F  A 531440     12-AUG-10 1       1       NO         TAG20100812T115040
3    6       B  F  A 531440     12-AUG-10 1       1       NO         TAG20100812T115040
4    6       B  F  A 531440     12-AUG-10 1       1       NO         TAG20100812T115040

List of Archived Log Backups
============================

Thrd Seq     Low SCN    Low Time  BS Key  S #Pieces #Copies Compressed Tag
---- ------- ---------- --------- ------- - ------- ------- ---------- ---
1    2       473040     10-AUG-10 5       A 1       1       NO         TAG20100812T115003
1    3       474104     10-AUG-10 5       A 1       1       NO         TAG20100812T115003
1    4       474117     10-AUG-10 5       A 1       1       NO         TAG20100812T115003
1    5       497563     10-AUG-10 5       A 1       1       NO         TAG20100812T115003
1    6       503686     10-AUG-10 5       A 1       1       NO         TAG20100812T115003
1    7       504368     10-AUG-10 5       A 1       1       NO         TAG20100812T115003
1    8       505036     10-AUG-10 5       A 1       1       NO         TAG20100812T115003
1    9       505706     10-AUG-10 5       A 1       1       NO         TAG20100812T115003
1    10      506370     10-AUG-10 5       A 1       1       NO         TAG20100812T115003
1    11      509812     10-AUG-10 5       A 1       1       NO         TAG20100812T115003
1    12      530702     12-AUG-10 5       A 1       1       NO         TAG20100812T115003
1    13      531425     12-AUG-10 8       A 1       1       NO         TAG20100812T115147

List of Control File Backups
============================

CF Ckp SCN Ckp Time  BS Key  S #Pieces #Copies Compressed Tag
---------- --------- ------- - ------- ------- ---------- ---
531470     12-AUG-10 7       A 1       1       NO         TAG20100812T115040
531228     12-AUG-10 4       A 1       1       NO         TAG20100812T114718
530896     12-AUG-10 1       A 1       1       NO         TAG20100812T113851

List of SPFILE Backups
======================

Modification Time BS Key  S #Pieces #Copies Compressed Tag
----------------- ------- - ------- ------- ---------- ---
12-AUG-10         7       A 1       1       NO         TAG20100812T115040
12-AUG-10         4       A 1       1       NO         TAG20100812T114718
12-AUG-10         2       A 1       1       NO         TAG20100812T113851

RMAN>

Please note that on "List of Datafile Backups" second row :

3       B  F  A 531208     12-AUG-10 1       1       NO         TAG20100812T114718

show that 1 datafile (system01.dbf) also was backed up before.

Grant with LOOP

Sometimes we have had request that grant to new or existing user privileges which already another user has. Example User2 is new database user and we have to grant him privilege(s) User1 table(s). To do it, we can use some methods, below is optimal solution. This PLSQL block can use DBA, USER1 or user which has USER1 tables WITH ADMIN OPTION rights.

begin
for cTab in (select * from dba_tables where owner='USER1') loop
execute immediate 'grant select on '||cTab.owner||'.'||cTab.table_name || ' to ' || 'USER2';
dbms_output.put_line(cTab.owner||'.'||cTab.table_name || ' granted to '||'USER2');
end loop;
end;

How to change varchar column data type to CLOB

Today I will demonstrate how we can made changes on existing table column type.

Describe that we have a table and column varchar2 and data on that column is not null. So, what should we do? We should add column on table with CLOB type. Please look at below example.

Our table name is TEST_TBL and structure of table is:

create table ulfet.test_tbl
(
log_id number,
log_date date,
log_text varchar2(4000)
);

We want change log_text column type VARCHAR2 to CLOB type.

Add new column to TEST_TBL table.
SQL> alter table test_tbl add log_text2 clob;

Then update value of log_text to log_text2.

update TEST_TBL
set log_text2 = log_text;

Drop log_text column.
SQL> alter table test_tbl drop log_text;

Rename log_text2 to log_text.
SQL> alter table test_tbl rename column log_text2 to log_text;

That is all :)

Truncate listener log file

One of the DBA responsibility is checking listener log file size. If your Oracle is running 7 x 24 you can`t rename online log file as alert.log. It means that if you try "moving" hot listener.log file is useless - ORACLE will still write into the moved file. I faced with problem, my listener.log file was to big size. After reading some blog and searching OTN forum I found guide and now I am going to demonstrate it :

[oracle@localhost log]$ ls -lh
total 298M
-rw-r--r-- 1 oracle oinstall 298M Jul  6 15:59 listener.log
-rw-r--r-- 1 oracle oinstall  682 Jul  6 11:51 sqlnet.log


[oracle@localhost admin]$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-JUL-2011 16:05:36

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener listener
Current Listener is listener
LSNRCTL> show log_file
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
listener parameter "log_file" set to listener.log
The command completed successfully
LSNRCTL> set log_file new_listener.log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
listener parameter "log_file" set to new_listener.log
The command completed successfully
LSNRCTL> show log_file
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
listener parameter "log_file" set to new_listener.log
The command completed successfully
LSNRCTL>


Now, above command created new listener log file, let`s check:

[oracle@localhost log]$ ls -lrt
total 304976
-rw-r--r-- 1 oracle oinstall       682 Jul  6 11:51 sqlnet.log
-rw-r--r-- 1 oracle oinstall 311973339 Jul  6 16:05 listener.log
-rw-r--r-- 1 oracle oinstall       606 Jul  6 16:06 new_listener.log
[oracle@localhost log]$


So, old one we are going to compress

[oracle@localhost log]$ gzip listener.log
[oracle@localhost log]$ ls -lrt
total 2116
-rw-r--r-- 1 oracle oinstall     682 Jul  6 11:51 sqlnet.log
-rw-r--r-- 1 oracle oinstall 2153490 Jul  6 16:05 listener.log.gz
-rw-r--r-- 1 oracle oinstall     657 Jul  6 16:08 new_listener.log

[oracle@localhost log]$ ls -lh
total 2.1M

-rw-r--r-- 1 oracle oinstall 2.1M Jul  6 16:05 listener.log.gz

-rw-r--r-- 1 oracle oinstall  657 Jul  6 16:08 new_listener.log
-rw-r--r-- 1 oracle oinstall  682 Jul  6 11:51 sqlnet.log
[oracle@localhost log]$

After gzip our log file locates few space

Now, going to back...

LSNRCTL> set log_file listener.log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
listener parameter "log_file" set to listener.log
The command completed successfully
LSNRCTL> show log_file
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
listener parameter "log_file" set to listener.log
The command completed successfully
LSNRCTL>

New listener.log file was created and our log of listener will write to listener.log

[oracle@localhost log]$ ls -lrt
total 2120
-rw-r--r-- 1 oracle oinstall     682 Jul  6 11:51 sqlnet.log
-rw-r--r-- 1 oracle oinstall 2153490 Jul  6 16:05 listener.log.gz
-rw-r--r-- 1 oracle oinstall     708 Jul  6 16:09 new_listener.log
-rw-r--r-- 1 oracle oinstall      72 Jul  6 16:11 listener.log
[oracle@localhost log]$

Unix/Linux for DBAs

Most essential commands for DBAs:

#Find files
find . -print |grep -i listener.ora

#Kill similar processes

ps -ef | grep mon | grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}

#Change owner of file, directory, sub-directory
chown user:group file(s) or folder

#Check particularly process count

ps -ef | grep runner | wc -l

#Display total file space in directory

du -ks

du -ks /home/oracle

#Change permission

chmod 777 runInstaller
--give read, write and execute to user/group/all

#Compress file
compress file <file name> - compress file
gzip <file name>    -    creates *.gz file
gunzip <file name>  -    decompresses *.gz file

#Replacing command - !!
[runner@runner ~]$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda1              2030736    421328   1504588  22% /
/dev/sda6               505604     17860    461640   4% /tmp

!!
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda1              2030736    421328   1504588  22% /
/dev/sda6               505604     17860    461640   4% /tmp

#Archive files
tar -cvf <new TAR-file name> <directory to be TARed>
or
tar cvf <new TAR-file name> <directory to be TARed>

--untar and gunzip together

tar -xzf log_files_2011-6-29.log.tar.gz

#Use alias
alias ll ='ls -l'
alias sql ='sqlplus '/ as sysdba'


alias alert='tail -100 \
           $DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log|more'
   alias errors='tail -100 \
           $DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log|more'
   alias arch='cd $DBA/$ORACLE_SID/arch'
   alias bdump='cd $DBA/$ORACLE_SID/bdump'
   alias cdump='cd $DBA/$ORACLE_SID/cdump'
   alias pfile='cd $DBA/$ORACLE_SID/pfile'
   alias rm='rm -i'
   alias sid='env|grep ORACLE_SID'
   alias admin='cd $DBA/admin'
Source: http://www.dba-oracle.com/t_linux_profile_alias_settings.htm