Redo Log Recovery

Recover Redo01.dbf
============================
SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP MOUNT;

SQL>SELECT GROUP#, ARCHIVED,STATUS FROM V$LOG;
1 NO CURRENT
2 YES INACTIVE
3 YES INACTIVE

SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

DROP logfile Group
====================
SQL>ALTER DATABASE DROP LOGFILE GROUP 1;
(Remove redo01.log manualy)

Create Logfile Group
====================

SQL>ALTER DATABASE ADD LOGFILE GROUP 1 ('/opt/app/oracle/oradata/scdcc/redo01.log') size 50000K;

SQL>ALTER DATABASE OPEN;

Oracle Index

Oracle Index
An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

Indexes are automatically maintained and used by Oracle.


SQL>create index test on company (cname);

Index created.

SQL>drop index test;

Index dropped.

Altering Oracle Index
SQL>ALTER INDEX ix_emp_01 REBUILD ;

Synonym

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

SQL>create public synonym suppliers for app.suppliers;

Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app..

SQL>select * from suppliers;



Drop public synonym suppliers;

SQL>drop public synonym suppliers;
This drop statement would drop the synonym called suppliers that we defined earlier.

Create View

A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

SQL> CREATE OR REPLACE VIEW myView (First_Name, Last_Name) AS
select First_Name,Last_Name
from Employee
where Salary > 2000;

View created.


SQL> drop view myView;

View dropped.

Official Certification Track

Preparation video

OCA 11g Preparation 4

Give name of manager and salary of employee SUNIL.

SQL>select emp_company.salary,manager.mname
from emp_company,manager
where emp_company.ename = 'SUNIL'
and emp_company.ename = manager.ename;

List the employee living in city 'BOMBAY' and those having company located in city 'DELHI'

SQL>select ename from emp_company
where ename In
(select ename from employee where city='BOMBAY') and
cname In (select cname from company where city ='DELHI');

OCA 11g Preparation 3

Basic SQL Commands

List name of companies located in BOMBAY.
SQL>select cname from company where city =`BOMBAY';

list name of employees living in city `NAGPUR'
SQL>select ename from employees where city = `Nagpur';

List the names of employee who are not employee with the company 'ACC'.
SQL>select ename from emp_company where not (cname='ACC');
-OR-
SQL>select ename from emp_company where cname<>'ACC';

Suppose you want the names of employee from company 'ACC' or 'TATA'
SQL>select ename from emp_company where cname='ACC' or cname='TATA';

LAB:-
Create your own commands to find out the required data

OCA 11g Preparation 2

Create Tables and data or Install example database for Learning SQL Basics.

$sqlplus
Enter user-name: shinoj/shinoj

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> select * from tab; (List all the Tables)

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COMPANY TABLE
EMPLOYEE TABLE
EMP_COMPANY TABLE
EMP_SHIFT TABLE
MANAGER TABLE
SALES_DATA TABLE

6 rows selected.

Details of Each Tables.........

SQL> desc company;
Name Null? Type
----------------------------------------- -------- ----------------------------
CNAME VARCHAR2(30)
CITY VARCHAR2(30)

SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(30)
CITY VARCHAR2(30)

SQL> desc emp_company;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(30)
CNAME VARCHAR2(30)
SALARY NUMBER(7,2)
JDATE DATE

SQL> desc emp_shift;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(30)
SHIFT CHAR(3)

SQL> desc manager;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(30)
MNAME VARCHAR2(30)

SQL> desc sales_data;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODID NOT NULL NUMBER(6)
CUSTID NOT NULL NUMBER
TIMEID NOT NULL DATE
CHANNELID NOT NULL CHAR(1)
PROMOID NOT NULL NUMBER(6)
QUANTITY NOT NULL NUMBER(3)
AMOUNT NOT NULL NUMBER(10,2)


I have just imported two dumps to create the above tables and data
1. company.dmp
2. sales.dmp

Creating tables and inserting data manually will help to improve SQL commands..Hi hi

OCA 11g Preparation 1

Certification Path:-
OCA
SQL - 125$ - (1Z0-051)
Admin1 - 195$ - (1Z0-052)
============================

OCP
Admin2 - 195$ - (1Z0-053)
============================

We have to create an enviornment first to start Preparation.....

Operating System - Red Hat Enterprise Linux 5
Oracle - Oracle 11g Ver 11.2.0.1.0
-----------------------------------------------------------------------------
Create a User for all R&D

SQL>

create user shinoj identified by shinoj
Default tablespace users
Quota unlimited on users;

grant connect, resource,DBA to shinoj;

grant create role to shinoj;

SQL>

Start Enterprise Manager

$emctl start dbconsole

Now you can access enterprise Manager by

https://serverIP:1158/em