Schema Refresh (Using Export /Import)

1. In PROD Database (Source Database name: Prod1)

1.1 CHECK THE NUMBER OF OBJECTS IN THE SOURCE DATABASE:

SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='PROD_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                          OBJECT_TYPE         COUNT(OBJECT_TYPE)

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

PROD_USER123                   TABLE                               45

PROD_USER123                   INDEX                              120

PROD_USER123                   VIEW                                 1

PROD_USER123                   TRIGGER                              5

PROD_USER123                   SEQUENCE                            10

PROD_USER123                   LOB                                 19

6 rows selected.

1.2 CHECK THE INVALID OBJECTS IN THE SOURCE DATABASE:

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER='PROD_USER123';

no rows selected

1.3 TAKE EXPORT BACKUP OF PROD_USER123 SCHEMA FROM THE SOURCE DATABASE:

exp userid=system/xxxxxxx file=/p02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.dmp log=/p02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.log owner=PROD_USER123 rows=y indexes=y grants=y constraints=y

1.4 TAKE THE REQUIRED INFORMATION FROM THE SOURCE DATABASE:

SQL> set line 1000

SQL> select username,password,default_tablespace,temporary_tablespace,profile from dba_users where username ='PROD_USER123';

USERNAME        PASSWORD   DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE   PROFILE

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

PROD_USER123               USERDATA             TEMP                   DEFAULT

SQL> select * from dba_role_privs where grantee in('PROD_USER123');

no rows selected

SQL> select * from dba_sys_privs where grantee in('PROD_USER123');

GRANTEE                        PRIVILEGE                                ADM

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

PROD_USER123                   CREATE TRIGGER                           NO

PROD_USER123                   CREATE PROCEDURE                         NO

PROD_USER123                   CREATE SEQUENCE                          NO

PROD_USER123                   CREATE SESSION                           NO

PROD_USER123                   GLOBAL QUERY REWRITE                     NO

PROD_USER123                   CREATE MATERIALIZED VIEW                 NO

PROD_USER123                   CREATE DATABASE LINK                     NO

PROD_USER123                   ALTER  SESSION                           NO

PROD_USER123                   CREATE INDEXTYPE                         NO

PROD_USER123                   CREATE OPERATOR                          NO

PROD_USER123                   CREATE SYNONYM                           NO

PROD_USER123                   CREATE TABLE                             NO

PROD_USER123                   CREATE TYPE                              NO

PROD_USER123                   CREATE VIEW                              NO

PROD_USER123                   CREATE CLUSTER                           NO

15 rows selected.
   
SQL> select * from dba_ts_quotas where username='PROD_USER123';

TABLESPACE_NAME  USERNAME        BYTES      MAX_BYTES  BLOCKS     MAX_BLOCKS DRO

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

USERDATA         PROD_USER123    12582912   -1         1536       -1         NO

USERINDX         PROD_USER123    4980736    -1         608        -1         NO


1.4 SEND THE FULL EXPORT BACKUP TO THE TARGET DATABASE SERVER:

[oracle@proddb1 export]$ scp prod1.exp_PROD_USER123_11DEC.dmp oracle@devdb1:/d02/app/oracle/export

 ***********************************Target Database-Start******************************************************** 

2. In wmdevdb1 DEV Database server:(dev1)

2.1 CHECK THE NUMBER OF OBJECTS IN THE TARGET DATABASE :

SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='DEV_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                          OBJECT_TYPE         COUNT(OBJECT_TYPE)

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

DEV_USER123                    TRIGGER                              5

DEV_USER123                    SEQUENCE                             3

DEV_USER123                    LOB                                 12

DEV_USER123                    INDEX                               65

DEV_USER123                    VIEW                                 1

DEV_USER123                    TABLE                               26

6 rows selected.

2.2 CHECK THE INVALID OBJECTS IN THE TARGET DATABASE:

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER ='DEV_USER123';

no rows selected

2.3 TAKE EXPORT BACKUP OF DEV_USER123 SCHEMA FROM THE TARGET DATABASE :

[oracle@devdb1 export]$ exp userid=system/xxxxxx file=/d02/app/oracle/export/exp_DEV_USER123_11DEC.dmp log=/d02/app/oracle/export/exp_DEV_USER123_11DEC.log owner=DEV_USER123 rows=y indexes=y grants=y constraints=y

2.4 TAKE THE REQUIRED INFORMATION FROM THE TARGET DATABASE:
SQL> set line 1000    
SQL> select username,password,default_tablespace,temporary_tablespace,profile from dba_users where username ='DEV_USER123';

USERNAME    PASSWORD   DEFAULT_TABLESPACE TEMP_TABLESPACE PROFILE

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

DEV_USER123            USERDATA           TEMP            DEV_USER123_DEFAULT

SQL> select * from dba_role_privs where grantee in ('DEV_USER123');

no rows selected

SQL> select * from dba_sys_privs where grantee in ('DEV_USER123');

GRANTEE                        PRIVILEGE                                ADM

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

DEV_USER123                    CREATE OPERATOR                          NO

DEV_USER123                    CREATE MATERIALIZED VIEW                 NO

DEV_USER123                    CREATE TYPE                              NO

DEV_USER123                    CREATE SEQUENCE                          NO

DEV_USER123                    CREATE TABLE                             NO

DEV_USER123                    GLOBAL QUERY REWRITE                     NO

DEV_USER123                    CREATE INDEXTYPE                         NO

DEV_USER123                    ALTER  SESSION                           NO

DEV_USER123                    CREATE SESSION                           NO

DEV_USER123                    CREATE DATABASE LINK                     NO

DEV_USER123                    CREATE SYNONYM                           NO

DEV_USER123                    CREATE PROCEDURE                         NO

DEV_USER123                    CREATE VIEW                              NO

DEV_USER123                    CREATE CLUSTER                           NO

DEV_USER123                    CREATE TRIGGER                           NO

15 rows selected.

SQL> select * from dba_ts_quotas where username='DEV_USER123';

TABLESPACE_NAME  USERNAME        BYTES      MAX_BYTES  BLOCKS     MAX_BLOCKS DRO

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

USERDATA         DEV_USER123     38928384   -1         4752       -1         NO

USERINDX         DEV_USER123     8847360    -1         1080       -1         NO

2.5 DROP THE DEV_USER123 USERS FROM THE TARGET DATABASE:
SQL> drop user DEV_USER123 cascade;

2.6 CREATE THE DEV_USER123 USER AS PER THE SOURCE DATABASE :
SQL> create user DEV_USER123 identified by DEV_USER123

default tablespace USERDATA

temporary tablespace TEMP

profile DEV_USER123_DEFAULT

quota unlimited on USERDATA

quota unlimited on USERINDX;

2.7 GRANT THE PRIVILAGES TO THE PRODAPP USER AS PER SOURCE DATABASE :
SQL> grant connect,resource to DEV_USER123;
SQL> grant CREATE TABLE,CREATE SESSION,CREATE TRIGGER,CREATE SEQUENCE,UNLIMITED TABLESPACE,SELECT ANY DICTIONARY,CREATE VIEW to DEV_USER123;

2.8 IMPORT THE DATA TO THE TARGET DATABASE FROM THE SOURCE DATABASE :

[oracle@devdb1 export]$ imp userid=system/xxxxxxx file=/d02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.dmp 

log=/d02/app/oracle/export/prod1.exp_PROD_USER123_11DEC_imp.log 

fromuser=PROD_USER123 touser=DEV_USER123 ignore=y

2.9 CHECK THE NUMBER OF OBJECTS IN THE TARGET DATABASE :
SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='DEV_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                         OBJECT_TYPE         COUNT(OBJECT_TYPE)

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

DEV_USER123                   TABLE                               45

DEV_USER123                   INDEX                              120

DEV_USER123                   VIEW                                 1

DEV_USER123                   TRIGGER                              5

DEV_USER123                   SEQUENCE                            10

DEV_USER123                   LOB                                 19

6 rows selected.
Note: Output of above command should be same as production.

2.10 CHECK THE INVALID OBJECTS IN THE TARGET DATABASE :

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER ='DEV_USER123'; 

2.11 IF ANY INVALID OBJECTS THEN RUN UTLRP.SQL
    
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

**********************************END**********************************

Some usefull unix command for Oracle DBA

How to kill all similar processes with single command (in this case opmn)
ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}

Locating Files under a particular directory
find . -print |grep -i test.sql
 Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)
ps -ef |grep -i oracle |awk '{ print $2 }'

Changing the standard prompt for Oracle Users
PS1="`hostname`*$ORACLE_SID:$PWD>"

Display top 10 CPU consumers using the ps command\
/usr/ucb/ps auxgw | head -11

Show number of active Oracle dedicated connection users for a particular ORACLE_SID
ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l

Display the number of CPU’s in Solaris
psrinfo -v | grep "Status of processor"|wc -l

Display the number of CPU’s in AIX
lsdev -C | grep Process|wc -l

Display RAM Memory size on Solaris
prtconf |grep -i mem

Display RAM memory size on AIX

First determine name of memory device
lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’
lsattr -El mem0

Swap space allocation and usage
Solaris : swap -s or swap -l
Aix : lsps -a

Total number of semaphores held by all instances on server
ipcs -as | awk '{sum += $9} END {print sum}'

View allocated RAM memory segments
ipcs -pmb

Manually deallocate shared memeory segments
ipcrm -m '<ID>'

Show mount points for a disk in AIX
lspv -l hdisk13

Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory
du -ks * | sort -n| tail

Display total file space in a directory
du -ks .

Cleanup any unwanted trace files more than seven days old
find . *.trc -mtime +7 -exec rm {} \;

Locate Oracle files that contain certain strings
find . -print | xargs grep rollback

Locate recently created UNIX files (in the past one day)
find . -mtime -1 -print

Finding large files on the server (more than 100MB in size)
find . -size +102400 -print

Crontab :

To submit a task every Tuesday (day 2) at 2:45PM
45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every 15 minutes on weekdays (days 1-5)
15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)
15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

Creating standby database in oracle 11g without rman backups

Quick steps to set up a 11g Standby database with Active Data Guard using the Active Duplication feature available in 11g where we can create a standby database without having to take a backup on the primary database. Datafiles are copied over the network.

Primary machine – PROD01
Standby machine – STAND01

Database Name – PROD

TNS alias for Primary – prod_ps
TNS alias for standby – prod_ss

Enable force logging on the Primary database

SQL> alter database force logging;

Database altered.

Create the Standby log files on the Primary database

Copy the password file from the $ORACLE_HOME/dbs directory on primary server to $ORACLE_HOME/dbs on the standby server

Update listener.ora on Standby machine
(SID_DESC=
(GLOBAL_DBNAME=prod_ss)
(ORACLE_HOME=/p01/oracle/product/11.1.0/db_1)
(SID_NAME=prod)
)

Stop and Restart the listener on the standby site

Update tnsnames.ora on Standby as well as Primary site with the alias ‘prod_ss’ and ‘prod_ps’
prod_ss =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stand01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod_ss )
)
)

prod_ps =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = PROD01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod01.domain.com)
)
)

Create an init.ora on the Standby machine with just a single line which is the db_name parameter
[oracle@stand01 dbs]$ cat initprod.ora
db_name=prod

Startup the Standby instance in nomount state
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2152328 bytes
Variable Size 159385720 bytes
Database Buffers 50331648 bytes
Redo Buffers 5287936 bytes

On the Primary launch RMAN and establish an auxiliary connection to the standby instance
[oracle@prod01 ~]$ rman target / auxiliary sys/xxx@prod_ss

Recovery Manager: Release 11.1.0.7.0 – Production on Fri JAN 24 10:28:51 2013

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

connected to target database: PROD (DBID=2860177231)
connected to auxiliary database: PROD (not mounted)

Run the command to create the Standby Database
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK
DORECOVER
SPFILE
SET DB_UNIQUE_NAME=”prod_ss”
SET LOG_ARCHIVE_DEST_2=”service=prod_ps LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)”
SET FAL_SERVER=”prod_ps”
SET FAL_CLIENT=”prod_ss”
SET CONTROL_FILES=’/p02/oradata/prod/control01.ctl’,'/p03/oradata/prod/control02.ctl’;

Change the init.ora parameters related to redo transport and redo apply
On standby and primary

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

System altered.

On Primary

SQL> alter system set fal_server=prod_ss scope=both;

System altered.

SQL> alter system set fal_client=prod_ps scope=both;

System altered.

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

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod’;

System altered.

Shutdown the Standby and enable managed recovery (active standby mode)
SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size 2160352 bytes
Variable Size 775948576 bytes
Database Buffers 260046848 bytes
Redo Buffers 5730304 bytes
Database mounted.
Database opened.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>

Check if the MRP process is running
SQL> !ps -ef |grep mrp
oracle 446526 1 0 10:59:01 – 0:00 ora_mrp0_prod

TEST
--------------------
On Primary
SQL> conn system/xxx
Connected.
SQL> create table test123(mydate date);

Table created.

SQL> insert into test123 values(sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> alter system switch logfile;

System altered.

On Standby
SQL> conn system/xxx
Connected.
SQL> select to_char(mydate,’DD-MON-YY HH24:MI:SS’) from test123;

TO_CHAR(MYDATE,’DD-MON-YYHH
—————————
24-JAN-13 11:15:49