Oracle tips 2

Oracle ASM requiered packages

oracleasm-2.6.18-194.el5xen-2.0.5-1.el5.x86_64.rpm
oracleasmlib-2.0.4-1.el5.x86_64.rpm
oracleasm-support-2.1.7-1.el5.x86_64.rpm

Configuration disk for ASM
/etc/init.d/oracleasm configure
/etc/init.d/oracleasm createdisk data /dev/xvdb1

Connecting to asm from SQLPLUS
sqlplus / as sysasm

Checking ASM Groups
select g.name, d.path
from v$asm_Diskgroup g, v$asm_disk d
where g.GROUP_NUMBER=d.GROUP_NUMBER;


RMAN

Changing location of files
catalog start with '+DATA';

Checking archievelog
crosscheck archivelog all;
delete expired archivelog all;

Checking all backups
RMAN> run {
crosscheck backupset;
delete expired backupset;
}


Checking database to copy
switch database to copy;


Restoring controlfile:
restore controlfile from '/u01/app/oracle/backup/control_2012.03.28_10.44.23.0un70f5n_1_1';

Select for create new name of files
select 'set newname for datafile ' || file# || ' to ''+DATA'';' as newname
from v$datafile

Saving result to file
SYS:orclasm > save newname

Adding service name to database
alter system
set service_names='orclasm','orcl'
scope=both;

Adding listerner to database configuration
alter system
set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=10.8.2.2)(PORT=1523))'
scope=both;

Change database unique name
alter system
set db_unique_name='orclasm'
scope=spfile;

Database restart
startup force mount


Package for undo in SQLPLUS
rlwrap-0.37-1.el5.x86_64.rpm
alias sqlplus='rlwrap -r sqlplus'
alias rman='rlwrap -r rman'


Calculating from console
echo "2147483648/1024/1024" | bc

Checking system page_size
getconf PAGE_SIZE

Recompilation binaries - turning of options
make -f ins_rdbms.mk rac_off ioracle


Opening database after unclean recovery
alter database open resetlogs;

Oracle tips

Checking from with group is runinnig instance
ORACLE_HOME\lib\config.c

Creating pfile from spfile
from sqlplus connected
create pfile from spfile

Creating instance - prepare spfile and may run database to nomount

Repair database though RMAN:
LIST FAILURE;
ADVISE FAILURE;
REPAIR FAILURE;

RMAN command
report schema
list copy of database;

catalog start with '/u01/oradata'; - changing database location


Recompiling oracle binareies without rack option
make -f $ORACLE_HOME/rdbms/ins_rdbms.mk rac_off ioracle

Relinking database
from $ORACLE_HOME/rdbms relink all

Editor from sqlplus
SQL> define _Editor=vim
SQL> ed