Monday, April 30, 2012

deinstall oracle cloud control 12c

This document is for installation cleanup of oracle cloud control 12c without using installed oracle home.

1. drop user from repository database.
  select 'drop user ' || username || ' cascade ;' from dba_users where username in  ('SYSMAN', 'SYSMAN_MDS','MGMT_VIEW','SYSMAN_BIP','SYSMAN_APM','BIP','SYSMAN_OPSS','SYSMAN_RO')
2. drop synonym from repository database
  select 'drop public synonym ' || synonym_name ||  ';' from dba_synonyms where owner='PUBLIC' and table_owner in ('SYSMAN', 'SYSMAN_MDS','MGMT_VIEW','SYSMAN_BIP','SYSMAN_APM','BIP','SYSMAN_OPSS','SYSMAN_RO')

 select 'drop ' || owner || '.' || synonym_name || ';' from dba_synonyms where table_owner in ('SYSMAN', 'SYSMAN_MDS','MGMT_VIEW','SYSMAN_BIP','SYSMAN_APM','BIP','SYSMAN_OPSS','SYSMAN_RO')

3. delete registry from system.schema_version_registry

SQL> select comp_name from SCHEMA_VERSION_REGISTRY;

COMP_NAME
--------------------------------------------------------------------------------
Authorization Policy Manager
Metadata Services
Oracle Platform Security Service


SQL> delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Authorization Policy Manager';
SQL> delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Oracle Platform Security Services';
SQL> delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Metadata Services';

SQL> commit;

4. deinstall EM12c software throgh original installation software.
  ./runInstaller -deinstall
  select software home and click remove.
  sequence:  a. plugin homes
                    b. agent12c
                    c. sbin
                    d. jdk1
                    e.oms12c
                    f. common

5. clean up any process of EM12c

6. rm -rf $OEM_HOME

7. check if repository was cleaned up
  SQL> select username,account_status from dba_users where username in ('SYSMAN', 'SYSMAN_MDS','MGMT_VIEW','SYSMAN_BIP','SYSMAN_APM','BIP','SYSMAN_OPSS','SYSMAN_RO') ;
SQL> select owner,synonym_name from dba_synonyms where table_owner in ('SYSMAN', 'SYSMAN_MDS','MGMT_VIEW','SYSMAN_BIP','SYSMAN_APM','BIP','SYSMAN_OPSS','SYSMAN_RO') ;
SQL> select tablespace_name from dba_tablespaces where tablespace_name like 'MGMT%';
SQL> select comp_name from SCHEMA_VERSION_REGISTRY;

above four query should retrun 0 row.



reference:
metalink  EM 12c Cloud Control Re-Installation Fails at Repository Configuration With Error " RCU-6016:The specified prefix already exists" [ID 1365834.1]
metalink: 12c Cloud Control Repository: RepManager Dropall Command Fails To Drop all the Repository Objects [ID 1365820.1]
http://xiaoruanjian.iteye.com/blog/1365905
http://docs.oracle.com/cd/E24628_01/install.121/e24089/appdx_troubleshooting.htm
https://forums.oracle.com/forums/thread.jspa?threadID=1068831&tstart=45

s3cmd repo

# 
# Save this file to /etc/yum.repos.d on your system
# and run "yum install s3cmd"
# 
[s3tools]
name=Tools for managing Amazon S3 - Simple Storage Service (RHEL_5)
type=rpm-md
baseurl=http://s3tools.org/repo/RHEL_5/
gpgcheck=1
gpgkey=http://s3tools.org/repo/RHEL_5/repodata/repomd.xml.key
enabled=1
wget http://s3tools.org/repo/RHEL_5/s3tools.repo

Wednesday, April 25, 2012

yum repo

for oracle linux 5.5

vi /etc/yum.repo.d/public-yum-el5.repo

[el5_ga_base]
name=Enterprise Linux $releasever GA - $basearch - base
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/0/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
[el5_u1_base]
name=Enterprise Linux $releasever U1 - $basearch - base
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/1/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
[el5_u2_base]
name=Enterprise Linux $releasever U2 - $basearch - base
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/2/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
[el5_u3_base]
name=Enterprise Linux $releasever U3 - $basearch - base
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/3/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
[el5_u4_base]
name=Enterprise Linux $releasever U4 - $basearch - base
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/4/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
[el5_u5_base]
name=Enterprise Linux $releasever U5 - $basearch - base
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/5/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
[ol5_u5_base]
name=Oracle Linux $releasever - U5 - x86_64 - base
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/5/base/x86_64/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
[ol5_u6_base]
name=Oracle Linux $releasever - U6 - $basearch - base
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/6/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
[ol5_u7_base]
name=Oracle Linux $releasever - U7 - $basearch - base
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/7/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
[el5_addons]
name=Enterprise Linux $releasever - $basearch - addons
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/addons/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
[el5_oracle_addons]
name=Enterprise Linux $releasever - $basearch - oracle_addons
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/oracle_addons/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1

Tuesday, April 17, 2012

ORA-904 "MAXSIZE" When Exporting From 10g Database Using 11g Export Client.(exp)

if you use 11g client to export 10g schema, you willget this error.

check metalink document ID 957380.1

Cause

When export any objects export client run a query agains dictionary view SYS.EXU9STOU to gather storage parameters about object segments.

Since release 11g this view has been updated by a new column MAXSIZE. When using the release 11g export client the utility expect the MAXSIZE column SYS.EXU9STOU of source database.

Given the fact that this column is unavailable in 10g the ORA-904 is reported.

solution: user data pump

error:
exp test/test@orcl file=test.dmp log=test.log
getting errors
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier

reference: http://halimju.wordpress.com/2010/07/08/exp-00008-oracle-error-904-encountered-ora-00904-maxsize-invalid-identifier-ora-01003-no-statement-parsed/

Friday, April 13, 2012

reclaim space from nonsystem tablespace

1. create a new tablespace
create tablespace move_users datafile '/u01/datafile/move_users_01.dbf' size 10m autoextend on maxsize 30G extent management local segment space management auto;

2. grant quota on move_users
select 'alter user ' || owner || ' quota unlimited on move_users;'
from dba_segments
where tablespace_name='TBS_NAME'
and segment_type in ('TABLE', 'INDEX')
group by owner
;

run the output.

3. move segments out of table space TBS_NAME
spool MOVE_SEG.sql
select 'alter table '||owner||'."'||table_name||
'" move lob('||column_name||') store as (tablespace move_users);'
from dba_lobs
where tablespace_name = 'TBS_NAME'
;

select 'alter ' || segment_type || ' ' || owner ||
'."' || segment_name || '" move nologging tablespace move_users;'
from dba_segments
where tablespace_name = 'TBS_NAME'
and segment_type in ('TABLE')
;

select 'alter ' || segment_type || ' ' || owner ||
'."' || segment_name || '" rebuild nologging tablespace move_users;'
from dba_segments
where tablespace_name = 'TBS_NAME'
and segment_type in ('INDEX')
;
spool off

run @MOVE_SEG.sql

4. check if TBS_NAME is empty
select segment_type, owner , segment_name
from dba_segments
where tablespace_name = 'TBS_NAME'
;

5. resize datafile of TBS_NAME
select 'alter database datafile ' || '''' || file_name || '''' || ' resize 10m ;'
from dba_data_files where tablespace_name = 'TBS_NAME';

run output.

6. move segment back to TBS_NAME
spool MOVE_BACK_SEG.sql
select 'alter table '||owner||'."'||table_name||
'" move lob('||column_name||') store as (tablespace TBS_NAME);'
from dba_lobs
where tablespace_name = 'MOVE_USERS'
;

select 'alter ' || segment_type || ' ' || owner ||
'."' || segment_name || '" move nologging tablespace TBS_NAME;'
from dba_segments
where tablespace_name = 'MOVE_USERS'
and segment_type in ('TABLE')
;

select 'alter ' || segment_type || ' ' || owner ||
'."' || segment_name || '" rebuild nologging tablespace TBS_NAME;'
from dba_segments
where tablespace_name = 'MOVE_USERS'
and segment_type in ('INDEX')
;
spool off

run @MOVE_BACK_SEG.sql

7. check if MOVE_USERS is empty
select segment_type, owner , segment_name
from dba_segments
where tablespace_name = 'MOVE_USERS'
;

8. resize MOVE_USERS
select 'alter database datafile ' || '''' || file_name || '''' || ' resize 10m ;'
from dba_data_files where tablespace_name = 'MOVE_USERS';

run output

9. drop MOVE_USERS
drop tablespace MOVE_USERS including contents and datafiles;

Monday, April 9, 2012

file and directory permission in linux

for file:

it has set uid, set gid and sticky bit
set uid: command will run as the owner
set gid: command will run ans the owner's group
stickey bit: keep the program in memory

for directory:

it has set gid and seticky bit
set gid: all file under the directory have the same gid as the directory
sticke bit: user can delete and modify files that he own or have write prifilege.


example
for /tmp
chmod 1777 /tmp


referecne: http://www.comptechdoc.org/os/linux/usersguide/linux_ugfilesp.html'

TNS-12547: TNS:lost contact and Linux Error: 32: Broken pipe

Problem: client connection failed if use service_name in TNSNAMES.ORA and succeeds if use SID.

environment: Oracle runs against os user oracle and asm runs against os user grid. Lisner runs against grid.


error in listener log:

TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe


cause: @ORCLE_HOME/bin/oracle permission was set incorectly.

solution: chmod 6751 @ORCLE_HOME/bin/oracle

analyzation: when connect with sid, the listener use shared server method, wiich not require listener to start a new process trhough @ORCLE_HOME/bin/oracle . when connect with service name, the listener user dedicated server method, which require os user grid to start an OS user oralce process through @ORCLE_HOME/bin/oracle. since sticky bit was not set up for group permission of @ORCLE_HOME/bin/oracle, listerner can not start up dedicated server process for new connection, which give the linux error: broken pipe.


reference: ORA-12546 or ORA-12537 While Connecting To Database Via Listener [ID 1050756.6]