Friday, 10 January 2014

How to change rman setting using "CONFIGURE" command?

Configure * : - To change or set the configuration of rman will use configure command.

++++++++++++++++++++++++++++++++++++++++++++++
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)


RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/prod/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
new RMAN configuration parameters are successfully stored


++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN>


++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored

RMAN>


++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters are successfully stored

RMAN>


+++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE RETENTION POLICY CLEAR;
++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE RETENTION POLICY CLEAR;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
RMAN configuration parameters are successfully reset to default value

RMAN>



++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN>  CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
new RMAN configuration parameters are successfully stored

RMAN> show CONTROLFILE AUTOBACKUP FORMAT;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';

RMAN>


++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
RMAN configuration parameters are successfully reset to default value

RMAN>


+++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;
+++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
RMAN configuration parameters are successfully reset to default value

RMAN>


+++++++++++++++++++++++++++++++++++++++++++++
CONFIGURE DEFAULT DEVICE TYPE TO disk;
+++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

RMAN>


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored

RMAN>




++++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
+++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN>


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN>  CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored

RMAN>



++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN>


+++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;

old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION OFF;
new RMAN configuration parameters are successfully stored

RMAN>


++++++++++++++++++++++++++++++++++
CONFIGURING MAXSETSIZE
++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE MAXSETSIZE TO 100M;

new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 100 M;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE MAXSETSIZE TO UNLIMITED;

old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 100 M;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO UNLIMITED;
new RMAN configuration parameters are successfully stored

RMAN>


+++++++++++++++++++++++++++++++++++++++++++++++++++++++
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d02/oracle/snapcf_%d.f';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

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

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)


RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d02/oracle/snapcf_%d.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d02/oracle/snapcf_%d.f';
new RMAN configuration parameters are successfully stored

RMAN>

Posted by at No comments:

How to use show command in rman?


Show * :-   use to display the current configuration of rman.

[oracle@wasiq d01]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 21:43:39 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target /;
connected to target database: PROD (DBID=246965557)
using target database control file instead of recovery catalog
RMAN> SHOW RETENTION POLICY;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
RMAN> SHOW DEVICE TYPE;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
RMAN> SHOW CHANNEL;
RMAN configuration parameters for database with db_unique_name PROD are:
RMAN configuration has no stored or default parameters
RMAN> SHOW MAXSETSIZE;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE MAXSETSIZE TO UNLIMITED;
RMAN> CONFIGURE MAXSETSIZE TO 500m;
old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO UNLIMITED;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 500 M;
new RMAN configuration parameters are successfully stored
RMAN> SHOW MAXSETSIZE;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE MAXSETSIZE TO 500 M;
RMAN> SHOW BACKUP OPTIMIZATION;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> SHOW SNAPSHOT CONTROLFILE NAME;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d01/oracle/product/11.2.0/dbs/snapcf_prod.f';
RMAN> SHOW CONTROLFILE AUTOBACKUP;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> SHOW COMPRESSION ALGORITHM;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
RMAN> SHOW ENCRYPTION ALGORITHM;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
RMAN>
Posted by at No comments:

How to check the configuration of rman?

By default the RMAN configuration is stored in the control file. You can see the default values
for the configuration parameters by using RMAN to connect to the database and issuing the SHOW ALL command.
==============================
Check  RMAN configuration
==============================
[oracle@wasiq d01]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 21:43:39 2013

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

RMAN>
RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
using target database control file instead of recovery catalog

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d01/oracle/product/11.2.0/dbs/snapcf_prod.f'; # default

RMAN>

Posted by at No comments:

ALL ABOUT RMAN

rman
++++++++
rman stands for recovery manager .
It is utility/tool to take backup,perform restore and recovery.
And also known as rman client.
rman executable is located at $ORACLE_HOME/bin.
when we type rman and hit enter it will internally call recover.bsq file.
Brain behain rman is recover.bsq which is located at $ORACLE_HOME/rdbms/admin.

Target Database:
+++++++
The database which is going to backup(source).

Backup Set
+++++++++
It is a logical structure that contain one or more backup pieces.
Default one backup piece for one backup set.

Backup Piece
+++++++++++
It is a physical file or operating system file which containing the backup of datafile, archive redolog file and control file.

No  Catalog
++++++++++
Default RMAN is configured in NO CATALOG Mode.
RMAN backup information will be stored in Controlfile.

Catalog
++++++
Backup information stored in CATALOG that is seperate from TARGET database in the form of tables and views.Backup information also stored in controlfile.

Catalog Database
++++++++++++++
In which catalog object will store.
Catalog objects are tables and views.

Catalog Owner
++++++++++++
Who owns the catalog object.

Degree of Paralism
++++++++++++++
The number of channel that rman can open during backup or recovery.
Default is 1 Channel.

Backup Optimization
++++++++++++++++
rman doesnt perform a file backup if it has already backed up identical version of the file.

Image Copy
+++++++++
Image copies are similar to the copies of operating system files.
We can make image copies of datafile, controlfile and archive redolog file.
Rman image copies can be made only to disk.
They can't be made to tape.

Proxy Copy
++++++++++
rman can alos perform a special kind of backup called the proxy backup.
proxy copies cant be used to disks.




Posted by at No comments:

How to clone a table?

SQL> ---How to clone a table--
SQL> show user
USER is "SYS"
SQL> create table cloneof_emp as select * from scott.emp;
Table created.
SQL> select * from cloneof_emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


14 rows selected.

SQL>

Posted by at No comments:

How to Remove Data Files before Opening a Database?

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1824624640 bytes
Fixed Size                  1337184 bytes
Variable Size            1090521248 bytes
Database Buffers          721420288 bytes
Redo Buffers               11345920 bytes
Database mounted.
SQL> alter database datafile '/d01/oracle/oradata/prod/abdulwasiq01.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

Posted by at No comments:

How To Add Another Datafile to a Tablespace?


SQL> -- This is comment on sql--before adding a datafile in a tablespace check mount point space --
SQL> !df -h  /d01/oracle/oradata/prod
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda9              15G   11G  2.6G  82% /d01

SQL> -- To see datafiles in a tablespace use below command ---
SQL> select file_name from dba_data_files where file_name like '%abdulwasiq%';

FILE_NAME
--------------------------------------------------------------------------------
/d01/oracle/oradata/prod/abdulwasiq.dbf

SQL> ---Adding a datafile in a tablespace ---
SQL> alter tablespace abdulwasiq add datafile '/d01/oracle/oradata/prod/abdulwasiq01.dbf' size 100m;

Tablespace altered.

SQL> --Now check whether the datafiles is added or not-----
SQL> select file_name from dba_data_files where file_name like '%abdulwasiq%';

FILE_NAME
--------------------------------------------------------------------------------
/d01/oracle/oradata/prod/abdulwasiq.dbf
/d01/oracle/oradata/prod/abdulwasiq01.dbf

SQL>

Posted by at No comments:

How To Bring a Tablespace Online?

SQL> alter tablespace abdulwasiq online;

 Tablespace altered.

 SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

 TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
ABDULWASIQ                     ONLINE
MYTBS                          ONLINE

 8 rows selected.

 SQL>

Posted by at No comments:

How To Bring a Tablespace Offline?

If you want to stop users using a tablespace, you can bring it offline using the ALTER TABLESPACE ... OFFLINE statement as shown in the following script:
SQL> alter tablespace abdulwasiq offline normal;

 Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

 TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
ABDULWASIQ                     OFFLINE
MYTBS                          ONLINE

 8 rows selected.

 SQL>
Posted by at No comments:

How To See Free Space of Each Tablespace?

SQL> conn scott/tiger
Connected.
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES  FROM USER_FREE_SPACE  WHERE TABLESPACE_NAME IN ('USERS', 'ABDULWASIQ');

 TABLESPACE_NAME              FILE_ID      BYTES
------------------------- ---------- ----------
ABDULWASIQ                         7    9371648
USERS                              5  208666624
USERS                              4    3866624

 SQL>

Posted by at No comments:

How To Create a Table in a Specific Tablespace?

SQL> alter user scott account unlock;

User altered.

SQL> alter user scott identified by tiger;

User altered.

SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp;

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

SQL> CREATE TABLE image_emp TABLESPACE abdulwasiq as select * from emp;

Table created.

SQL> SELECT table_name,tablespace_name, num_rows FROM USER_TABLES WHERE tablespace_name in ('USERS', 'ABDULWASIQ');

TABLE_NAME                     TABLESPACE_NAME             NUM_ROWS
------------------------------ ------------------------- ----------
SALGRADE                       USERS                              5
BONUS                          USERS                              0
EMP                            USERS                             14
DEPT                           USERS                              4
IMAGE_EMP                      ABDULWASIQ

SQL>

Posted by at No comments:

What Happens to Data Files If a Tablespace Is Dropped?

If a tablespace is dropped then datafiles are remain in operating system .
The logical path is broken between datafile and tablespace.
If you want to delete the tablespace along with the datafiles use this clause
INCLUDING CONTENTS AND DATAFILES.


SQL>  CREATE TABLESPACE IMMU DATAFILE '/d01/oracle/oradata/prod/IMMU.dbf' size 100m;

Tablespace created.

SQL>  select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME           FILE_NAME
------------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USERS                     /d01/oracle/oradata/prod/users01.dbf
UNDOTBS1                  /d01/oracle/oradata/prod/undotbs01.dbf
SYSAUX                    /d01/oracle/oradata/prod/sysaux01.dbf
SYSTEM                    /d01/oracle/oradata/prod/system01.dbf
USERS                     /d01/oracle/oradata/prod/users02.dbf
EXAMPLE                   /d01/oracle/oradata/prod/example01.dbf
ABDULWASIQ                /d01/oracle/oradata/prod/abdulwasiq.dbf
MYTBS                     /d01/oracle/oradata/prod/tbs.dbf
IMMU                      /d01/oracle/oradata/prod/IMMU.dbf

9 rows selected.

SQL> drop tablespace IMMU including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME           FILE_NAME
------------------------- -----------------------------------------------------------------------------------
USERS                     /d01/oracle/oradata/prod/users01.dbf
UNDOTBS1                  /d01/oracle/oradata/prod/undotbs01.dbf
SYSAUX                    /d01/oracle/oradata/prod/sysaux01.dbf
SYSTEM                    /d01/oracle/oradata/prod/system01.dbf
USERS                     /d01/oracle/oradata/prod/users02.dbf
EXAMPLE                   /d01/oracle/oradata/prod/example01.dbf
ABDULWASIQ                /d01/oracle/oradata/prod/abdulwasiq.dbf
MYTBS                     /d01/oracle/oradata/prod/tbs.dbf

8 rows selected.

SQL>

Tablespace is dropped along with datafile's.
Posted by at No comments:

How To Drop a Tablespace?

If you have an existing tablespace and you don't want it anymore.
You can delete a tablespace by using the DROP TABLESPACE statement as shown in the example below.

SQL> CREATE TABLESPACE my_space DATAFILE '/d01/oracle/oradata/prod/myspace.dbf' SIZE 100M;

Tablespace created.

SQL> drop tablespace my_space;

Tablespace dropped.

SQL>
Posted by at No comments:

How To Rename a Tablespace?

SQL> CREATE TABLESPACE tbs DATAFILE '/d01/oracle/oradata/prod/tbs.dbf' SIZE 10M;

Tablespace created.

SQL> alter tablespace tbs rename to mytbs;

Tablespace altered.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

TABLESPACE_NAME           STATUS    CONTENTS
------------------------- --------- ---------
SYSTEM                    ONLINE    PERMANENT
SYSAUX                    ONLINE    PERMANENT
UNDOTBS1                  ONLINE    UNDO
TEMP                      ONLINE    TEMPORARY
USERS                     ONLINE    PERMANENT
EXAMPLE                   ONLINE    PERMANENT
ABDULWASIQ                ONLINE    PERMANENT
MYTBS                     ONLINE    PERMANENT

8 rows selected.

SQL>


Posted by at No comments:

How To Create a New Tablespace?

SQL>CREATE TABLESPACE abdulwasiq DATAFILE '/d01/oracle/oradata/prod/abdulwasiq.dbf' SIZE 10M;

Tablespace created.

SQL> SELECT TABLESPACE_NAME,file_name from dba_data_files;

TABLESPACE_NAME           FILE_NAME
------------------------- -----------------------------------------------------------------------------------
USERS                     /d01/oracle/oradata/prod/users01.dbf
UNDOTBS1                  /d01/oracle/oradata/prod/undotbs01.dbf
SYSAUX                    /d01/oracle/oradata/prod/sysaux01.dbf
SYSTEM                    /d01/oracle/oradata/prod/system01.dbf
USERS                     /d01/oracle/oradata/prod/users02.dbf
EXAMPLE                   /d01/oracle/oradata/prod/example01.dbf
ABDULWASIQ                /d01/oracle/oradata/prod/abdulwasiq.dbf

7 rows selected.

SQL>

Posted by at No comments:

How To View Tablespaces in the Current Database?

To view the all the tablespace in the current database use below two views.
1)dba_tablespaces
2)user_tablepsaces

SQL>  SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT

6 rows selected.

SQL>

Posted by at No comments:

How a Database Is Related to Tablespaces?

Database is a collection of one or more tablespaces and tablespace is a collection of one or more
datafiles.Data in the database store in the form of datafiles which are physical structure to os in which oracle instance is running.
Posted by at No comments:

How a Tablespace Is Related to Data Files?

Tablespace in oracle database  is a collection of one or more files called datafiles,which are physical structure that confirm to os in which oracle is running.

Posted by at No comments:

What is a datafile?

Data in the database store in the form  datafiles and these datafile's contain actual data or business data.
These datafiles are physical which we can see in operating system.


Posted by at No comments:

What is a tablespace ?

A database is divided into logical structure unit called tablespaces.
A logical unit inside the database is called tablespace.
Posted by at No comments:

Saturday, 4 January 2014

How to increase the size of SGA ?.

+++++++++++++++++++++++++++++++++++++
Please increase the of SGA 3G TO 7G
+++++++++++++++++++++++++++++++++++++
SQL> startup
ORACLE instance started.
Total System Global Area 3207790592 bytes
Fixed Size 2162600 bytes
Variable Size 2785017944 bytes
Database Buffers 402653184 bytes
Redo Buffers 17956864 bytes
Database mounted.
Database opened.
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3G
sga_target big integer 0
SQL>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Put below commands in a file with .sql extension and run it.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
vi sgaset7gb.sql
alter system set java_pool_size=512M scope=spfile;
alter system set large_pool_size=200M scope=spfile;
alter system set sga_target=0 scope=spfile;
alter system set sga_max_size=0 scope=spfile;
alter system set db_cache_size=2G scope=spfile;
alter system set shared_pool_reserved_size=500M scope=spfile;
alter system set shared_pool_size=4000M scope=spfile;
alter system set streams_pool_size=208M scope=spfile;
alter system set pga_aggregate_target=2G scope=spfile;

SQL> @sgaset7gb.sql
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
SQL>

+++++++++++++++++++++++++
Now bounce the database.
+++++++++++++++++++++++++
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 7334707200 bytes
Fixed Size 2172384 bytes
Variable Size 5167389216 bytes
Database Buffers 2147483648 bytes
Redo Buffers 17661952 bytes
Database mounted.
Database opened.
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 7024M
sga_target big integer 0
SQL>













Posted by at No comments:

ORA-32001: write to SPFILE requested but no SPFILE is in use


When trying to change the value of  parameters db_cache_size to 2G getting below error.

alter system set db_cache_size=2G scope=spfile;
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use

Cause :Not able to change the value of db_cache_size parameter ,because the instance is running
with Memory/pfile.

Solution :
create spfile from memory/pfile;
shutdown the database and start it again.



Posted by at 1 comment:

ORA-01565: error in identifying file / ORA-27037: unable to obtain file status.

When trying to create pfile from spfile getting below error.

SQL> create pfile='/d01/oracle/initsetting.ora' from spfile;
create pfile='/d01/oracle/initsetting.ora' from spfile

ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Cause: Database is not running with spfile,It is running with memory.
issue seen  while doing rman duplicate clone.

Solution:-

SQL> create pfile='/d01/oracle/initsetting.ora' from memory;

File created.

SQL>

Posted by at 2 comments:

Thursday, 2 January 2014

Multiplexing the controlfile using pfile in oracle database?

What is multiplexing ?

Storing each copy of controlfile on different physical disk is called  multiplexing.
One controlfile is lost another copy of controlfile can be used to re start  the instance without database recovery.
++++++++++++++++++++++++++++++++++++++++
Steps for  multiplexing controlfile
++++++++++++++++++++++++++++++++++++++
1)Check the number of controlfile in the database.
2)Shutdown the database
3)Copy the controlfile to new location
4)add the new controlfile location in pfile and start the database with pfile.
5)query the v$controlfile view.

 SQL> show parameter control_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /d02/oracle/oradata/orcl/contr
                                                 ol01.ctl, /d02/oracle/flash_re
                                                 covery_area/orcl/control02.ctl
SQL> ---Now shutdown the database --
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@wasiq ~]$
+++++++++++++++++++++++++++
Copy the controlfile to new location
+++++++++++++++++++++++++++++++++
[oracle@wasiq orcl]$  cp -r /d02/oracle/flash_recovery_area/orcl/control02.ctl /d08/dbdata/data1/orcl/control03.ctl
++++++++++++++++++++++++++++++++++
Moved the spfile if it is there in
$ORACLE_HOME/dbs location.
++++++++++++++++++++++++++++++++++
[oracle@wasiq dbs]$ ls -tlrh *.ora
-rw-r----- 1 oracle dba 3.5K Jan  2 14:08 spfileorcl.ora
-rw-r--r-- 1 oracle dba 1003 Jan  2 14:13 initorcl.ora
[oracle@wasiq dbs]$ mv spfileorcl.ora spfileorcl.ora_latest
[oracle@wasiq dbs]$


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Go to $ORACLE_HOME/dbs location and in pfile  edit the parameter control_files and add new location where you want
to multiplex the controlfile.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
/d02/oracle/prod/11.2.0/dbs
[oracle@wasiq dbs]$ ls -ltrh *.ora
-rw-r--r-- 1 oracle dba 2.8K May 15  2009 init.ora
-rw-r--r-- 1 oracle dba  964 Dec 28 21:05 initorcl.ora
-rw-r----- 1 oracle dba 3.5K Jan  2 14:08 spfileorcl.ora
[oracle@wasiq dbs]$ vi initorcl.ora
orcl.__db_cache_size=973078528
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/d02/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=469762048
orcl.__sga_target=1375731712
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=335544320
orcl.__streams_pool_size=16777216
*.audit_file_dest='/d02/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/d02/oracle/oradata/orcl/control01.ctl','/d02/oracle/flash_recovery_area/orcl/control02.ctl','/d08/dbdata/data1/orcl/control03.ctl'
*.db_block_size=8192
*.db_domain='oracle.com'
*.db_name='orcl'
*.db_recovery_file_dest='/d02/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/d02/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.local_listener='LISTENER_ORCL'
*.open_cursors=300
*.pga_aggregate_target=455081984
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1365245952
*.undo_tablespace='UNDOTBS1'
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
:wq!
++++++++++++++++++++++++++++++++++
Now startup the database with pfile
++++++++++++++++++++++++++++++++++++
/d02/oracle/prod/11.2.0/dbs
[oracle@wasiq dbs]$ ls -tlrh *.ora
-rw-r--r-- 1 oracle dba 1003 Jan  2 14:13 initorcl.ora
[oracle@wasiq dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 2 14:23:52 2014
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> --- I have only pfile in $ORACLE_HOME/dbs location,if i give startup then it will use pfile and bring up the instance--
SQL> startup
ORACLE instance started.
Total System Global Area 1372651520 bytes
Fixed Size                  1336428 bytes
Variable Size             385878932 bytes
Database Buffers          973078528 bytes
Redo Buffers               12357632 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/d02/oracle/oradata/orcl/control01.ctl
/d02/oracle/flash_recovery_area/orcl/control02.ctl
/d08/dbdata/data1/orcl/control03.ctl

SQL>

Posted by at No comments:

Multiplexing the controlfile using spfile.

Multiplexing can be done in two ways.
1)Using spfile
2)Using pfile.


What is multiplexing ?

Storing each copy of controlfile on different physical disk is called  multiplexing.
One controlfile is lost another copy of controlfile can be used to re start  the instance without database recovery.
++++++++++++++++++++++++++++++++++++++++
Steps for  multiplexing controlfile
++++++++++++++++++++++++++++++++++++++
1)check whether the database is running with pfile/spfile.
2)Check the number of controlfile in the database.
3)Add a new controlfile to another disk.
4)shutdown the database
5)start the database
6)query the v$controlfile view.

SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /d01/oracle/product/11.2.0/dbs
                                                 /spfileprod.ora
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/d01/oracle/oradata/prod/control01.ctl
/d01/oracle/flash_recovery_area/prod/control02.ctl
SQL>

SQL> alter system set control_files='/d01/oracle/oradata/prod/control01.ctl','/d01/oracle/flash_recovery_area/prod/control02.ctl','/d08/dbdata/data1/prod/control03.ctl' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@wasiq d08]$ cp -r /d01/oracle/oradata/prod/control01.ctl /d08/dbdata/data1/prod/control03.ctl
[oracle@wasiq d08]$
SQL> startup
ORACLE instance started.
Total System Global Area 1824624640 bytes
Fixed Size                  1337184 bytes
Variable Size            1056966816 bytes
Database Buffers          754974720 bytes
Redo Buffers               11345920 bytes
Database mounted.
Database opened.
SQL>  select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/d01/oracle/oradata/prod/control01.ctl
/d01/oracle/flash_recovery_area/prod/control02.ctl
/d08/dbdata/data1/prod/control03.ctl
SQL>



Posted by at No comments:
Subscribe to: Posts (Atom)