Enable backup mode for all database files
1- Enable backup on entire database :
SQL> alter database begin backup;
Database altered.
2- Check that all file id status be in "ACTIVE"
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 2.6814E+11 22-SEP-15
2 ACTIVE 2.6814E+11 22-SEP-15
3 ACTIVE 2.6814E+11 22-SEP-15
4 ACTIVE 2.6814E+11 22-SEP-15
5 ACTIVE 2.6814E+11 22-SEP-15
6 ACTIVE 2.6814E+11 22-SEP-15
7 ACTIVE 2.6814E+11 22-SEP-15
8 ACTIVE 2.6814E+11 22-SEP-15
9 ACTIVE 2.6814E+11 22-SEP-15
10 ACTIVE 2.6814E+11 22-SEP-15
11 ACTIVE 2.6814E+11 22-SEP-15
3- For disable backup on the entire database :
SQL> alter database end backup;
Database altered.
4- Check that all file id status be in "NOT ACTIVE".
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 2.6814E+11 22-SEP-15
2 NOT ACTIVE 2.6814E+11 22-SEP-15
3 NOT ACTIVE 2.6814E+11 22-SEP-15
4 NOT ACTIVE 2.6814E+11 22-SEP-15
5 NOT ACTIVE 2.6814E+11 22-SEP-15
6 NOT ACTIVE 2.6814E+11 22-SEP-15
7 NOT ACTIVE 2.6814E+11 22-SEP-15
8 NOT ACTIVE 2.6814E+11 22-SEP-15
9 NOT ACTIVE 2.6814E+11 22-SEP-15
10 NOT ACTIVE 2.6814E+11 22-SEP-15
11 NOT ACTIVE 2.6814E+11 22-SEP-15
Enable backup mode for tablespace
1- Check the tablespace that you want to set the backup mode.
set linesize 300
set pagesize 300
col FILE_NAME format a60
col file_id format 999999
col tablespace_name format a25
select FILE_NAME,file_id,tablespace_name
from dba_data_files
where tablespace_name ='UNDOTBS1'
group by FILE_NAME,file_id,tablespace_name;
FILE_NAME FILE_ID TABLESPACE_NAME
------------------------------------------------------------ ------- -------------------------
/data1/oracle11/orapae11/undotbs01.dbf 3 UNDOTBS1
2- In this case, we will set the UNDOTBS1 tablespace in backup mode :
SQL> alter tablespace UNDOTBS1 begin backup;
Tablespace altered.
3- Check the status for all tablespace :
select *
from v$backup
where status='ACTIVE';
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
3 ACTIVE 2.6814E+11 22-SEP-15
See that file_id 3 that belongs to UNDOTBS1 is in backup mode.
4- Disable the tablespace of backup mode.
SQL> alter tablespace UNDOTBS1 end backup;
Tablespace altered.
5- Finally check the status of all tablespaces.
select *
from v$backup
where status='ACTIVE';
no rows selected
Disable database archive log
1- shutdown database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2- mount database
SQL> startup mount
ORACLE instance started.
Total System Global Area 6499344384 bytes
Fixed Size 2171376 bytes
Variable Size 3271564816 bytes
Database Buffers 3204448256 bytes
Redo Buffers 21159936 bytes
Database mounted.
3- Disable archive log mode.
SQL> alter database noarchivelog;
Database altered.
4- Open the database.
SQL> alter database open;
Database altered.
5- Disable the automatic archiving.
SQL> alter system archive log stop;
System altered.
6- Check the archive log deactivation.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/TEST/oraarch/
Oldest online log sequence 1
Current log sequence 4
Enable database archive log
1- Check if log_archive_dest_1 parameter does have a location:
show parameter show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=/oracle/TEST/oraarch/
2- shutdown immediate
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
3- Mount the database.
SQL> startup mount
ORACLE instance started.
Total System Global Area 6499344384 bytes
Fixed Size 2171376 bytes
Variable Size 3271564816 bytes
Database Buffers 3204448256 bytes
Redo Buffers 21159936 bytes
Database mounted.
4- Enable archive log mode.
SQL> alter database noarchivelog;
Database altered.
5- Open the database.
SQL> alter database open;
Database altered.
6- Active the automatic archiving
SQL> alter system archive log start;
System altered.
7- Check if archive log is enabled.
How to identify all the Corrupted Objects in the Database with RMAN
1- Clean the v$backup_corruption and v$copy_corruption views.
SQL> execute dbms_backup_restore.resetCfileSection(17);
PL/SQL procedure successfully completed.
SQL> execute dbms_backup_restore.resetCfileSection(18);
PL/SQL procedure successfully completed.
2- Export environtment variables (ORACLE_HOME, ORACLE_SID and PATH)
3- Perform rman scripts (This command may take many hours, depending on the speed of the hardware and the database volume)
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
backup check logical validate database;
release channel d1;
release channel d2;
}
V$DATABASE_BLOCK_CORRUPTION is updated with the corrupt blocks. In 11g RMAN generates a trace file with the details of the corruption description. Example:
RMAN VALIDATE screen output:
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 FAILED 0 501 640 1950088
File Name: /oracle/dbs/users.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 9 9
Index 0 0
Other 0 130
validate found one or more corrupt blocks
See trace file /oracle/log/diag/rdbms/orcl/orcl/trace/orcl_ora_28424.trc for details
Finished validate at <Date>
4- The trace file has output with the corruption description. This is an example for two of the corrupt blocks; one Physical corrupt block (file 6 block 9) one Logical corrupt block (file 6 block 10) respectively.
Corrupt block relative dba: 0x01000009 (file 4, block 9)
Bad check value found during validation
Data in bad block:
type: 16 format: 2 rdba: 0x01000009
last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x000010ff
check value in block header: 0xb4e0
computed block checksum: 0xa800
Reread of blocknum=9, file=/oracle/dbs/users.dbf found same corrupt data
Block Checking: DBA = 25165834, Block Type = KTB-managed data block
data header at 0x2b2deb49e07c
kdbchk: fsbo(144) wrong, (hsz 78)
Error backing up file 6, block 10: logical corruption
5- Corrupt blocks are listed in the view V$DATABASE_BLOCK_CORRUPTION:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------- --------------- --------------- ------------------ ---------
6 10 1 8183236781662 LOGICAL
6 42 1 0 FRACTURED
6 34 2 0 CHECKSUM
6 50 1 8183236781952 LOGICAL
6 26 4 0 FRACTURED
6- The following query will identify the corrupts segments.
sqlplus / as sysdba
set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, corruption_type description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, corruption_type||' Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
An output example is:
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_ FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
----- ------------------ ------------ ---------- ----- ----------------- --------------- ---------------- -------------
SCOTT TABLE EMP 6 10 10 1
SCOTT TABLE PARTITION ORDER ORDER_JAN 6 26 28 3
6 29 29 1 Free Block
SCOTT TABLE BONUS 6 34 34 1
6 35 35 1 Free Block
SCOTT TABLE DEPT 6 42 42 1 Segment Header
SCOTT TABLE INVOICE 6 50 50 1
RMAN unregister database from catalog
1- Connect to catalog database :
$ORACLE_HOME/bin/rman target dbuser/password catalog catalog_dbuser/password_dbuser@db_catalog
2- RMAN> UNREGISTER DATABASE;
dbuser : Local database user.
catalog_dbuser : Catalog database user
db_catalog : Database catalog.
RMAN register database in catalog database
1- Connect to catalog database :
$ORACLE_HOME/bin/rman target dbuser/password catalog catalog_dbuser/password_dbuser@db_catalog
2- RMAN> REGISTER DATABASE;
dbuser : Local database user.
catalog_dbuser : Catalog database user
db_catalog : Database catalog.
Check rman status
There is several views to monitor the running jobs :
select sid,start_time,totalwork sofar, (sofar/totalwork) * 100 pct_done
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';
select sid,spid,client_info,event,seconds_in_wait,p1, p2, p3
from v$process p, v$session s
where p.addr = s.paddr
and client_info like 'rman channel=%';
set lines 120
column sid format 9999
column spid format 99999
column client_info format a25
column event format a30
column secs format 9999
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';
Example of catalog user creation in rman
1- Create the tablespace that will use rman user.
CREATE TABLESPACE TS_RMAN DATAFILE '/u01/oracle11/TEST/rman01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 10M MAXSIZE 4G
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
2- Create role that will use catalog user.
GRANT ALTER SESSION TO RECOVERY_CATALOG_OWNER;
GRANT CREATE CLUSTER TO RECOVERY_CATALOG_OWNER;
GRANT CREATE DATABASE LINK TO RECOVERY_CATALOG_OWNER;
GRANT CREATE PROCEDURE TO RECOVERY_CATALOG_OWNER;
GRANT CREATE SEQUENCE TO RECOVERY_CATALOG_OWNER;
GRANT CREATE SESSION TO RECOVERY_CATALOG_OWNER;
GRANT CREATE SYNONYM TO RECOVERY_CATALOG_OWNER;
GRANT CREATE TABLE TO RECOVERY_CATALOG_OWNER;
GRANT CREATE TRIGGER TO RECOVERY_CATALOG_OWNER;
GRANT CREATE VIEW TO RECOVERY_CATALOG_OWNER;
3- Create the catalog user with the corresponding privilegies.
CREATE USER RMAN_USER
IDENTIFIED BY <password>
DEFAULT TABLESPACE TS_RMAN
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for RMAN_USER
GRANT CONNECT TO RMAN_USER;
GRANT RECOVERY_CATALOG_OWNER TO RMAN_USER;
GRANT RESOURCE TO RMAN_USER;
ALTER USER RMAN_USER DEFAULT ROLE ALL;
-- 1 System Privilege for RMAN_USER
GRANT UNLIMITED TABLESPACE TO RMAN_USER;
-- 1 Tablespace Quota for RMAN_USER
ALTER USER RMAN_USER QUOTA UNLIMITED ON TS_RMAN;
Comments