How To Check Tablespace Size Dynamically

------------------------------------------------------------------------
 How To Check Tablespace Size Dynamically 
------------------------------------------------------------------------

If you want to check Tablespace Size then use Below query.

 select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES;

 If you want to check Tablespace Size of particular tablespace then use below Query

 select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name='USERS';

 If you do not want to mention tablespace name again and again for different tablespaces then use Dynamic query for it

 select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name='&VALUE';

 Here i have used '&' symbol ahead of VALUE ,by using it it will ask for tablespace name at run time.

 If you do not want to write query again and again for tablespace size make it as a saved notepad file with .sql extension

 Name of .sql file is Tablespace_size.sql .

 Content of file :

col tablespace_name for a25
col file_name for a40
select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name='&VALUE';

Here

col tablespace_name for a25
col file_name for a40

used for getting output in readable form.

To run it from sqlplus just use @filelocation.file_name.sql

example : SQL> @Tablespace_Size.sql;

after invoking script , it asks for tablespace name ,then enter name in capital form or use UPPER function before '&VALUE' like

select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name=UPPER('&VALUE');

Here you can easily check that either you use Tablespace Name in small letters or capital you will get the desired result.

Use of TABLE_EXISTS_ACTION parameter in IMPDP

Use of TABLE_EXISTS_ACTION parameter in IMPDP

Table_Exists_Action used in impdp if table is already exists in the database where you want to import.
Possible values of TABLE_EXISTS_ACTION are:-


TABLE_EXISTS_ACTION = SKIP

Table_Exists_Action =Skip: skip is for skipping the table which are already exist in the database this is the default value for parameter table_exists_action.

TABLE_EXISTS_ACTION = APPEND

Table_Exists_Action =Append: append is for appending the data in tables which are already in the database .by using append new rows are inserted in tables and skipping already exist rows and for this number of columns and type of column matches.

TABLE_EXISTS_ACTION = TRUNCATE

Table_Exists_Action =Truncate:  In this option if tables is already exist in the database then truncate that table and insert the data into existing table and not changing the definition.
 
TABLE_EXISTS_ACTION = REPLACE

Table_Exists_Action =Replace:  replace is for replacing the data as well as the definition of existing table and it is basically for when table definitions are different in database from dmp file.

What is Async and Sync in Oracle Dataguard

What is Async and Sync in Oracle Dataguard

There are three protection modes for primary database in oracle dataguard

1.Maximum Protection

2.Maximum Performance

3.Maximum Availability

1.Maximum Protection

Transactions on the primary are not commit until redo information has been written to the online redo log and at least one of the the standby standby location. If there is not suitable standby location is available or found for any reason, the primary database shuts down.

2.Maximum Performance

Transactions on the primary database commit as soon as redo information has been written to the online redo log and Transfer of redo information to the standby server is asynchronous .so in this type of protection mode there is no impact on performance of the primary.

3.Maximum Availability

Transactions on the primary are not commit until redo information has been written to the online redo log and at least one of the the standby standby location. If there is not suitable standby location is available or found for any reason it acts same as the maximum performance mode.

For knowing the protection mode of your primary database use query

SQL>SELECT protection_mode FROM v$database;

if you want to switch into other mode from your current protection mode use

1.Maximum Availability

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

2.Maximum Performance

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

3.Maximum Protection

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

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