Mani's Blog

May 31, 2012

Migrate Oracle RAC database into Single instance

Filed under: Uncategorized — mani @ 10:24 pm

Objective

We had a requirement to Migrate RAC database into Non-RAC single instance.

Assumptions

The RAC Cluster has 2 nodes called xnodbcontr1 and 2, instance names are XNODBCNT1,and XNODBCNT2 and the DB service name is XNODDBCNT. The goal is to remove the 2nd instance and convert the DB into Non-Cluster DB.

Steps involved

Following steps are followed to convert the RAC DB to Non-RAC.

  1. Stop the DB server and remove instance from cluster
    [oracle@xnodbcontr1 ~]$ srvctl status service -d XNODBCNT
    Service xno_contact is running on instance(s) XNODBCNT1, XNODBCNT2
    [oracle@xnodbcontr1 ~]$ srvctl stop database -d XNODBCNT
    }
    # Remove the instance
    {
    [oracle@xnodbcontr1 ~]$ srvctl remove instance -d XNODBCNT -i XNODBCNT2
    Remove instance XNODBCNT2 from the database XNODBCNT? (y/[n]) y
    You have new mail in /var/spool/mail/oracle
    [oracle@xnodbcontr1 ~]$ srvctl remove instance -d XNODBCNT -i XNODBCNT1
    Remove instance XNODBCNT1 from the database XNODBCNT? (y/[n]) y
    PRKP-1075 : Instance XNODBCNT1 is the last preferred instance for service xno_contact.
  2. Reset the cluster database value
    [oracle@xnodbcontr1 ~]$ dba
    SQL*Plus: Release 11.1.0.7.0 - Production on Thu May 31 16:57:15 2012
    Copyright (c) 1982, 2008, Oracle. All rights reserved.
    Connected to an idle instance.
    SQL> startup
    ORACLE instance started.
    Total System Global Area 5.9861E+10 bytes
    Fixed Size 2165248 bytes
    Variable Size 2.5233E+10 bytes
    Database Buffers 3.4360E+10 bytes
    Redo Buffers 266256384 bytes
    Database mounted.
    Database opened.
    SQL> alter system set cluster_database=false scope=spfile;
    System altered.
    SQL> alter system set cluster_database_instances=1 scope=spfile;
    System altered.
    SQL> alter database disable thread 2;
    Database altered.
  3. Drop unnecessary log files
    SQL> select thread#, group# from v$log;
       THREAD# GROUP#
    ---------- ----------
    1 11
    1 12
    1 13
    1 14
    1 15
    2 21
    2 22
    2 23
    2 24
    2 25
    10 rows selected.
    SQL> alter database drop logfile group 21;
    Database altered.
    SQL> alter database drop logfile group 22;
    Database altered.
    SQL> alter database drop logfile group 23;
    Database altered.
    SQL> alter database drop logfile group 24;
    Database altered.
    SQL> alter database drop logfile group 25;
    Database altered.
    SQL>
  4. Remove unnecessary UNDO tablespace.
    SQL> drop tablespace UNDOTBS2 including contents and datafiles;
    Tablespace dropped.
  5. Create pfile and remove parameters refer to 2nd instance.
    SQL> create pfile from spfile;
    File created.
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL>
    ## Removed the reference to instance2 from pfile.
        
    SQL> create spfile from pfile;
    File created.
  6. Start up the database with new spfile.
    SQL> startup
    ORACLE instance started.
    Total System Global Area 5.9861E+10 bytes
    Fixed Size 2165248 bytes
    Variable Size 2.7917E+10 bytes
    Database Buffers 3.1675E+10 bytes
    Redo Buffers 266256384 bytes
    Database mounted.
    Database opened.
  7. Verified the transaction is flowing in.
    SQL> select max(id) from xn_contacts.profile2;
       MAX(ID)
    ----------
     180701340
    SQL> /
       MAX(ID)
    ----------
     180701413
    SQL>

May 24, 2012

Oracle installation failed on shmsys:shminfo_shmmax

Filed under: Database,Oracle — mani @ 8:26 pm
Tags:

During Oracle 10g installation on Solaris box,  I ran into following issue.

Performing check for Kernel
Checking kernel parameters
Checking for BIT_SIZE=64; found BIT_SIZE=64.    Passed
Checking for shmsys:shminfo_shmmax=4294967295; found no entry.  Failed <<<<
Checking for shmsys:shminfo_shmmni=100; found no entry. Failed <<<<
Checking for semsys:seminfo_semmni=100; found no entry. Failed <<<<
Checking for semsys:seminfo_semmsl=256; found no entry. Failed <<<<
Check complete. The overall result of this check is: Failed <<<<

Solution:

I had to increase shmmax to big value using prctl.  You will have to run as root privileged user.

prctl -n project.max-shm-memory -v 35gb -r -i project user.oracle

May 22, 2012

How to kill interrupted Oracle impdp/expdp job.

Filed under: Database,Oracle — mani @ 6:18 pm
Tags: , , ,

How to kill interrupted impdp/expdp job.

I have kicked in impdp then pressed ctrl+c and exit due to some errors.  But the job will never die, it needs to be cleared properly in order to proceed further. Here is the step I followed to kill the job.
Select owner and job_name:

SQL> select *from dba_datapump_jobs;

OWNER_NAME               JOB_NAME
—————————— ——————————
OPERATION
——————————————————————————–
JOB_MODE
——————————————————————————–
STATE                   DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
—————————— ———- —————– —————–
SYSTEM                   SYS_IMPORT_FULL_01
IMPORT
FULL
EXECUTING                1          0            2

Killing the Job:

[oracle@xnodb911 ~]$ impdp system attach=system.SYS_IMPORT_FULL_01

Import: Release 11.1.0.7.0 – 64bit Production on Tuesday, 22 May, 2012 18:11:25

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_IMPORT_FULL_01
Owner: SYSTEM
Operation: IMPORT
Creator Privs: TRUE
GUID: C0A4D32CF738BCCBE040100A54626673
Start Time: Tuesday, 22 May, 2012 18:02:44
Mode: FULL
Instance: PRETZEL
Max Parallelism: 1
EXPORT Job Parameters:
CLIENT_COMMAND        system/******** dumpfile=xnodb023.dmp directory=dmpdir logfile=xnodb023.log SCHEMAS=XN_SEARCH,SCRAWLER,ORAMONKEY,XN_EXPIMP,S_2811027_ACLU23,S_2797846_PRETZE,XN_CORE,XN_DBA version=10.2
IMPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        system/******** dumpfile=xnodb023.dmp directory=dmpdir logfile=xnodb023_imp.log full=y
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /local/data/oracle/nfs/xnodb023.dmp

Worker 1 Status:
Process Name: DW01
State: EXECUTING
Object Schema: S_2797846_PRETZE
Object Name: SHAPE_MAINUSER_132
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 38
Completed Rows: 7,504,787
Completed Bytes: 8,638,320,560
Percent Done: 100
Worker Parallelism: 1

Import> kill_job
Are you sure you wish to stop this job ([yes]/no): y
Are you sure you wish to stop this job ([yes]/no): yes

[oracle@xnodb911 ~]$

Blog at WordPress.com.