Soumya's Database Blog : March 2015

Shell script for redirecting output of rman command for oracle 11g

vi /backups/scripts/rmanoutput.sh

#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=prim
export PATH=$PATH:$ORACLE_HOME/bin
export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
export DATE=$(date +%Y-%m-%d)

rman target sys/sys@prim msglog /u01/rman_full_backup_db_online_TEST1_${DATE}.log <<EOF

backup database plus archivelog;

exit;

EOF

:wq

P.S Output for currently executing RMAN jobs is also stored in the V$RMAN_OUTPUT view,
which reads only from memory (that is, the information is not stored in the control file).
The V$RMAN_STATUS view contains metadata about jobs in progress as well as completed jobs.
The metadata for completed jobs is stored in the control file.

Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Few words on CPU, PSU, SPU - Oracle Critical Patch Update Terminology Update

It all started in January 2005 with Critical Patch Updates (CPU).Then Patch Set Updates (PSU) were added
as cumulative patches that included priority fixes as well as security fixes. As of the October 2012
Critical Patch Update, Oracle has changed the terminology to better differentiate between patch types.
This terminology will be used for the Oracle Database, Enterprise Manager, Fusion Middleware, and
WebLogic.

Critical Patch Update (CPU) now refers to the overall release of security fixes each quarter rather
than the cumulative database security patch for the quarter. Think of the CPU as the overarching
quarterly release and not as a single patch.

Patch Set Updates (PSU) are the same cumulative patches that include both the security fixes and
priority fixes. The key with PSUs is they are minor version upgrades (e.g., 11.2.0.1.1 to 11.2.0.1.2).
Once a PSU is applied, only PSUs can be applied in future quarters until the database is upgraded to a
new base version.

Security Patch Update (SPU) terminology is introduced in the October 2012 Critical Patch Update as the
term for the quarterly security patch. SPU patches are the same as previous CPU patches, just a new name.
For the database, SPUs can not be applied once PSUs have been applied until the database is upgraded to a
new base version.

Bundle Patches are the quarterly patches for Windows and Exadata which include both the quarterly security
patches as well as recommended fixes.

Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Oracle 11.2.0.4 download link for Linux x86_64

Oracle 11.2.0.4 download link for Linux x86_64:-

p13390677_112040_Linux-x86-64_1of7.zip- https://mega.nz/file/rB5FCC7A#YfSa5BIC25CgP8ANeQQAZ2buu1NknYXvplGTG3bo6is

p13390677_112040_Linux-x86-64_2of7.zip -

https://1drv.ms/u/s!AojZQaghYsq5biIeThihzYoMyC0

p13390677_112040_Linux-x86-64_3of7.zip - https://1drv.ms/u/s!AojZQaghYsq5gQRQSKLSqpRG47uN

p13390677_112040_Linux-x86-64_4of7.zip -

https://1drv.ms/u/s!Auolf32UbJXrexWWDLcIis1EQYw

p13390677_112040_Linux-x86-64_5of7.zip - https://1drv.ms/u/s!AojZQaghYsq5gQYfyLHITzpZK4R5

p13390677_112040_Linux-x86-64_6of7.zip -

https://1drv.ms/u/s!Auolf32UbJXreq7VmLvF-Iws6-U

p13390677_112040_Linux-x86-64_7of7.zip -

https://1drv.ms/u/s!AojZQaghYsq5gQu-kZlsX_xshcDH

Please share your ideas and opinions about this topic.

improve alexa rank If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

How to move all types of datafiles from one location to another in oracle 11g

Oracle Version: 11g
OS Version: Rhel 6

Step 1. Check the datafiles location & tablespace name .
SQL> column file_name format a50
SQL> select file_name , tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/prim/users01.dbf USERS
/u01/app/oracle/oradata/prim/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/prim/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf EXAMPLE
/u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf GHHSTORE_DATA_TBS

SQL> select file_name , tablespace_name from dba_temp_files;

FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/prim/temp01.dbf TEMP

Step 2.Shutdown the database and move the data files into new location.

SQL>shut immediate;
SQL>! mv /u01/app/oracle/oradata/prim/users01.dbf /u02/oradata/users01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/undotbs01.dbf /u02/oradata/undotbs01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/sysaux01.dbf /u02/oradata/sysaux01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/system01.dbf /u02/oradata/system01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf /u02/oradata/GHHSTORE_DATA_TBS.dbf
SQL>! mv /u01/app/oracle/oradata/prim/temp01.dbf /u02/oradata/temp01.dbf
Step 3. Start the database in mount point and rename the datafiles.

SQL> alter database rename file '/u01/app/oracle/oradata/prim/users01.dbf' to
'/u02/oradata/users01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/undotbs01.dbf' to
'/u02/oradata/undotbs01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/sysaux01.dbf' to
'/u02/oradata/sysaux01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/system01.dbf' to
'/u02/oradata/system01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf' to
'/u02/oradata/GHHSTORE_DATA_TBS.dbf' ;

SQL> alter database rename file '/u01/app/oracle/oradata/prim/temp01.dbf' to
'/u02/oradata/temp01.dbf';

Step 4.Open the database and check datafile location.
SQL>alter database open;
SQL> column file_name format a50
SQL> select file_name , tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u02/oradata/users01.dbf USERS
/u02/oradata/undotbs01.dbf UNDOTBS1
/u02/oradata/sysaux01.dbf SYSAUX
/u02/oradata/system01.dbf SYSTEM
/u02/oradata/example01.dbf EXAMPLE
/u02/oradata/GHHSTORE_DATA_TBS.dbf GHHSTORE_DATA_TBS

SQL> select file_name , tablespace_name from dba_temp_files;

FILE_NAME TABLESPACE_NAME
---------------------------------- ------------------------------
/u02/oradata/temp01.dbf TEMP

Done..

Incase if we cant shutdown the database, we can still perform the activity except for system datafile
and undo datafile.However we can change the default undo tablespace by creating a new undo tablespace
and drop the old one.

For users or any non-default system datafile:-
SQL> alter tablespace users offline;
SQL> alter database rename file '/u01/app/oracle/oradata/prim/users01.dbf' to
'/u02/oradata/users01.dbf';
SQL> alter tablespace users online;

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

FILE_NAME ONLINE_ TABLESPACE_NAME
-------------------------------------------------- ------- ------------------------------
/u02/oradata/users01.dbf ONLINE USERS

For sysaux datafile:-
SQL> alter tablespace sysaux offline;

Tablespace altered.

SQL> ! mv /u01/app/oracle/oradata/prim/sysaux01.dbf /u02/oradata/sysaux01.dbf;
SQL> alter database rename file '/u01/app/oracle/oradata/prim/sysaux01.dbf' to '/u02/oradata/sysaux01.dbf';

Database altered.

SQL> alter tablespace sysaux online;

SQL> select file_name , online_status , tablespace_name from dba_data_files where tablespace_name='SYSAUX';

FILE_NAME ONLINE_ TABLESPACE_NAME
-------------------------------------------------- ------- ------------------------------
/u02/oradata/sysaux01.dbf ONLINE SYSAUX

For temporary datafile:-

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_TEMP_FILES;

FILE_NAME TABLESPACE_NAME STATUS
---------------------------------------- ---------------------- -------
/u01/app/oracle/oradata/prim/temp01.dbf TEMP ONLINE

SQL> alter database tempfile '/u01/app/oracle/oradata/prim/temp01.dbf' offline;

Database altered.

SQL> ! mv /u01/app/oracle/oradata/prim/temp01.dbf /u02/oradata/temp01.dbf

SQL> alter database rename file '/u01/app/oracle/oradata/prim/temp01.dbf' to
'/u02/oradata/temp01.dbf';

Database altered.
SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_TEMP_FILES;

FILE_NAME TABLESPACE_NAME STATUS
---------------------------------------- ---------------------- -------
/u02/oradata/temp01.dbf TEMP OFFINE

SQL> alter database tempfile '/u02/oradata/temp01.dbf' online;

Database altered.
SQL> select file_name, tablespace_name , status from dba_temp_files;

FILE_NAME TABLESPACE_NAME STATUS
---------------------------------------- ------------------------------ -------
/u02/oradata/temp01.dbf TEMP ONLINE

For system Datafile:-
We cant offline a system tablespace. so for the we need to shutdown the database and then
move it to the new location as describe before.

For undo datafile:-
We cant drop or move default undo tablespace.
First create a new undo tablespace and then drop the old one.
SQL> create undo tablespace undotbs2 datafile '/u02/oradata/undotbs01.dbf' size 100M;

Tablespace created.
SQL> alter system set undo_tablespace= undotbs2 ;
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL>select file_name , online_status , tablespace_name from dba_data_files where tablespace_name like 'UNDO%';

FILE_NAME ONLINE_ TABLESPACE_NAME
---------------------------------------- ------- ------------------------------
/u02/oradata/undotbs01.dbf ONLINE UNDOTBS2

Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

How to move redolog files from one location to another in oracle 11g

Oracle Version :11g
OS: Rhel 6

Step 1:-Determine current location for redolog files.

SQL> column member format a40
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ---------------------------------------- ---------------------
3 ONLINE /u01/app/oracle/oradata/prim/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/prim/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/prim/redo01.log NO

Step 2.Shutdown the database and move the redolog files into new location.

SQL>shut immediate;
SQL>! mv /u01/app/oracle/oradata/prim/redo03.log /u02/redo03.log
SQL>! mv /u01/app/oracle/oradata/prim/redo02.log /u02/redo02.log
SQL>! mv /u01/app/oracle/oradata/prim/redo01.log /u02/redo01.log

Step 3.Start the database in mount point and rename the redolog files .

SQL> startup mount
SQL> alter database rename file '/u01/app/oracle/oradata/prim/redo03.log' to '/u02/redo03.log';
SQL> alter database rename file '/u01/app/oracle/oradata/prim/redo02.log' to '/u02/redo02.log';
SQL> alter database rename file '/u01/app/oracle/oradata/prim/redo01.log' to '/u02/redo01.log';

Step 4. Open the database and check new location of redolog files.

SQL>alter database open;
SQL> column member format a40
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ---------------- ---------------------
3 ONLINE /u02/redo03.log NO
2 ONLINE /u02/redo02.log NO
1 ONLINE /u02/redo01.log NO

Done..

Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

bash_profile setting for different oracle versions installed in a single server.

vi /home/oracle/.bash_profile

umask 022
export ORACLE_BASE=/u01/app/oracle
export TNS_ADMIN=$ORACLE_BASE/product/11.2.0/db_1/network/admin

10g()
{ export ORACLE_SID=prim10g
export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export PS1='[\u@\h\W]\$ '
}

11g()
{ export ORACLE_SID=prim11g
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export PS1='[\u@\h\W]\$ '
}

12c()
{ export ORACLE_SID=prim12c
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export PS1='[\u@\h\W]\$ '
}

--save and quit (:wq)

[oracle@server1 ~]$ . .bash_profile

Simply type "10g" to set 10g environment, "11g" for 12c, and so on.

Table replication using Materialized View in oracle 11g

Source Database:prim
Target Database:-stand

Step 1.create the USERS table and add some sample records inside.

At source database:-
[oracle@server1 ~]$ sqlplus sys/system as sysdba@prim
SQL> create user appuser identified by appuser;
SQL> GRANT CONNECT,RESOURCE TO appuser;
SQL> conn appuser/appuser
Connected.

SQL> insert into users values (1 ,'RAM','DAS','REDHAT');
SQL> insert into users values (2, 'SHYAM','SINGH','SUSE');
SQL> insert into users values (3, 'ROHAN','SHARMA','UBUNTU');
SQL> COMMIT;
Commit complete.

At target database:-
Step 2.Now before creating database link we need to add entry inside tnsnames.ora file
so that the target database can connect to the source database.

[oracle@server1 ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
STAND =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stand)
)
)

prim =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)
)
)

:wq
so we added the tnsentry for source database i.e. prim inside tnsnames.ora file

Step 3.Now connect to the local database i.e. target and create a user appuser2

[oracle@server2 ~]$ sqlplus sys/system as sysdba@stand
SQL> create user appuser2 identified by appuser2;
SQL> grant connect , resource , create database link , create materialized view to appuser2;
SQL> conn appuser2/appuser2
Connected.

At target database:-
Step 3.Now create a database link to connect source database from appuser2
[oracle@server2 ~]$ sqlplus appuser2/appuser2@stand
SQL> CREATE DATABASE LINK stand CONNECT TO appuser IDENTIFIED BY appuser USING 'prim';
CREATE DATABASE LINK stand CONNECT TO appuser IDENTIFIED BY appuser USING 'prim'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

While creating the database link i was facing the above error and i fixed it using the below steps
SQL>select * from global_name;
STAND
-- write down the current name;

SQL> Alter database rename global_name to tttt.xxx;

SQL> CREATE DATABASE LINK mylink CONNECT TO appuser IDENTIFIED BY appuser USING 'prim';

Rename global name back to original from the query above.

SQL> Alter database rename global_name to STAND.soumya.com;

Now You must be able to Select any table from user APP1 On Source Database from target database.
At target database:-
[oracle@server2 ~]$ sqlplus appuser/appuser@prim
SQL> select * from users;

USER_ID FIRST_NAME LAST_NAME PASSWORD
---------- ---------- ------------------------------ --------------------
1 RAM DAS REDHAT
2 SHYAM SINGH SUSE
3 ROHAN SHARMA UBUNTU

Step 4.Now we will create materialized view On target Database:-
SQL> CREATE MATERIALIZED VIEW V_USERS REFRESH NEXT
SYSDATE+INTERVAL '10' MINUTE WITH ROWID AS SELECT * FROM users@mylink;

Step 5. Now test the scenario:-
[oracle@server2 ~]$ sqlplus appuser2/appuser2@stand
At target database:-
SQL> select count(*) from V_USERS;
It Must Give you same Number Of Row In appuser@prim , If you want to test synchronization
All you have to do is

At source database:-
SQL> conn appuser/appuser
SQL> INSERT INTO USERS VALUES (4, 'ROBERT', 'ASPRIN', 'PASS4')

And then check at target database after 10mins.It would be in sync.

Error in invoking target 'rat_on part_on dm_on olap_on sdo_on' while installing oracle 11g

Today while i was browsing on OTN i found a thread related to oracle 11gR2 Installation , the OP faced an error like the below :-

The above error related to Space On Temp File system all you have to do make sure that there is enough space on temp ( tmp ) or use the below command :-

export TMP=/u01/Foldername
export TMPDIR=/u01/Foldername

Try again now.It should resolve the error