alter tablespace TEMP shrink tempfile '/data/oracle/PRODDB01/datafiles/temp_20.dbf' KEEP 8G;
This feature was introduced in Oracle 11g.
Friday, January 24, 2014
How to create a temporary tablespace and assign it as the database default
CREATE TEMPORARY TABLESPACE TMP TEMPFILE '/data/oracle/u01/PRODDB01/datafile/tmp_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;
Tuesday, January 21, 2014
How to restore a sequence of archivelogs back to their original location
connect catalog catowner/*********@RMANCAT
connect target
run {
allocate channel t1 type sbt PARMS="BLKSIZE=1048576" maxopenfiles 64;
allocate channel t2 type sbt PARMS="BLKSIZE=1048576" maxopenfiles 64;
send 'NB_ORA_POLICY=ora_dbserver1_00_netwzone1,NB_ORA_CLIENT=dbserver1-bkp.mydomain.com,NB_ORA_SCHED=ora_dbserver1_00_netwzone1_user';
restore archivelog from logseq 412594 until logseq 412656;
release channel t1;
release channel t2;
}
SQL: standby database information
This script is validated against Oracle 11g.
Run this script on the standby database to obtain basic information about the database state and the managed recovery processes:
set linesize 300
set pagesize 100
col "controlfile type" format a30
col "Unique name" format a12
col "Open Mode" format a12
col "Flashback on" format a15
col "Protection Mode" format a30
col "Current SCN" format 9999999999999
col "Database Role" format a20
col "Log Mode" format a20
col "DG broker" format a20
col "Destination name" format a20
col "Destination" format a20
col member format a70
col type format a20
col units format a20
col name format a15
set feedback off
alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:SS:MI';
Prompt
Prompt ======================================================================================================================
prompt General information:
select name,db_unique_name as "Unique name",created,open_mode as "Open Mode",flashback_on as "Flashback on",resetlogs_time as "Resetlogs time",current_scn as "Current SCN"
from v$database
/
Prompt ======================================================================================================================
prompt Standby database information, if relevant:
select database_role as "Database Role",log_mode as "Log Mode",controlfile_type as "Controlfile type",protection_mode as "Protection Mode"
from v$database
/
Prompt ======================================================================================================================
select dest_name as "Destination Name",status,type,database_mode,recovery_mode,destination,archived_seq#,applied_seq#
from v$archive_dest_status
where status <> 'INACTIVE'
/
Prompt ======================================================================================================================
prompt Is the MRP process running?
select process, status from v$managed_standby
where process like '%MRP%'
/
Prompt ======================================================================================================================
Prompt Are there standby redo logs configured?
select s.group#,s.thread#,s.sequence#,s.archived,s.status,f.member, f.type
from v$standby_log s, v$logfile f
where f.type = 'STANDBY'
and s.group# = f.group#
/
Prompt ======================================================================================================================
Prompt Check the recovery progress:
select START_TIME
,TYPE
,ITEM
,UNITS
,SOFAR
,TOTAL
,TIMESTAMP
from v$recovery_progress
/
Prompt ======================================================================================================================
prompt Data guard broker information, if relevant:
select dataguard_broker as "DG broker"
from v$database
/
Prompt ======================================================================================================================
exit
Example output. Note particularly the output in red. When the archived_seq# and the applied_seq# match, together with an MRP process that is in status "WAITING_FOR_LOG", your standby database is in synch with the primary.
======================================================================================================================
General information:
NAME Unique name CREATED Open Mode Flashback on Resetlogs time Current SCN
--------------- ------------ ------------------- ------------ --------------- ------------------- --------------
PRODDB01 STBDB01 19-01-2009 15:25:32 MOUNTED YES 17-11-2012 13:17:40 7203583291843
======================================================================================================================
Standby database information, if relevant:
Database Role Log Mode Controlfile type Protection Mode
-------------------- -------------------- ------------------------------ ------------------------------
PHYSICAL STANDBY ARCHIVELOG STANDBY MAXIMUM PERFORMANCE
======================================================================================================================
Destination Name STATUS TYPE DATABASE_MODE RECOVERY_MODE DESTINATION ARCHIVED_SEQ# APPLIED_SEQ#
-------------------- --------- -------------------- --------------- -------------- ------------ ------------- ------------
LOG_ARCHIVE_DEST_2 DEFERRED PHYSICAL MOUNTED-STANDBY MANAGED PRODDB01 0 0
LOG_ARCHIVE_DEST_10 VALID PHYSICAL MOUNTED-STANDBY MANAGED 48610 0
STANDBY_ARCHIVE_DEST VALID PHYSICAL MOUNTED-STANDBY MANAGED 48609 48609
======================================================================================================================
Is the MRP process running?
PROCESS STATUS
--------- ------------
MRP0 WAIT_FOR_LOG
======================================================================================================================
Are there standby redo logs configured?
GROUP# THREAD# SEQUENCE# ARC STATUS MEMBER TYPE
---------- ---------- ---------- --- ---------- ---------------------------------------------------------------------- --------------------
6 1 48611 YES ACTIVE /data/oracle/u01/STBDB01/stb_redo_6a.dbf STANDBY
6 1 48611 YES ACTIVE /data/oracle/u01/STBDB01/stb_redo_6b.dbf STANDBY
7 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_7a.dbf STANDBY
7 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_7b.dbf STANDBY
8 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_8a.dbf STANDBY
8 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_8b.dbf STANDBY
9 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_9a.dbf STANDBY
9 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_9b.dbf STANDBY
10 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_10a.dbf STANDBY
10 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_10b.dbf STANDBY
11 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_11a.dbf STANDBY
11 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_11b.dbf STANDBY
======================================================================================================================
Check the recovery progress:
START_TIME TYPE ITEM UNITS SOFAR TOTAL TIMESTAMP
------------------- -------------------- -------------------------------- -------------------- ---------- ---------- -------------------
15-11-2013 17:11:58 Media Recovery Log Files Files 7571 0
15-11-2013 17:11:58 Media Recovery Active Apply Rate KB/sec 8118 0
15-11-2013 17:11:58 Media Recovery Average Apply Rate KB/sec 110 0
15-11-2013 17:11:58 Media Recovery Redo Applied Megabytes 620510 0
15-11-2013 17:11:58 Media Recovery Last Applied Redo SCN+Time 923136452 0 21-01-2014 12:00:08
15-11-2013 17:11:58 Media Recovery Active Time Seconds 80785 0
15-11-2013 17:11:58 Media Recovery Apply Time per Log Seconds 8 0
15-11-2013 17:11:58 Media Recovery Checkpoint Time per Log Seconds 1 0
15-11-2013 17:11:58 Media Recovery Elapsed Time Seconds 5767807 0
======================================================================================================================
Data guard broker information, if relevant:
DG broker
--------------------
DISABLED
======================================================================================================================
How to use the DUPLICATE ... FOR STANDBY command to create a physical standby database
run {
allocate auxiliary channel t1 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
allocate auxiliary channel t2 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
send 'NB_ORA_POLICY=mypolicy,NB_ORA_CLIENT=myclient-bkp.mydomain,NB_ORA_SCHED=my_schedule';
allocate auxiliary channel aux1 device type disk;
set until logseq=l2345 thread=1;
duplicate target database for standby dorecover;
release channel t1;
release channel t2;
}
Note that the parameters db_file_name_convert and log_file_name_convert must be set in order to use this simplified syntax.If not, you must specify file location for all the involved files and redo log members.
How to use ssh to a Solaris 8 box - overcoming file size limitation
When transferring files to a Solaris 8 server, I have had some difficulties with files larger than 2 GB. The ssh process will simply abort after it has reached the limit.
To work around this problem, use the following syntax instead:
To work around this problem, use the following syntax instead:
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # NOTE: if you are transfering dump files to a Solaris 8 box, # you need to tar and pipe the files to the receiving server. # Make sure you cd to the directory first, before attempting # to tar and ssh them. # Vegard K, 25.02.2010 #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #!/usr/bin/bash cd /oracle/datapump/ tar cEf - dmpfile_01.dmp | ssh prodserver1 "cd /oracle/backup/datapump; tar xf -" exit $?
How to use RMANs RESTORE...VALIDATE command
The RESTORE ... VALIDATE and VALIDATE BACKUPSET commands test whether you can restore from your backups:
RESTORE CONTROLFILE VALIDATE; RESTORE TABLESPACE SYSTEM VALIDATE; RESTORE ARCHIVELOG ALL VALIDATE;This example validates the restore of backup sets 56 and 57:
VALIDATE BACKUPSET 56,57;Same thing, but for individual datafiles:
RESTORE DATAFILE 4,5,6 VALIDATE;
In addition to listing the files needed for restore and recovery, the RESTORE ... VALIDATE HEADER command validates the backup file headers to determine whether the files on disk or in the media management catalog correspond to the metadata in the RMAN repository:
RESTORE DATABASE VALIDATE HEADER;
Subscribe to:
Posts (Atom)