RMAN backup script.

#!/bin/bash
SHELL=/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export ORACLE_SID=xxx

#wd=`date +%Y_%m_%d`
#mkdir -p /orabackup/test_backup/$wd

rman target backup/backup "two less than simbole" EOF
run {
set controlfile autobackup format for device type disk to '/backup/%F';
allocate channel c1 device type disk format='/backup/%d_%T_%U';
backup database;
sql 'alter system archive log current';
backup archivelog all;
delete noprompt archivelog all completed before 'SYSDATE-7';
release channel c1;
}
EOF

Linux automate backup copy to windows script

#!/bin/bash

SHELL=/bin/bash


# get sysdate to variable
wd=`date +%Y_%m_%d`


# get previous day to variable if required
#wd=`date -d "-1 day" +"%Y_%m_%d"`


mount -t smbfs -o username=administrator,password=password //[IP]/Exp /share/winShare

tar -czPf /share/winShare/MiddleDB_$wd.tgz /orabackup/backup/$wd/*


umount /share/winShare

Find Lock on database.

select username,
v$lock.sid,
id1, id2,
lmode,
request, block, v$lock.type , v$session.osuser , v$session.machine
from v$lock, v$session
where v$lock.sid = v$session.sid
and v$lock.type = 'TX'
--and v$session.username = USER
order by username

-------------------------------------------------------

select lock.

SQL> select * from v$lock ;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60 479 TX 131078 16739 0 6
ADDF7EC8 ADDF7EE0 422 TM 88519 0 3 0
ADDF7F74 ADDF7F8C 479 TM 88519 0 3 0
ADEBEA20 ADEBEB3C 422 TX 131078 16739 6 0


select lock object

SQL> select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
3 dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
4 from v$session s, dba_objects do
5 where sid=543
6 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- ------------- -------------- --------------- ------------- ------------------
TSTLOCK 88519 16 171309 0 AAAVnHAAQAAAp0tAAA

get the record.

SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR
--- ---
1 a

-------------------------------------------------------------

Lock Type

'RT', 'Redo Thread'
'UN', 'User Name'
'TX', 'Transaction'
'TM', 'DML'
'UL', 'PL/SQL User Lock'
'DX', 'Distributed Xaction'
'CF', 'Control File'
'IS', 'Instance State'
'FS', 'File Set'
'IR', 'Instance Recovery'
'ST', 'Disk Space Transaction'
'TS', 'Temp Segment'
'IV', 'Library Cache Invalidation'
'LS', 'Log Start or Switch'
'RW', 'Row Wait'
'SQ', 'Sequence Number'
'TE', 'Extend Table'
'TT', 'Temp Table'

Oracle password profile.

credit to Scott Stephens

CREATE PROFILE myprofile LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;Users assigned to this profile will be locked out of theiraccounts after five login attempts with an incorrect password. The account willbe inaccessible for one day or until a DBA issues this command:

ALTER USER ACCOUNT UNLOCK.Even after several years, I've found that my old passwordstill works on previous projects. This makes a good case for placing a limit ona password's lifetime so it will expire after a certain period (e.g., at theend of a contract). There's also an option to allow a specific grace period,which is useful for projects that aren't used very often. If the user doesn'tlog in until after the password expires, the user can still connect, but awarning will display until the grace period expires. Use the PASSWORD_LIFE_TIMEand PASSWORD_GRACE_TIME tags on a profile to enable these features.

ALTER PROFILE myprofile LIMIT PASSWORD_LIFE_TIME 30 PASSWORD_GRACE_TIME 3;Users assigned to that profile will be locked out of theiraccounts 30 days after the last time the password is changed. After 30 days,attempting to log in will result in warning messages for three more days beforethe account is locked.

Many users will see these limits and simply try to resettheir passwords to what they were previously using rather than using a newpassword each time. You can prevent users from reusing a password with thePASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX tags.

ALTER PROFILE myprofile LIMIT PASSWORD_REUSE_TIME 30 PASSWORD_REUSE_MAX 100;Users with this profile will not be able to reuse a passwordfor 30 days, or until after they change the password 100 times.

Finally, some users will use passwords that are easy toguess. It's possible to restrict a password's format (such as checking for aminimum width, letters, numbers, or mixed case, or verifying that the passwordisn't a variation of the username) by creating a PL/SQL procedure thatvalidates passwords. You must format the procedure like this:

CREATE OR REPLACE FUNCTION verify_password( useridvarchar(30), password varchar(30), old_passwordvarchar(30)) RETURN BOOLEAN…You can assign this function (which can be any name, but itmust be owned by the SYS account) with the following:

ALTER PROFILE myprofile LIMIT PASSWORD_VERIFY_FUNCTION verify_password;

Delete Files Older Than x Days on Linux

The find utility on linux allows you to pass in a bunch of interesting arguments, including one to execute another command on each file. We’ll use this in order to figure out what files are older than a certain number of days, and then use the rm command to delete them.

Command Syntax

find /path/to/files* -mtime +5 -exec rm {} \;

Note that there are spaces between rm, {}, and \;

Explanation

• The first argument is the path to the files. This can be a path, a directory, or a wild card as in the example above. I would recommend using the full path, and make sure that you run the command without the exec rm to make sure you are getting the right results.
• The second argument, -mtime, is used to specify the number of days old that the file is. If you enter +5, it will find files older than 5 days.
• The third argument, -exec, allows you to pass in a command such as rm. The {} \; at the end is required to end the command.

This should work on Ubuntu, Suse, Redhat, or pretty much any version of linux.

Top SQL session monitoring

Get the TOP session by following command

$ top

top - 14:30:26 up 36 days, 20:35, 3 users, load average: 0.59, 1.09, 1.37
Tasks: 532 total, 4 running, 528 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.5%us, 1.2%sy, 0.0%ni, 87.5%id, 7.4%wa, 0.1%hi, 0.3%si, 0.0%st
Mem: 32960328k total, 32489652k used, 470676k free, 259284k buffers
Swap: 16876140k total, 77236k used, 16798904k free, 26004652k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8167 oracle 15 0 6151m 131m 122m S 10 0.4 0:00.46 oracle
14723 oracle 15 0 6150m 2.1g 2.1g R 4 6.8 1:20.46 oracle
24356 oracle -2 0 6160m 4.5g 4.5g S 3 14.4 148:25.96 oracle
24360 oracle -2 0 6160m 4.5g 4.5g S 3 14.4 145:54.39 oracle
15450 oracle 15 0 6152m 1.8g 1.7g S 3 5.6 0:34.34 oracle
2517 oracle 15 0 6150m 536m 528m S 2 1.7 0:16.67 oracle
24354 oracle 15 0 6160m 153m 140m S 2 0.5 21:09.91 oracle

get the top CPU utilize PID.

apply PID to below select

select x.inst_id,x.sid, x.serial#,x.USERNAME,x.osuser,x.MACHINE,x.TERMINAL,x.program,x.TYPE,x.sql_hash_value,x.module,y.sql_text
from gv$session x,gv$sqlarea y
where paddr in (select addr from gv$process where spid =8167 )
and x.SQL_ADDRESS = y.address

Select the HASH value and apply below select.
select SQL_TEXT from v$sqlTEXT where HASH_VALUE in (2024423863) ORDER BY PIECE;

DBMS JOB

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TEST_SHEDULE',
job_type => 'STORED_PROCEDURE',
job_action => 'DPG.SP_SHEDULE_TEST',
start_date => to_date('26/05/2011 13:38:00' , 'DD/MM/RRRR HH24:MI:SS' ),
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2;',
comments => 'My new job');
END;
/

DECLARE
BEGIN
DBMS_SCHEDULER.ENABLE('TEST_SHEDULE');
END;

-------------------------------------------------------------

EXEC dbms_scheduler.run_job('TEST_SHEDULE');

-------------------------------------------------------------

EXEC DBMS_SCHEDULER.DROP_JOB('my_java_job');

-------------------------------------------------------------

SELECT * FROM dba_scheduler_jobs

DBMS_FILE_TRANSFER PL/SQL package.

Sample

CREATE or replace DIRECTORY source_dir AS '+DATA/DPG/ARCHIVELOG/2011_07_16';
CREATE or replace DIRECTORY dest_dir AS '/home/oracle/ARC';

exec dbms_file_transfer.COPY_FILE('source_dir','thread_1_seq_22287.328.756667191','dest_dir','thread_1_seq_22287.328.756667191');

COPY_FILE
The COPY_FILE procedure allows you to copy binary files from one location to another on the same server.

-- Create the source and destination directory objects.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';

-- Switch a tablespace into read only mode so we can
-- use it for a test file transfer.
ALTER TABLESPACE users READ ONLY;

-- Copy the file.
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'DB_FILES_DIR1',
source_file_name => 'USERS01.DBF',
destination_directory_object => 'DB_FILES_DIR2',
destination_file_name => 'USERS01.DBF');
END;
/

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;

Checking the destination directory will reveal that the file has been copied successfully.

GET_FILE
The GET_FILE procedure allows you to copy binary files from a remote server to the local server.

-- Login to the remote server.
CONN system/password@remote

-- Create the source directory object and switch mode of a tablespace.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
ALTER TABLESPACE users READ ONLY;

-- Login to the local server.
CONN system/password@local

-- Create the destination directory object and a database link.
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';
CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY password USING 'REMOTE';

-- Get the file.
BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object => 'DB_FILES_DIR1',
source_file_name => 'USERS01.DBF',
source_database => 'REMOTE',
destination_directory_object => 'DB_FILES_DIR2',
destination_file_name => 'USERS01.DBF');
END;
/

-- Login to the remote server.
CONN system/password@remote

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;

Checking the destination directory on the local server will reveal that the file has been copied successfully.

PUT_FILE
The PUT_FILE procedure allows you to copy binary files from the local server to a remote server.

-- Login to the remote server.
CONN system/password@remote

-- Create the destination directory object.
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';

-- Login to the local server.
CONN system/password@local

-- Create the source directory object, database link and switch mode of a tablespace.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY password USING 'REMOTE';
ALTER TABLESPACE users READ ONLY;

-- Put the file.
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => 'DB_FILES_DIR1',
source_file_name => 'USERS01.DBF',
destination_directory_object => 'DB_FILES_DIR2',
destination_file_name => 'USERS01.DBF',
destination_database => 'REMOTE');
END;
/

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;

Moving a datafile from File System to ASM

Moving datafiles from file system to ASM can be done in different ways, one of them using the RMAN copy command.

These are the steps:

1. Check where to build a new file system based tablespace:

[oracle@rac1 ~]$ cd /u01/oradata/racdb
[oracle@rac1 ~]$ df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/vx/dsk/u01
31457280 17540576 13819976 56% /u01

2. Connect to sqlplus and create a new tablespace

[oracle@rac1 racdb]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 19 06:07:50 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> create tablespace trtst datafile '/u01/oradata/racdb/trtst01.dbf' size 150M;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name ='TRTST';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/racdb/trtst01.dbf

3. take the tablespace offline, you may take offline a single datafile from a multifile tablespace if required.

SQL> ALTER TABLESPACE TRTST OFFLINE;

Tablespace altered.

4. Check where are your datafiles located on ASM

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
+DATADG/racdb/datafile/users.259.623629857
+DATADG/racdb/datafile/sysaux.257.623629849
+DATADG/racdb/datafile/undotbs1.258.623629855
+DATADG/racdb/datafile/system.256.623629845
+DATADG/racdb/datafile/undotbs2.261.623630209
/u01/oradata/racdb/trtst01.dbf

6 rows selected.

5. Log out from sqlplus, start an RMAN session and execute the copy command

Note that when giving the destination inside ASM you just need to pass the disk group name when using omf (Oracle Managed Files) that is the best practice in ASM.

SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
[oracle@rac1 racdb]$ rman target / nocatalog

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Jun 19 06:12:14 2007

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

connected to target database: RACDB (DBID=621246832)
using target database control file instead of recovery catalog

RMAN> copy datafile '/u01/oradata/racdb/trtst01.dbf' to '+DATADG';

Starting backup at 19-JUN-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/racdb/trtst01.dbf
output filename=+DATADG/racdb/datafile/trtst.263.625644857 tag=TAG20070619T061416 recid=1 stamp=625644858
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 19-JUN-07

RMAN> exit


Recovery Manager complete.

6. Start an sqlplus session, rename the old file to the new ASM file

[oracle@rac1 racdb]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 19 06:15:11 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> alter database rename file '/u01/oradata/racdb/trtst01.dbf' to '+DATADG/racdb/datafile/trtst.263.625644857';

Database altered.

SQL> alter tablespace trtst online;

Tablespace altered.

7. remove the old file

rm /u01/oradata/racdb/trtst01.dbf

8. Create a test object

SQL> create table testtb tablespace trtst as select * from dba_source;

Table created.

How to configure Proxy Settings for the Linux Console and APT

You can use the following methods to configure your console to use a proxy server so that console based programs like wget could get connect to the internet through the proxy.

1 - Set the environment variable

# export http_proxy=http://DOMAIN\USERNAME:PASSWORD@SERVER:PORT/
# export ftp_proxy=http://DOMAIN\USERNAME:PASSWORD@SERVER:PORT/

export http_proxy=http://upul:pass@10.32.11.6:80/

In the above configuration you can ommit the DOMAIN\USERNAME:PASSWORD@ part if you are not using proxy authentication.

Note: If you get the following error when you try to use wget, you might have to pass the proxy authentication credentials to wget as arguments.

Connecting to SERVER:PORT... connected.
Proxy request sent, awaiting response... 407 Proxy Authentication Required
11:14:45 ERROR 407: Proxy Authentication Required.

1.1 - Passing proxy authentication credentials to wget as arguments

$ wget --proxy-user "DOMAIN\USERNAME" --proxy-passwd "PASSWORD" URL

2 - Configure the proxy settings in the .bashrc

If you want set the proxy for all the users you can do it in the system wide .bashrc file.

nano /etc/bash.bashrc

#proxy settings
export http_proxy=http://DOMAIN\USERNAME:PASSWORD@SERVER:PORT/
export ftp_proxy=http://DOMAIN\USERNAME:PASSWORD@SERVER:PORT/

Note: The system wide .bashrc file may not be available in all Linux systems and only can be used if you are using the bash shell

2.1 - Having an alias for wget with proxy

If you don't want to pass the proxy arguments to wget all the time, you create an alias for wget in the .bashrc file

alias wget 'wget --proxy-user "DOMAIN\USERNAME" --proxy-passwd "PASSWORD"'

Public Yum Server

The Oracle public yum server offers a free and convenient way to install packages from the Oracle Linux and Oracle VM installation media via a yum client.

You can download the full Oracle Linux and Oracle VM installation media via edelivery.oracle.com/linux. Note that errata are not available via this public yum server. To access errata (bug fixes, security fixes and enhancement), you must have a valid support contract to access Unbreakable Linux Network (ULN). For more information, see the FAQ below.

This yum server is offered without support of any kind. If you require support, please consider purchasing Oracle Linux support via the online store, or via your sales representative.

Getting Started

1. Download and Install Oracle Linux

2. Download and copy the appropriate yum configuration file in place, by running the following commands as root:

   Oracle Linux 4, Update 6 or Newer

   # cd /etc/yum.repos.d
   # mv Oracle-Base.repo Oracle-Base.repo.disabled
   # wget http://public-yum.oracle.com/public-yum-el4.repo
   

   Oracle Linux 5

   # cd /etc/yum.repos.d
   # wget http://public-yum.oracle.com/public-yum-el5.repo
   

   Oracle Linux 6

   # cd /etc/yum.repos.d
   # wget http://public-yum.oracle.com/public-yum-ol6.repo
   

   Oracle VM 2

   # cd /etc/yum.repos.d
   # wget http://public-yum.oracle.com/public-yum-ovm2.repo
   

3. Enable the appropriate repository by editing the yum configuration file

   • Open the yum configuration file in a text editor
   • Locate the section in the file for the repository you plan to update from, e.g. [el4_u6_base]
   • Change enabled=0 to enabled=1

4. Begin using yum, for example:

   yum list

   yum install firefox

You may be prompted to confirm the import of the Oracle OSS Group GPG key.