Renaming schema in oracle database

Oracle : Rename schema

In oracle there is no straight way to rename a schema, but it can be done by using transport tablespaces.

The following is the brief overview of the steps using transport tablespaces. Normally we will use transport tablespaces to migrate the database. In our scenario, the source and target database will be same to achieve schema rename.

Source schema : The schema name which is going to be renamed
Target schema : The new schema name
TT= transport tablespaces

• Check whether tablespace can be used for TT 
• Take a note on source schema objects. 
• Create target schema in db with the previlages same as source schema
• Export the source schema metadata excluding tables and indexes
• Export all the tablespace metadata which are going to be TT
• Put the tablespace in read only  which is involved in TT activity.
• Shutdown the database
• Rename all the transport tablespace datafiles 
• Start the database in mount.
• Since we renamed, database will complain that it cant identify the datafiles. So put all the transport tablespace datafiles to offline. 
• Drop the source schema tables - > This is required if you have partition tables spanned to different tablespaces or tables have referential tables in other tablespaces.
• Drop the tablespaces with keep datafile option which are marked for TT
• Rename the datafiles back to original name. 
• Import the metadata of the TT with remap_schema option (source_schema:target_schema). This will migrate all the tables and index but not the procedures,packages etc.
• Import the metadata of the source schema to target schema.
• Verify the objects counts, recompile the invalid objects.
• Drop the source schema.
• You may have to recompile any public synonyms created for this source schema. 

so renaming schema is done :)

---------------------------------------------------------------------------------------------------------
Example
Pre-check-
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 13 11:24:34 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option

SQL> execute dbms_tts.transport_set_check('user_data,pay_data',TRUE,TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM transport_set_violations;

no rows selected

-- export meta data
> cat transport_expdp.par
transport_tablespaces=user_data,pay_data
directory=test_exp
dumpfile=trans_expdp.dmp
logfile=trans_expdp.log
userid=system 

SQL> @create_target_user.sql --create target user with same previlages as source schema (sys_privs and roles )

SQL> alter tablespace user_data read only;

Tablespace altered.

SQL> alter tablespace pay_data read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option


>  expdp parfile=transport_expdp.par

Export: Release 12.1.0.2.0 - Production on Mon Jul 13 11:08:30 2015

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** parfile=transport_expdp.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /usr/work/trans_expdp.dmp
******************************************************************************
Datafiles required for transportable tablespace USER_DATA:
  /usr/data/Testdb/user_data_01.dbf
Datafiles required for transportable tablespace PAY_DATA: 
  /usr/data/Testdb/pay_data_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Jul 13 1:09:10 2015 elapsed 0 00:00:35

Rename the files

SQL>shutdown immediate

mv  /usr/data/Testdb/user_data_01.dbf /usr/data/Testdb/temp_user_data_01.dbf
mv 
/usr/data/Testdb/pay_data_01.dbf /usr/data/Testdb/temp_pay_data_01.dbf


Drop the tablespaces 
 
SQL> startup mount;
SQL> alter database datafile '/usr/data/Testdb/user_data_01.dbf' offline;
SQL>alter database datafile '/usr/data/Testdb/pay_data_01.dbf' offline;
SQL> alter database open;
SQL> @drop_source_schema_tables.sql

-- the above step is required if the table has partitions span over different tablespaces. That's why we are putting the datafiles offline and dropping the tables. otherwise you may face the following errors.

SQL> drop tablespace user_data including contents keep datafiles;
drop tablespace user_data including contents keep datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

SQL> drop tablespace pay_data including contents keep datafiles;
drop tablespace pay_data including contents keep datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
 

or 

constraint errors which has referential in different partition.


SQL> drop tablespace user_data including contents keep datafiles;

Tablespace dropped.

SQL> drop tablespace pay_data including contents keep datafiles;

Tablespace dropped.
SQL> shutdown immediate;




Rename the files back to original name    
 
 

mv /usr/data/Testdb/temp_user_data_01.dbf /usr/data/Testdb/user_data_01.dbf
mv/usr/data/Testdb/temp_pay_data_01.dbf /usr/data/Testdb/pay_data_01.dbf

Start the db and import the metadata  

SQL> startup 

> cat trans_impdp.par
remap_schema=TESTUSER:NEW_TESTU
directory=test_exp
dumpfile=trans_expdp.dmp
logfile=trans_test_imp.log
transport_datafiles=/usr/data/Testdb/user_data_01.dbf,
  /usr/data/Testdb/pay_data_01.dbf

 
> impdp parfile=trans_impdp.par userid=system

Import: Release 12.1.0.2.0 - Production on Mon Jul 13 11:16:51 2015

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  parfile=trans_impdp.par userid=system/********
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Jul 13 11:17:21 2015 elapsed 0 00:00:13



SQL> select count(object_type),object_type from dba_objects where owner='TESTUSER' group by object_type;

COUNT(OBJECT_TYPE) OBJECT_TYPE
------------------ -----------------------
                 1 SEQUENCE
                 2 PROCEDURE
                25 VIEW
                 5 FUNCTION

SQL>  select count(object_type),object_type from dba_objects where owner='NEW_TESTU' group by object_type;

COUNT(OBJECT_TYPE) OBJECT_TYPE
------------------ -----------------------
                 1 LOB
                54 INDEX
                74 TABLE
 
Import the metadata of source schema excluding table and index

Import the metadata and then check the object count again.

impdp remap_schema=TESTUSER:NEW_TESTU directory=test_exp dumpfile=TEST_metadata.dmp logfile=import_test.log userid=system


SQL> select count(object_type),object_type from dba_objects where owner='TESTUSER' group by object_type;

COUNT(OBJECT_TYPE) OBJECT_TYPE
------------------ -----------------------
                 1 SEQUENCE
                 2 PROCEDURE
                25 VIEW
                 5 FUNCTION

SQL>   select count(object_type),object_type from dba_objects where owner='NEW_TESTU' group by object_type;

COUNT(OBJECT_TYPE) OBJECT_TYPE
------------------ -----------------------
                 1 SEQUENCE
                 2 PROCEDURE
                 1 LOB
                54 INDEX
                74 TABLE
                25 VIEW
                 5 FUNCTION

7 rows selected.

EM - Express 12c

EM Express – 12c Edition

Oracle Enterprise Manager Database Express, also referred to as EM Express, is a web-based tool for managing Oracle Database 12c

Reference : http://www.oracle.com/technetwork/database/manageability/emx-intro-1965965.html

About license: This tool is free tool with oracle 12c standard or 12c Enterprise edition

To access EM express you need local listener configuration and this will get installed by default when u use DBCA to create database.

In my case I don't have listener so I am creating it from scratch

I registered my database with the newly created listener as follows

in sqlplus
 alter system register;

to know the port of your em please run the following query

 select dbms_xdb.getHttpPort() from dual;

DBMS_XDB.GETHTTPPORT()

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

    0

SQL> select dbms_xdb_config.getHttpsPort() from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()

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

 5500

okay to invoke the em express the following is the formart

in web browser  https://hostname:portnumber://em/

in my case the following is the example

Note: This em plugin requires flash player. I tried more tough to update my firefox to install flash player.

finally I managed to install through following command

yum install flash-plugin nspluginwrapper.x86_64 nspluginwrapper.i686 

here you go first screen

==================================================================================

please post your questions

12c New Features - Data file move without taking tablespace read only or offline.

12c New Features - Data file move without taking tablespace read only or offline.

one of the features of 12.1.0 is datafile rename online. so from 12.1.0 no longer we need to take tablespace offline or readonly. It can be done online.

I have tested this in normal database to understand the behaviour easily.

database version: 12.1.0

file management: file system

SQL> 1
  1* select file_name,tablespace_name,status from dba_data_files where tablespace_name='USERS'
SQL> /

FILE_NAME                             TABLESPACE_NAME            STATUS
------------------------------------------------------------ ------------------------------ ---------
/media/12c_db/oradata/ora12cdb/loc2/users02.dbf          USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/users01.dbf             USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/loc2/users03.dbf          USERS                AVAILABLE

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME            STATUS
------------------------------ ---------
USERS                   ONLINE

SQL>
SQL> select * from v$version;

BANNER                               CON_ID
-------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.        0
1.0.1.0 - 64bit Production

PL/SQL Release 12.1.0.1.0 - Production                0
CORE    12.1.0.1.0    Production                    0
TNS for Linux: Version 12.1.0.1.0 - Production            0
NLSRTL Version 12.1.0.1.0 - Production                0

SQL> alter database move datafile '/media/12c_db/oradata/ora12cdb/loc2/users02.dbf' to '/media/12c_db/oradata/ora12cdb/users02.dbf';  

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME            STATUS
------------------------------ ---------
USERS                   ONLINE

SQL> select file_name,tablespace_name,status from dba_data_files where tablespace_name='USERS';

FILE_NAME                             TABLESPACE_NAME            STATUS
------------------------------------------------------------ ------------------------------ ---------
/media/12c_db/oradata/ora12cdb/users01.dbf             USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/loc2/users03.dbf          USERS                AVAILABLE
/media/12c_db/oradata/ora12cdb/users02.dbf             USERS                AVAILABLE
 

alert log

alter database move datafile '/media/12c_db/oradata/ora12cdb/loc2/users02.dbf' to '/media/12c_db/oradata/ora12cdb/users02.dbf' Sat Jun 29 20:58:00 2013 Moving datafile /media/12c_db/oradata/ora12cdb/loc2/users02.dbf (5) to /media/12c_db/oradata/ora12cdb/users02.dbf Move operation committed for file /media/12c_db/oradata/ora12cdb/users02.dbf Completed: alter database move datafile '/media/12c_db/oradata/ora12cdb/loc2/users02.dbf' to '/media/12c_db/oradata/ora12cdb/users02.dbf' $

12c feature: Pluggable Databases

The below demo is created on the basis of the following reference link

Oracle Database 12c: New Features - Pluggable Databases- by mikerajendran

details provided by mikerajendran in his blog

CDB vs. PDB

CDB is an acronym for “Container Database” and PDB is an acronym for “Pluggable Database”. I think it will be easier to explain with a metaphor for DBAs. Think of a freight train with many cars up to 250. Each container could be having different contents, with delivery target for different customers and completely packed/sealed independently with customer options but the entire freight is carried by a single engine or carrier at the front. It will be stupid enough to run 250 freight trucks but rather it is efficient to consolidate them into a single freight train. When running independently we will spend on gas, drivers’ expenses and much more complicated to manage them. The freight train is basically the CDB and each car is the PDB.

PDB is fully backward compatible to pre-12.1 database releases. There is nothing different from a developer or application connectivity perspective. Everything stays the same but the PDB will belong to a single CDB. When application connects to the PDB, it will specify the destination PDB via a database service. All home-grown or third party applications typically will have connectivity defined out of the application so it is easier to just change the service name outside of the application code. So all database connectivity should use “database service” rather than using the legacy approach of ORACLE_SID based connectivity. ORACLE_SID ties the application connectivity to a specific database instance and does not give the scalability or high availability. You can have many pluggable databases in 12.1.

So now we will see the creation of pluggabble database
I am using dbca to create the pluggable database as it is first time.
I got to see the process and it creating normal database then it converting the database as plugabble database

create pluggable database PDB$SEED as clone  using '/media/12c_db/product/12.1.0/dbhome_1/assistants/dbca/templates//pdbseed.xml'  source_file_nam
e_convert = ('/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/temp01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/pdbseed_temp01.dbf',
'/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/system01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_system_8wz2hq6d_.dbf',
'/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/sysaux01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_sysaux_8wz2hq68_.dbf') file_name_c
onvert=NONE  NOCOPY
Sat Jun 29 18:30:39 2013
****************************************************************
Pluggable Database PDB$SEED with pdb id - 2 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Database Characterset is US7ASCII
Deleting old file#2 from file$ 
Deleting old file#4 from file$ 
Adding new file#5 to file$(old file#2) 
Adding new file#7 to file$(old file#4) 
ALTER SYSTEM: Flushing buffer cache inst=0 container=2 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB$SEED with pdb id - 2 is now marked as NEW.
****************************************************************
Completed: create pluggable database PDB$SEED as clone  using '/media/12c_db/product/12.1.0/dbhome_1/assistants/dbca/templates//pdbseed.xml'  sour
ce_file_name_convert = ('/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/temp01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/pdbseed_temp01.dbf
',
'/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/system01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_system_8wz2hq6d_.dbf',
'/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/sysaux01.dbf','/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_sysaux_8wz2hq68_.dbf') file_name_c
onvert=NONE  NOCOPY
alter pluggable database PDB$SEED open restricted

once created you can see this parameter is enabled
  enable_pluggable_database= TRUE

SQL> set pages 1000

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

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

HOST_NAME

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

VERSION  STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT

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

LOGINS   SHU DATABASE_STATUS INSTANCE_ROLE    ACTIVE_ST BLO     CON_ID

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

INSTANCE_MO EDITION

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

FAMILY

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

     1 ora12cc1

ovs1.co.nz

12.1.0.1.0  29-JUN-13 OPEN NO      1 STOPPED

ALLOWED    NO  ACTIVE PRIMARY_INSTANCE   NORMAL    NO   0

REGULAR     EE

$ cat initora12cc1.ora

ora12cc1.__data_transfer_cache_size=0

ora12cc1.__db_cache_size=402653184

ora12cc1.__java_pool_size=16777216

ora12cc1.__large_pool_size=50331648

ora12cc1.__oracle_base='/media/12c_db'#ORACLE_BASE set from environment

ora12cc1.__pga_aggregate_target=503316480

ora12cc1.__sga_target=738197504

ora12cc1.__shared_io_pool_size=0

ora12cc1.__shared_pool_size=251658240

ora12cc1.__streams_pool_size=0

*.audit_file_dest='/media/12c_db/admin/ora12cc1/adump'

*.audit_trail='db'

*.compatible='12.1.0.0.0'

*.control_files='/media/12c_db/oradata/ORA12CC1/controlfile/o1_mf_8wz2gv8x_.ctl','/media/12c_db/fast_recovery_area/ORA12CC1/controlfile/o1_mf_8wz2gvog_.ctl'

*.db_block_size=8192

*.db_create_file_dest='/media/12c_db/oradata'

*.db_domain=''

*.db_name='ora12cc1'

*.db_recovery_file_dest='/media/12c_db/fast_recovery_area'

*.db_recovery_file_dest_size=4800m

*.diagnostic_dest='/media/12c_db'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora12cc1XDB)'

*.enable_pluggable_database=true

*.memory_target=1177m

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

SQL> COLUMN "RESTRICTED" FORMAT A10
SQL> select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
  2  from v$PDBs v inner join dba_pdbs d
  3   using (GUID)
  4  order by v.create_scn
  5  /

NAME                   OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- -------------
PDB$SEED               READ ONLY  NO         NORMAL
ORA12CP1               MOUNTED      n/a         NORMAL


SQL> select name,TS#,STATUS from v$datafile;

NAME
--------------------------------------------------------------------------------
       TS# STATUS
---------- -------
/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_system_8wz29n4m_.dbf
     0 SYSTEM

/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_sysaux_8wz26nvn_.dbf
     1 ONLINE

/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_undotbs1_8wz2dnof_.dbf
     2 ONLINE


NAME
--------------------------------------------------------------------------------
       TS# STATUS
---------- -------
/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_system_8wz2hq6d_.dbf
     0 SYSTEM

/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_users_8wz2dmgk_.dbf
     4 ONLINE

/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_sysaux_8wz2hq68_.dbf
     1 ONLINE


NAME
--------------------------------------------------------------------------------
       TS# STATUS
---------- -------
/media/12c_db/oradata/ORA12CC1/E05626A829151520E0430F02000A6A84/datafile/o1_mf_s
ystem_8wz2y8z4_.dbf
     0 SYSTEM

/media/12c_db/oradata/ORA12CC1/E05626A829151520E0430F02000A6A84/datafile/o1_mf_s
ysaux_8wz2y92h_.dbf
     1 ONLINE

/media/12c_db/oradata/ORA12CC1/E05626A829151520E0430F02000A6A84/datafile/o1_mf_u

NAME
--------------------------------------------------------------------------------
       TS# STATUS
---------- -------
sers_8wz30lod_.dbf
     3 ONLINE

/media/12c_db/oradata/ORA12CC1/datafile/o1_mf_users_8wz81r9g_.dbf
     4 ONLINE


10 rows selected.


I will post little more examples on pluggable database. This above is just creation of pluggable database.

ORACLE-BASE - Oracle Database 12c Release 1 (12.1) Installation On Oracle Linux 6

ORACLE-BASE - Oracle Database 12c Release 1 (12.1) Installation On Oracle Linux 6

Please click the above link. I used the above link as reference and I have did the following installation in oracle linux 6

I have unziped the linuxamd64_12c_database* to database.

for pre-requiste I simply did 11G pre-requiste and the prequiste completed sucessfully for 12c database install.

so you can go for 

yum install

or

yum install oracle-rdbms-server-11gR2-preinstall -y

running root scripts 

$ su - root
Password: 
[root@ovs1 ~]# /media/12c_db/product/12.1.0/dbhome_1/root.sh
Performing root user operation for Oracle 12c 

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /media/12c_db/product/12.1.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

12cR1 installation done.

Please say your comments and let me know if you need any different scenario.

adding swap space in linux

Adding swap space in linux

easy  way is to use dd and add it on


1. decide the mountpoint where you are going to create swapspace. In my example, I plan to add swapspace in "/media/db_disk" mount point.

cd /media/db_disk
as root user


[root@ovs1 Desktop]#  dd if=/dev/zero of=/media/db_disk/myswapfile3 bs=1M count=2048
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 74.1111 s, 29.0 MB/s

checking the swap file

[root@ovs1 Desktop]# ls -l /media/db_disk/myswapfile3
-rw-r--r--. 1 root root 2147483648 Jun 28 15:45 /media/db_disk/myswapfile3

change the permission of the swap file

[root@ovs1 Desktop]# ls -l /media/db_disk/myswapfile3
-rw-------. 1 root root 2147483648 Jun 28 15:45 /media/db_disk/myswapfile

run mkswap command to format and ready for swap file use

[root@ovs1 Desktop]# mkswap /media/db_disk/myswapfile3
Setting up swapspace version 1, size = 2097148 KiB
no label, UUID=c0928bcc-ffe3-47c1-9051-a9bf43c14228


add the swapfile to swap group

[root@ovs1 Desktop]# swapon /media/db_disk/myswapfile3
[root@ovs1 Desktop]# swapon -s
Filename                Type        Size    Used    Priority
/dev/dm-1                               partition    2064380    0    -1
/media/grid/swapfile1                   file        1048572    0    -2
/media/db_disk/myswapfile3              file        2097148    0    -3


now important thing is that we need to add this swapfile in automount list. otherwise after reboot it wont get appear in the swap list


/etc/fstab is the file which is readed to do automount during startup.

# /etc/fstab
# Created by anaconda on Thu Jan 12 13:21:03 2012
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/vg_oraclelinux6-lv_root /                       btrfs    defaults        1 1
UUID=ed6b5002-07d3-4381-9057-47ee31704c78 /boot                   ext4    defaults        1 2
/dev/sde                /media/grid             ext4    defaults        0 0
/dev/sdf                /media/db_disk          ext4    defaults        0 0
/dev/sdg                /media/12c_db           ext4    defaults        0 0
/dev/mapper/vg_oraclelinux6-lv_swap swap                    swap    defaults        0 0
/media/grid/swapfile1   swapfile1               swap  defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0

now add the following line in /etc/fstab


/media/db_disk/myswapfile3 swapfile3            swap  defaults        0 0



[root@ovs1 Desktop]#
[root@ovs1 Desktop]# cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Thu Jan 12 13:21:03 2012
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/vg_oraclelinux6-lv_root /                       btrfs    defaults        1 1
UUID=ed6b5002-07d3-4381-9057-47ee31704c78 /boot                   ext4    defaults        1 2
/dev/sde                /media/grid             ext4    defaults        0 0
/dev/sdf                /media/db_disk          ext4    defaults        0 0
/dev/sdg                /media/12c_db           ext4    defaults        0 0
/dev/mapper/vg_oraclelinux6-lv_swap swap                    swap    defaults        0 0
/media/grid/swapfile1   swapfile1               swap  defaults        0 0
/media/db_disk/myswapfile3 swapfile3            swap  defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
[root@ovs1 Desktop]#


now after restart the swap file will get automatically mounted

ASH - Active session history

Active Session History


For performance issues, normally we will go with top-down approach.
Okay if we are coming to database level , from 11gR2 on-wards we could use the ASH efficiently and quickly to look the top events. Please note that ASH have added feature in 11GR2 than in 10g.

Most important thing is that we don’t have to depend on sql trace instead which we get all the information from dba_hist_active_session_history. (only thing is this needs license which sql trace doesn’t) but you know need to have manual work to capture all these things.

The ASH report will provide you the following thing which helps us to drill down quickly than AWR reports.

ASH Report
• Top Events
• Load Profile
• Top SQL
• Top PL/SQL
• Top Java
• Top Call Types
• Top Sessions
• Top Objects/Files/Latches
• Activity Over Time

When you invoke the ASH report it will give you the available min of reports for you. So you could give -1 (which will consider as sysdate -1 hour) or 0.5 (which will consider as sysdate – 0.5 hour).

Example

Oldest ASH sample available: 01-Feb-12 22:00:26 [ 12381 mins in the past]
Latest ASH sample available: 10-Feb-12 12:21:41 [ 0 mins in the past]

Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:
- Valid input formats:
-- To specify absolute begin time:
-- [MM/DD[/YY]] HH24:MI[:SS]
-- Examples: 02/23/03 14:30:15
-- 02/23 14:30:15
-- 14:30:15
-- 14:30
-- To specify relative begin time: (start with '-' sign)
-- -[HH24:]MI
-- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)
-- -25 (SYSDATE - 25 Mins)

Example report
Views
• V$ACTIVE_SESSION_HISTORY
• DBA_HIST_ACTIVE_SESS_HISTORY
This view dba_hist_active_sess_history will give information like “ event 10046 trace”. This view will give lots of information so we need to trill down it.
Example we could use column EVENT to point in particual column
For example
Where event=’ buffer busy waits’ Or event like ‘%I/O%’

Likewise there are many useful ways to make this table to have good information.

If you combine the view dba_hist_snapshot and dba_hist_active_sess_history. You could able to find lots of information



Reports
@?/rdbms/admin/ashrpt.sql
@?/rdbms/admin/ashrpti.sql