RMAN delete archive log and cross check

ORA-00257: archiver error. Connect internal only, until freed.

then delete old archive log



RMAN delete archive log file


This will delete all the archive log before SYSDATE-2

RMAN> delete noprompt force archivelog until time 'SYSDATE-2';



Or

RMAN> delete force backup completed before 'sysdate - 1';


IF the archive log no longer there (delete) you need to do crosscheck to make RMAN know that this file is not available for backup anymore :


RMAN> crosscheck copy of archivelog all;




RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
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 CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/RMAN/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/app/oracle/product/11.2/dbs/snapcf_orcl.f'; # default

Oracle 10g RMAN DURATION

RMAN duration



RMAN> BACKUP DURATION 1:00 TABLESPACE anuj;

RMAN > BACKUP DURATION 3:00 DATABASE PLUS ARCHIVELOGS;


DURATION hh:mm -> Specifies a maximum time for a backup command to run , if backup not
complete upto this time , then RMAN backup will stop



RMAN> backup device type disk format='/home/oracle/RMAN/reportA%U.bkp' as compressed backupset duration 1:00 minimize load database;



to check RMAN BACKUP

select message,time_remaining from v$session_longops where sid=XXX and sofar<>totalwork;



[PARTIAL][MINIMIZE (TIME|LOAD)] -> With disk backups, you can use MINIMIZE TIME run the backup at maximum speed (default)

MINIMIZE LOAD to slow the rate of backup to lessen the load on the system. With MINIMIZE LOAD the backup will take the full specified duration.

Oracle RMAN backup script

RMAN Script



RMAN> CREATE GLOBAL SCRIPT full_backup FROM FILE 'full_backup.txt';

starting full resync of recovery catalog
full resync complete
script commands will be loaded from file full_backup.txt
created global script full_backup



RMAN> PRINT GLOBAL SCRIPT full_backup;

printing stored global script: full_backup
CREATE GLOBAL SCRIPT full_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE FORCE NOPROMPT OBSOLETE;
}



RMAN> LIST GLOBAL SCRIPT NAMES;

List of Stored Scripts in Recovery Catalog


Global Scripts


Script Name
Description
-----------------------------------------------------------------------
full_backup


RMAN> LIST ALL SCRIPT NAMES; # Global and local scripts.


List of Stored Scripts in Recovery Catalog


Global Scripts


Script Name
Description
-----------------------------------------------------------------------
full_backup





REPLACE GLOBAL SCRIPT full_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE FORCE NOPROMPT OBSOLETE;
}

REPLACE GLOBAL SCRIPT full_backup FROM FILE 'full_backup.txt';

DELETE GLOBAL SCRIPT 'full_backup';


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

in full_backup.txt

REPLACE GLOBAL SCRIPT full_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE FORCE NOPROMPT OBSOLETE;
}



RMAN> REPLACE GLOBAL SCRIPT full_backup FROM FILE 'full_backup.txt';

script commands will be loaded from file full_backup.txt
replaced global script full_backup




RMAN> RUN { EXECUTE GLOBAL SCRIPT full_backup; }

executing global script: full_backup

replaced global script full_backup




RMAN> RESTORE DATABASE PREVIEW SUMMARY;

Starting restore at 04-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK

datafile 10 will be created automatically during restore operation

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
403 B F A DISK 30-JUN-11 1 1 NO TAG20110630T154709
using channel ORA_DISK_1

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
401 1 503 A 30-JUN-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_07_03/o1_mf_1_503_7105ld5k_.arc

Media recovery start SCN is 11182696
Recovery must be done beyond SCN 11488234 to clear datafile fuzziness
Finished restore at 04-JUL-11


RMAN> RESTORE TABLESPACE users PREVIEW SUMMARY;

Starting restore at 04-JUL-11
using channel ORA_DISK_1


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
403 B F A DISK 30-JUN-11 1 1 NO TAG20110630T154709

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
401 1 503 A 30-JUN-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_07_03/o1_mf_1_503_7105ld5k_.arc

Media recovery start SCN is 11182696
Recovery must be done beyond SCN 11182696 to clear datafile fuzziness
Finished restore at 04-JUL-11







RMAN> RESTORE DATABASE PREVIEW;

Starting restore at 04-JUL-11
using channel ORA_DISK_1

datafile 10 will be created automatically during restore operation

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
403 Full 1.62G DISK 00:03:09 30-JUN-11
BP Key: 407 Status: AVAILABLE Compressed: NO Tag: TAG20110630T154709
Piece Name: /home/oracle/RMAN/full_0fmg6kvu_1_1.bak
List of Datafiles in backup set 403
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 11182696 30-JUN-11 /opt/app/oracle/oradata/orcl/system01.dbf
2 Full 11182696 30-JUN-11 /opt/app/oracle/oradata/orcl/sysaux01.dbf
3 Full 11182696 30-JUN-11 /opt/app/oracle/oradata/orcl/undotbs01.dbf
4 Full 11182696 30-JUN-11 /opt/app/oracle/oradata/orcl/users01.dbf
5 Full 11182696 30-JUN-11 /opt/app/oracle/oradata/orcl/example01.dbf
6 Full 11182696 30-JUN-11 /opt/app/oracle/oradata/orcl/anujtest.dbf
7 Full 11182696 30-JUN-11 /opt/app/oracle/oradata/orcl/tsapexf01.dbf
8 Full 11182696 30-JUN-11 /opt/app/oracle/oradata/orcl/tsapexu01.dbf
9 Full 11182696 30-JUN-11 /opt/app/oracle/oradata/orcl/test.dbf

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
401 1 503 A 30-JUN-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_07_03/o1_mf_1_503_7105ld5k_.arc

Media recovery start SCN is 11182696
Recovery must be done beyond SCN 11488234 to clear datafile fuzziness
Finished restore at 04-JUL-11

RMAN> RESTORE TABLESPACE users PREVIEW;

Starting restore at 04-JUL-11
using channel ORA_DISK_1


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
403 Full 1.62G DISK 00:03:09 30-JUN-11
BP Key: 407 Status: AVAILABLE Compressed: NO Tag: TAG20110630T154709
Piece Name: /home/oracle/RMAN/full_0fmg6kvu_1_1.bak
List of Datafiles in backup set 403
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 11182696 30-JUN-11 /opt/app/oracle/oradata/orcl/users01.dbf

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
401 1 503 A 30-JUN-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_07_03/o1_mf_1_503_7105ld5k_.arc

Media recovery start SCN is 11182696
Recovery must be done beyond SCN 11182696 to clear datafile fuzziness
Finished restore at 04-JUL-11

Oracle RMAN Create RMAN catalog on oracle 11g

Create rman catalog




Create 11g rman catalog

create tablespace rman datafile '/opt/app/oracle/oradata/orcl/rman.dbf' size 50m;

create user rman identified by rman DEFAULT TABLESPACE rman QUOTA UNLIMITED ON rman;


SQL> create tablespace rman datafile '/opt/app/oracle/oradata/orcl/rman.dbf' size 50m;

Tablespace created.


SQL> create user rman identified by rman DEFAULT TABLESPACE rman QUOTA UNLIMITED ON rman;

User created.



SQL> GRANT RECOVERY_CATALOG_OWNER TO rman ;

Grant succeeded.


on same server

oracle@apt-amd-02:~> rman catalog rman/rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 4 12:09:51 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to recovery catalog database



RMAN> CREATE CATALOG;

recovery catalog created


or


oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> rman catalog rman/rman@anuj

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 4 12:23:02 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to recovery catalog database

RMAN>




Recovery Manager complete.
oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> rman target / catalog rman/rman@anuj

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 4 12:25:16 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1267852645)
connected to recovery catalog database




RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/RMAN/%U';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/RMAN/%U';
new RMAN configuration parameters are successfully stored


RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> backup database plus archivelog;

RMAN> backup database plus archivelog DELETE ALL INPUT;

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 4;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored


RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 4
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
no obsolete backups found

Oracle sample table

Create sample table and loop


declare
i number := 0;
cursor s1 is SELECT * FROM tab1 WHERE col1 = 'value1' FOR UPDATE;

begin

for c1 in s1 loop
update tab1 set col1 = 'value2'
WHERE current of s1;

i := i + 1; -- Commit after every X records X=1000
if i > 1000 then
commit;
i := 0;
end if;

end loop;
commit;
end;
/


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

CREATE TABLE TEST
(
ID NUMBER(10) NULL,
DATUM DATE NULL,
NAME VARCHAR2(10) NULL
);

declare
i number := 1000;
begin
while i>1 loop
insert into TEST
values (1, sysdate+i,'joop');

i := i - 1;
commit;

end loop;
commit;
end;
/

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

CREATE TABLE TEST2
(
i number NULL,
ID NUMBER(10) NULL,
DATUM DATE NULL,
DAG VARCHAR2(10) NULL,
NAME VARCHAR2(10) NULL
);

declare
i number := 1;
j date;
k varchar2(10);
begin
while i<1000000 loop
j:=sysdate+i;
k:=TO_CHAR(SYSDATE+i,'DAY');
insert into TEST2
values (i,1, j, k,'joop');

i := i + 1;
commit;

end loop;
commit;
end;
/


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

CREATE TABLE TEST3
(
ID NUMBER(10) NULL,
DATUM DATE NULL,
DAG VARCHAR2(10) NULL,
VORIG VARCHAR2(10) NULL,
NAME VARCHAR2(10) NULL
);


declare
i number := 1;
j date;
k varchar2(10);
l varchar2(10);
begin
while i<1000 loop
j:=sysdate+i;
k:=TO_CHAR(SYSDATE+i,'DAY');
l:=TO_CHAR(SYSDATE+i-1,'DAY');
insert into TEST3
(ID,DATUM,DAG,VORIG,NAME)
values (i, j, k, l,'joop');

i := i + 1;
commit;

end loop;
commit;
end;
/

from
http://www.akadia.com/services/ora_build_big_tables.html

An «ALL_OBJECTS» Table with 1'000'000 Rows

The following Code is from Tom Kyte (http://asktom.oracle.com)

-- Create Table with same structure as ALL_TABLES from Oracle Dictionary
create table bigtab
as
select rownum id, a.*
from all_objects a
where 1=0;
alter table bigtab nologging;

-- Fill 1'000'000 Rows into the Table
declare
l_cnt number;
l_rows number := 1000000;
begin
-- Copy ALL_OBJECTS
insert /*+ append */
into bigtab
select rownum, a.*
from all_objects a;
l_cnt := sql%rowcount;
commit;

-- Generate Rows
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into bigtab
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from bigtab
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/

alter table bigtab add constraint
bigtab_pk primary key(id);

A Table with Random Data and same Size as ALL_OBJECTS

CREATE TABLE bigtab (
id NUMBER,
weight NUMBER,
adate DATE
);

INSERT INTO bigtab (id, weight, adate)
SELECT MOD(ROWNUM,1000),
DBMS_RANDOM.RANDOM,
SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
FROM all_objects
/
51502 rows created.

A Table which can be used for Partition Tests

The ID of the table can be used for Range Partitioning

create table bigtab (
id number(12,6),
v1 varchar2(10),
padding varchar2(50)
)
nologging -- just to save a bit of time
/

insert /*+ append ordered full(s1) use_nl(s2) */
into bigtab
select
3000 + trunc((rownum-1)/500,6),
to_char(rownum),
rpad('x',50,'x')
from
all_objects s1, -- you’ll need the privilege
all_objects s2
where
rownum <= 1000000
/
commit;

ID V1 PADDING
---------- ---------- --------------------------------------------------
3000 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.002 2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.004 3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.006 4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.008 5 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.01 6 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.012 7 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.014 8 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.016 9 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

A Table with Date's which can be used for Partition Tests

This code is from http://www.oracle-base.com

CREATE TABLE bigtab (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
);

DECLARE
l_lookup_id NUMBER(10);
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;

INSERT INTO bigtab (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
END LOOP;
COMMIT;
END;
/

SQL> select id,to_char(created_date,'DD.MM.YYYY'),
lookup_id, data
from bigtab where rownum < 10;

ID TO_CHAR(CR LOOKUP_ID DATA
---------- ---------- ---------- -----------------------------
1 21.08.2007 3 This is some data for 1
2 21.08.2006 1 This is some data for 2
3 21.08.2005 2 This is some data for 3
4 21.08.2006 1 This is some data for 4
5 21.08.2007 3 This is some data for 5
6 21.08.2005 2 This is some data for 6
7 21.08.2007 3 This is some data for 7
8 21.08.2006 1 This is some data for 8
9 21.08.2005 2 This is some data for 9