Scripts

Internal parameters

Posted by Amin Jaffer on May 14, 2008

– view oracle internal parameters (name, value, default value, data type, description)
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode(a.ksppity, 1, ‘boolean’,
2, ’string’,
3, ‘number’,
4, ‘file’, a.ksppity) type,
a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like ‘\_%’ escape ‘\’
order by name
/

Posted in Parameters, Scripts | No Comments »
How to extract the DB LINKS DDL with the password

Posted by Alex Lima on March 28, 2008

Here is a simple script to extract the DB Links DDL with the encripted password.

SET LONG 9000 — to print the complete string
SELECT DBMS_METADATA.GET_DDL(’DB_LINK’,a.db_link,a.owner) FROM dba_db_links a;

Posted in General DBA, Scripts | No Comments »
Creation SQL script for DB Link within a database

Posted by Amin Jaffer on March 26, 2008

The following can be used to create a script for DB Links with work on 8i/9i and will contain the password but not in 10g. The SQL script for creating the DB Link would need to be run the user who owns the DB Link if it’s not a PUBLIC.

SELECT ‘create ‘||DECODE(U.NAME,’PUBLIC’,'public ‘)||’database link ‘||CHR(10)
||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.')|| L.NAME||chr(10)
||’connect to ‘ || L.USERID || ‘ identified by ‘
||L.PASSWORD||’ using ”’ || L.host || ””
||chr(10)||’;’ TEXT
FROM sys.link$ L, sys.user$ U
WHERE L.OWNER# = U.USER#;

Posted in Scripts | No Comments »
How to move datafiles in temp tablespace?

Posted by Amin Jaffer on March 7, 2008

One can’t move the temp tablespace during mount stage like other datafiles using “ALTER DATABASE RENAME FILE..” so a workaround this issue is to create a new temp tablespace.

SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

– create a new temp tablespace
SQL> CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE ‘/u01/oradata/TESTDB/temp2_01.dbf’ SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
SEGMENT SPACE MANAGEMENT MANUAL
/

– change default temporary tablespace
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

– drop old temp tablespace
SQL> drop tablespace temp including contents and datafiles;

– recreate the temp with new file location
SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE ‘/u03/oradata/TESTDB/temp_01.dbf’ SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
SEGMENT SPACE MANAGEMENT MANUAL
/

– make the temp default again
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

– drop temp2 tablespace
SQL> drop tablespace temp2 including contents and datafiles;

Posted in Scripts | No Comments »
Enable trace in a running session from PL/SQL

Posted by Amin Jaffer on March 7, 2008

Using Oracle SID and SERIAL# can turn trace on a Oracle session that has already started. The values of SID and serial# can be obtained from V$SESSION. This will create trace file in directory set by the parameter user_dump_dest.

From: PL/SQL

SQL> exec dbms_support.start_trace_in_session(, );

Posted in Scripts | No Comments »
TEMP space usage

Posted by Amin Jaffer on February 16, 2008

Get current total TEMP tablespace currently allocated, used and free:

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

TABLESPACE MB_TOTAL MB_USED MB_FREE
——————————- ———- ———- ———-
TEMP2 27000 80 26920

Get Current TEMP space usage by session id (9i/10g):

SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

Posted in Scripts | No Comments »
Script to move Oracle Datafiles

Posted by Alex Lima on February 5, 2008

Here is a nice script if you need to move your datafiles to a new location.

SQL>spool rename_datafile.sql

set line 140
set pagesize 2000
set heading off
select ‘alter database rename file ‘ ||””|| file_name || ”” || ‘ to ‘|| ””||
’/oracle/IMPRD/data’ || substr(file_name, instr(file_name, ‘/’, -1)) ||”” || ‘;’
from dba_data_files
/
SQL>spool off

Spool to a file and then:

1- Shutdown immediate;

2- OS copy the files to the new location

3- Startup Mount;

4- Execute the spooled file ( SQL>@rename_datafile.sql )

5- Alter database open;

Posted in General DBA, Scripts | No Comments »
Grant Full Access to all tables and views within a Schema

Posted by Alex Lima on January 30, 2008

set serveroutput on
set serveroutput on size 1000000
DECLARE

vcount1 number := 0;
vcount2 number := 0;
v_tablename varchar2(32);
v_viewname varchar2(32);
vschema varchar2(30) := ‘&schema’;
vrole varchar2(30) := ‘&role’;
# — vprivilege varchar2(30) := ‘&privilege’;

BEGIN

vschema := UPPER(vschema);
vrole := UPPER(vrole);

for ctable in ( select owner, table_name from dba_tables where owner like vschema )
loop
vcount1:= vcount1+1;
v_tablename :=ctable.table_name;
begin
–dbms_output.put_line(ctable.owner||’.'||ctable.table_name);
execute immediate ‘grant select,update, insert,delete on ‘||ctable.owner||’.'||ctable.table_name||’ to ‘||vrole;
EXCEPTION — exception handlers begin
WHEN OTHERS THEN — handles all other errors
dbms_output.put_line(’err_num: ‘||SQLCODE);
dbms_output.put_line(’err_msg: ‘||SUBSTR(SQLERRM, 1, 100));
dbms_output.put_line(’table_name: ‘||v_tablename);
vcount1:= vcount1-1;
END;
end loop;

dbms_output.put_line(’—– ‘);
dbms_output.put_line(’Counter Tables is: ‘||vcount1);
dbms_output.put_line(’—– ‘);

for cview in ( select owner, view_name from dba_views where owner like vschema )
loop
vcount2:= vcount2+1;
v_viewname :=cview.view_name;
begin
–dbms_output.put_line(cview.owner||’.'||cview.view_name);
execute immediate ‘grant select on ‘||cview.owner||’.'||cview.view_name||’ to ‘||vrole;
EXCEPTION — exception handlers begin
WHEN OTHERS THEN — handles all other errors
dbms_output.put_line(’err_num: ‘||SQLCODE);
dbms_output.put_line(’err_msg: ‘||SUBSTR(SQLERRM, 1, 100));
dbms_output.put_line(’view_name: ‘||v_viewname);
vcount2:= vcount2-1;
END;
end loop;
dbms_output.put_line(”);
dbms_output.put_line(’Counter Views is: ‘||vcount2);
EXCEPTION — exception handlers begin
WHEN OTHERS THEN — handles all other errors
dbms_output.put_line(’err_num: ‘||SQLCODE);
dbms_output.put_line(’err_msg: ‘||SUBSTR(SQLERRM, 1, 100));
dbms_output.put_line(’view_name: ‘||v_viewname);

END;
/

Posted in Scripts | No Comments »
Check Oracle Password for Expiration

Posted by Alex Lima on January 30, 2008

This simple script will check if a user password is expiring in the next 120 days.

set pagesize 500
set linesize 200
set trimspool on
column “EXPIRE DATE” format a20
select username as “USER NAME”, expiry_date as “EXPIRE DATE”, account_status
from dba_users
where expiry_date < sysdate+120
and account_status IN ( ‘OPEN’, ‘EXPIRED(GRACE)’ )
order by account_status, expiry_date, username
/

Posted in Scripts | No Comments »
Archive log history by the hour

Posted by Amin Jaffer on January 26, 2008

– Print # of archive logs for yesterday by the hour
– can be used to determine to schedule archive logs backups, to view number of times log switches occurs

set linesize 200
set trimspool on
set feedback off

Column 00 format 999
Column 01 format 999
Column 02 format 999
Column 03 format 999
Column 04 format 999
Column 05 format 999
Column 06 format 999
Column 07 format 999
Column 08 format 999
Column 09 format 999
Column 10 format 999
Column 11 format 999
Column 12 format 999
Column 13 format 999
Column 14 format 999
Column 15 format 999
Column 16 format 999
Column 17 format 999
Column 18 format 999
Column 19 format 999
Column 20 format 999
Column 21 format 999
Column 22 format 999
Column 23 format 999

select trunc(first_time) AS Day,
sum(DECODE(to_char(first_time, ‘HH24′), ‘00′, 1, 0)) AS “00″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘01′, 1, 0)) AS “01″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘02′, 1, 0)) AS “02″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘03′, 1, 0)) AS “03″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘04′, 1, 0)) AS “04″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘05′, 1, 0)) AS “05″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘06′, 1, 0)) AS “06″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘07′, 1, 0)) AS “07″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘08′, 1, 0)) AS “08″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘09′, 1, 0)) AS “09″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘10′, 1, 0)) AS “10″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘11′, 1, 0)) AS “11″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘12′, 1, 0)) AS “12″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘13′, 1, 0)) AS “13″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘14′, 1, 0)) AS “14″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘15′, 1, 0)) AS “15″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘16′, 1, 0)) AS “16″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘17′, 1, 0)) AS “17″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘18′, 1, 0)) AS “18″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘19′, 1, 0)) AS “19″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘20′, 1, 0)) AS “20″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘21′, 1, 0)) AS “21″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘22′, 1, 0)) AS “22″,
sum(DECODE(to_char(first_time, ‘HH24′), ‘13′, 1, 0)) AS “23″
FROM v$log_history
WHERE trunc(FIRST_TIME) = trunc(sysdate - 1)
GROUP BY trunc(first_time);



How to fix Online redo log corruption

Posted by Alex Lima on April 2, 2008

Today, due to a network issue the host (Solaries) lost contact with the NetApp filer and it corrupted some datafiles and redo log files.
The solution was to recreate all indexes from the corrupted datafile in a new tablespace and drop the corrupted tablespace.
The archived log process hung because it could not archive the log 464704 which got corrupted, the solution was to clear the redo log and manually switch the logs to test the fix.
1- Archived log hung because the online redo log was corrupted and could not be archived.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464713 104857600 2 NO CURRENT
2 1 464711 104857600 2 NO INACTIVE
3 1 464709 104857600 2 NO INACTIVE
4 1 464704 104857600 2 NO INACTIVE
5 1 464712 104857600 2 NO INACTIVE
6 1 464710 104857600 2 NO INACTIVE

6 rows selected.

2- When tried to clear the online redo log it wouldn’t let us do it because of the datafile corrupted as well.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4
*
ERROR at line 1:
ORA-00393: log 4 of thread 1 is needed for recovery of offline datafiles
ORA-00312: online log 4 thread 1: ‘/oracle/EPB/WISPRD/redo/redo2/redo4b.log’
ORA-00312: online log 4 thread 1: ‘/oracle/EPB/WISPRD/redo/redo1/redo4a.log’
ORA-01110: data file 281: ‘/oracle/EPB/WISPRD/data/cust_index_jul2007.dbf’

3- Found an additional clause to force the CLEAR.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4 UNRECOVERABLE DATAFILE ;

Database altered.

4- Online redo log group is now clear but keep in mind that we don’t have that archive log (464704) and recoverability is compromised at this point. We need to take a full snap backup.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464713 104857600 2 NO INACTIVE
2 1 464711 104857600 2 YES INACTIVE
3 1 464715 104857600 2 NO CURRENT
4 1 0 104857600 2 YES UNUSED
5 1 464712 104857600 2 YES INACTIVE
6 1 464714 104857600 2 NO INACTIVE
6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464719 104857600 2 YES INACTIVE
2 1 464717 104857600 2 YES INACTIVE
3 1 464715 104857600 2 YES INACTIVE
4 1 464720 104857600 2 YES ACTIVE
5 1 464718 104857600 2 YES INACTIVE
6 1 464721 104857600 2 NO CURRENT

6 rows selected.
SQL> DROP TABLESPACE CUST_INDEX_JUL2007 INCLUDING CONTENTS;

Tablespace dropped.
SQL> select distinct status from v$datafile;

STATUS
——-
ONLINE
SYSTEM

After all this a full backup was taken.

A hope that helps…

Posted in General DBA | No Comments »
How to extract the DB LINKS DDL with the password

Posted by Alex Lima on March 28, 2008

Here is a simple script to extract the DB Links DDL with the encripted password.

SET LONG 9000 — to print the complete string
SELECT DBMS_METADATA.GET_DDL(’DB_LINK’,a.db_link,a.owner) FROM dba_db_links a;

Posted in General DBA, Scripts | No Comments »
How to password protect your listener

Posted by Alex Lima on March 27, 2008

To avoid inadvertent stopping of your listener or to prevent unauthorized access to your listener, you may set up password protection for your listener.

If you forget the password, do the worst.
- hard kill the listener process from the OS
- modify the listener.ora file by taking out the “PASSWORDS_LISTENER” parameter.
- Restart the listener
- Proceed with one of the following methods

1. Cleartext Password
- Add PASSWORDS_ entry to your existing listener.ora file. e.g. PASSWORDS_listener1 = (p1,p2)
- Stop your listener, and restart it.
Now passwords are in effect.

To stop the listener, set password command must be used.
e.g. lsnrctl
LSNRCTL> set current_listener listener1
LSNRCTL> set password p1
LSNRCTL> stop

2. Encrypted Password
- Comment out PASSWORD_ line if cleartext password is set.
- Restart listener.
- Run lsnrctl
LSNRCTL> set current_listener
LSNRCTL> set save_config_on_stop on
LSNRCTL> change_password
Old password:
New password:
Reenter new password:

e.g:
LSNRCTL> change_password
Old password:
New password: dummy
Reenter new password: dummy

Just hit key for old password since no previuos password is set. The passwords you entered will not be echoed.

- Stop the listener
LSNRCTL> set password
Password:
LSNRCTL> stop

e.g:
LSNRCTL> set password Password: e1
LSNRCTL> stop - Check your listener.ora file

Entries similar to the following should have been added to your listener.ora automatically.

SAVE_CONFIG_ON_STOP_listener1 = ON
PASSWORDS_LISTENER = 2D6C48144CF753AC


Posted in General DBA, Listener | No Comments »
How full is the current redo log file?

Posted by Alex Lima on March 20, 2008

SQL> SELECT le.leseq “Current log sequence No”,
2 100*cp.cpodr_bno/le.lesiz “Percent Full”,
3 cp.cpodr_bno “Current Block No”,
4 le.lesiz “Size of Log in Blocks”
5 FROM x$kcccp cp, x$kccle le
6 WHERE le.leseq =CP.cpodr_seq
7 AND bitand(le.leflg,24) = 8;

Current log sequence No Percent Full Current Block No Size of Log in Blocks
———————– ———— —————- ———————
13 29.0625 29760 102400

Posted in General DBA | No Comments »
Extract all tablespaces DDL

Posted by Alex Lima on February 8, 2008

Nice way to extract the DDL for all tablepaces.. This also can be used for other objects as well.

SQL>set heading off;
SQL>set echo off;
SQL>Set pages 999;
SQL>set long 90000;

SQL>spool ddl_list.sql

SQL>select dbms_metadata.get_ddl(’TABLESPACE’,tb.tablespace_name) from dba_tablespaces tb;

SQL>spool off

Posted in General DBA | No Comments »
Clone a database with RMAN

Posted by Alex Lima on February 7, 2008

Duplicating a Database using RMAN - (UNIX / Linux)

PMMPRD to PMMQA1
Pre-requisites
A valid full database backup of the target database
$rman target sys/xxxx@pmmprd.world catalog rman/xxxxx@recprd
RMAN> list backup summary;

Target database must be mounted or open (Target means PMMPRD)

$ sqlplus “/ as sysdba”
SQL> startup open

Steps Required
Create Password File for Auxiliary Database
$ orapwd file=/u01/app/oracle/product/9.2.0/dbs/orapwPMMQA1 password=sys_password

Create an Initialization Parameter for the Auxiliary Database if 9i

$ export ORACLE_SID=PMMPRD
$ sqlplus “/ as sysdba”

SQL> create pfile=’/u01/app/oracle/product/9.2.0/dbs/initPMMQA1.ora’ from spfile;

NOTE: if 8i copy the initPMMPRD.ora file over to the other server and rename to initPMMQA1.ora

NOTE2: If the instance is 9i you can configure db_file_name_convert and log_file_name_convert parameters in the init file with more then one pair of strings. If the instance is 8i you can only have one pair of strings, if you need more then one pair of strings in 8i you will need to use SET NEWNAME in the RUN{} script.

db_file_name_convert = (’/u06/app/oradata/PMMPRD’, ‘/u06/app/oradata/PMMQA1′)
log_file_name_convert = (’/u03/app/oradata/PMMPRD ‘, ‘/u03/app/oradata/PMMQA1′,
‘/u04/app/oradata/PMMPRD ‘, ‘/u04/app/oradata/PMMQA1′,
‘/u05/app/oradata/PMMPRD ‘, ‘/u05/app/oradata/PMMQA1′)

control_files = ‘/u03/app/oradata/PMMQA1/control01.ctl’
, ‘/u04/app/oradata/PMMQA1/control02.ctl’
, ‘/u05/app/oradata/PMMQA1/control03.ctl’

db_name = ‘PMMQA1′
instance_name = ‘PMMQA1′
audit_file_dest = ‘/u01/app/oracle/admin/PMMQA1/adump’
background_dump_dest = ‘/u01/app/oracle/admin/PMMQA1/bdump’
core_dump_dest = ‘/u01/app/oracle/admin/PMMQA1/cdump’
user_dump_dest = ‘/u01/app/oracle/admin/PMMQA1/udump’
service_names = ‘PMMQA1.WORLD’
dispatchers = ‘(PROTOCOL=TCP) (SERVICE=TESTDBXDB)’
log_archive_dest_1 = ‘location=/u06/app/oradata/PMMQA1/archive mandatory’

NOTE3: The mount points are example only, check all before the clone.
Create / Start the Auxiliary Instance

Create all needed directories for the duplicate database:

$ mkdir /u01/app/oracle/admin/PMMQA1
$ mkdir /u01/app/oracle/admin/PMMQA1/adump
$ mkdir /u01/app/oracle/admin/PMMQA1/bdump
$ mkdir /u01/app/oracle/admin/PMMQA1/cdump
$ mkdir /u01/app/oracle/admin/PMMQA1/create
$ mkdir /u01/app/oracle/admin/PMMQA1/pfile
$ mkdir /u01/app/oracle/admin/PMMQA1/scripts
$ mkdir /u01/app/oracle/admin/PMMQA1/udump
$ mkdir /u03/app/oradata/PMMQA1
$ mkdir /u04/app/oradata/PMMQA1
$ mkdir /u05/app/oradata/PMMQA1
$ mkdir /u06/app/oradata/PMMQA1
$ mkdir /u06/app/oradata/PMMQA1/archive

NOTE4: Mount point are example only, check all before clone.

$ export ORACLE_SID=PMMQA1
$ sqlplus “/ as sysdba”

SQL> startup nomount

Ensure Oracle Net Connectivity to Auxiliary Database from PMMPRD

$ sqlplus “sys/xxxxx@PMMQA1 as sysdba”

Login to Target and Auxiliary Database using RMAN
rman target sys/xxxxx@pmmprd.world catalog rman/xxxxx@recprd auxiliary sys/xxxxx@pmmqa1.world

Run the RMAN DUPLICATE DATABASE Command

RMAN> run
{
allocate auxiliary channel t1 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate auxiliary channel t2 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate auxiliary channel t3 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate auxiliary channel t4 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate auxiliary channel t5 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate auxiliary channel t6 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate auxiliary channel t7 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate auxiliary channel t8 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate auxiliary channel t9 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate auxiliary channel t10 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate auxiliary channel t11 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate auxiliary channel t12 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
duplicate target database to pmmqa1
logfile
group 1 (’/u01/oradata/PMMQA1/redo01a.log’,
‘/u03/oradata/PMMQA1/redo01b.log’) size 500M,
group 2 (’/u02/oradata/PMMQA1/redo02a.log’,
‘/u04/oradata/PMMQA1/redo02b.log’) size 500M,
group 3 (’/u01/oradata/PMMQA1/redo03a.log’,
‘/u03/oradata/PMMQA1/redo03b.log’) size 500M,
group 4 (’/u02/oradata/PMMQA1/redo04a.log’,
‘/u04/oradata/PMMQA1/redo04b.log’) size 500M;
}

RMAN> exit

If Recovery Fail

NOTE: There might be a possibility that the recover process fail because of missing arch logs that is trying to apply, if that happen you need to identify the logs missing and restore them to the PMMQA1 arch directory.
What I did was to log into PMMPRD restore the files there and copy over PMMQA1 and renamed the file.
After that I recover until cancel and open the database with resetlogs;

Export ORACLE_SID=PMMPRD
$ rman target sys/xxxxx@pmmprd.world catalog rman/xxxx@recprd

RMAN> run
{
allocate channel t1 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
allocate channel t2 type ‘SBT_TAPE’ parms ‘ENV=(NSR_SERVER=professor, NSR_CLIENT=pmmprod1), BLKSIZE=4194304′;
restore archivelog from logseq=29200 until logseq=29202 thread=1;
}

RMAN> exit

Trying manual recovery from sqlplus session.

Export ORACLE_SID=PMMQA1
$ sqlplus ‘/as sysdba’
SQL> recover database using backup controlfile until cancel;

Oracle will then prompt for a particular change for a particular thread. This information is obtained from the datafile headers. For the first log Oracle is able to suggest a filename to apply, again using information from the datafile header (the log sequence number) plus information from the init.ora parameter file (the log archive destination and format).

Example

ORA-00279: Change 4848 generated at 10/26/94 12:36:09 needed for thread 1
ORA-00289: Suggestion : $1$DUA15:[RGILL.DB.DB_RON.ARCH]T0001S0000000030.ARC
ORA-00280: Change 4848 for thread 1 is in sequence #30
Specify log: {=suggested | filename | AUTO | FROM logsource | CANCEL}

SQL> — to accept the suggestion.

Let Oracle apply all the available archived logfiles.

Example

ORA-00279: Change 4977 generated at 10/26/94 13:34:13 needed for thread 1
ORA-00289: Suggestion : $1$DUA15:[RGILL.DB.DB_RON.ARCH]T0001S0000000031.ARC
ORA-00280: Change 4977 for thread 1 is in sequence #31
ORA-00278: Logfile ‘$1$DUA15:[RGILL.DB.DB_RON.ARCH]T0001S0000000030.ARC’ no
longer needed for this recovery
Specify log: {=suggested | filename | AUTO | FROM logsource | CANCEL}

SQL> AUTO
Open the database, using the RESETLOGS option.

SQL> alter database open resetlogs;
Create All tempfiles for Temporary Tablespace

$ export ORACLE_SID=PMMQA1
$ sqlplus “/ as sysdba”

SQL> alter tablespace temp2
add TEMPFILE ‘/u14/oradata/PMMQA1/TEMP2_01.dbf’ SIZE 10000M AUTOEXTEND OFF,
‘/u14/oradata/PMMQA1/TEMP2_02.dbf’ SIZE 6000M AUTOEXTEND OFF;


Posted in Backup & Recovery, General DBA | No Comments »
How to create a NetApp FlexClone RAC Database

Posted by Alex Lima on February 7, 2008

In the past I used these procedures to create a FlexClone Oracle RAC database.

Mainly for refreshing a data-warehouse from production.

1- Create pfile from spfile production
create pfile=’/home/oracle/create_clone/initCDWD.ora’ from spfile;
create pfile=’/home/oracle/create_clone/initCDWQ.ora’ from spfile;
2- Create backup control file to trace
alter database backup controlfile to trace as ‘/home/oracle/backup_controlfile/ct_CDWP_resetlogs_151106.sql’ resetlogs; (WE USE THIS ONE FOR FLEX CLONE)
3- Hot Backup Begin
4- Take NetApp snapshot
5- Hot Backup End
6- Perform a Log Switch on Instance 1
7- Perform a Log Switch on Instance 2
8- Wait for 60 seconds so ARC process finishes writing the arc file
9- Take a snapshot of Arch Logs file system
10- Create a Flex Clone file system from the SnapShot
11- Edit pfile created in the step 1 with all the instances changes and cluster_database=false parameter set (Very Important)
*.remote_listener=’LISTENERS_CDWP’ Keep as is…
12- Edit the control file created on step 2 with all new file locations, database name and etc.
13- Create all directories for udump, bdump and etc (IN ALL NODES)
14- Set ORACLE_SID
15- Create password file
16- Create soft links of password file and pfile
17- Delete control files from the FlexClone
18- Startup nomount
19- Create control file from backup controfile
20- recover database using backup controlfile until cancel;
recover database using backup controlfile until cancel;
21- Apply all arch logs from both instances. (this is one of the trick part as SCN can be on either node and only the actual control file knows that, since we have created the new control file in the step 16 all history information were lost), ALSO make sure to change the arch log file name.
22- Add ADD LOGFILE THREAD 2 (Make sure do this before OPEN)
23- Alter database open resetlogs
24- Add TEMP files
25- Shutdown database
26- Edit pfile and set cluster_database=true
27- Startup instance 1
28- Add database to CRS with srvctl
srvctl add database -d -o $ORACLE_HOME
29- Add instance 1 to CRS
srvctl add instance -d -i
30- Add instance 2 to CRS
srvctl add instance -d -i
31- Copy pfile to node 2
32- Login to calrac02 and start up instance 2
33- Create spfile in a share drive
34- Edit the pfile on both nodes to point to this spfile in the share drive
35- Shutdown the database with srvctl stop database –d (Database Name)
36- Startup the database with srvctl start database –d (Database Name)
37- Change all the passwords
38- Change all the DB LINKS

Posted in General DBA | No Comments »
How to resolve user locking problems

Posted by Alex Lima on February 7, 2008

Method 1: Check and remove session locking other from Database

Find the USER locking others
Select a.serial#, a.sid, a.username, b.id1, c.sql_text
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and c.hash_value = a.sql_hash_value
and b.request = 0;

Output:

USER SERIAL# SID USERNAME ID1 SQL_TEXT
13 23 ALIMA 393242 update authuser.emp set salary=1000

To Kill session
SQL>alter system kill session ’23,13’;
Session Killed.
Method 2: Check and remove by process id from Unix session, this query give you more details about the locking problem

To Kill session

On the Unix box
$ps -ef|grep username (i.e. alima)

alima 12102 12083 0 10:55:14 pts/3 20:98 sqlplus
alima 13011 12078 0 9:25:01 pts/5 0.12 -ksh

Process id is the first number column, determine which process id by matching the number

i.e. the process id to be killed is 12102
$kill -15 12102
$ps -ef|grep 12102

If the process id do not die
$kill -9 12102

Posted in General DBA | No Comments »
Enabling Flashback Database

Posted by Alex Lima on February 7, 2008

The Folowing are the requirments for enabling Flashback Database:

- Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
- You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
- For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.

To enable logging for Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. Follow the process outlined here:

Start SQL*Plus and ensure that the database is mounted, but not open. For example:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

By default DB_FLASHBACK_RETENTION_TARGET is set to one day (1440 minutes).

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=536870912;

Enable the Flashback Database feature for the whole database:

SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
——————
YES

Posted in General DBA | No Comments »
Change from Rollback Segments 8i to UNDO in 9i/10g

Posted by Alex Lima on February 7, 2008

Created UNDO tablespace

CREATE UNDO TABLESPACE undotbs
DATAFILE ‘/ora_data_1/GIST/oradata/undotbs_01.dbf’
SIZE 100M REUSE AUTOEXTEND ON;

ALTER DATABASE DATAFILE ‘/ora_data_1/GIST/oradata/undotbs_01.dbf’ RESIZE 3000M;

shutdown immediate;

Changed UNDO parameters in the init file

Make sure COMPATIBLE=9.0.2 in the init.ora file is set.
### ROLLBACK SEGMENTS ###
undo_management = AUTO
undo_tablespace = UNDOTBS
undo_retention = 1800

Comment out RBS parameters in init file

# rollback_segments = (r01,r02,r03,r04,r05)

startup;

Check undo and rollback parameters:

SQL> show undo
SQL> show rollback

Drop RBS tablespace

DROP TABLESPACE rbs INCLUDING CONTENTS CASCADE CONSTRAINTS;