Check alert log

export ALERT_LOG=$BDUMP/alert_$ORACLE_SID.log

tail -200 $ALERT_LOG | grep "ORA-" > $CRONDIR/maint/alert_log_errors.txt

let lc="$(wc -l <$CRONDIR/maint/alert_log_errors.txt)" if ((lc > 0));
then
mailx -s "$ORACLE_SID: Alert Log Errors!!" k@a.com r@a.com < $CRONDIR/maint/alert_log_errors.txt
fi

exp dump clean

find /u06/oradata/export -name "exp*dmp.Z" -ctime +6 -exec rm -f {} \;
find /u06/oradata/export -name "exp*.log" -ctime +6 -exec rm -f {} \;

Check if instance is up or not

#! /bin/ksh
sqlplus -s "/ as sysdba" <>EOF //use less than redirection symbols
set pages 0
set feedback off
spool /tmp/instance_status.log
select status from v\$instance;
spool off
EOF
grep "OPEN" /tmp/instance_status.log
if [$? -eq 0]; then
echo "Instance is up!"
else
echo "Instance is Down!"
fi

Check if Listener is up or not

#! /bin/ksh
LISTENER_NAME=$1
ps -ef|grep "tnslsnr $LISTENER_NAME"|grep -v grep
if[ $? -eq 0 ]; then
echo "The listener $LISTENER_NAME is UP"
else
echo "The listener $LISTENER_NAME is DOWN"
fi

Snaplog and Snapshot script

-------------SNAPLOG SCRIPT ON SOURCE---------------------

CREATE SNAPSHOT LOG ON TABLENAME
INITRANS 2 PCTFREE 1 TABLESPACE NFSNAPLOG NOLOGGING
WITH PRIMARY KEY;

---------------FOR ROW ID TABLES-----------
CREATE SNAPSHOT LOG ON TABLENAME
INITRANS 2 PCTFREE 1 TABLESPACE NFSNAPLOG NOLOGGING
WITH ROWID;


-------------SNAPSHOT SCRIPT ON TARGET----------------
CREATE SNAPSHOT TABLENAME
PCTFREE 0 TABLESPACE NFSNAPDATA NOLOGGING
USING INDEX
PCTFREE 5 TABLESPACE NFSNAPIDX
REFRESH FAST
START WITH sysdate
NEXT sysdate + 10 / (24 * 60)
WITH PRIMARY KEY
AS SELECT * FROM NFADMIN.TABLENAME@DBLINK;

----FOR ROWID TABLES----

CREATE SNAPSHOT TABLENAME
PCTFREE 0 TABLESPACE NFSNAPDATA NOLOGGING
USING INDEX
PCTFREE 5 TABLESPACE NFSNAPIDX
REFRESH FAST
START WITH sysdate
NEXT sysdate + 10 / (24 * 60)
WITH ROWID
AS SELECT * FROM NFADMIN.TABLENAME@DBLINK;

Materialized Views in Oracle

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).
For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.
For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.

In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.
In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.

Primary Key Materialized Views
The following statement creates the primary-key materialized view on the table emp located on a remote database.
SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;

Materialized view created.

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.

Rowid Materialized Views
The following statement creates the rowid materialized view on table emp located on a remote database:
SQL> CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID
AS SELECT * FROM emp@remote_db;

Materialized view log created.

Subquery Materialized Views
The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:
SQL> CREATE MATERIALIZED VIEW mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
(SELECT * FROM dept@remote_db d
WHERE e.dept_no = d.dept_no)

REFRESH CLAUSE
[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]
The refresh option specifies:
The refresh method used by Oracle to refresh data in materialized view
Whether the view is primary key based or row-id based
The time and interval at which the view is to be refreshed

Refresh Method - FAST Clause
The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.
You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.
SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.

Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

Refresh Method - COMPLETE Clause
The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Refresh Method - FORCE Clause
When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause
WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.

Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
Rowid materialized views should have a single master table and cannot contain any of the following:
Distinct or aggregate functions
GROUP BY Subqueries , Joins & Set operations

Timing the refresh
The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes
SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 2
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;

Materialized view created.

In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.

Summary

• Materialized Views thus offer us flexibility of basing a view on Primary key or ROWID, specifying refresh methods and specifying time of automatic refreshes.

• Manually Refreshing Materialized Views and Creating Refresh Groups in Oracle

• You can perform manual refreshes in addition to automatic refreshes as explained in my earlier article (Materialized Views). Oracle supplies DBMS_SNAPSHOT and DBMS_MVIEW packages, which we can use to refresh materialized views / snapshots.

DBMS_SNAPSHOT
SQL> execute DBMS_SNAPSHOT.REFRESH( 'MV_EMP','f');
PL/SQL procedure successfully completed.
Parameters of Procedure REFRESH
The first parameter to the procedure REFRESH is the name of the materialized view or snapshot, the second parameter specifies the type of refresh.

Type of Refresh Description
F, f Fast Refresh
C, c Complete Refresh
A Always perform complete refresh
? Use the default option

The manual refresh overtakes any previous refresh timing options, which were specified during the creation of the view. It more specifically overrides the 'start with' clause, which is specified with the 'create materialized view' command.
Also provided with DBMS_SNAPSHOT is the REFRESH_ALL procedure. This procedure refreshes all materialized views, which were defined using the automatic refreshes.

SQL> execute DBMS_SNAPSHOT.REFRESH_ALL;
PL/SQL procedure successfully completed.

Parameters of procedure REFRESH_ALL
The REFRESH_ALL procedure does not accept any parameters.
REFRESH GROUPS - CLUBBING RELATED VIEWS
Oracle provides the means by which you can group related views together. Oracle supplies the DBMS_REFRESH package with the following procedures;
MAKE Make a Refresh Group
ADD Add materialized view to the refresh group
SUBTRACT Remove materialized view from the refresh group
REFRESH Manually refresh the group
CHANGE Change refresh interval of the refresh group
DESTROY Remove all materialized views from the refresh group and delete the refresh group

DBMS_REFRESH - Procedure MAKE
The MAKE procedure is used to create a new Refresh group.
We will make a refresh group my_group_1:
SQL> execute DBMS_REFRESH.MAKE(
name => 'my_group_1',
list => ' mv_market_rate, mv_dealer_rate',
next_date => sysdate,
interval => 'sysdate+1/48');
my_group_1 has two views in its group, mv_market_rate and mv_dealer_rate. Both of these views will be refreshed at an interval of 30 minutes

DBMS_REFRESH - Procedure ADD
Add a snapshot/materialized view to the already existing refresh group:
SQL> execute DBMS_REFRESH.ADD(
name => 'my_group_1',
list => 'mv_borrowing_rate');
my_group_1 now has three views in its group, mv_market_rate, mv_dealer_rate and mv_borrowing_rate ( the newly added view). All of these views will be refreshed at an interval of 30 minutes

DBMS_REFRESH - Procedure SUBTRACT
Removes a snapshot/materialized view from the already existing refresh group.
SQL> execute DBMS_REFRESH.SUBTRACT(
name => 'my_group_1',
list => 'mv_market_rate');
my_group_1 now has two views in its group, mv_dealer_rate and mv_borrowing_rate. We have removed mv_market_rate from the refresh group, my_group_1.

DBMS_REFRESH - Procedure REFRESH
Manually refreshes the already existing refresh group.
SQL> execute DBMS_REFRESH.REFRESH(
name => 'my_group_1');

DBMS_REFRESH - Procedure CHANGE
The CHANGE procedure is used to change the refresh interval of the refresh group.
SQL> execute DBMS_REFRESH.CHANGE(
name => 'my_group_1',
next_date => NULL,
interval => 'sysdate+1/96');
The views in my_group_1 will now be refreshed at an interval of 15 minutes.

DBMS_REFRESH - Procedure DESTROY
Removes all materialized views from the refresh group and deletes the refresh group.
SQL> execute DBMS_REFRESH.DESTROY(
name => 'my_group_1');

Summary

• Creating a refresh group helps to club all related views together and thus refreshes them together. Manual refresh gives us an opportunity to override the automatic refresh settings.

• USER_MVIEWS describes all materialized views owned by the current user. Its columns are the same as those in ALL_MVIEWS.

Useful commands Part 2

SQL> show release
release 1002000300
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 – Production

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

SQL> conn system/manager
SQL> select DBID, NAME from v$database;

DBID NAME
---------- ---------
3235629315 WORK
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL> select current_scn from v$database;

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

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------
db_name string vinay

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

SQL> conn scott/tiger@sample;
SQL> select owner,table_name from all_tables order by owner,table_name;

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PLAN_TABLE USERS
DEPT USERS
EMP USERS
BONUS USERS
SALGRADE USERS

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


SQL> select file_name, tablespace_name from dba_data_files;


SQL> select tablespace_name,file_name from dba_temp_files;


SQL> select group#,member from v$logfile order by group#;


SQL> select * from v$log;


SQL> select log_mode from v$database;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> startup mount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1292036 bytes
Variable Size 201328892 bytes
Database Buffers 402653184 bytes
Redo Buffers 7094272 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL>SELECT OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS where status='INVALID'
group by object_type; /*to see what objects are invalid*/

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

SQL> show sga

Total System Global Area 612368384 bytes
Fixed Size 1292036 bytes
Variable Size 184551676 bytes
Database Buffers 419430400 bytes
Redo Buffers 7094272 bytes

SQL> select * from v$sga;

NAME VALUE
-------------------- ----------
Fixed Size 1292036
Variable Size 184551676
Database Buffers 419430400
Redo Buffers 7094272

SQL> select component,current_size from v$sga_dynamic_components;

COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 163577856
large pool 4194304
java pool 16777216
streams pool 0
DEFAULT buffer cache 419430400
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
ASM Buffer Cache 0

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

SQL> select * from dba_tablespace_usage_metrics;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX 37888 4194302 .903320743
SYSTEM 61560 4194302 1.46770547
TEMP 0 4194302 0
UNDOTBS1 288 4194302 .006866458
USERS 112 4194302 .002670289

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

SQL> select tablespace_name,status,contents,extent_management extents,segment_space_management free_
space from dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS EXTENTS FREE_S
------------------------------ --------- --------- ---------- ------
SYSTEM ONLINE PERMANENT LOCAL MANUAL
UNDOTBS1 ONLINE UNDO LOCAL MANUAL
SYSAUX ONLINE PERMANENT LOCAL AUTO
TEMP ONLINE TEMPORARY LOCAL MANUAL
USERS READ ONLY PERMANENT LOCAL AUTO

SQL> select tablespace_name, ((bytes/1024)/1024) MB from dba_data_files;

TABLESPACE_NAME MB
------------------------------ ----------
USERS 5
SYSAUX 300
UNDOTBS1 55
SYSTEM 490

For small file tablespaces:
SQL> alter database datafile
'E:\ORACLE\ORADATA\SAMPLE\UNDOTBS01.DBF'
RESIZE 75M;

SQL> ALTER DATABASE DATAFILE '/u07/oracle/oradata/train/media01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;


For bigfile tablespaces:
SQL> ALTER TABLESPACE media RESIZE 1G;
SQL> ALTER TABLESPACE media AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

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

SQL> select DBMS_METADATA.GET_DDL('TABLESPACE','USERS') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
-----------------------------------------------------------------------

CREATE TABLESPACE "USERS" DATAFILE
'E:\ORACLE\ORADATA\SAMPLE\USERS01.DBF'


Note that TABLE and TEST are case-sensitive
SQL> select DBMS_METADATA.GET_DDL('TABLE', 'TEST') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST')
----------------------------------------------------------------------------

CREATE TABLE "SAMPLE"."TEST"
( "TNAME" VARCHAR2(10)
) PCTFREE 10 PCTUS

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

SQL> comment on table sample is ‘This is my comment’;

SQL> select owner, table_name, comments from all_tab_comments where table_name='SAMPLE';

OWNER TABLE_NAME
------------------------------ ------------------------------
COMMENTS
-----------------------------------------------------------------------------------------
SCOTT SAMPLE
This is my comment

SQL> comment on column sample.sname is 'sample column comment';

SQL> select table_name,column_name, comments from all_col_comments where table_name='SAMPLE';

TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
COMMENTS
----------------------------------------------------------------------------------------------
SAMPLE SNAME
sample column comment

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

SQL> select constraint_name,constraint_type, r_constraint_name from all_constraints where table_name='EMP';

CONSTRAINT_NAME C R_CONSTRAINT_NAME
------------------------------ - ------------------------------
PK_EMP P
FK_DEPTNO R PK_DEPT

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

SQL>GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;

SQL>ALTER USER SCOTT DEFAULT TABLESPACE USERS;
SQL>ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;

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

SQL> select sid,serial#,username,server,program from v$session;

select status, substr(username,1,22) username, substr(osuser,1,15) osuser,
substr(machine,1,15) machine, substr(program,1,15) program
from v$session
where username is not null
order by 2, 1;


SQL> select sessions_current,sessions_highwater from v$license;

SESSIONS_CURRENT SESSIONS_HIGHWATER
---------------- ------------------
2 5

SQL> select name,status,messages,idle,busy,bytes,breaks from v$dispatcher;

NAME STATUS MESSAGES IDLE BUSY BYTES BREAKS
---- ---------------- ---------- ---------- ---------- ---------- ----------
D000 WAIT 0 531628 8 0 0

SQL> select * from v$queue;

PADDR TYPE QUEUED WAIT TOTALQ
-------- ---------- ---------- ---------- ----------
00 COMMON 0 0 0
3464F39C DISPATCHER 0 0 0

SQL> select circuit,dispatcher,server,waiter WTR,status,queue,bytes from v$circuit;

SQL> select name,status,messages,bytes,idle,busy,requests from v$shared_server;

NAME STATUS MESSAGES BYTES IDLE BUSY REQUESTS
---- ---------------- ---------- ---------- ---------- ---------- ----------
S000 WAIT(COMMON) 0 0 596321 0 0

SQL> select username,program,server from v$session;

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

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
2612432
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL> select username, default_tablespace from dba_users; To get the default tablespace for a user

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

SQL> select property_name, property_value from database_properties where
property_name like ‘%TABLESPACE’;

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

SQL> alter session recyclebin=on;

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

SQL> select reason, metric_value from dba_outstanding_alerts;

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

Enable logging. On your primary database, instruct Oracle Database to force all logging of changes to the redo, even if nologging or unrecoverable data loads are performed:
SQL> alter database force logging;
Verify that forced logging has been enabled on your primary database, by issuing the following:

SQL> select force_logging from v$database;

Enable supplemental logging. Enabling supplemental logging will direct Oracle Database to add a small amount of extra information to the redo stream. The SQL Apply process uses this additional information to maintain tables being replicated. On your primary database, enable supplemental logging as follows:
SQL> alter database add supplemental log data (primary key, unique index) columns;
SQL> alter system archive log current;
You can verify that supplemental logging has been enabled, by issuing the following on your primary database:

SQL> select supplemental_log_data_pk, supplemental_log_data_ui
from v$database;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/admin/myDB/arch
Oldest online log sequence 345
Next log sequence to archive 347
Current log sequence 347

SQL> select * from log_history;

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

SQL> explain plan for select * from emp;

Explained.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 448 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

OR

SQL> select * from table (dbms_xplan.display);

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

#Access system statistics values stored in dynamic performance tables
SQL> conn system/manager
SQL> select statistic#, name, value from v$sysstat where rownum<=20;

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

CREATE SYNONYM "SCOTT"."SAMPLE" FOR "SCOTT"."EMP";

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

Get the file name and max size of datafile
SQL> select file_name, bytes from dba_data_files where bytes=(select max(bytes) from dba_data_files);

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

The user can view the dba_ and V$ tables if granted the following role
SQL> grant select_catalog_role to sample;

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

Grant unlimited quota on tablespace
SQL> alter user sample quota unlimited on users;

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

SQL> select tablespace_name, sum((bytes/1024)/1024) from dba_data_files group by tablespace_name;

SQL> select tablespace_name, sum((bytes/1024)/1024) from dba_free_space group by tablespace_name;

SQL> select * from system_privilege_map;

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

SQL> select next_run_date from sys.dba_scheduler_jobs;

NEXT_RUN_DATE
-----------------------------------------------------------------
13-11-08 03:00:00.000000 AM US/PACIFIC
12-11-08 04:24:40.000000 PM -05:00
12-11-08 02:17:06.000000 PM -07:00

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

SQL > select * from nls_session_parameters;

PARAMETER VALUE
------------------------------ -----------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY INDIA
NLS_CURRENCY Rs
NLS_ISO_CURRENCY INDIA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MM-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH12:MI:SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MM-RR HH12:MI:SSXFF AM

PARAMETER VALUE
------------------------------ -----------------------------------
NLS_TIME_TZ_FORMAT HH12:MI:SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MM-RR HH12:MI:SSXFF AM TZR
NLS_DUAL_CURRENCY Rs
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

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

SQL> select to_char(sysdate, 'MM-DD-yyyy HH24:MI') from dual;

TO_CHAR(SYSDATE,
----------------
11-12-2008 18:19

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

SQL> STARTUP RESTRICT // One with RESTRICTED SESSION privilege can only connect to db
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

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

To see which archived logs are applied to the database:
SQL> select sequence#, applied from v$archivedlog order by sequence#;

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

Copying data from one column to another column in same table
SQL> select * from sample;

SNO SNAME LOCATION
---------- -------------------- --------------------
1 vinay NJ
2 suman KS
3 ravi WA

SQL> update [table_name] set [new_column]=[old_column];
The entries from old column will be copied to the new column.

SQL> update sample set location=sname;
4 rows updated.

SQL> select * from sample;

SNO SNAME LOCATION
---------- -------------------- --------------------
1 vinay vinay
2 suman suman
3 ravi ravi

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

SQL> create table test(tno, tname) as select sno,location from sample;
Table created.


Primary Key and Foreign Key:
SQL> create table master (mno number(3), mname varchar(20), dept varchar(20),
2 constraint master_mno_pk primary key (mno));

SQL> create table child1 (cno number(3), cname varchar(20),
2 constraint child1_cno_fk foreign key (cno)
3 references master(mno));

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

Identify Locks
SQL> select oracle_username os_user_name, locked_mode, object_name, object_type from v$locked_object a,dba_objects b where a.object_id = b.object_id;

OS_USER_NAME LOCKED_MODE OBJECT_NAME
------------------------------ ----------- ---------------------------------------------------------
SCOTT 3 SAMPLE

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

Current time
SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
2 from dual;

Current Time
------------------------
Tue 10-Feb-2009 00:40:27

[OR]

SQL> alter session set nls_date_format='dd-mon-yyyy HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
10-feb-2009 00:47:48

[OR]


SQL> select current_date from dual;

CURRENT_DATE
--------------------
10-feb-2009 00:48:52

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
----------------------------------------------------
-05:00

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

Query the DBA_STREAMS_UNSUPPORTED data dictionary view to determine which database objects are not supported by Streams. If unsupported database objects are not excluded, then capture errors will result

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

SQL> select platform_name from v$database;

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

From Metalink Supoort to resume ur backups:
BACKUP NOT BACKED UP SINCE TIME 'SYSDATE-1' DATABASE for resume your backup.

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

SQL> select * from v$session where type ='BACKGROUND';

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

Partitioning:

SQL> select partition_name, high_value from user_tab_partitions where table_name = 'DAILY_SALES' order by partition_position;

SQL> alter table daily_sales merge partitions for(to_date('01-JAN-2007','dd-MON-yyyy')) , for(to_date('01-FEB-2007','dd-MON-yyyy'))into partition p_31_2007;

SQL> alter table daily_sales rename partition sys_p41 to p_Jan_2007;

-- Look at the partitioned tables.
select table_name, partitioning_type, ref_ptn_constraint_name
from user_part_tables where table_name in ('CUSTOMER_ORDERS','CUSTOMER_ORDER_ITEMS');

TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ --------- ------------------------------
CUSTOMER_ORDERS RANGE
CUSTOMER_ORDER_ITEMS REFERENCE CUSTOMER_ORDER_ITEMS_ORDERS_FK

-- Look at the partitions created.
select table_name, partition_name, high_value
from user_tab_partitions where table_name in ('CUSTOMER_ORDERS','CUSTOMER_ORDER_ITEMS')
order by partition_position, table_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------- --------------------
CUSTOMER_ORDERS P_BEFORE_JAN_2007 TO_DATE(' 2007-01-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
CUSTOMER_ORDER_ITEMS P_BEFORE_JAN_2007

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

info about registered databases in RMAN catalog.

select dbid, name, resetlogs_time from rc_database;
RMAN> CONNECT CATALOG rman/cat@catdb
RMAN> SQL 'SELECT NAME FROM RC_DATABASE';

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

Below Query you can check the rman backup status of all databases from catalog database.

set pagesize 5000;
set linesize 400;
col OBJECT_TYPE for a10;
col STATUS for a15;
col operation for a15;
select DB_KEY,DB_NAME,STATUS,START_TIME,END_TIME,INPUT_BYTES/1024/1024/1024 INPUT_BYTES,OUTPUT_BYTES/1024/1024/1024 OUTPUT_BYTES,OBJECT_TYPE,OPERATION from rc_rman_status where START_TIME >= (sysdate-1) and OBJECT_TYPE not like '%ARCH%' and OPERATION not in ('DELETE','LIST') order by db_name,START_TIME;

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

SQL> select * from v$recover_file; // to see corrupted datafiles;

SELECT TABLESPACE_NAME, SUM (BYTES)/1024/1024 FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

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

Check user sessions:
select status, substr(username,1,22) username, substr(osuser,1,15) osuser,
substr(machine,1,15) machine, substr(program,1,15) program
from v$session
where username is not null
order by 2, 1;

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

set scan on
set pagesize 500
set linesize 500
column sid format a5
column osuser format a15
column program format a30
column opname format a30
column elapsed format a9
column remaining format a9
column updated format a8
column Comp format a5

SQL> clear columns //to clear formatted columns
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

First zero out the raw device before using it for new datafiles.
$ dd if=/dev/*zero* of=/dev/*raw*/raw2

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Size of a table:
select segment_name, sum(bytes)/(1024*1024) table_size_meg
from user_extents
where segment_type='TABLE' and segment_name = 'TABLE_NAME' group by segment_name;

OR

CREATE OR REPLACE FUNCTION get_table_size
(t_table_name VARCHAR2)RETURN NUMBER IS
l_size NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024)
INTO l_size
FROM user_extents
WHERE segment_type='TABLE'
AND segment_name = t_table_name;

RETURN l_size;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/

Example:
SELECT get_table_size('EMP') Table_Size from dual;

Result:
Table_Size
0.0625
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

RMAN backup status in 10g:
SELECT JD.COMMAND_ID,JD.STATUS,JD.OUTPUT_DEVICE_TYPE,JD.START_TIME,JD.TIME_TAKEN_DISPLAY,JD.END_TIME--,JD.*
FROM V$RMAN_BACKUP_JOB_DETAILS JD;

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

Sizing Redo Logs:
The value for optimal_logfile_size is expressed in megabytes and it changes frequently, based on the DML load on your database. For example,
SQL> SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

OPTIMAL_LOGFILE_SIZE
--------------------
256

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

Determine size of database:
select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;

Used space within the database:
SQL> SELECT SUM(bytes)/1024/1024 "Meg" FROM dba_segments;

Database size and free space:
col "Database Size" format a20
col "Free space" format a20
select round(sum(used.bytes) / 1024 / 1024 ) || ' MB' "Database Size"
, round(free.p / 1024 / 1024) || ' MB' "Free space"
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p
/

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

SELECT OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS where status='INVALID'
group by object_type;

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

SELECT segment_name, tablespace_name
FROM dba_segments
WHERE segment_name IN ('OBJ$', 'COL$', 'IND$', 'TAB$');

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

select * from v$session_longops where username='SCOTT';

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='AUD$';

TABLESPACE_NAME
--------------------------------------------------------------------------------
SYSTEM
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT NAME, VALUE, ISDEFAULT, DESCRIPTION, ISSES_MODIFIABLE SES_MODIFIABLE,
ISSYS_MODIFIABLE SYS_MODIFIABLE, UPDATE_COMMENT,ISINSTANCE_MODIFIABLE,
ISDEPRECATED
FROM v$parameter ORDER BY name;

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

SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace

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

Delete Duplicates

without using rowid:

DELETE FROM TBL_E_ISSUES a
WHERE a.REC_ID != (SELECT MAX(b.REC_ID)
FROM TBL_E_ISSUES b
WHERE a.ORDER_ID = b.ORDER_ID);

using rowid:

DELETE FROM TBL_E_ISSUES L1
WHERE L1.ROWID < ANY (SELECT L2.ROWID FROM TBL_E_ISSUES L2 WHERE
L2.order_id = L1.order_id);

Using RMAN to Perform Database Migration to ASM

Because ASM files cannot be accessed via the operating system, you must use RMAN to move database objects from a non-ASM disk location to an ASM disk group. Steps to move these database objects:

• Note the filenames of the control files and online redo log files
• Shutdown the database using NORMAL, IMMEDIATE, or TRANSACTIONAL keywords
• Back up the database
• Edit SPFILE to use OMF for all file destinations
• Edit SPFILE to remove CONTROL_FILES parameter
• Run the following RMAN script, substituting your specific filenames as needed:

STARTUP NOMOUNT;

RESTORE CONTROLFILE FROM ‘controlfile_location’;

ALTER DATABASE MOUNT;

BACKUP AS COPY DATABASE FORMAT
‘+disk_group_destination’;

SWITCH DATABASE TO COPY;

SQL “ALTER DATABASE RENAME logfile1
TO ‘+disk group destination’ “;

# repeat for all log file members

ALTER DATABASE OPEN RESETLOGS;

Delete or archive the old database files

Even though all files are now ASM files, you can still create a non-ASM tablespace if, for example, you want to transport a tablespace to a database that does not use ASM.

NOTE:
Each database datafile can reside in a different disk group; each disk group can also contain datafiles from other databases.
ASM does not support password files and init.ora files

Flashback Drop/Flashback Table

Using Flashback Drop
Flashback drop is the process of saving a copy of the dropped database object and dependent objects in the Recycle Bin so that these objects can be recovered if necessary. The dropped database object is not removed from the database until the Recycle Bin is emptied. This provides a mechanism for the user to recover an accidental drop of a table. In addition, a Flashback Drop does not impact other users in the database to restore a dropped table, whereas incomplete recovery has database-wide impacts because there may be multiple database objects involved in a tablespace or datafile.

SQL> flashback table t1 to before drop;

Flashback Drop is designed to temporarily store the dropped object and dependent objects for a period of time, which can be seen in Recycle Bin.

In order to query the object in the Recycle Bin, you must have the privileges. You also need the FLASHBACK privilege.

SQL> flashback table “BIN$9GjGHKB754HGC==$0” to before drop rename to t2;
SQL> select * from t2;

Using Flashback Table
Flashback Table is a Flashback Technology that allows you to recover a table or set of tables to a specific point-in-time without performing an incomplete recovery. All dependent objects are also recovered when using Flashback Table. Benefits over incomplete recovery:

• Much faster and easier to use
• Flashback Table does not impact the availability of the database
• DBA is not required to perform Flashback Table, so users can quickly recover from logical corruptions

Like other Flashback Technologies, Flashback Table is based on undo data. The privilege required to use Flashback Table is the system privilege FLASHBACK ANY TABLE or FLASHBACK TABLE. You must also grant SELECT, INSERT, DELETE, and ALTER object privileges to user performing Flashback Table.

There are two main clauses that are used with Flashback Table:

• TO SCN clause
• TO TIMESTAMP clause

NOTE: Flashback Table must have ROW MOVEMENT enabled with the following command: ALTER TABLE table_name ENABLE ROW MOVEMENT.

Ex:
SQL> alter table t1 enable row movement;
SQL> select current_scn from v$database;
SQL> update t1 set salary=500000 where employee=’jones’;
SQL> commit

SQL> flashback table t1 to scn 1071333;

NOTE: Triggers are disabled by default during Flashback Table process. Triggers can be enabled with ENABLE TRIGGERS option on the FLASHBACK TABLE command.
SQL> flashback table table_name to scn 168879 enable triggers;

Configuring Flashback Database

In order to use Flashback Database, the database must have multiple features configured prior to configuring Flashback Database. Database must have ARCHIVE LOG enabled, and flash recovery area must be configured to store Flashback Database logs.
First you can configure Flashback Database, so the database must be shutdown. Next, the database must be started in MOUNT mode. Then, the parameter DB_FLASHBACK_RETENTION_TARGET can be set to desired value, in minutes. This value determines how far back in time you can flash back the database. Next, the Flashback Database can be enabled with ALTER DATABASE FLASHBACK ON command. Finally, the database can be opened for normal use.
SQL> connect / as sysdba
SQL> startup mount
SQL> alter system set db_flashback_retention_target=4320; # for 3days
SQL> alter database flashback on;
SQL> alter database open;

Using Flashback Database with RMAN
Flashback database can be used with RMAN to perform recoveries. You need to get either OLDEST_FLASHBACK_SCN or OLDEST_FLASHBACK_TIME from V$FLASHBACK_DATABASE_LOG view. This will allow you to utilize the TO SCN or TO TIME clause in the FLASHBACK DATABASE clause. There is also TO SEQUENCE clause, which uses redo log sequence and thread to perform recovery.

Ex: Performing Flashback Database recovery to a SCN

SQL> connect / as sysdba
SQL> select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

Next, shutdown and start the instance in MOUNT mode
SQL> shutdown
SQL> startup mount

> rman
RMAN> connect target
RMAN> flashback database to scn=689316;

Finally, open database with RESETLOGS option
SQL> alter database open resetlogs;

The V$FLASHBACK_DATABASE_LOG dynamic view is useful for both TO SCN and TO TIME recoveries.

Monitoring Flashback Database
Flashback database can be monitored using few dynamic views: V$DATABASE, V$FLASHBACK_DATABASE_LOG, and V$FLASHBACK_DATABASE_STAT.
V$DATABASE displays if the Flashback Database is on or off.
SQL> select flashback_on from v$database;

The V$FLASHBACK_DATABASE_LOG view is new to 10g and was created to support Flashback Database.
SQL> select oldest_flashback_scn, oldest_flashback_time, retention_target, estimated_flashback_size from v$flashback_database_log;


The V$FLASHBACK_DATABASE_STAT is used to monitor the overhead of maintaining the data in the Flashback Database logs.
SQL> select * from v$flashback_database_stat;

Using Flashback Database with Enterprise Manager

Configuring Flashback Database with EM
To configure Flashback database with EM, you have to log in with SYSDBA account. Go to Maintenance screen -> choose Configure Recovery Settings under Backup/Recovery section.

Using Flashback Database with EM
Maintenance -> Perform Recovery

On the Perform Recovery: Type page, specify the type of recovery.

The database will need to be shutdown and mounted to proceed with the recovery. After 2 or 3 min, click Refresh button. An information screen appears that tells the database is unavailable.

Click Perform Recovery and choose type of recovery you need to perform.

Monitoring Flashback Database with EM
Maintenance -> Configure Recovery Settings

Using OEM for Backup and Recovery

Configuring RMAN settings with Enterprise Manager
Click Maintenance link to go to maintenance screen, and then click Configure Backup Settings link.

Database Recovery Using Enterprise Manager
Recovering the database using EM performs the identical process of performing recovery with the RMAN command-line interface (CLI), except it is done through EM web interface.

EM Main Screen -> Maintenance tab -> Select Perform Recovery under Backup/Recovery

When you start Performance Recovery wizard, it will ask for host credentials username and password. This account needs to have administrative privilege for Windows and Oracle user privilege in Unix. You also need to enter database credentials.

Performing User-Managed Incomplete Recovery

User-managed incomplete recovery can be performed by time, change, and cancelling. The methods of performing incomplete recovery are using the RECOVER DATABASE command with UNTIL TIME, CHANGE, or CANCEL clauses.

Ex: time-based recovery

Make sure that NLS_DATE_FORMAT is set to a value that you can reproduce in SQL*Plus.
C:\> set NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS

Remove USERS01.dbf and restore it from backup using copy command to simulate the recovery situation:
C:\oracle\ora101t\> delete USERS01.dbf
C:\oracle\ora101t\> copy C:\oracle\backup\ora101t\USERS01.dbf

SQL> startup mount

SQL> recover database until time ’06-SEP-2004 15:15:00’;

SQL> alter database open resetlogs;

Ex: cancel-based recovery

Copy online backup of all the datafiles that makeup ora101t
C:\oracle\ora101t\> copy C:\oracle\backup\ora101t\*.dbf

SQL> connect / as sysdba
SQL> @backup_control_resetlogs.sql #create control file by using backup control to trace

SQL> recover database using backup controlfile until cancel;
SQL> alter database open resetlogs;

Performing Incomplete Recovery

Incomplete recovery is a recovery that stops before the failure that forced the recovery. Another way of looking at incomplete recovery is that not all the transactions in archived redo logs get applied to the database to make the database complete. With incomplete recovery, after the recovery process has ended, the database is still missing transactions that were in the database before failure.
Incomplete recovery is sometimes called database point-in-time recovery (DBPITR), because this recovery is to a determined point-in-time.
RMAN incomplete recovery is performed using SET UNTIL TIME and SET UNTIL SEQUENCE clauses prior to RECOVER command. These clauses direct the recovery process to stop at a designated time, a redo log sequence, or a system change number (SCN) before full recovery is completed.
User managed incomplete recovery is performed by using RECOVER DATABASE command in conjunction with the UNTIL TIME, UNTIL CHANGE, or UNTIL CANCEL clauses. The UNTIL CANCEL clause is designed to just stop the recovery process at a random point.

NOTE: RMAN based incomplete recovery doesn’t have a CANCEL-based option; however, RMAN-based incomplete recovery has SCN and SEQUENCE methods.

RMAN Incomplete Recovery
RMAN incomplete recovery can be performed by time, redo log sequence, or SCN. To perform an incomplete recovery, use the RECOVER DATABASE command with the UNTIL TIME, SCN, or SEQUENCE clause or use SET UNTIL clause prior to the RECOVER DATABASE command.

SQL> startup mount

Make sure NLS_DATE_FORMAT is set to a value that you can reproduce in RMAN:
C:\> set NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS

RMAN> run
{
set until time ’06-SEP-2004 11:25:00’;
restore database;
recover database;
}
RMAN> alter database open resetlogs;

Example using sequence based recovery, which uses a redo log sequence number to terminate the recovery process. There are some steps required to identify the redo log sequence number that require accessing V$REDO_LOG_HISTORY dynamic view.

Get the sequence number and thread information using V$LOG_HISTORY.
SQL> select * from log_history;

SQL> startup mount

RMAN> run
{
set until sequence 3 thread 1;
restore database;
recover database;
}

RMAN> alter database open resetlogs;

Recovering from Control File loss

Control files contain RMAN metadata information and the required repository information, if you are not going to use Recovery Manager Catalog. In 10g, RMAN has introduced control file autobackup, which allows you to configure RMAN to automatically backup the control file with other backups directly to the flash recovery area. This assures that you have control file for recovery purposes.

Recovering a Control File Autobackup
First configure RMAN settings to perform a control file autobackup, which consists of enabling a configuration parameter. The control file autobackup configures all backups to automatically backup the control file. If you are not using recovery catalog, you need to specify the database identifier (DBID) after connecting to the target database when performing control file recovery operation.
Ex:
RMAN> connect target //note the DBID of database
RMAN> configure controlfile autobackup on;
RMAN> run
{
backup database;
backup (archivelog all);
}

Next, you simulate the missing control files by deleting all control files. The database will need to be shutdown to perform this simulation)
C:\oracle\>delete *.ctl

Next, start the database in NOMOUNT mode, which is required because there is no control file to mount.
SQL> startup nomount
RMAN> connect target /
RMAN> set dbid 2738992764;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;

Re-creating Control File
The control file contains physical map of an Oracle database. In other words, control file has all the locations of the physical files, including datafiles, redo logs, and control files. Control file has also the information about whether the database is in ARCHIVELOG mode, as well as RMAN metadata information.
The control file create script can be created with the command ALTER DATABASE BACKUP CONTROLFILE TO TRACE.

SQL> alter database backup control file to trace;

The trace file will be present in UDUMP location. The trace file has two options: RESETLOGS and NORESETLOGS. In this case we will go with NORESETLOGS. Save the file as backup_script_noresetlogs.sql.

Simulate the loss of control file by deleting control files for the database. This is performed with the database shutdown.
C:\oracle\> delete *.ctl

SQL> startup nomount
SQL> @backup_script_noresetlogs.sql

Control file will be created.

Recovery Walk Through and User Managed Recovery

Walk through using RESTORE and RECOVER command to restore database from backup.
First, target db must be in a mounted state to perform a full database recovery. The database can be open if you are performing online tablespace recovery or something less than full database recovery.
SQL> startup mount

Run RESTORE and RECOVER command
RMAN> run
{
allocate channel c1 type disk;
restore database;
recover database;
alter database open;
}

User Managed Recovery
It is the traditional recovery method where you directly manage the database files required to recover the database using operating system commands.
Ex:
SQL> startup

You get an error saying that USERS01.dbf file is missing.

Restore the USERS01.dbf file, using cp command

SQL> startup mount
SQL> recover database;
SQL> alter database open;

Recovering from Non-critical files

An Overview of Non-critical files
The recovery of non-critical files is an important matter that you should be familiar with so you can resolve them in an efficient manner. Non-critical files are essentially database files that do not have a critical impact on the operations of the database when they have been compromised.

• Temporary Tablespaces: Can be recovered without impacting database operations. All database users need a temporary tablespace of some kind to perform database operations. They essentially provide sorting operations.

• Redo log files: Non-current redo log files are also considered non-critical database files. A lost redo group can be much more severe and does not come into the category of a non-critical recovery

• Index tablespaces: Index tablespaces contain only indexes and can be re-created or rebuilt.

• Read-only tablespaces: These tablespaces are static. This allows recovery to be fairly straightforward process under most circumstances.

• Password files: Password files contain the passwords for privileged administrative users such as SYSDBA and SYSOPER. This allows you to connect remotely to a database instance and perform administrative functions. The password file can be deleted and re-created if necessary.

Creating a New Temporary Tablespace
A temporary tablespace is responsible for various database sorting operations. A temporary tablespace is part of the physical database files, which the Oracle control file will expect to exist under normal operations. Because temporary tablespace does not have any permanent objects stored within it, there is no change in the SCN from the checkpoint process in the control file or file header.

SQL> create temporary tablespace temp2 tempfile
‘C:\oracle\ora101t\temp2_01.dbf’ size 100M
Extent management local uniform size 1M;

Starting Database with a Missing Tempfile
startup mount

drop tablespace temp including contents;

create temporary tablespace temp tempfile
‘C:\oracle\ora101t\temp01.dbf’ size 100M
extent management local uniform size 1M;

Altering Default Temporary Tablespace
alter database default temporary tablespace temp2;

Re-creating Redo Log Files
Redo logs contain all the transactions committed or uncommitted. An important standard for creating Oracle database is to have mirrored redo logs, also called multiplexed redo logs. If a redo log member is lost or deleted and the mirrored log member still exists, then redo log member can be easily rebuilt. The command ALTER DATABASE ADD LOGFILE MEMBER will create a log member if one has been lost or deleted.

SQL> alter database drop logfile member ‘C:\oracle\redo01.log’;
SQL> alter database add logfile member ‘C:\oracle\redo01.log’ to group 1;

NOTE: Make sure that the database is in restrict mode if you do not have many redo logs.

Recovering an Index Tablespace
Recovering the database with a missing index tablespace is another non-critical recovery. An index tablespace should contain only indexes. Indexes are objects that can be created from the underlying database tables. Rebuild index scripts can be rerun to build the indexes in the index tablespace.

startup mount

drop tablespace indexes including contents;

create tablespace indexes datafile ‘C:\oracle\ora101t\index01.dbf’ size 20M;

Re-creating Indexes:
Re-creating indexes is required after rebuilding the index tablespace. As long as you have the create index scripts, this is a non-critical recovery process.

Recovering Read-Only Tablespaces
A read-only tablespace is a tablespace that contains static information. This means that in most cases, no media recovery is needed.

Re-creating the Password File
There are multiple methods for a DBA to authenticate to an Oracle database. The standard method is to log in directly to the operating system of the server, connect directly to the database with Inter-Process Control (IPC), and establish local connection on the database, which does not need to use SQL*Net. This method requires the operating system’s account to require the password for validation. Once in the secure OS account, you can connect as SYSDBA or SYSOPER. SYSOPER has partial database administration privilege, which is good for operational support.
A second primary method is to connect remotely using SQL*Net and authenticate with a password file. The password file is required for all remote database administrative connections to an Oracle database using SYSDBA or SYSOPER. ORAPWD is an Oracle utility that generates a password file for remote connections. ORAPWD should be run when the database is shutdown. When using ORAPWD, one should use appropriate naming convention, which includes orapw$ORACLE_SID. The password file must be located in $ORACLE_HOME/dbs in UNIX and in $ORACLE_HOME\database in Windows. The init.ora file must also contain REMOTE_LOGIN_PASSWORDFILE parameter, set to SHARED or EXCLUSIVE.

orapwd file=orapwdORA101T password=syspass entries=20

The entries option determines how many users can be stored in password file. To see what users are utilizing the password file,
SQL> select * from v$pwfile_users;


NOTE:
Redo log member cannot be added to current or active online redo log group, because the log group is actively recording transactions

Monitoring RMAN Backups / List and Report commands

Monitoring RMAN Backups
Monitoring actual sessions during RMAN backups or recoveries can be performed utilizing RMAN dynamic views.

V$RMAN_OUTPUT Displays messages reported by an RMAN job in progress

V$RMAN_STATUS Displays the success or failure of all completed RMAN jobs

V$RECOVER_FILE Shows the datafiles that require recovery

An incarnation is a unique backup of the target database that is identified by a unique DB_KEY value. A new incarnation is generated each time a database is opened with RESETLOGS or BACKUP CONTROLFILE.

SQL> select dbid from V$database;
SQL> conn rman_user/rman_user@ora101rc
SQL> select db_key from rc_database where dbid=72737292;

Using LIST Commands
Is used to query the RMAN repository and get the data regarding the BACKUP command, COPY command, and database incarnations. The output of LIST commands displays the files that the CHANGE, CROSSCHECK, and DELETE commands have used.
LIST command displays backup information by using the BY BACKUP and BY FILE options. There are also SUMMARY and VERBOSE options to condense or expand the output.

RMAN> list backupset by backup summary;

RMAN> list backupset by file;

Using REPORT commands
Used to query RMAN repository and get the data regarding which need a backup, unneeded backups, database physical schema, and whether or not unrecoverable operations were performed on files. The output of REPORT commands will generate more detailed information from RMAN repository.

NOTE: The RMAN repository must synchronize with the controlfile. In addition, the CHANGE, UNCATALOG, and CROSSCHECK commands should have been recently executed for the report to be completely accurate.

The REPORT command options include REPORT NEED BACKUP, REPORT OBSOLETE, and REPORT SCHEMA.

RMAN> report obsolete;
RMAN> report schema;

Block Change Tracking using RMAN

Enabling and Disabling Block Change Tracking
Block change tracking is new capability in Oracle 10g. The block change tracking process records the blocks modified since the last backup and stores them in a block change tracking file. RMAN uses this file to determine the blocks that were backed up in an incremental backup. This improves performance because RMAN doesn’t have to scan the whole datafile during the backup. This change was a big improvement for large databases.
Block change tracking is enabled and disabled with a SQL command. By default, block change tracking is disables. Block change tracking status can be verified by accessing a dynamic view v$block_change_tracking.

SQL> alter database enable block change tracking using file ‘C:\oracle\ora_block_track.log’;

NOTE: There is a new background process responsible for writing data to the block change tracking file, which is called block change writer CTRW.

SQL> select filename, status, bytes from v$block_change_tracking;
SQL> alter database disable block change tracking;

Parallelization of BackupSets and Backup Options

Parallelization of Backup Sets
Parallelization of backup sets is performed by causing multiple backup sets to be concurrently backed up over multiple device channels. This is done by allocating multiple channels, one for each backup set that needs to be concurrently backed up before the backup process occurs. You can either modify the CONFIGURE settings for channel parallelism greater than 1 or use manual channel allocation.

RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup
(datafile 1, 2, 3 channel c1)
(archivelog all channel c2);
}

The automated method of parallelizing your backup requires modifying the CONFIGURE setting of parallelization parameter.
RMAN> configure device type disk parallelism 3;
RMAN> backup
(datafile 1, 2)
(datafile 3, 4)
(archivelog all);

Backup Options
RMAN provides many options for the backup process. These options control filenames, backup performance, and the size of backups. Options that control filenames are handled with FORMAT and TAG parameters with BACKUP command. The RATE option limits backup I/O bandwidth usage on a computer. This limits RMAN from consuming all of a server’s resources during backup operations. The DURATION option determines the maximum time a backup can process before being terminated. The options that control sizes are MAXSETSIZE and MAXPIECESIZE. These options limit the size of backup sets and backup pieces.

RMAN> backup tablespace users format=’user_bs_%d%p%s’;

RMAN> backup as copy tablespace users format=’C:\oracle\backup\ora101c\users_%d%p%s’;

RMAN> backup database tag weekly_backup;

The RATE option is designed to limit RMAN from using excessive system resources during backup and restore operations.
RMAN> configure channel device type disk rate 5M;

RMAN> configure channel device type disk maxsetsize=10G;
RMAN> backup database maxsetsize=10G;

RMAN> configure channel device type disk maxpeicesize=2G;

NOTE: Caution must be taken when using MAXSETSIZE parameter. If the datafile being backed up is larger than the MAXSETSIZE parameter, the backup will fail.

Compressed, Full and Incremental Backups

Compressed Backups
New with 10g RMAN is capability to compress backups. In previous versions, reducing the size of backups was performed by backing up only used blocks and skipping unused blocks. With 10g, you can now compress backups regardless of the contents of the datafiles. This allows real compression of backups. Compressed backups works only with backup sets, not image copies. This includes database, tablespace, and datafile backup sets.

RMAN> backup as compressed backupset database;

A default device can be configured for compressed backups.
RMAN> configure device type disk backup type to compressed backupset;

NOTE: Compressed database backup sets are compressed at approximately a 5-to-1 ratio, or 20 percent of the size of a standard backup set.

Full and Incremental Backups
The full and incremental backups are differentiated by how the data blocks are backed up in the target database. The full backup backs up all the data blocks in the datafiles, modified or not. An incremental backup backs up only the data blocks in the datafiles that were modified since the last incremental backup. The baseline backup for an incremental backup is a level 0 backup. A level 0 backup is a full backup at that point in time. Thus, all blocks, modified or not are backed up, allowing the level 0 backup to serve as a baseline for all future incremental backups. Benefit of incremental backup is that it is quicker, because not all data blocks need to be backed up.
There are two types of incremental backups: differential and cumulative. Both differential and cumulative backups backup only modified blocks.
Differential incremental backup backs up only data blocks modified since the most recent backup at the same level or lower. It is the default incremental backup. The cumulative incremental backup backs up only the data blocks that have changed since the most recent backup of the next lowest level or n-1 or lower (with ‘n’ being the existing level of backup).

NOTE: Full backups do not mean the complete database was backed up.

Performing Differential Incremental Backup
RMAN> backup incremental level 0 database;

Next, take level 1 incremental backup after some data has been changed in the database. The incremental level 1 backup will pick up the changes since the level 0 backup.
RMAN> backup incremental level 1 database;

Performing Cumulative Incremental Backup
It requires more space than incremental back ups. The benefit of this is that cumulative incremental backups are usually faster and easier to restore because only one backup for a given level is needed to restore.

RMAN> backup incremental level 1 cumulative database;

RMAN Backup Sets and Image Copies

Database files in backup sets are stored in a special RMAN format and must be processed with the RESTORE command before these files are usable. This can take more time and effort during the recovery process.

Creating Backup Sets
The RMAN BACKUP command is used to perform the backup set backup process.

>rman
RMAN> connect target
RMAN> run
{
allocate channel c1 type disk;
backup database format ‘db_%u_%d_%s’;
backup format ‘log_t%t_s%s_p%p’
(archivelog all);
}

NOTE: Backup sets have an inherent performance capability called multiplexing. Image copies cannot be multiplexed.

Creating Image Copies
Image copies are actual copies of database files, archive logs, or control files and are not stored in a special RMAN format. Image copies can be stored only on disk. An image copy in RMAN is equivalent to an operating system copy command such as cp in Unix or COPY in Windows. Thus, no RMAN restore processing is necessary to make image copies usable in a recovery situation. This can improve speed and efficiency of restore and recovery process. However, there is also a price for this restore efficiency—the size of image copy backups. The image copy backup cannot be compressed and requires much more space than backup set.

RMAN> run
{
allocate channel ch1 type disk;
copy
datafile 1 to ‘C:\oracle\staging\ora101t\system.dbf’,
current controlfile to ‘C:\oracle\staging\ora101t\control01.ctl’;
}

In Oracle 10g, there is a new backup command that simplifies image copies: BACKUP AS COPY. The benefit of this image copy is that you can perform image copies of an entire database, multiple tablespaces, datafiles and archive logs without having to specify all of the individual files.

[OR]

RMAN> connect target
RMAN> backup as copy tag “062508_backup” database;