Friday, November 3, 2017

READ ONLY Tablespace Restore and Recovery

Please refer


http://gavinsoorma.com/2009/08/read-only-tablespace-restore-and-recovery/


READ ONLY Tablespace Restore and Recovery

Keeping static or historical data in read only tablespaces is a good practice especially for data warehouse type environments.
Using the RMAN SKIP READONLY command, we can reduce the backup window and overhead as well by excluding these read only tablespaces from the database backupsets.
But we need to keep in mind that we need to take at least one backup of the tablespace after it has been made read only and thereafter we can use the SKIP READONLY command to exclude these tablespaces from the daily or weekly database backups.
However, while doing a restore we need to use the CHECK READONLY keywords otherwise by default the read only tablespaces will not be restored and hence the recovery will also bypass these tablespaces. Subssequent attempts to open the database will fail.
Let us illustrate the same with an example where we have made the USERS tablespace read only and then simulated a media failure by deleting the three datafiles of the USERS tablespace at the OS level.
We will first attempt a normal restore and recovery and see how that fails. We follow that by using the CHECK READONLY keywords and see how the restore and recovery succeeds.
 restore database;

Starting restore at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

datafile 4 not processed because file is read-only
datafile 6 not processed because file is read-only
datafile 9 not processed because file is read-only
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/testdb/system01.dbf
restoring datafile 00002 to /u02/oradata/testdb/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/testdb/sysaux01.dbf
restoring datafile 00005 to /u02/oradata/testdb/example01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 08kmb8f7_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=08kmb8f7_1_1 tag=TAG20090810T120039
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:10:36
Finished restore at 12-AUG-09

RMAN> recover database;

Starting recover at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
datafile 4 not processed because file is read-only
datafile 6 not processed because file is read-only
datafile 9 not processed because file is read-only
channel ORA_SBT_TAPE_1: starting incremental datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/testdb/system01.dbf
.....
.......

archive log filename=/u02/oradata/testdb/arch/arch.36.1.694170424.log thread=1 sequence=36
archive log filename=/u02/oradata/testdb/arch/arch.37.1.694170424.log thread=1 sequence=37
archive log filename=/u02/oradata/testdb/arch/arch.38.1.694170424.log thread=1 sequence=38
media recovery complete, elapsed time: 00:00:43
Finished recover at 12-AUG-09

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/12/2009 12:55:30
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/testdb/users03.dbf'
Now, the read only tableaspaces are restored as well, but we notice the recovery stage will skip these tablespaces since no recovery is required as no changes have happened on these tablespaces since the last backup of these tablespaces after they were made read only.
RMAN> restore database check readonly;

Starting restore at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/testdb/system01.dbf
restoring datafile 00002 to /u02/oradata/testdb/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/testdb/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/testdb/users03.dbf
restoring datafile 00005 to /u02/oradata/testdb/example01.dbf
restoring datafile 00006 to /u02/oradata/testdb/users02.dbf
restoring datafile 00009 to /u02/oradata/testdb/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 08kmb8f7_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=08kmb8f7_1_1 tag=TAG20090810T120039
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:05:25
Finished restore at 12-AUG-09



RMAN> recover database;
Starting recover at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
datafile 4 not processed because file is read-only
datafile 6 not processed because file is read-only
datafile 9 not processed because file is read-only
channel ORA_SBT_TAPE_1: starting incremental datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/testdb/system01.dbf
destination for restore of datafile 00002: /u02/oradata/testdb/undotbs01.dbf
destination for restore of datafile 00003: /u02/oradata/testdb/sysaux01.dbf
destination for restore of datafile 00005: /u02/oradata/testdb/example01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0akmb8uu_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=0akmb8uu_1_1 tag=TAG20090810T120901
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:07

starting media recovery

archive log thread 1 sequence 36 is already on disk as file /u02/oradata/testdb/arch/arch.36.1.694170424.log
archive log thread 1 sequence 37 is already on disk as file /u02/oradata/testdb/arch/arch.37.1.694170424.log
archive log thread 1 sequence 38 is already on disk as file /u02/oradata/testdb/arch/arch.38.1.694170424.log
archive log thread 1 sequence 39 is already on disk as file /u02/oradata/testdb/arch/arch.39.1.694170424.log
archive log thread 1 sequence 40 is already on disk as file /u02/oradata/testdb/arch/arch.40.1.694170424.log
archive log filename=/u02/oradata/testdb/arch/arch.36.1.694170424.log thread=1 sequence=36
archive log filename=/u02/oradata/testdb/arch/arch.37.1.694170424.log thread=1 sequence=37
archive log filename=/u02/oradata/testdb/arch/arch.38.1.694170424.log thread=1 sequence=38
media recovery complete, elapsed time: 00:00:39
Finished recover at 12-AUG-09

RMAN> alter database open;

database opened
Posted by at No comments:

Long running SQL and verify database locks


CPU usage

 SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec" 
FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' -- CPU 
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

ORDER BY st.value;
SET ECHO OFF
COL HOURS          FORMAT 999,990.99
COL MESSAGE        FORMAT A22
COL OPNAME         FORMAT A16        HEA "OPERATION"
COL PCT_COMPLETE   FORMAT 99.9        HEA "PCT" 
COL SERIAL#        FORMAT 99999
COL SID            FORMAT 9999
COL STARTED        FORMAT A16        HEA "START TIME"
COL TARGET         FORMAT A16
COL TARGET_DESC    FORMAT A16
COL REMAIN_MIN     FORMAT 99,999,999
COL ELASPSED_MIN   FORMAT 99,999,999
COL UNITS          FORMAT A10
COL USERNAME       FORMAT A15
set linesize 120
-- SET ECHO ON
SELECT SID
--      ,SERIAL#
      ,USERNAME
      ,OPNAME
      ,TO_CHAR(START_TIME,'MM/DD HH24:MI:SS') STARTED
      , decode(totalwork, 0, 100,SOFAR/TOTALWORK*100)   PCT_COMPLETE
      ,ELAPSED_SECONDS/60       ELASPSED_MIN
--     ,TOTALWORK
--      ,UNITS
     ,TIME_REMAINING/60         REMAIN_MIN
   FROM gV$SESSION_LONGOPS
   WHERE  USERNAME != 'SYS'
--    and SOFAR/TOTALWORK*100 !=100
   and  TIME_REMAINING/60 !=0 
   ORDER BY ELAPSED_SECONDS DESC
;



Jobs

set lines 130
col LOG_USER from a15
select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,LAST_DATE,NEXT_DATE,BROKEN from dba_jobs;
SELECT JOB,WHAT,BROKEN,TO_CHAR(NEXT_DATE,'YY-MM-DD HH24:MI:SS') FROM DBA_JOBS;

lock

SELECT a.serial# as serial,a.sid,a.username,b.type,b.ctime,lmode,a.osuser,c.sql_text
FROM v$session a,v$lock b, v$sqlarea c
WHERE b.type in ('TM','TX','UL') and a.sid=b.sid and lmode > 0 and ((a.PREV_HASH_VALUE = c.hash_value and a.prev_sql_addr = c.address and a.sql_hash_value = 0) or (c.hash_value=a.sql_hash_value and c.address = a.sql_address))
;



sleep 30
SELECT job_name, job_class, operation, status, to_char(log_date, 'Dy DD-Mon-YYYY HH24:MI:SS')  FROM DBA_SCHEDULER_JOB_LOG where log_date>=sysdate-1 and owner in('XXX','XXX') and status!='SUCCEEDED';
Posted by at No comments:
Subscribe to: Posts (Atom)

About Me

My photo
Sydney, NSW, Australia
An experienced IT professional (14+ years) worked in multiple industries such as consulting, education, financial, retail sectors. Carries good work experience in relational database design, analysis, modeling, development, administration, implementation, trouble shooting, support etc. Experienced in Oracle/SQL Server/MySQL DBA involving setup, configuration, tuning, backups, disaster recovery, high availability Oracle 11g/12C/19C RAC clusters; SQL server 2008/2012/2016 clusters, Oracle Engineered Systems such as EXADATA, ODA and Oracle and Azure Cloud. Performed Software Installations, Migrations, Database Capacity Planning, Automation of backup implementation, Cloud migration Tuning Oracle in windows/Unix platforms. In addition, experienced in UNIX administration, Shell scripting. PowerShell scripting A team player with communication skills.