Saturday, November 16, 2013

Applying Incremental backup on Physical standby database

Lets assume, We have a situation like standby database thats lagged so much behind from production database or unfortunatly some archive logs got deleted from primary database without being shipped and applied to standby database. In those situations in order to sync up standby database with production, we need to apply incremental backup of production database on Standby.

Follow the below steps to sync standby with production DB:

Step1: Find current SCN from standby database, So from that SCN number we will take backup on Primary side.
 sql> select current_scn from v$database;

Step2: Take the incremental backup from the SCN number (37763034 - from Step1) in Production database
  RMAN> run {
 allocate channel c1 type disk format 'D:\backup\%U.rmb';
 backup incremental from scn 37763034 database;
 }

Step3: Take the standby control file backup in production database.
 RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT 'D:\backup\standby_Prod.bck';

Step4: Move incremental backup and standby controlfile to standby database

STEP5: Shutdown the standby database.
 SQL> alter database recover managed standby database cancel;
 SQL> shutdown

Step6: Now start standby database in nomount state and restore the backup control file.
 SQL> startup nomount;
 RMAN> RESTORE STANDBY CONTROLFILE FROM 'D:\backup\standby_Prod.bck';

Step7: Then mount the standby database and start recover the DR database.
 SQL> alter database mount standby database;
 RMAN> catalog start with 'D:\backup\';
 RMAN> recover database noredo;

Step8: Then shutdown and start the database and put it in recovery mode
 SQL> shutdown
 SQL> startup nomount;
 SQL> alter database mount standby database;
 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Thats It..Hope this will help you...:)

Best Regards,

Posted by at 1 comment:

Listener INTERMEDIATE status with "Not All Endpoints Registered" in 11gR2 RAC

When I issue clusterware services status resources command "crsctl stat res -t" it shows the listeners are in INTERMEDIATE status on node 1. The problem is caused by another listener defined statically in listener.ora,  using the same port and IP is running from the  ORACLE_HOME, started manually causing the default listener starting from GRID_HOME can not register its endpoint.

bash-3.2$ crsctl stat res -t
--------------------------------------------------------------------------------------------------------
NAME       TARGET  STATE   SERVER              STATE_DETAILS     Local Resources
---------------------------------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.FRA.dg
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.LISTENER.lsnr
               ONLINE  INTERMEDIATE rac1            Not All Endpoints Registered                                              
               ONLINE  ONLINE       rac2                                      
ora.asm
               ONLINE  ONLINE       rac1                  Started            
               ONLINE  ONLINE       rac2                  Started            
ora.gsd
               OFFLINE OFFLINE      rac1                                      
               OFFLINE OFFLINE      rac2                                      
ora.net1.network
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.ons
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.registry.acfs
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac2                                      
ora.cvu
      1        ONLINE  ONLINE       rac2                                      
ora.rac1.vip
      1        ONLINE  ONLINE       rac1                                      
ora.rac2.vip
      1        ONLINE  ONLINE       rac2                                      
ora.racdb.db
      1        ONLINE  ONLINE       rac1                   Open              
      2        ONLINE  ONLINE       rac2                   Open              
ora.oc4j
      1        ONLINE  ONLINE       rac2                                      
ora.scan1.vip
      1        ONLINE  ONLINE       rac2                                      
bash-3.2$

bash-3.2$ ps -ef|grep tns
  oracle  7471166        1   0   Oct 10      -  0:04 /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit
  oracle 30343230        1   0 13:11:50      -  0:00 /u01/app/grid/bin/tnslsnr LISTENER -inherit
  oracle 53674128 58720430   0 13:13:13  pts/1  0:00 grep tns
bash-3.2$

Solution for the above problem is, From 11g release 2 onwards all listeners should be running from GRID_HOME, listener and listener_scan(n) entry should be added automatically in listener.ora file.

1.Stop the listener running from ORACLE_HOME (RDBMS)

$/bin/lsnrctl stop
2. stop the listener from GRID_HOME

$/bin/srvctl stop listener -n node_name
$/bin/srvctl stop scan_listener -i scan#
3. restart the LISTENER and LISTENER_SCAN1  from GRID_HOME

$/bin/srvctl start listener -n node_name
$/bin/srvctl start scan_listener -i scan#
4. check crsctl stat res -t output, they both should show ONLINE status now.

Demonstration:

       
bash-3.2$ crsctl stat res -t
--------------------------------------------------------------------------------------------------------
NAME       TARGET  STATE   SERVER              STATE_DETAILS     Local Resources
---------------------------------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.FRA.dg
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.LISTENER.lsnr
               ONLINE  INTERMEDIATE rac1                    Not All Endpoints Registered                                              
               ONLINE  ONLINE       rac2                                      
ora.asm
               ONLINE  ONLINE       rac1                             Started            
               ONLINE  ONLINE       rac2                             Started            
ora.gsd
               OFFLINE OFFLINE      rac1                                      
               OFFLINE OFFLINE      rac2                                      
ora.net1.network
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.ons
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.registry.acfs
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac2                                      
ora.cvu
      1        ONLINE  ONLINE       rac2                                      
ora.rac1.vip
      1        ONLINE  ONLINE       rac1                                      
ora.rac2.vip
      1        ONLINE  ONLINE       rac2                                      
ora.racdb.db
      1        ONLINE  ONLINE       rac1                      Open              
      2        ONLINE  ONLINE       rac2                      Open              
ora.oc4j
      1        ONLINE  ONLINE       rac2                                      
ora.scan1.vip
      1        ONLINE  ONLINE       rac2                                      
bash-3.2$
You have mail in /usr/spool/mail/oracle

bash-3.2$ ps -ef|grep tns
  oracle  7471166        1   0   Oct 10      -  0:04 /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit
  oracle 30343230        1   0 13:11:50      -  0:00 /u01/app/grid/bin/tnslsnr LISTENER -inherit
  oracle 53674128 58720430   0 13:13:13  pts/1  0:00 grep tns
bash-3.2$

bash-3.2$ cd $ORACLE_HOME/bin
bash-3.2$
bash-3.2$ lsnrctl stop        

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 31-OCT-2013 13:18:08
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

bash-3.2$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 31-OCT-2013 13:18:15
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   IBM/AIX RISC System/6000 Error: 79: Connection refused
bash-3.2$
bash-3.2$ ps -ef |grep tns
  oracle 58261656 58720430   0 13:18:32  pts/1  0:00 grep tns
bash-3.2$
bash-3.2$ srvctl stop listener -n rac1
bash-3.2$
bash-3.2$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): rac2
bash-3.2$
bash-3.2$ srvctl start listener -n rac1
bash-3.2$
bash-3.2$ ps -ef|grep tns
  oracle 17432626        1   0 13:20:00      -  0:00 /u01/app/grid/bin/tnslsnr LISTENER -inherit
  oracle 33357886 58720430   0 13:20:07  pts/1  0:00 grep tns
bash-3.2$
bash-3.2$ crsctl stat res -t
--------------------------------------------------------------------------------------------------------
NAME       TARGET  STATE   SERVER              STATE_DETAILS     Local Resources
---------------------------------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.FRA.dg
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.asm
               ONLINE  ONLINE       rac1                           Started            
               ONLINE  ONLINE       rac2                           Started            
ora.gsd
               OFFLINE OFFLINE      rac1                                      
               OFFLINE OFFLINE      rac2                                      
ora.net1.network
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.ons
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.registry.acfs
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac2                                      
ora.cvu
      1        ONLINE  ONLINE       rac2                                      
ora.rac1.vip
      1        ONLINE  ONLINE       rac1                                      
ora.rac2.vip
      1        ONLINE  ONLINE       rac2                                      
ora.racdb.db
      1        ONLINE  ONLINE       rac1                          Open              
      2        ONLINE  ONLINE       rac2                          Open              
ora.oc4j
      1        ONLINE  ONLINE       rac2                                      
ora.scan1.vip
      1        ONLINE  ONLINE       rac2                                      
bash-3.2$
bash-3.2$
bash-3.2$
bash-3.2$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): rac2,rac1
bash-3.2$

Thats it...Hope this will help you...:)

Best Regards,
Posted by at 12 comments:
Labels:

Wednesday, November 6, 2013

Usage of SQLFILE parameter in Data Pump Import

It specifies a file into which all of the SQL DDL that Import would have executed. So Sqlfile option will show you the DDL in the text format. SQLFILE parameter is pretty much similar to SHOW=YES in IMP. Sometimes customer provides us data pump export file, by that time we will use SQLFILE parameter and find out DDL's (Schema names) and perform data pump import accordingly. See below syntax and example for SQLFILE parameter usage.

Syntax: impdp directory=DATA_PUMP_DIR dumpfile=PRODDB_Full.dmp sqlfile=FULL_DDL.sql

Example of usage:

[oracle@01H15439 PRODDB]$ impdp directory=DATA_PUMP_DIR dumpfile=PRODDB_Full.dmp sqlfile=FULL_DDL.sql

Import: Release 11.2.0.3.0 - Production on Wed Nov 6 14:47:14 2013

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

Username: / as sysdba 

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=PRODDB_Full.dmp sqlfile=FULL_DDL.sql
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/EVENT/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/MATERIALIZED_VIEW_LOG
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 14:55:01
[oracle@01H15439 PRODDB]$

Hope this will help you...:)

Best Regards,
Posted by at No comments:

Saturday, October 26, 2013

Renaming an Oracle database

Follow below steps to rename database name from UATDB to UATNEWDB.

Step1: In UATDB, connect to SQLPLUS and issue command "alter database backup controlfile to trace;". this command generate a script containg a create controlfile

command and stored it in trace (Udump) directory. you can find location as USER_DUMP_DEST paramenter in parameter file.

Step2: The trace file output looks like below.

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "UATDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/oracle/oradata/UATDB/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/UATDB/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/UATDB/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/UATDB/system01.dbf',
  '/oracle/oradata/UATDB/sysaux01.dbf',
  '/oracle/oradata/UATDB/users01.dbf',
  '/oracle/oradata/UATDB/undotbs0.dbf',
  '/oracle/oradata/UATDB/DATA01.dbf'
CHARACTER SET WE8ISO8859P1
;

Step3: Shutdown the UATDB database

Step4: Change all locations to new DB name in Spfile, tnsnames and listener etc..

Step5: Change the database name in trace file that pointing to new DB name "UATNEWDB" like below

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "UATNEWDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/oracle/oradata/UATNEWDB/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/UATNEWDB/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/UATNEWDB/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/UATNEWDB/system01.dbf',
  '/oracle/oradata/UATNEWDB/sysaux01.dbf',
  '/oracle/oradata/UATNEWDB/users01.dbf',
  '/oracle/oradata/UATNEWDB/undotbs0.dbf',
  '/oracle/oradata/UATNEWDB/DATA01.dbf'
CHARACTER SET WE8ISO8859P1
;

Step6: Run the above script to create new controlfiles and open database in resetlogs option.

Practical demonstration for renaming database name from UATDB to UATNEWDB:

$ export ORACLE_SID=UATDB

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 18:19:31 2012

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database backup controlfile to trace;

Database altered.

SQL> create pfile from spfile;
File created.

SQL> show parameter dump;  -- find the udump location where trace control file created

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Modify the trace file like below

$ cat UATDB_ctrl.sql

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "UATNEWDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/oracle/oradata/UATNEWDB/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/UATNEWDB/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/UATNEWDB/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/UATNEWDB/system01.dbf',
  '/oracle/oradata/UATNEWDB/sysaux01.dbf',
  '/oracle/oradata/UATNEWDB/users01.dbf',
  '/oracle/oradata/UATNEWDB/undotbs0.dbf',
  '/oracle/oradata/UATNEWDB/DATA01.dbf'
CHARACTER SET WE8ISO8859P1
;

$

Modify prameters in initUATDB.ora file and change file to initUATNEWDB.ora

$ mv initUATDB.ora initUATNEWDB.ora

Just backup of old controlfiles

$ mv control01.ctl control01.ctl_bkp
$ mv control02.ctl control02.ctl_bkp
$ mv control03.ctl control03.ctl_bkp
$

$ export ORACLE_SID=UATNEWDB
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 19:10:39 2012

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

Connected to an idle instance.
SQL>
SQL> @UATDB_ctrl.sql
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  2020960 bytes
Variable Size             285215136 bytes
Database Buffers         1224736768 bytes
Redo Buffers               14753792 bytes

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select file_name from dba_data_files;

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> exit

$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 19:15:06 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup;
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  2020960 bytes
Variable Size             369101216 bytes
Database Buffers         1140850688 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.
SQL>

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
$

Finally create Temporary tablespace, make it as default and create password file for new DB "UATNEWDB"

Create tempfile and make aa a default:

SQL> create temporary tablespace TEMP01 tempfile '/oracle1/oradata/uatnewdb/temp01.dbf' size 1024M autoextend on;

SQL> alter database default temporary tablespace TEMP01;


make an entry to /etc/oratab:

$ vi  /etc/oratab

CREATE PASSWORD FILE FOR NEW DB:

orapwd force=y file=orapwUATNEWDB password=sys entries=5

Thats It!!!

Hope this will help you...:)

Best Regards,
Posted by at No comments:

Friday, October 25, 2013

Installation of Oracle Database 11gR2 on Oracle Enterprise Linux 5.7

Prerequisite:  Oracle Enterprise Linux 5.7 Installation

Start PrimDB VMachine


Follow below steps to Installation of oracle database 11gR2 and creation of PRIMDB database:

Create group and User:

[root@primdb ~]# groupadd -g 1000 oinstall
[root@primdb ~]# useradd -u 1100 -g oinstall oracle
[root@primdb ~]# passwd oracle
Changing password for user oracle.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@primdb ~]#

Create directories and give folder permissions in which Oracle software will be installed:

[root@primdb ~]# mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1
[root@primdb ~]# chown -R oracle:oinstall /u01
[root@primdb ~]# chmod -R 775 /u01

Copy 11.2.0.3 database software to /u01/database location:

[root@primdb ~]# cd /u01/
[root@primdb ~]# ls
app database
[root@primdb ~]# chown -R oracle:oinstall /u01
[root@primdb ~]# chmod -R 775 /u01
[root@primdb ~]# su  –  oracle
[root@primdb ~]$ cd /u01/database/

Before running runInstaller open a newterminal and run xhost + as a root user:

[root@primdb ~]# xhost +
access control disabled, clients can connect from any host

Start Installation with runInstaller:

[root@primdb database]$ ./runInstaller


Uncheck the Updates box and Click Next



Click Yes


Select Skip Software updates and Click Next


Select Create and Configure a database and Click Next


Select Server Class and Click Next


Select Single instance database installation and Click Next


 Select Advanced Install and Click Next



Select Enterprise Edition and Click Next


























Run the scripts as shown below








That's it..Completed Oracle 11gR2 database installation and created new PRIMDB database.

Hope this will help you!!! :)

Best Regards,

Posted by at 1 comment:
Labels:

Script: Shell Script to export Full DB or Single or Multiple Schemas

Just recently I have written below script for export full database or single/multiple schemas.

Follow the steps in comments section.

#!/bin/bash
###################################################################################################** Logical Database/Schema's backup Script**###########################
############## **Chowdari's Oracle DBA Blog - mbc-dba.blogspot.com ** ####################
########################################################################################################## Please create exp_user and provide grants #######################
# create user EXP_USR identified by EXP_USR;                                                                             #####
# grant CONNECT,EXP_FULL_DATABASE,IMP_FULL_DATABASE to EXP_USR;           #######
#################################################################################
# If you want schema wise backup, Please add list of schemas with single space otherwise                
# keep it blank.  Follow below examples                                                              ###################
# Example for schemawise backup: SchemaName=(ABC XYZ MNO) - Here ABC,XYZ, MNO are  ### #  schema names                                                                                                        ################
# Example for full db backup: SchemaName=()                                                          ################
#################################################################################
# Please assign database name in SID variable. Example I have given TESTDB            ###############
#################################################################################  

SID=TESTDB
BKP_LOCATION=`pwd`
SchemaName=(CRM EMP)
#SchemaName=()
SchLen=${#SchemaName[@]}

if [ ${SchLen} -eq 0 ]; then
 exp EXP_USR/EXP_USR@$SID FILE=$BKP_LOCATION/EXP_FULL.DMP LOG=$BKP_LOCATION/EXP_FULL.LOG FULL=Y STATISTICS=NONE
else
 echo "Schema Bkp"
 for (( i = 0; i <= `expr ${SchLen} - 1`; i++ ))
 do
  exp EXP_USR/EXP_USR@$SID FILE=$BKP_LOCATION/${SchemaName[$i]}_EXP.DMP LOG=$BKP_LOCATION/${SchemaName[$i]}_EXP.LOG OWNER=${SchemaName[$i]} STATISTICS=NONE
 done
fi


Hope this will help you... :)

Best Regards,
Posted by at 4 comments:

Saturday, October 19, 2013

AIOUG (All India Oracle User Group) Conference Sangam 13

The Oracle user conference, Sangam '13 is being conducted by AIOUG (All India Oracle User Group) at  Hyderabad, India on 8th and 9th November 2013. Registration is open now.

For more details please visit Sangam13 or AIOUG

Hope will see you there!!! :)

Have a nice day!!!


Posted by at No comments:

Thursday, October 17, 2013

Managing Temp Tablespace

Here you will see how to manage temporary tablespace.

Temporary tablespace is used for Global temporary tables and sorting operations with the query that uses group by, order by, distinct etc.. which needed intermediate storage to produce the results. Global temporary tables  will release the storage either on commit or on disconnect, depending on how it was created.

In any case Temp tablespace will not shrink by itself, we have to do manually cleanup the temp tablespace.

Below are step by steps to cleanup Temperary tablespace:

1) Using below queries, find default temporary tablespace and size

   SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

   PROPERTY_NAME                        PROPERTY_VALUE     DESCRIPTION
   -------------------------------           ---------------               ----------------------------------------
   DEFAULT_TEMP_TABLESPACE   TEMP002                     Name of default temporary tablespace

   SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

   TABLESPACE_NAME       FILE_NAME                                               BYTES
   ------------------------        -----------------                                           ---------
   TEMP002                           /Oracle/oradata/Prod/TEMP0002.DBF          10737418240

   SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

   TABLESPACE_NAME                BYTES_USED     BYTES_FREE
   ------------------------------        ----------------      ----------
   TEMP002                                    10737418240          0

2) Create new temporary tablespace and make it as a default 

   SQL> CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE '/Oracle/oradata/Prod/TEMP01.DBF' SIZE 100M autoextend on;

   Tablespace created.

   SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;

   Database altered.

3) Crosscheck whether default temp tablespace changed or not

   SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

   PROPERTY_NAME                         PROPERTY_VALUE     DESCRIPTION
   --------------------------                    ---------------               --------------------------------------
   DEFAULT_TEMP_TABLESPACE     TEMP01                         Name of default temporary tablespace

4) Now drop OLD temp tablespace.

   SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

   TABLESPACE_NAME    FILE_NAME                                                  BYTES
   ------------------------     -----------                                                       ------------
   TEMP002                         /Oracle/oradata/Prod/TEMP0002.DBF           10737418240         
   TEMP01                          /Oracle/oradata/Prod/TEMP01.DBF                104857600

   SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

   TABLESPACE_NAME                BYTES_USED        BYTES_FREE
   ------------------------------           ----------                 ----------
   TEMP01                                       27262976               77594624
   TEMP002                                     10737418240          0

   SQL> DROP TABLESPACE TEMP002  INCLUDING CONTENTS AND DATAFILES;

Thats it.

Hope this will help you... :)

Best Regards,
Posted by at No comments:

Oracle Enterprise Linux 5.7 installation on Oracle VirtualBox

Follow below Step by Steps pictorial guide to installing OEL 5.7 on Oracle Virtual Box

Download VirtualBox
Download Oracle Enterprise Linux 5.7 64-bit

Select New virtual machine and click next
Select Linux operating system and select Oracle 64 bit version
 Select 2GB RAM
 Select new hard disk
Select VMDK
Select Dynamically allocated storage

Create New virtual machine



























 


Select reboot

















Install guest additions
Copy guest addition as shown below
Eject the guest addition CD
Mount Linux software
Go to Server directory in Linux Software and install the selected package
Go to Guest Additions software location and install Guest Additions now
 Once reboot the operating system now. Completed Oracle Enterprise Linux 5.7 installation on Oracle VirtualBox.

Hope this will help you... :)

Best Regards,

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

Some Most Popular Articles