How To Use Active Data Guard Feature In Oracle 11g

How To Use Active Data Guard Feature In Oracle 11g

oracle 11g introduces new feature of Active Data Guard in which standby database is in Read Only Mode as well as applying redo from primary database.

Means your standby database is syncing with your primary database and at the same time you can use your standby as a reporting server in OLTP.

For activating Active Data Guard feature in 11g you have to issue these commands at

Oracle Standby Database

1. SHUTDOWN THE STANDBY DATABASE

  SQL> SHUTDOWN IMMEDIATE;


 2. STARTUP THE STANDBY DATABASE IN NOMOUNT STAGE ( AT NOMOUNT STAGE ONLY    PARAMETER FILE IS READ)
 
  SQL> STARTUP NOMOUNT ;


 3. MOUNT THE STANDBY DATABASE( AT MOUNT STAGE CONTROL FILE IS READ)

  SQL> ALTER DATABASE MOUNT STANDBY DATABASE ;


 4. OPEN THE STANDBY DATABASE IN READ ONLY MODE STAGE 

  SQL> ALTER DATABASE OPEN READ ONLY;


 5. APPLY THE MANAGED RECOVERY PROCESS
 
  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM    SESSION;

How To Re-create Oracle Database 11g Enterprise Control Manager

HOW TO RE-CREATE ORACLE DATABASE 11G ENTERPRISE CONTROL MANAGER

Steps are:

Drop the user and dependent on version a few other objects, like:

SQL>DROP USER sysman CASCADE; SQL>DROP PUBLIC SYNONYM setemviewusercontext; SQL>DROP ROLE mgmt_user; SQL>DROP PUBLIC SYNONYM mgmt_target_blackouts; SQL>DROP USER mgmt_view;

It will take some time for droping the user

and after dropping user and their role drop all the public synonym which are invalid after dropping the above users.

Save the below query result as a text file invalid_synonym.txt

SQL>SELECT 'drop public synonym ' || a.object_name || ';' FROM all_objects a WHERE a.object_type='SYNONYM' AND a.owner = 'PUBLIC' AND a.STATUS = 'INVALID'

And run it in sqlplus command prompt as

SQL> @ADDRESS_OF_FILE/invalid_synonym.txt;

and then issue the below command on linux prompt and follow the instructions

STARTED EMCA at Jul 13, 2008 8:28:48 AM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  ALL rights reserved.
 
Enter the following information:
DATABASE SID: orcl
Listener port NUMBER: 1521
Password FOR SYS USER:
Password FOR DBSNMP USER:
Password FOR SYSMAN USER:
Password FOR SYSMAN USER: Email address FOR notifications (optional):
Outgoing Mail (SMTP) server FOR notifications (optional):
You have specified the following settings

DATABASE ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1

LOCAL hostname ................ ora
Listener port NUMBER ................ 1521
DATABASE SID ................ orcl
Email address FOR notifications ...............


Outgoing Mail (SMTP) server FOR notifications ...............

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

Do you wish TO continue? [yes(Y)/no(N)]: y

and it will take some time to create enterprise manager

important commands

for creation of enterprise manager in linux on oracle 11g

emca -config dbcontrol db -repos create

for Dropping of enterprise manager in linux on oracle 11g

emca -config dbcontrol db -repos DROP

for status of enterprise manager in linux on oracle 11g

emca -config dbcontrol db -repos STATUS

Feature of DDL With the WAIT Option OR Ora-00054 Resource busy error solution in Oracle Database 11g

This is usually common that some time when we try to modify some table and 

we got error of "ORA-00054: resource busy" because when we try to modify table 

that table is in use.
For overcoming this error in oracle 11g database introduce a parameter 

DDL_LOCK_TIMEOUT in which you set the time for which your modify command wait 

for showing the error of Ora-00054 . And you can set DDL_LOCK_TIMEOUT parameter 

at session as well as on instance level what ever you want.
Example:-
1. In your First session
CREATE TABLE new (
  no  NUMBER
);

INSERT INTO new VALUES (10);
2. In your second session
open another session and set  DDL_LOCK_TIMEOUT to value say 60 sec
ALTER SESSION SET ddl_lock_timeout=60;
and then issue alter command on created table


ALTER TABLE new ADD (
  name  VARCHAR2(50)
);
Note: Here  DDL_LOCK_TIMEOUT value is 60 if in between these 60 seconds you 

issue commit command in your first session then your alter command run 

successfully in second session and if you didn't issue commit command in 

first session then your second session wait for 60 seconds and on 61st seconds 

it will give the error of  

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.
 

First Case: if issue commit in first session 

 1. In your First session 

 CREATE TABLE new (
  no  NUMBER
);

INSERT INTO new VALUES (10);
commit;
2. In your second session 

 ALTER TABLE new ADD (
  name  VARCHAR2(50)
);

Table altered.

SQL>

 Second Case: if commit is not issue in first session 

 1. In your First session
CREATE TABLE new (
  no  NUMBER
);

INSERT INTO new VALUES (10);
2. In your second session
ALTER TABLE new ADD (
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

How To Create Restore Point In Oracle 11g With Illustration

HOW TO CREATE RESTORE POINT IN ORACLE 11G WITH EXAMPLE

SQL> CREATE RESTORE POINT before_update GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:20:27 2012

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

RMAN> connect target

connected to target database: DB (DBID=2351874955)

RMAN> list restore point all

2> ;

using target database control file instead of recovery catalog

SCN RSP Time Type Time Name

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

3280671 GUARANTEED 12-OCT-12 BEFORE_UPDATE

RMAN> exit

Recovery Manager complete.

SQL> create table before_restore_point(ename varchar2(40));

Table created.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:21:35 2012

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

RMAN> shutdown immediate

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of shutdown command at 19/20/2012 16:21:40

RMAN-06171: not connected to target database

RMAN> connect target

connected to target database: DB (DBID=2351874955)

using target database control file instead of recovery catalog

RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE';

Starting flashback at 20-OCT-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=241 device type=DISK

starting media recovery

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of flashback command at 10/20/2012 16:22:21

ORA-38757: Database must be mounted and not open to FLASHBACK.

RMAN> exit

Recovery Manager complete.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:22:35 2012

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

RMAN> connect target

connected to target database: DB (DBID=2351874955)

RMAN> shutdown immediate

using target database control file instead of recovery catalog

RMAN> exit

Recovery Manager complete.

SQL> shutdown abort

ORA-03135: connection lost contact

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing

[oracle@oracleoel test]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 20 16:25:21 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:25:46 2012

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

RMAN> connect target

connected to target database (not started)

RMAN> startup mount

Oracle instance started

database mounted

Total System Global Area 1837244416 bytes

Fixed Size 2214336 bytes

Variable Size 1207961152 bytes

Database Buffers 603979776 bytes

Redo Buffers 23089152 bytes

RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE';

Starting flashback at 20-OCT-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=201 device type=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished flashback at 20-OCT-12

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.

SQL> select instance_name,status from v$instance;

select instance_name,status from v$instance

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 22495

Session ID: 153 Serial number: 3

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oracleoel test]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 20 16:27:10 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

DB OPEN

SQL> select * from before_restore;

select * from before_restore

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select * from before_restore_point;

select * from before_restore_point

*

ERROR at line 1:

ORA-00942: table or view does not exist

How To Make Tables Read Only in oracle 11g Database with illustration

How To Make Tables Read Only in oracle 11g Database

there is new feature of making tables read only in oracle 11g database .prior to oracle 11g there ,you can make tables read only to other users by only give SELECT grant to the user.

For making tables read only in oracle 11g

alter table table_name read only;

example:-

let us we have to make a table named TEST (no number(10),name varchar2(30)) as read only

1. SQL> select * from test;
   SQL> insert into test values (2,'kumar');
   SQL> commit;
2. SQL> alter table test read only;
   SQL>insert into test values (3,'john');
   ORA-12081: update operation not allowed on table "TEST"
   SQL> update test set no=1 where no=2
   ORA-12081: update operation not allowed on table "TEST"

Now TEST table is read only no one can alter ,update or delete operation on TEST.

For making tables read only to read write oracle 11g

3. SQL> alter table test read write;

SQL> insert into test values (3,'john');
SQL> commit;

How To Enable Archivlelog Mode In Oracle Datbase 11g

How To Enable Archivlelog Mode In Oracle Datbase 11g

for enabling archive log mode in oracle 11g

steps are:-

1. SQL> ARCHIVE LOG LIST;
   Database log mode NoArchive Mode
   Automatic archival Disabled
   Archive destination /Database/archivelog/
   Oldest online log sequence 1129
   Next log sequence to archive 1131
   Current log sequence 1131
2. Database is in noarchive log mode to change it to archivelog mode the first requirement is that database must be mounted not opened for mounting the database shutdown the database and then mount it for this issue the following command
   SQL> SHUTDOWN IMMEDIATE
   SQL> STARTUP MOUNT

after putting the database to mount stage issue the following command

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
now issue the command to check whether database is in archivelog mode or not
2. SQL> ARCHIVE LOG LIST;
   Database log mode NoArchive Mode
   Automatic archival Disabled
   Archive destination /Database/archivelog/
   Oldest online log sequence 1132
   Next log sequence to archive 1133
   Current log sequence 1134

Feature of Case sensitive passwords in 11g with illustration

Feature of Case sensitive passwords in 11g with illustration

In Oracle 11g database you can use case sensitive security feature with parameter

SEC_CASE_SENSITIVE_LOGON

How to enable case sensitive password feature

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE
SQL> 


SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

System altered.


CONN / AS SYSDBA
CREATE USER test IDENTIFIED BY Test;
GRANT CONNECT TO test;
SQL> CONN test/Test
Connected.
SQL> CONN test2/test2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

Here you can easily see that if SEC_CASE_SENSITIVE_LOGON to TRUE then you have to enter password in exact case in which you assign the password to user and in case when you change the case of password it is not connected .

How to Disable case sensitive password feature

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> 


SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;



System altered.

CONN / AS SYSDBA
CREATE USER test_2 IDENTIFIED BY Test_2;
GRANT CONNECT TO test;
SQL> CONN test_2/Test_2
Connected.
SQL> CONN test_2/test_2

Connected.


Here you can easily see that if SEC_CASE_SENSITIVE_LOGON to FALSE then you have to enter password in any case you connected .