Physical Standby Database Configuration: Standalone

 

Physical Standby Database (DR) Configuration: Standalone:

 

Primary Server : OLX495

Primary Database : RAZZ

 

Secondary Server : olx494

Secondary Database : RAZZDR

1. There are some basic prerequisites parameters need to be set at primary database as follows :

 

ON OLX495 : Primary

[oracle@olx495 dbawork]$ export ORACLE_SID=RAZZ

[oracle@olx495 dbawork]$ sqlplus sys/saturn08@RAZZ as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 17 21:13:32 2014

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the OLAP, Data Mining and Real Application Testing options

 

SQL> show parameter db_n

 

NAME TYPE VALUE

———————————— ———– ——————————

db_name string RAZZ

SQL> show parameter db_unique

 

NAME TYPE VALUE

———————————— ———– ——————————

db_unique_name string RAZZ

 

SQL> ALTER DATABASE FORCE LOGGING;

 

Database altered.

 

SQL> alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(RAZZ,RAZZDR)’ scope=both;

 

System altered.

 

SQL> show parameter control_files

 

NAME TYPE VALUE

———————————— ———– ——————————

control_files string /oracle/dbawork/RAZZ_TEST/cont

rol/RAZZ/control01.ctl, /oracl

e/dbawork/RAZZ_TEST/control/RA

ZZ/control02.ctl

 

 

SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=/oracle/dbawork/RAZZ_TEST/archive_logs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RAZZ’ scope=both;

 

System altered.

 

SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=RAZZDR LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RAZZDR’ scope=both;

 

System altered.

 

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

 

System altered.

 

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

 

System altered.

 

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;

 

System altered.

 

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

 

System altered.

 

SQL> show parameter fal_

 

NAME TYPE VALUE

———————————— ———– ——————————

fal_client string RAZZ

fal_server string RAZZDR

SQL>

 

SQL>exit

 

 

2 : Take Full backup of Primary database :

[oracle@olx495 backup]$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 – Production on Mon Feb 17 21:38:30 2014

 

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

 

connected to target database: RAZZ (DBID=824804085)

 

 

RMAN> run

2> {

3> allocate channel t1 device type disk format ‘/oracle/dbawork/RAZZ_TEST/backup/df_%U.bak’;

4> allocate channel t2 device type disk format ‘/oracle/dbawork/RAZZ_TEST/backup/df_%U.bak’;

5> backup database;

6> release channel t1;

7> release channel t2;

8> }

 

using target database control file instead of recovery catalog

allocated channel: t1

channel t1: SID=145 device type=DISK

 

allocated channel: t2

channel t2: SID=40 device type=DISK

 

Starting backup at 17-FEB-14

channel t1: starting full datafile backup set

channel t1: specifying datafile(s) in backup set

input datafile file number=00002 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/sysaux01.dbf

channel t1: starting piece 1 at 17-FEB-14

channel t2: starting full datafile backup set

channel t2: specifying datafile(s) in backup set

input datafile file number=00001 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/system01.dbf

input datafile file number=00004 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/users01.dbf

channel t2: starting piece 1 at 17-FEB-14

channel t1: finished piece 1 at 17-FEB-14

piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4bp0skq1_1_1.bak tag=TAG20140217T214017 comment=NONE

channel t1: backup set complete, elapsed time: 00:00:25

channel t1: starting full datafile backup set

channel t1: specifying datafile(s) in backup set

input datafile file number=00005 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/example01.dbf

input datafile file number=00003 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/undotbs01.dbf

input datafile file number=00006 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/razz01.dbf

input datafile file number=00007 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/TEST1.dbf

channel t1: starting piece 1 at 17-FEB-14

channel t2: finished piece 1 at 17-FEB-14

piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4cp0skq1_1_1.bak tag=TAG20140217T214017 comment=NONE

channel t2: backup set complete, elapsed time: 00:00:25

channel t2: starting full datafile backup set

channel t2: specifying datafile(s) in backup set

including current control file in backup set

channel t2: starting piece 1 at 17-FEB-14

channel t1: finished piece 1 at 17-FEB-14

piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4dp0skqq_1_1.bak tag=TAG20140217T214017 comment=NONE

channel t1: backup set complete, elapsed time: 00:00:02

channel t1: starting full datafile backup set

channel t1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel t1: starting piece 1 at 17-FEB-14

channel t2: finished piece 1 at 17-FEB-14

piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4ep0skqq_1_1.bak tag=TAG20140217T214017 comment=NONE

channel t2: backup set complete, elapsed time: 00:00:01

channel t1: finished piece 1 at 17-FEB-14

piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4fp0skqs_1_1.bak tag=TAG20140217T214017 comment=NONE

channel t1: backup set complete, elapsed time: 00:00:00

Finished backup at 17-FEB-14

 

released channel: t1

 

released channel: t2

 

RMAN> exit

 

 

Recovery Manager complete.

 

3. Create Controlfile and pfile for Standby Database :

 

[oracle@olx495 backup]$ sqlplus sys/saturn08@RAZZ as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 17 21:41:02 2014

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the OLAP, Data Mining and Real Application Testing options

 

SQL> alter database create standby controlfile as ‘/oracle/dbawork/RAZZ_TEST/control/RAZZ/RAZZDR_control.ctl’;

 

Database altered.

 

SQL> create pfile from spfile;

 

File created.

 

4. : Points to Do :

1. Create password file in both the databases (password must be same), you can copy the password file from prod to DR and rename it in DR site.

after creation of password file, put the tns entries in both the servers.check the connection in both the servers.

 

Eg :

orapwd file=orapwRAZZ.ora password=saturn08

or

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle entries=5

 

1. Transfer all backup pieces,controlfile and pfile on olx494(Secondary).

 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

 

5. Change parameter on init file according to Standby database :

 

 

on Glaolx494:

 

 

*.db_name=’RAZZ’

*.db_unique_name=’RAZZDR’

*.log_archive_config=’DG_CONFIG=(RAZZDR,RAZZ)’

*.log_archive_dest_1=’LOCATION=/oracle/dbawork/RAZZ_TEST/archive_logs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RAZZDR’

*.log_archive_dest_2=’SERVICE=RAZZ LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RAZZ’

*.fal_client=’RAZZDR’

*.fal_server=’RAZZ’

 

 

5. Start DR Database as follows :

 

[oracle@olx494 dbs]$ export ORACLE_SID=RAZZDR

[oracle@olx494 dbs]$ sqlplus “/as sysdba”

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 17 22:30:24 2014

 

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

 

Connected to an idle instance.

 

SQL> startup pfile=’initRAZZDR.ora’ mount;

ORACLE instance started.

 

Total System Global Area 939495424 bytes

Fixed Size 2233960 bytes

Variable Size 578816408 bytes

Database Buffers 352321536 bytes

Redo Buffers 6123520 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

 

Create password file in both the databases (password must be same), you can coppy the password file from prod to DR and rename it in DR site.

after creation of password file, put the tns entries in both the servers.check the connection in both the servers.

 

orapwd file=orapwRAZZ.ora password=saturn08

or

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle entries=5

 

 

6. Check Connection tns entries :

 

[oracle@olx494 dbs]$ sqlplus sys/saturn08@RAZZDR as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 17 22:30:59 2014

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

 

SQL> conn sys/saturn08@RAZZ as sysdba

Connected.

SQL> conn sys/saturn08@RAZZDR as sysdba

Connected.

SQL>exit

 

 

7. Restore Database using backup pieces :

[oracle@olx494 backup]$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 – Production on Mon Feb 17 22:41:48 2014

 

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

 

connected to target database: RAZZ (DBID=824804085, not open)

 

RMAN> catalog start with ‘/oracle/dbawork/RAZZ_TEST/backup/’;

 

using target database control file instead of recovery catalog

searching for all files that match the pattern /oracle/dbawork/RAZZ_TEST/backup/

no files found to be unknown to the database

 

RMAN> restore database;

 

Starting restore at 2014-02-17 22:42:15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=216 device type=DISK

 

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 00001 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/system01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/users01.dbf

channel ORA_DISK_1: reading from backup piece /oracle/dbawork/RAZZ_TEST/backup/df_4cp0skq1_1_1.bak

channel ORA_DISK_1: piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4cp0skq1_1_1.bak tag=TAG20140217T214017

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

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 00002 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /oracle/dbawork/RAZZ_TEST/backup/df_4bp0skq1_1_1.bak

channel ORA_DISK_1: piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4bp0skq1_1_1.bak tag=TAG20140217T214017

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

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 /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/example01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/razz01.dbf

channel ORA_DISK_1: restoring datafile 00007 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/TEST1.dbf

channel ORA_DISK_1: reading from backup piece /oracle/dbawork/RAZZ_TEST/backup/df_4dp0skqq_1_1.bak

channel ORA_DISK_1: piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4dp0skqq_1_1.bak tag=TAG20140217T214017

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 2014-02-17 22:42:45

 

RMAN> exit

 

 

Recovery Manager complete.

[oracle@olx494 backup]$ sqlplus sys/saturn08@RAZZDR as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 17 22:43:07 2014

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

 

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oracle/dbawork/RAZZ_TEST/archive_logs

Oldest online log sequence 279

Next log sequence to archive 0

Current log sequence 280

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

Database altered.

 

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oracle/dbawork/RAZZ_TEST/archive_logs

Oldest online log sequence 279

Next log sequence to archive 0

Current log sequence 280

 

 

 

 

Switch logfiles manually on RAZZ:

 

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oracle/dbawork/RAZZ_TEST/archive_logs

Oldest online log sequence 278

Next log sequence to archive 280

Current log sequence 280

SQL> alter system switch logfile;

 

System altered.

 

SQL> /

 

System altered.

 

SQL> /

 

System altered.

 

SQL> /

 

System altered.

 

SQL> /

 

System altered.

 

SQL> /

 

System altered.

 

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oracle/dbawork/RAZZ_TEST/archive_logs

Oldest online log sequence 284

Next log sequence to archive 286

Current log sequence 286

 

Now Check logs status on RAZZDR :

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oracle/dbawork/RAZZ_TEST/archive_logs

Oldest online log sequence 284

Next log sequence to archive 0

Current log sequence 286

SQL>

 

 

Change Database Characterset

How to change Database Characterset:

There are sometimes when there is a need to change the database charcaterset. This can be done but it requires restart of database in order to make it effective. I have tested this on 11g. Please test it on your sandbox before implementing it on Prod.

Current value: WE8MSWIN1252
Target value: TH8TISASCII

[oracle@olx495 archive_logs]$ export ORACLES_SID=TALLYOAT1
[oracle@olx495 archive_logs]$
[oracle@olx495 archive_logs]$
[oracle@olx495 archive_logs]$
[oracle@olx495 archive_logs]$ sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 26 00:33:55 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the OLAP, Data Mining and Real Application Testing options

SQL> SELECT *
FROM V$NLS_PARAMETERS
WHERE PARAMETER = ‘NLS_CHARACTERSET’; 2 3

PARAMETER
—————————————————————-
VALUE
—————————————————————-
NLS_CHARACTERSET
WE8MSWIN1252
SQL>
UPDATE PROPS$ SET VALUE$ = ‘TH8TISASCII’ WHERE NAME = ‘NLS_CHARACTERSET’;SQL>

1 row updated.

SQL> SELECT *
FROM V$NLS_PARAMETERS
WHERE PARAMETER = ‘NLS_CHARACTERSET’; 2 3

PARAMETER
—————————————————————-
VALUE
—————————————————————-
NLS_CHARACTERSET
WE8MSWIN1252
SQL> commit;

Commit complete.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 2238099456 bytes
Fixed Size 2230312 bytes
Variable Size 603981784 bytes
Database Buffers 1627389952 bytes
Redo Buffers 4497408 bytes
Database mounted.
Database opened.
SQL> SELECT *
FROM V$NLS_PARAMETERS
WHERE PARAMETER = ‘NLS_CHARACTERSET’; 2 3

PARAMETER
—————————————————————-
VALUE
—————————————————————-
NLS_CHARACTERSET
TH8TISASCII
SQL>

 

 

 

Managing Tablespace/datafiles/tables (Tweaks)

Create Tablespace :
===================
CREATE TABLESPACE test01
DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 100M
UNIFORM;
/* Default Uniform size will take 1Mb and Alternative to uniqform is AUTOALLOCATE */

CREATE TABLESPACE test01
DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 100M
UNIFORM SIZE 5M;

CREATE TABLESPACE test01
DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 100M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;

CREATE TABLESPACE test01 DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
/* Alternative to EXTENT is LOCAL */

Drop Tablespace :
=================
DROP TABLESPACE test01; /* This will drop the tablespace only if it is empty */

DROP TABLESPACE test01 INCLUDING CONTENTS;
/* This will drop the tablespace even if it is not empty. But the datafiles will not be deleted you have to use operating system command to delete the files. But if you include datafiles keyword then, the associated datafiles will also be deleted from the disk. */

DROP TABLESPACE test01 INCLUDING CONTENTS AND DATAFILES;

/* For reverential integrity constraints to drop */
===============================================
DROP TABLESPACE test01 CASCADE CONSTRAINTS;

Add datafile :
=============
ALTER TABLESPACE test01 ADD DATAFILE ‘/finance10/app/oracle/finance/test01.dbf’ SIZE 1000M;

Resize datafile:
==============
ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.dbf’ RESIZE 500m;

Take Offline/Online Datafile :
==============================
ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.dbf’ OFFLINE;
ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.dbf’ ONLINE;

/* Note: You can’t take individual datafiles offline it the database is running in NOARCHIVELOG mode. If the datafile has become corrupt or missing when the database is running in NOARCHIVELOG mode then you can only drop it by giving the following command:

ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.dbf’ OFFLINE DROP;

*/

Autoextent :
==========
ALTER TABLESPACE data01
ADD DATAFILE ‘/finance10/oradata/data01.dbf’ SIZE 200M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 1000M;

Rename Tablespace :
====================
SQL> ALTER TABLESPACE test01 RENAME TO test02;
Tablespace altered.
SQL>
You can rename both permanent and temporary tablespaces, but there are a few restrictions:
• You can’t rename the System and Sysaux tablespaces.
• The tablespace being renamed must have all its data files online.
• If the tablespace is read-only, renaming it doesn’t update the file headers of its data files.

Rename Datafile :
================
Sometimes, you may need to rename a data file. The process for this is straightforward:
1. Take the data file offline by taking its tablespace offline. Use the following command:
SQL> ALTER TABLESPACE test01 OFFLINE NORMAL;
Tablespace altered.
SQL>
2. Rename the file using an operating system utility such as cp or mv in UNIX, or copy in
Windows.
$ cp /u01/app/oracle/test01.dbf /u02/app/oracle/test01.dbf
3. Rename the data file in the database by using the following command:
SQL> ALTER TABLESPACE test01
2 RENAME DATAFILE
3 ‘/u01/app/oracle/test01.dbf’
4 TO
5* ‘/u02/app/oracle/test01.dbf’;
Tablespace altered.
SQL>

Read or Read write mode:
===================
SQL> ALTER TABLESPACE test01 READ ONLY;
If you want to make this read-only tablespace writable again, you can use the following
command:
SQL> ALTER TABLESPACE test01 READ WRITE;

Oflline /Online : Except System, all can be turn to offline.
=========================================
ALTER TABLESPACE index_01 OFFLINE NORMAL;
ALTER TABLESPACE index_01 ONLINE;

Temporary tabelspace :
=================
CREATE TEMPORARY TABLESPACE temp_demo TEMPFILE ‘temp01.dbf’ SIZE 500M AUTOEXTEND ON;
CREATE TEMPORARY TABLESPACE temp_demo TEMPFILE ‘temp01.dbf’ SIZE 500M extent management local uniform size 5M;

The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The AUTOALLOCATE clause is not allowed for temporary tablespaces like as for user tablespaces.

Resize Temporaray tablepace:
=====================
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/prod1/temp01.dbf’ RESIZE 50M;

Drop Temp Tablespace:
==================
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/prod1/temp01.dbf’ DROP INCLUDING DATAFILES;

Shrink Temporary Tablespace :
=============================
SQL> alter tablespace temp shrink space;
SQL> alter tablespace temp shrink space keep 100m;
SQL> alter tablespace temp shrink tempfile ‘/u01/app/oracle/oradata/or11/temp01.dbf’;
SQL> alter tablespace temp shrink space tempfile ‘/u01/app/oracle/oradata/prod1/temp02.dbf’ keep 100m;

Imp views:
==========
V$SORT_SEGMENT and V$TEMPSEG_USAGE

Temporary Tablespace Groups:
=======================
When you assign the first temporary tablespace to a tablespace group, you automatically create the temporary tablespace group.

desc dba_tablespace_groups

You can also use the DBA_USERS view to find out which temporary tablespaces or temporary
tablespace groups are assigned to each user….

CREATE TEMPORARY TABLESPACE temp01 TEMPFILE ‘/u01/oracle/oradata/temp01_01.dbf’ SIZE 500M TABLESPACE GROUP tmpgrp1;

ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp1
The preceding statement will cause Oracle to create a new group named tmpgrp1,

No group:
========
SQL> CREATE TEMPORARY TABLESPACE temp02
TEMPFILE ‘/u01/oracle/oradata/temp02_01.dbf’ SIZE 500M
TABLESPACE GROUP ”;
SQL> CREATE TEMPORARY TABLESPACE temp03
TEMPFILE ‘/u01/oracle/oradata/temp03_01.dbf’ SIZE 500M;
Tablespace created.

Setting a Group as the Default Temporary Tablespace for the Database:
===================================================
SQL> ALTER TABLESPACE DEFAULT TEMPORARY TABLESPACE tmpgrp1;

Using Temporary Tablespace Groups When You Create and Alter Users :
=================================================================
When you create new users, you can assign them to a temporary tablespace group instead of to the usual single temporary tablespace. Here’s an example:
SQL> CREATE USER salapati IDENTIFIED BY sammyy1
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE tmpgrp1;
User created.
SQL>

Bigfile Tablespaces:
==============
Creating Bigfile Tablespaces:
CREATE BIGFILE TABLESPACE bigtbs_01 DATAFILE ‘/u01/oracle/data/bigtbs_01.dbf’ SIZE 100GB;

Changing the Default Tablespace Type:
============================
SQL> ALTER TABLESPACE SET DEFAULT BIGFILE TABLESPACE;

Altering a Bigfile Tablespace:
=====================
SQL> ALTER TABLESPACE bigtbs RESIZE 120G;
SQL> ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;

deallocate unused space from table:
==================================
SQL> ALTER TABLE persons DEALLOCATE UNUSED;

To encrypt any colum of table : just use keyword as “ENCRYPT”.
=============================================
ssn NUMBER(9) ENCRYPT,where ssn is the column name.

Adding a Column to a Table:
====================
ALTER TABLE emp ADD (retired char(1));

Dropping a Column from a Table:
========================
SQL> ALTER TABLE emp DROP (retired);
Table altered.

For lagrge tables, can set the column as unused that will not be visible to table,views or constraints:
==============================================================
SQL> ALTER TABLE emp SET UNUSED (hiredate, mgr);

so during maintainence window we can drop all unused coloumns :
SQL> ALTER TABLE emp DROP UNUSED COLUMNS;

Renaming a Table Column:
========================
SQL> ALTER TABLE emp RENAME COLUMN retired TO non_active;

Renaming a Table:
=============
SQL> ALTER TABLE emp RENAME TO emp;

Removing All the Data from a Table:
==========================
TRUNCATE TABLE test; : DDL Comand => No Rollback
DELETE * FROM TABLE; : DML Command => Can Rollback

Creating a New Table with the CTAS Option:
=================================
SQL> CREATE TABLE emp_new AS SELECT * FROM emp;
Table created.

PARALLEL and NOLOGGING to speed up data loading into table:
=================================================
SQL> CREATE TABLE employee_new AS SELECT * FROM employees PARALLEL DEGREE 4,NOLOGGING;
Table created.

For more speed move table from one tablespace to other tablespace:
================================================
SQL> ALTER TABLE employee MOVE new_tablespace;
/* When you move a table, the ROWIDs of the rows change, thus making the indexes on the table unusable. You must either re-create the indexes or rebuild them after you move the table. */

Dropping Tables:
===============
SQL> drop table TEST1.EMP_BONUS_BKP1;

Table dropped.

Elapsed: 00:00:00.02
SQL> select count(*) from TEST1.EMP_BONUS_BKP1;
select count(*) from TEST1.EMP_BONUS_BKP1
*
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.00
SQL> flashback table TEST1.EMP_BONUS_BKP1 to before drop;

Flashback complete.

Elapsed: 00:00:00.01
SQL> select count(*) from TEST1.EMP_BONUS_BKP1;

COUNT(*)
———-
2

Elapsed: 00:00:00.02
SQL> drop table TEST1.EMP_BONUS_BKP1 purge;

Table dropped.

Elapsed: 00:00:00.87
SQL> flashback table TEST1.EMP_BONUS_BKP1 to before drop;
flashback table TEST1.EMP_BONUS_BKP1 to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

Elapsed: 00:00:00.01
SQL>

Read-Only Tables :
==================

In earlier releases of the Oracle database, you could have read-only tablespaces but not readonly
tables. One of the more useful but simple to implement new features in Oracle Database
11g is the read-only table feature. Simply specify the clause read only in the alter table statement
as shown in the following example, where we first create a normal read-write table and
then convert that into a read-only table:

SQL> create table test1 (name varchar2(30));
Table created.

SQL> alter table test1 read only;
Table altered.
SQL>

The new column READ_ONLY in the DBA_TABLES view shows whether a column has readonly
status. You can return a read-only table to a read-write status by using the read write
clause in the alter table statement, as shown in the following example:

SQL> alter table test1 read write;
Table altered.
SQL>