RMAN-06059: expected archived log not found

If rman unable to find archivelog run crosscheck

change archivelog all crosscheck;

Recreate controlfile - change oracle database sid

From sqlplus
alter database backup controlfile to trace;


From trace (trace location you can find in alert_log)

#change reuse to set and remove controlfile

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DM02DEV" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/s_ora01dmdev/oradata/dm02dev/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/data/s_ora01dmdev/oradata/dm02dev/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/data/s_ora01dmdev/oradata/dm02dev/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data/s_ora01dmdev/oradata/dm02dev/system01.dbf',
  '/data/s_ora01dmdev/oradata/dm02dev/sysaux01.dbf',
  '/data/s_ora01dmdev/oradata/dm02dev/undotbs01.dbf',
  '/data/s_ora01dmdev/oradata/dm02dev/users01.dbf'
CHARACTER SET AL32UTF8
;


ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/data/s_ora01dmdev/oradata/dm02dev/temp01.dbf'
     SIZE 39845888  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Rman backup script

#!/bin/bash

export ORACLE_SID=orcl_1
export ORACLE_BASE=/u01/app/oracle
export PATH=/u01/app/oracle/database/product/11.2.0/dbhome_1/bin:/u01/app/11.2.0/grid/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
export ORACLE_HOME=/u01/app/oracle/database/product/11.2.0/dbhome_1

basedir=/u01/app/oracle/backup
host=`hostname`
backup_dir=`date '+%Y.%m.%d'`

filename=`date '+%Y.%m.%d_%H.%M.%S'`

test -d ${basedir}/${host}/${backup_dir} || mkdir -p ${basedir}/${host}/${backup_dir}


if [ "x$1" = "x0" -o "x$1" = "x1" ]; then
inclevel=$1

rman target / log=${basedir}/${host}/${backup_dir}/backup_${filename}.log << !
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup as compressed backupset incremental level ${inclevel} database format '${basedir}/${host}/${backup_dir}/data_lv${inclevel}_${filename}.%U';
backup as compressed backupset archivelog all delete all input format '${basedir}/${host}/${backup_dir}/arch_${filename}.%U';
backup as compressed backupset current controlfile format '${basedir}/${host}/${backup_dir}/control_${filename}.%U';
backup spfile format '${basedir}/${host}/${backup_dir}/spfile_${filename}.%U';
delete noprompt obsolete recovery window of 7 days;
}
!

else

rman target / log=${basedir}/${host}/${backup_dir}/backup_${filename}.log << !
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup as compressed backupset archivelog all delete all input format '${basedir}/${host}/${backup_dir}/arch_${filename}.%U';
backup as compressed backupset current controlfile format '${basedir}/${host}/${backup_dir}/control_${filename}.%U';
backup spfile format '${basedir}/${host}/${backup_dir}/spfile_${filename}.%U';
delete noprompt obsolete recovery window of 7 days;
}
!

fi

Size oracle object in system schema

select owner,segment_name,segment_type ,bytes/(1024*1024) size_m from dba_segments where tablespace_name = 'SYSTEM' and bytes/(1024*1024) >1 order by size_m desc;

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;

Wylistowanie userów i ich dropowanie

select 'drop user ' || username || ' cascade; ' FROM DBA_USERS;

odświeżenie statystyk na bazie danych oracle

dbms_utility.analyze_schema(schema=>'INSIS',method=>'ESTIMATE',estimate_percent=>5);

Odblokowanie statystyk na tabelach oracle'a

select 'exec DBMS_STATS.UNLOCK_TABLE_STATS(''' || owner ||''' ,''' || table_name || '''); ' from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

Czyszczenie REDO logów

run {
crosscheck archivelog all;
delete expired archivelog all;
}

run {
BACKUP ARCHIVELOG ALL;
DELETE COPY OF ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
}

run {
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE
'sysdate-10/24'; ---kasuje wszystkie z przed 13h
}

uruchomienie RMANa

rman TARGET sys/

Oznaczenie w rmanie przeterminowanych redo logów na sysdate -1

set serveroutput on;

spool /tmp/output_del_logs.txt

declare CURSOR cur IS
SELECT a.name
FROM v$asm_alias a, v$asm_file b
where a.group_number = b.group_number
and b.creation_date < sysdate-1
and a.file_number = b.file_number
and b.type='ARCHIVELOG'
and a.name like '%.arc'
order by a.name;

sqlstr VARCHAR2(250);

BEGIN
FOR cur_rec IN cur
LOOP
sqlstr := 'alter diskgroup ASM_BACKUP drop file ''+ASM_BACKUP/DB1/' || cur_rec.name || '''';
DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
END LOOP;
END;
/

spool off;
exit