Feeds:
Posts
Comments

Archive for September, 2011

Dear Friends,

          Back to work after long time, was busy in trekking. Today we did database migration from 9i to 10g using Export / Import.

To upgrade a database using the Export/Import utilities kindly follow below mention steps.

  •  Keep database in restricted mode – Export data from the current database
    exp  FILE=exp_20092011.dmp FULL=y GRANTS=y BUFFER=4096  ROWS=y CONSISTENT=y

 

  • Now once export is done prerequisite for import :

1) Please verify for sysaux tablespace is available. And also the 10g parameter properly set.

2)Please create the required Users and Tablespace as per the requirement.

Query yo check  User with respective privilages.

select

  lpad(‘ ‘, 2*level) || granted_role “User, his roles and privileges”

from

  (

  /* THE USERS */

    select

      null     grantee,

      username granted_role

    from

      dba_users

    where

      username like upper(‘%&enter_username%’)

  /* THE ROLES TO ROLES RELATIONS */

  union

    select

      grantee,

      granted_role

    from

      dba_role_privs

  /* THE ROLES TO PRIVILEGE RELATIONS */

  union

    select

      grantee,

      privilege

    from

      dba_sys_privs

  )

start with grantee is null

connect by grantee = prior granted_role;

 
 Query to check Tablespaces and there sizes.

set line 120

col host_name for a20;

select w.name,Q.host_name,a.tablespace_name,round(b.total_mb/1024/1024,2) total_mb,

       round(x.maxbytes_mb/1024/1024,2) maxbytes_mb,

       round(nvl(c.used_mb/1024/1024,0),2) Used_Mb,

       round(nvl(d.free_mb/1024/1024,0),2) Free_mb,

       round(((nvl(Used_mb,1)/decode(maxbytes_mb,NULL,total_mb,0,total_mb,maxbytes_mb))*100),2) Used_percent

from   dba_tablespaces a, (select tablespace_name,bytes Total_Mb from sys.sm$ts_avail) b,

       (select tablespace_name,bytes Used_mb from sys.sm$ts_used) c,

       (select tablespace_name,bytes free_mb from sys.sm$ts_free) d, v$database w, v$instance Q,

       (select tablespace_name,sum(maxbytes) maxbytes_mb from dba_data_files group by tablespace_name) x

where  a.tablespace_name=b.tablespace_name(+)

and    a.tablespace_name=c.tablespace_name(+)

and    a.tablespace_name=x.tablespace_name(+)

and    a.tablespace_name=d.tablespace_name(+) order by 8 desc;

 

You can get the DDL of tablespace using below script.

set pagesize 0

set escape on

spool ‘\tablespace.sql’

select ‘create tablespace ‘ || df.tablespace_name || chr(10)

|| ‘ datafile ”’ || df.file_name || ”’ size ‘ || df.bytes

 || decode(autoextensible,’N’,null, chr(10) || ‘ autoextend on maxsize ‘

 || maxbytes)

 || chr(10)

 || ‘default storage ( initial ‘ || initial_extent

 || decode (next_extent, null, null, ‘ next ‘ || next_extent )

|| ‘ minextents ‘ || min_extents

|| ‘ maxextents ‘ ||  decode(max_extents,’2147483645′,’unlimited’,max_extents)

 || ‘) ;’

from dba_data_files df, dba_tablespaces t

where df.tablespace_name=t.tablespace_name;

set pagesize 100

set escape off

spool off

 

You can get the DDL of  Users using below script.

set pagesize 0

set escape on

spool users.sql

select ‘create user ‘ || U.username || ‘ identified ‘ ||

DECODE(password,

      NULL, ‘EXTERNALLY’,

      ‘ by values ‘ || ”” || password || ””

      )

|| chr(10) ||

‘default tablespace ‘ || default_tablespace || chr(10) ||

‘temporary tablespace ‘ || temporary_Tablespace || chr(10) ||

‘ profile ‘ || profile || chr(10) ||

‘quota ‘ ||

decode ( Q.max_bytes, -1, ‘UNLIMITED’, NULL, ‘UNLIMITED’, Q.max_bytes) ||

‘ on ‘ || default_tablespace ||

decode (account_status,’LOCKED’, ‘ account lock’,

                                                ‘EXPIRED’, ‘ password expire’,

                                                ‘EXPIRED \& LOCKED’, ‘ account lock password expire’,

                                                null)

||

‘;’

from dba_users U, dba_ts_quotas Q

— Comment this clause out to include system & default users

where U.username not in (‘SYS’,’SYSTEM’,

‘SCOTT’,’DBSNMP’,’OUTLN’,’WKPROXY’,’WMSYS’,’ORDSYS’,’ORDPLUGINS’,’MDSYS’,

‘CTXSYS’,’XDB’,’ANONYMOUS’,’OWNER’,’WKSYS’,’ODM_MTR’,’ODM’,’OLAPSYS’,

‘HR’,’OE’,’PM’,’SH’,’QS_ADM’,’QS’,’QS_WS’,’QS_ES’,’QS_OS’,’QS_CBADM’,

‘QS_CB’,’QS_CS’,’PERFSTAT’)

and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)

;

set pagesize 100

set escape off

spool off

 

Now run this  tablespace.sql  and users.sql  to create tablespace and users  in 10g.
[Note : Actually we need not create  user and tablespace while importing DB in 10g]
3)Make sure that 10g DB is in Non-Archive log mode.

 imp FILE=exp_20092011.dmp LOG=imp_20092011.log FULL=Y GRANTS=Y BUFFER=4096 ROWS=Y

  • Once import is done please follow the below steps:

 1)Once import is done please Check import logs for errors.
 
2)Make sure users are pointing to proper tablespaces.

 select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;
 
3)Please check the total object count and respective schema object count and confirm with original.

 select OBJECT_TYPE,count(*)from dba_objects where owner=’CROSSLNK’ group by OBJECT_TYPE;

 4)Please check the roles and privilages for respective user and do the required changes for same if required..

 select

  lpad(‘ ‘, 2*level) || granted_role “User, his roles and privileges”

from

  (

  /* THE USERS */

    select

      null     grantee,

      username granted_role

    from

      dba_users

    where

      username like upper(‘%&enter_username%’)

  /* THE ROLES TO ROLES RELATIONS */

  union

    select

      grantee,

      granted_role

    from

      dba_role_privs

  /* THE ROLES TO PRIVILEGE RELATIONS */

  union

    select

      grantee,

      privilege

    from

      dba_sys_privs

  )

start with grantee is null

connect by grantee = prior granted_role;

 
 
5)Please check the invalid object count.Recompile for invalid objects (run utrp.sql)

   select count(*) from dba_objects where STATUS=’INVALID’;
 
6)Gather statistics for entire database
 
 DATABASE LEVEL

begin

 dbms_stats.gather_database_stats(

options=> ‘GATHER AUTO’);

 end;

7) Put the 10g DB back into ARCHIVELOG mode if required.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  272629760 bytes

Fixed Size                   788472 bytes

Variable Size             103806984 bytes

Database Buffers          167772160 bytes

Redo Buffers                 262144 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

8) Verify the backup is configured properly.

9)Monitor the alert log and dump directories for possible issues.

I Hope this article helped to you. I am expecting your suggestions/feedback.
It will help to motivate me to write more articles….!!!!

Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for suceess, always fight even knowing your defeat is certain….!!!!”

Advertisements

Read Full Post »

Dear Friends,

          Today we face new issue relate to listener, so wanted to share with you same. We imported the dump into fresh blank copy of tha database and just chages IP of th server so that application configaration will not impacted. But when we swap the IP of both server we get the error while starting the listener.

Problem:

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 21-SEP-2011 12:54:12

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> start
Starting /u01/app/oracle/oracle/product/10.2.0/db_1//bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 10.2.0.2.0 – Production
System parameter file is /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12555: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00525: Insufficient privilege for operation
Linux Error: 1: Operation not permitted

Listener failed to start. See the error message(s) above…

LSNRCTL>

Cause:

1) Ensure that /tmp/.oracle or /var/tmp/.oracle directory exists.

2) Confirm that the DBA user who is trying to start the listener has adequate read and write permissions on the directory specified above. The permissions should be 777.

3) If the /tmp directory has reached full capacity, this would cause the listener to fail to write the socket files.

Solution

To implement the solution, please use the following example:

1. cd /var/tmp

2. Check the whether the .oracle directory exists:

cd .oracle

3. If the directory does not exist, request the System Administrator create the directory and set the ownership as root:root with the permissions set to 01777

mkdir /var/tmp/.oracle
chmod 01777 /var/tmp/.oracle
chown root /var/tmp/.oracle
chgrp root /var/tmp/.oracle

4. Next try starting the TNS Listener using the ‘lsnrctl start <listener_name>’ command.

I Hope this article helped to you. I am expecting your suggestions/feedback.
It will help to motivate me to write more articles….!!!!

Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for suceess, always fight even knowing your defeat is certain….!!!!”

Read Full Post »