Feeds:
Posts
Comments

Archive for December, 2010

set feedback off
set termout on
set pagesize 56
set linesize 800
ttitle off
spool out.log

set heading off
set verify off
column today NEW_VALUE p_currdate noprint
select TO_CHAR(SYSDATE,’fmMonth ddth, yyyy’) today from dual;
clear breaks
clear computes
clear columns
set heading off
column name heading ” justify center format a74
select ‘Database Name (SID): ‘ || name “name” from v$database;
prompt
prompt
prompt Version/SGA Information:
set heading off
select * from v$version;
column sgatot justify left format 9,999,999,999
select ‘Total System Global Area as of &p_currdate.:’ hd1, sum(value) sgatot, ‘bytes’ hd2 from v$sga;

— ************* TABLESPACES/DATAFILES **************

prompt
prompt
prompt Tablespaces and Datafiles:
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading ‘Tablespace’ justify left format a15
column file_id heading ‘File|ID’ justify center format 9999
column file_name heading ‘Datafile’ justify center format a60 word_wrapped
column size Heading ‘Size|in MB.’ justify center format 9,99990.99
break on tablespace_name skip 1 on report skip 2
compute sum label ‘TS SIZE:’ of size on tablespace_name
compute sum label ‘DB SIZE:’ of size on report
select tablespace_name,file_id,file_name,bytes/1024/1024 “size” from dba_data_files order by tablespace_name, file_id, file_name;

— ************* SPACE USAGE **************

prompt Space usage (as of &p_currdate.):
prompt
clear breaks
clear computes
clear columns
set heading on
column tspace heading ‘Tablespace’ justify left format a20 truncated
column tot_ts_size heading ‘Size|in MB.’ justify left format 9,99999999990
column free_ts_size heading ‘Used|in MB.’ justify right format 9,9999999990
column tbusedpct heading ” justify left format a6
column tbfreepct heading ” justify left format a6
break on report
compute sum label ‘Totals:’ of tot_ts_size free_ts_size on report
col tspace form a25 Heading “Tablespace”
col tot_ts_size form 99999999999999 Heading “Size (Mb)”
col free_ts_size form 99999999999999 Heading “Free (Mb)”
col ts_pct form 999 Heading “% Free”
col ts_pct1 form 999 Heading “% Used”

SELECT df.tablespace_name tspace,df.bytes/(1024*1024) tot_ts_size,
sum(fs.bytes)/(1024*1024) free_ts_size,round(sum(fs.bytes)*100/df.bytes) ts_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1
FROM dba_free_space fs,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df
WHERE fs.tablespace_name = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes;

— ************* REDO LOG FILES **************

prompt
prompt
prompt Online Redo Logfiles:
clear breaks
clear computes
clear columns
column member heading ‘Logfile’ justify center format a60 word_wrapped
column group heading ‘Group|Number’ justify center format 9999
column size heading ‘Size|in MB.’ justify center format 999990.99
select f.member “member”,f.group# “group”,l.bytes/1024/1024 “size” from v$logfile f, v$log l where f.group#=l.group# order by f.group#,f.member;

— ************* CONTROL FILES ************** —
prompt
prompt
prompt Control files:
clear breaks
clear computes
clear columns
column name heading ‘File Name’ format a60 word_wrapped
select name from v$controlfile;

— ************* ROLLBACK SEGMENTS ************** —
prompt
prompt
prompt Rollback Segments (sizes as of &p_currdate.):
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading ‘Tablespace’ justify left format a15 truncated
column segment_name heading ‘Seg|Name’ justify center format a7
column status heading ‘Status’ justify center format a8
column initial_extent heading ‘Initial|(in M)’ justify center format 99990.9
column next_extent heading ‘Next|(in M)’ justify center format 99990.9
column min_extents heading ‘Min|Ext’ justify center format 99990
column max_extents heading ‘Max|Ext’ justify center format 999999999990
column pct_increase heading ‘Pct|Inc’ justify center format 99990
column rbsize heading ‘Curr Size|(in M)’ justify left format 9,99990
break on tablespace_name skip 1 on report skip 2
select r.tablespace_name,r.segment_name,r.status,
r.initial_extent/1024/1024 “initial_extent”,
r.next_extent/1024/1024 “next_extent”,
r.min_extents,r.max_extents,
r.pct_increase,
sum(e.bytes)/1024/1024 “rbsize”
from dba_rollback_segs r, dba_extents e
where e.segment_name = r.segment_name
group by r.tablespace_name, r.segment_name, r.status,
r.initial_extent/1024, r.next_extent/1024,
r.min_extents, r.max_extents, r.pct_increase;


— ************* PARAMETERS **************

prompt
prompt Parameters (non-defaults):
clear breaks
clear computes
clear columns
column name heading ‘Name’ format a35 word_wrapped
column pvalue heading ‘Value’ format a50 word_wrapped
select name, rtrim(value) “pvalue” from v$parameter order by name;


— ************* USER DETAILS **************

prompt
prompt user details :
clear breaks
clear computes
clear columns
select username,profile,default_tablespace,temporary_tablespace from dba_users;
select * from nls_database_parameters;
select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
select owner,object_type,count(1) from dba_objects group by owner,object_type order by owner;
SELECT file_name,tablespace_name,autoextensible,maxbytes/1048576 FROM dba_data_files;

Read Full Post »

RMAN Login syntax…

Start the RMAN at the operation system command without specifying any connection options ;

% rmanStart the RMAN at the operating system with the options to connecting to one or more databases ;

% rman TARGET / CATALOG rman/cat@catdb
% rman TARGET SYS/oracle@trgt NOCATALOG
% rman TARGET / CATALOG rman/cat@catdb AUXILIARY SYS/oracle@auxdb

Related Documents:

https://samadhandba.wordpress.com/2010/12/26/rman-backup-and-recovery-scenarios/
https://samadhandba.wordpress.com/2010/12/26/getting-the-controlfile-backup-record/
https://samadhandba.wordpress.com/2010/12/26/full-restore-recovery/
https://samadhandba.wordpress.com/2010/12/26/step-by-step-backuprestore-with-rman/
https://samadhandba.wordpress.com/2010/12/26/making-consistent-and-inconsistent-backups-with-rman/
https://samadhandba.wordpress.com/2010/12/26/restore-to-a-%e2%80%9cpoint-in-time%e2%80%9d/
https://samadhandba.wordpress.com/2010/12/26/tablespace-recovery-with-rman-tecnology/
https://samadhandba.wordpress.com/2010/12/26/cold-backup-with-rman/

Read Full Post »

BACKUP Back up database files, archive logs, backups, or copies.
CHANGE Update the status of a backup in the RMAN repository.
CONFIGURE Persistent RMAN settings.
CROSSCHECK Check whether backup items still exist.
DELETE Delete backups from disk or tape
EXIT Exit the RMAN console
LIST List backups and copies
RECOVER Apply redo logs or incr. backups to a restored backup set
REPORT Report backup status: database, files, backups
RESTORE Restore files from an RMAN backup (or copy)
RUN Some RMAN commands are only valid inside a RUN block.
SET Settings for the current RMAN session.
SHOW Display the current configuration
SHUTDOWN Shutdown the database
SQL Execute a PL/SQL procedure or SQL statement(not SELECT)
STARTUP Startup the database

Related Documents:

https://samadhandba.wordpress.com/2010/12/26/rman-backup-and-recovery-scenarios/
https://samadhandba.wordpress.com/2010/12/26/getting-the-controlfile-backup-record/
https://samadhandba.wordpress.com/2010/12/26/full-restore-recovery/
https://samadhandba.wordpress.com/2010/12/26/step-by-step-backuprestore-with-rman/
https://samadhandba.wordpress.com/2010/12/26/making-consistent-and-inconsistent-backups-with-rman/
https://samadhandba.wordpress.com/2010/12/26/restore-to-a-%e2%80%9cpoint-in-time%e2%80%9d/
https://samadhandba.wordpress.com/2010/12/26/tablespace-recovery-with-rman-tecnology/
https://samadhandba.wordpress.com/2010/12/26/cold-backup-with-rman/

Read Full Post »

Start RMAN with a command file to run
% rman @/my_directory/my_command.txt

Related Documents:

https://samadhandba.wordpress.com/2010/12/26/rman-backup-and-recovery-scenarios/
https://samadhandba.wordpress.com/2010/12/26/getting-the-controlfile-backup-record/
https://samadhandba.wordpress.com/2010/12/26/full-restore-recovery/
https://samadhandba.wordpress.com/2010/12/26/step-by-step-backuprestore-with-rman/
https://samadhandba.wordpress.com/2010/12/26/making-consistent-and-inconsistent-backups-with-rman/
https://samadhandba.wordpress.com/2010/12/26/restore-to-a-%e2%80%9cpoint-in-time%e2%80%9d/
https://samadhandba.wordpress.com/2010/12/26/tablespace-recovery-with-rman-tecnology/
https://samadhandba.wordpress.com/2010/12/26/cold-backup-with-rman/

Read Full Post »

If the database closed, then run the startup command at the RMAN prompt, specifying an initialization parameter file only if you do not use a server parameter file. This example starts the instance with the server parameter file:

RMAN> STARTUP MOUNT

Oracle instance started
database mountedIf the database is in open state, then you can run the following RMAN commands to close it cleanly and then mount it:

database closed
database dismounted
Oracle instance shut down

RMAN> STARTUP MOUNT

Related Documents:

https://samadhandba.wordpress.com/2010/12/26/rman-backup-and-recovery-scenarios/
https://samadhandba.wordpress.com/2010/12/26/getting-the-controlfile-backup-record/
https://samadhandba.wordpress.com/2010/12/26/full-restore-recovery/
https://samadhandba.wordpress.com/2010/12/26/step-by-step-backuprestore-with-rman/
https://samadhandba.wordpress.com/2010/12/26/making-consistent-and-inconsistent-backups-with-rman/
https://samadhandba.wordpress.com/2010/12/26/restore-to-a-%e2%80%9cpoint-in-time%e2%80%9d/
https://samadhandba.wordpress.com/2010/12/26/tablespace-recovery-with-rman-tecnology/
https://samadhandba.wordpress.com/2010/12/26/cold-backup-with-rman/

Read Full Post »

Run the ”BACKUP“ command at the RMAN prompt as follows to make a full backup of the DATAFILES, CONTROL FILE and current SERVER PARAMETER FILE (if the instance is started with a server parameter file) to the default device type:

RMAN> BACKUP DATABASE;

Starting backup at JUL 21 2010 13:19:21
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=08 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=/oracle/oradb/trgt/system01.dbf
input datafile fno=00002 name=/oracle/oradb/trgt/undotbs01.dbf
input datafile fno=00003 name=/oracle/oradb/trgt/example01.dbf
input datafile fno=00004 name=/oracle/oradb/trgt/index01.dbf
input datafile fno=00005 name=/oracle/oradb/trgt/users01.dbf
input datafile fno=00006 name=/oracle/oradb/trgt/tools01.dbf
channel ORA_DISK_1: starting piece 1 at JUL 21 2010 13:19:24
channel ORA_DISK_1: finished piece 1 at JUL 21 2010 13:20:27
piece handle=/oracle/dbs/lvd6dtk1_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:02
Finished backup at JUL 21 2010 13:10:32

Related Documents:

https://samadhandba.wordpress.com/2010/12/26/rman-backup-and-recovery-scenarios/
https://samadhandba.wordpress.com/2010/12/26/getting-the-controlfile-backup-record/
https://samadhandba.wordpress.com/2010/12/26/full-restore-recovery/
https://samadhandba.wordpress.com/2010/12/26/step-by-step-backuprestore-with-rman/
https://samadhandba.wordpress.com/2010/12/26/making-consistent-and-inconsistent-backups-with-rman/
https://samadhandba.wordpress.com/2010/12/26/restore-to-a-%e2%80%9cpoint-in-time%e2%80%9d/
https://samadhandba.wordpress.com/2010/12/26/tablespace-recovery-with-rman-tecnology/
https://samadhandba.wordpress.com/2010/12/26/cold-backup-with-rman/

Read Full Post »

You can generate a report describing the target datafiles with RMAN

RMAN> REPORT SCHEMA;

Report of database schema

File K-bytes Tablespace RB segs Datafile Name

—- ———- ——————– ——- ——————-

1 103450 SYSTEM *** /oracle/oradb/trgt/system01.dbf

2 10240 UNDOTBS *** /oracle/oradb/trgt/undotbs01.dbf

3 10240 EXAMPLE *** /oracle/oradb/trgt/example01.dbf

4 10240 INDEX *** /oracle/oradb/trgt/index01.dbf

5 10240 USERS *** /oracle/oradb/trgt/users01.dbf

6 10240 TOOLS *** /oracle/oradb/trgt/tools01.dbf

Related Documents:

https://samadhandba.wordpress.com/2010/12/26/rman-backup-and-recovery-scenarios/
https://samadhandba.wordpress.com/2010/12/26/getting-the-controlfile-backup-record/
https://samadhandba.wordpress.com/2010/12/26/full-restore-recovery/
https://samadhandba.wordpress.com/2010/12/26/step-by-step-backuprestore-with-rman/
https://samadhandba.wordpress.com/2010/12/26/making-consistent-and-inconsistent-backups-with-rman/
https://samadhandba.wordpress.com/2010/12/26/restore-to-a-%e2%80%9cpoint-in-time%e2%80%9d/
https://samadhandba.wordpress.com/2010/12/26/tablespace-recovery-with-rman-tecnology/
https://samadhandba.wordpress.com/2010/12/26/cold-backup-with-rman/

Read Full Post »

Incremental backups are useful for saving storage space because they backup only data blocks that have changed.

Rman compares the current files to a “base backup” (also called a “level 0 backup”)

Example , If you have a base backup and after changed something from the DB , you can run the following command for the changing blocks ;

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;If you don’t have any LEVEL 0 backup before this command , you will see ;

“no parent backup or copy of datafile 1 found”RMAN automatically creates a LEVEL 0 backup for its base incremental backup.

Related Documents:

https://samadhandba.wordpress.com/2010/12/26/rman-backup-and-recovery-scenarios/
https://samadhandba.wordpress.com/2010/12/26/getting-the-controlfile-backup-record/
https://samadhandba.wordpress.com/2010/12/26/full-restore-recovery/
https://samadhandba.wordpress.com/2010/12/26/step-by-step-backuprestore-with-rman/
https://samadhandba.wordpress.com/2010/12/26/making-consistent-and-inconsistent-backups-with-rman/
https://samadhandba.wordpress.com/2010/12/26/restore-to-a-%e2%80%9cpoint-in-time%e2%80%9d/
https://samadhandba.wordpress.com/2010/12/26/tablespace-recovery-with-rman-tecnology/
https://samadhandba.wordpress.com/2010/12/26/cold-backup-with-rman/

Read Full Post »

« Newer Posts - Older Posts »