Data Recovery Advisor 11g?

for example we shutdown the database and remove the datafile.Then when we startup the database the database will not open and we will get an error.
before removing the datafile take the backup of the datafile.I am deleting the undo datafile.
when a non critical datafile is missing we had two options
1)we can do recovery immediately.
2)we can take that datafile offline and open the rest of the database.

Here we are not opening the database just we are just recovering the datafile.

[oracle@subbu1 oradata]$ cp undotbs1.dbf undotbs1.dbf.bkp          (backup the undo datafile)

[oracle@subbu1 oradata]$ ls
arch           redolog1b.log  redolog2b.log  subbu1       temp1.dbf     undotbs1.dbf.bkp
redolog1a.log  redolog2a.log  rmanbkp        sysaux1.dbf  undotbs1.dbf

[oracle@subbu1 oradata]$ rm undotbs1.dbf              (here deleting the undo datafile)

[oracle@subbu1 oradata]$ ls
arch  redolog1a.log  redolog1b.log  redolog2a.log  redolog2b.log  rmanbkp  subbu1  sysaux1.dbf  temp1.dbf  undotbs1.dbf.bkp

[oracle@subbu1 oradata]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 23 17:28:29 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oradata/undotbs1.dbf'


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@subbu1 oradata]$ cd

[oracle@subbu1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 23 17:29:12 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SUBBU1 (DBID=880804144, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
242        HIGH     OPEN      23-SEP-16     One or more non-system datafiles are missing

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
242        HIGH     OPEN      23-SEP-16     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=17 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oradata/undotbs1.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 3
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oradata/subbu1/diag/rdbms/subbu1/subbu1/hm/reco_3896397302.hm


In another terminal go to the above repair script location

file 3;  and recover datafile
   restore datafile 3;
   recover datafile 3;

RMAN> restore datafile 3;

Starting restore at 23-SEP-16
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oradata/undotbs1.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oradata/rmanbkp/08rgf9ej.dbf%
channel ORA_DISK_1: piece handle=/u01/app/oradata/rmanbkp/08rgf9ej.dbf% tag=TAG20160922T174802
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 23-SEP-16

RMAN> recover datafile 3;

Starting recover at 23-SEP-16
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 41 is already on disk as file /u01/app/oradata/arch/1_41_922985200.dbf
archived log for thread 1 with sequence 42 is already on disk as file /u01/app/oradata/arch/1_42_922985200.dbf
archived log file name=/u01/app/oradata/arch/1_41_922985200.dbf thread=1 sequence=41
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-SEP-16

RMAN> exit;


Recovery Manager complete.

[oracle@subbu1 ~]$ cd /u01/app/oradata
[oracle@subbu1 oradata]$ ls
arch           redolog1b.log  redolog2b.log  subbu1       temp1.dbf     undotbs1.dbf.bkp
redolog1a.log  redolog2a.log  rmanbkp        sysaux1.dbf  undotbs1.dbf

we sucessfully recovered the datafile.

AWR report creation?

AWR stands for automatic workload repository.It consists of system performance statistics owned by the sys user.It contains the information about cache sizes(buffer cache and shared pool size),instance efficiency percentages(buffer nowait%,buffer hit%,library hit%),memory statistics(sga%,pga%),fore ground wait events,background wait events,instance activity statistics,tablespace statistics,check point activity and so on.

There is no need to connect using sysdba we can connect using system  user and password.

SQL> show user;
USER is "SYSTEM"

Here we are manually creating the snapshot of awr.

sql>exec dbms_workload_repository.create_snapshot;
           pl/sql procedure sucessfully completed.

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num       Instance

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

 1007941435 SUBBU2              1        subbu2

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: text

Type Specified:  text

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

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

* 1007941435        1 SUBBU2       subbu2       subbu2.erp.com

Using 1007941435 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

                                                        

Instance     DB Name        Snap Id    Snap      Started    Level

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

subbu2       SUBBU2               8 23 Sep 2016   15:20      1

                                                9 23 Sep 2016    16:30      1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 8

Begin Snapshot Id specified: 8

Enter value for end_snap: 9

End   Snapshot Id specified: 9

report will be in the default location i.e /home/oracle1/awrrpt_1_8_9.html

Please download the below sample file for your reference.

https://drive.google.com/file/d/0B2TY8bOhYJjFS1NuSml6V29MVnM/view?usp=sharing

How to change the password of SYSTEM in oracle 11g?

By default the system password will be manager.

[oracle1@subbu2 ~]$ sqlplus system/manager

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 23 16:44:41 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user;

USER is "SYSTEM"  Here i connected as system user. now i am changing this password to system.

[oracle1@subbu2 ~]$ sqlplus system/manager

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 23 16:44:41 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user;
USER is "SYSTEM"
SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> alter user system identified by system; 

User altered.

SQL> commit;

Commit complete.

SQL> conn system/manager
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn system/system;
Connected.
SQL> show user;

USER is "SYSTEM"

change date and time formats for database?

SQL> startup
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
Database mounted.
Database opened.

SQL> select sysdate from dual;

SYSDATE
---------
23-SEP-16   

Here it is showing in format in DD-MONTH-YY format.If i want to change this format to DD/MONTH/YYYY format.

SQL> alter session set nls_date_format='DD/MONTH/YYYY';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-----------------
23/SEPTEMBER/2016

If we want get the both(i.e date and time)

SQL> alter session set nls_date_format='DD/MONTH/YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE

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

23/SEPTEMBER/2016 15:56:47  --It is 24 hours format.

SQL> alter session set nls_date_format='DD/MONTH/YYYY HH:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE

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

23/SEPTEMBER/2016 03:57:18   --It is 12 hours format.

listener.ora and tnsnames.ora?

These are in the location cd $ORACLE_HOME/network/admin

vi listener.ora
prod = ( DESCRIPTION_LIST = ( DESCRIPTION = ( ADDRESS = ( PROTOCOL = TCP )( HOST = 192.168.205.131 )( PORT = 1521 ))))

SID_LIST_prod1 = ( SID_LIST = ( SID_DESC = ( ORACLE_HOME = /u01/app/subbu/product/11.2.0/dbhome_1 )( SID_NAME = prod )))

vi tnsnames.ora


to_subbu1 = (DESCRIPTION = (ADDRESS = (HOST = subbu1.erp.com)(PROTOCOL = TCP)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = subbu1)))

db links(database links)?

A database link is the connection between two database servers.Database links are used to access the server side records from client side.It is possible if we created the database link.

The main advantage of the database links is that they allow users to access the another users objects in a remote database.

Demo:--

192.168.75.129     subbu1.erp.com(host name)                     server side

[oracle@subbu1 ~]$ . subbu1.env

[oracle@subbu1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 22 14:39:07 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size    2211928 bytes
Variable Size  159387560 bytes
Database Buffers   50331648 bytes
Redo Buffers    5226496 bytes
Database mounted.
Database opened.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@subbu1 ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-SEP-2016 14:39:54

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start subbu1

Starting /u01/app/oracle/product/11.2.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/11.2.0/log/diag/tnslsnr/subbu1/subbu1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=subbu1.erp.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(HOST=subbu1.erp.com)(PROTOCOL=TCP)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     subbu1
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                22-SEP-2016 14:40:03
Uptime                    0 days 0 hr. 0 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/log/diag/tnslsnr/subbu1/subbu1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=subbu1.erp.com)(PORT=1521)))
Services Summary...
Service "subbu1" has 1 instance(s).
  Instance "subbu1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

192.168.75.132  subbu2.erp.com(host name)                            client side

[oracle1@subbu2 ~]$ . subbu2.env

[oracle1@subbu2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 22 14:41:55 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size    2211928 bytes
Variable Size  159387560 bytes
Database Buffers   50331648 bytes
Redo Buffers    5226496 bytes
Database mounted.
Database opened.

[oracle1@subbu2 ~]$ tnsping to_subbu1

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-SEP-2016 14:45:42

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (HOST = subbu1.erp.com)(PROTOCOL = TCP)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = subbu1)))
OK (5380 msec)

server:--

SQL> select username from dba_users;

USERNAME
------------------------------
OUTLN
U1
A1
SYS
SYSTEM
DIP
ORACLE_OCM
APPQOSSYS
DBSNMP

9 rows selected.

SQL> grant connect,resource to l1 identified by l1;

Grant succeeded.

SQL> select username from dba_users;

USERNAME
------------------------------
OUTLN
U1
A1
L1
SYS
SYSTEM
DIP
ORACLE_OCM
APPQOSSYS
DBSNMP

10 rows selected.

SQL> conn l1/l1;
Connected.
SQL> sho user;
USER is "L1"

SQL> select * from tab;

no rows selected

SQL> @?/sqlplus/demo/demobld
BUILDING DEMONSTRATION TABLES.PLEASE WAIT
DEMONSTRATION TABLES BUILD IS COMPLETE
SQL> select * from tab;

TNAME       TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS       TABLE
DUMMY       TABLE
EMP       TABLE
SALGRADE       TABLE

SQL> select count(*) from emp;

  COUNT(*)
----------
14

client:---

SQL> select username from dba_users;

USERNAME
------------------------------
OUTLN
U2
DL
SYS
SYSTEM
DIP
ORACLE_OCM
APPQOSSYS
DBSNMP

SQL> grant connect,resource to dl1 identified by dl1;

Grant succeeded.

Here we are granting create database link privilige to user dl1.if we want to check check what are the privileges for a user?
                           select * from session_privs;

SQL> grant create database link to dl1;

Grant succeeded.

SQL> conn dl1/dl1;
Connected.
SQL> select * from tab;

no rows selected

SQL> sho user;
USER is "DL1"

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

11 rows selected.

SQL> select * from user_db_links;

no rows selected

here we are trying to create the database link for serverside user l1 and client side user dl1 and we are trying to connect to server side user l1 with the password l1 from client side user dl1.

SQL> create database link dl1l1 connect to l1 identified by l1 using 'to_subbu1';

Database link created.

SQL> select * from user_db_links;

DB_LINK    USERNAME   PASSWORD HOST
---------- ---------- ---------- ----------
CREATED
---------------
DL1L1   L1 to_subbu1
22-SEP-16


SQL> SHO USER;
USER is "DL1"
SQL> select * from tab;

no rows selected

SQL> select count(*) from emp@dl1l1;

  COUNT(*)
----------
14

SQL> select * from tab@dl1l1;

TNAME       TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS       TABLE
DUMMY       TABLE
EMP       TABLE
SALGRADE       TABLE

SQL> sho user;
USER is "DL1"
SQL> select * from tab;

no rows selected

ORA-01045: user lacks CREATE SESSION privilege logon denied?

SQL> select username from dba_users;

USERNAME
------------------------------
OUTLN
SYS
SYSTEM
DIP
ORACLE_OCM
APPQOSSYS
DBSNMP

7 rows selected.

SQL> create user u1 identified by u1;

User created.

SQL> select username from dba_users;

USERNAME
------------------------------
OUTLN
U1
SYS
SYSTEM
DIP
ORACLE_OCM
APPQOSSYS
DBSNMP

8 rows selected.

SQL> conn u1/u1;
ERROR:
ORA-01045: user U1 lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> grant connect,resource to u1;
Grant succeeded.

SQL> conn u1/u1;
Connected.

HOT BACKUP

For taking the cold backup the database should be shutdown where as for hot backup there is no need to to shutdown the database.If the organization requires database 24/7 then hot backup is the only choice.

Prerequisites:- 
 1.In order to take the hot backup the archive log should be enabled.
 2.In hot backup control file will be backup at oracle level using a sql statement.
            Alter database backup control file to "location of backup controlfile".
 3.In cold backup we can restore the data but not in the hot backup because hot backup does not contain redolog files.Redologs  are not backed up because they are continuosly getting updated/modified because of transactions.

Whenever we keep our database in begin backup mode our SCN number will be freezed.

[oracle@appsdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 12 15:54:34 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
SUBBU

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/arch
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE                  0
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
         5 NOT ACTIVE                  0
         6 NOT ACTIVE                  0

6 rows selected.
Here not active indicates our database is not backup.

SQL> alter database begin backup;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                 632008 12-SEP-16
         2 ACTIVE                 632008 12-SEP-16
         3 ACTIVE                 632008 12-SEP-16
         4 ACTIVE                 632008 12-SEP-16
         5 ACTIVE                 632008 12-SEP-16
         6 ACTIVE                 632008 12-SEP-16


6 rows selected.