Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to carry out oracle data pumping data practice. Many people may not know much about it. In order to let everyone know more, Xiaobian summarized the following contents for everyone. I hope you can gain something according to this article.

Requirements:

Migrate the user OCHIS in ocdbhis library above zxjfdb2 and zxjfdb4 to migudb2 library above migudb3 and migudb4.

Ocdbhis and migudb2 are rac databases.

Steps:

1. Check the environment:

1) The table space involved by the user, ensure that the table space of the target library contains the table space of the original library, and the capacity is sufficient:

select tablespace_name,sum(bytes/1024/1024/1024) gb from dba_segments where owner='&OCHIS' group by tablespace_name;

2) Temporary tablespaces, ensuring that the tablespaces on both sides are consistent.

SELECT a.tablespace_name "TABLESPACE_NAME", a.total "TOTAL(MB)", (a.total - nvl(b.used, 0)) "FREE(MB)" , nvl(b.used,0) "USED(MB)", round(nvl(b.used,0) * 100 / a.total, 3) "USED_PERCENT(%)" FROM (SELECT tablespace_name, SUM (bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM (bytes_cached)/1024/1024 used FROM v$temp_extent_pool GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name(+);

3) Check the role

select distinct GRANTED_ROLE from dba_role_privs where grantee in ('&OCHIS') order by 1;

4) Check the profile

select distinct PROFILE from dba_users where username in ('&OCHIS') order by 1;

5) Check tnsname.ora

cat $ORACLE_HOME/network/admin/tnsnames.ora

6) Check dblink

select * from DBA_DB_LINKS where OWNER in ('PUBLIC','&OCHIS');

7) Check the network connectivity between the source and destination, because you need to copy the dmp file to the peer using scp.

8) Check whether the file systems on the source and destination sides are large enough to accommodate all dump files.

9) Check whether the archive space on the target side is sufficient to avoid full archive during import.

2. Notify app colleagues to close apps related to OCHIS users

3. Lock OCHIS users to prevent changes in user data.

alter user OCHIS account lock;

4. Export data in source library

1) Creating a directory

chmod oracle:oinstall /oratemp

create directory ggdump as '/oratemp';

2) Writing a parfile

#####Export by user

zxjfdb2:/oratemp$cat expdp_ochis.par

userid='/ as sysdba'

directory=ggdump

dumpfile=expdp_ochis_%U.dmp

logfile=expdp_ochis.log

parallel=8 ----8 parallel

compression=all ---Compression

CLUSTER=N

exclude=statistics ----Cancel exporting statistics

SCHEMAS=(

OCHIS

)

#####Export metadata by user for reference

#userid='/ as sysdba'

#directory=ggdump

#dumpfile=expdp_ich_metadata.dmp

#logfile=expdp_ich_metadata.log

#CONTENT=METADATA_ONLY

#CLUSTER=N

#SCHEMAS=(

#PUBDBA,

#OCHIS

#)

#####Export pure data according to the table for reference

#userid='/ as sysdba'

#directory=ggdump

#dumpfile=expdp_ich_pubdba_data.dmp

#logfile=expdp_ich_pubdba_data.log

#CONTENT=DATA_ONLY

#CLUSTER=N

#TABLES=(

#PUBDBA.ACCNT_AGREEMENT

#PUBDBA.CARRIER_ACCNT_BANK_INFO

#)

3) Export and view logs

nohup expdp parfile=expdp_ochis.par > expdp_ochis.par.out &

tail -f expdp_ochis.par.out

5. copy data

scp expdp_*.dmp migudb3:/backup

6. Destination import data

1) Creating a directory

2) Writing a parfile

migudb3:/backup#cat impdp_ochis.par

userid='/ as sysdba'

directory=ggdump

dumpfile=expdp_ochis_%U.dmp

logfile=impdp_ochis.log

parallel=8

CLUSTER=N

3) Import and view logs

nohup impdp parfile=impdp_ich_metadata.par > impdp_ich_metadata.par.out &

tail -f impdp_ich_metadata.par.out

7. collect statistics

migudb3:/backup#cat gather_ochis_info.sh

sqlplus / as sysdba 10,method_opt=>'for all columns size 1',cascade=>true,force=>true,degree=>8,no_invalidate=>false);

spool off

exit

EOF

8. Target Library Unlock OCHIS User

alter user ochis account unlock;

9. Notify app colleagues to check apps.

After reading the above, do you have any further understanding of how to implement oracle data pumping practices? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.