Solved

export full of a database.

Posted on 2011-09-09
19
581 Views
Last Modified: 2012-05-12
Hi,

  I need to do a full export of my database.      I am planning to first kill all the current sessions, put the database in restricted mode and do the full export.

  a) How can i identify and kill all the users sessions ?

  b) How can i check which users has the privelige to connet even when the database is in restricted mode ?

 c) What rollback segments problems i could have ?

 d) Any tips or consideration for doing an export to a 1Tb database ?

 e) I will use the expdp for the export.  

 f) How much time could the export take ?
 
 g) How big the size of the export file could be ?

Regards,



0
Comment
Question by:joe_echavarria
  • 8
  • 7
  • 3
  • +1
19 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36512213
>> I am planning to first kill all the current sessions, put the database in restricted mode and do the full export.

I would not kill sessions.  This can result in lost data.

Best to do a shutdown immediate.

a) select sid and serial# from v$session then issue alter system kill session.

b) The docs:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/start001.htm#ADMIN11146

Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrators should have the RESTRICTED SESSION system privilege.

c) none really since you are restricted.

d) Why are you wanting to use Export?

f) No way for us to answer this.  A lot depends on your system.

g)  The docs:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_export.htm#SUTIL885

expdp has an ESTIMATE flag.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36512730
Consider:-
Mainly import takes more time than export.
You can also use Parallel parameter to speedup the process.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 36513351
The purpose of the export is for importing it in another server.

I will put the database in restricted mode without restarting it..

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36513370
export does a read-consistent export.  Not seeing the reason for the shutdown.

Do you not have the redo to do on a live system?
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36514081
Adding more to Slightwv comments:-
You can use FLASHBACK_SCN and FLASHBACK_TIME to take export in consistent state.
Therefore you dont need to kill users.
I believe this consumes more rollback and temp segments to maintain consistent image, but not sure.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36514110
The old exp had a consistent option.  Are you saying you need to do something more involved than that with expdp?
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 36514233
No i will not shutdown.  I will only put the database in restricted mode and kill current users.
I think that will work.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36514280
I believe you need to shutdown to restart in restricted mode.

I'm still advising to not kill sessions.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 36514291
I am having a problem with the expdp.   It does not work on the directory i want, but if i use the old normal exp utility it works.

Here is the information :

Y:\MFG01PRD>expdp system/oradba@mfg01prd.domain.corp directory=EXPORT  dumpfile=
mfg01prd_01.dmp logfile=mfg01prd_log_01.log full=y;

Export: Release 10.2.0.4.0 - 64bit Production on Friday, 09 September, 2011 17:4
5:11

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

Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation


SQL> col privilege format a15;
SQL> col directory_name format a15;
SQL> col grantee format a15;
SQL> col grant format a15;
SQL> select privilege,directory_name,grantee,grantor from all_tab_privs t, all_d
irectories d where t.table_name(+)=d.directory_name
  2  and d.directory_name='EXPORT' order by 2,1;

PRIVILEGE       DIRECTORY_NAME  GRANTEE         GRANTOR
--------------- --------------- --------------- ------------------------------
READ            EXPORT          SYSTEM          SYS
WRITE           EXPORT          SYSTEM          SYS

SQL> list
  1* select * from all_directories where directory_name='EXPORT'
SQL> /

OWNER           DIRECTORY_NAME  DIRECTORY_PATH
--------------- --------------- ---------------
SYS             EXPORT          Y:\MFG01PRD
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36514319
Am I safe to assume Y:\MFG01PRD exists and Oracle has write access to it?

Oracle has had issues in the past with Network Folders.  For grins try a local disk.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 36514406
If the normal exp utitly works on Y:\MFG01PRD  Oracle has write access to it  i guess.

And because i do not know how big this 1 TB database export file could be i have this Share drive mapped in the server.  And have available 7 TB for me.

I think it should work.

0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 36514415
You are right, i changed the path the directory and the expdp works....

How can i solve this oracle problem with networks folders ?
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 36514451
How big an export of a 1TB could be ?

I have 1 TB in a local disk and i think it will be enough, what u think ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36514458
>>If the normal exp utitly works on

I'm thinking the old version actually executes as the user running the exp.exe program.

expdp runs as the Oracle System user.

>>And because i do not know how big this 1 TB database export file could be
>>How big an export of a 1TB could be ?

Did you run an estimate?

>>How can i solve this oracle problem with networks folders ?

Like I mentioned above, the oracle.exe does something it is different than than when a logged in user does something.  Going from memory here but oracle runs as a service.  This is the issue.

I thought they had fixed most of the problems in recent versions.  Maybe 10.2.0.4 is still having issues.

There is a lot of information out there on Oracle and Networked mappings.

I have limited Internet access right now and cannot research it fully for you.


0
 
LVL 34

Expert Comment

by:johnsone
ID: 36522837
Just to address one point that was brought up.

You do not need to bounce the database to get it into restricted mode.  All you need is an alter system:

ALTER SYSTEM ENABLE RESTRICTED SESSION;

It doesn't affect already connected session, which is the reason you need to kill all the existing sessions after running the ALTER SYSTEM.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 36522872
Which will be the option to speed up a full export using the expdp tool ?

It took me 21 hours to finish..., is there an option or a way to speed it up ?
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36524047
You can go for TTS instead.
For more info read the below link
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tspaces013.htm
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 36525261
Is the transportable tablespaces available only for the Enterprise Edition of Oracle ?

My current version is and i trying to
SQL> show user
USER is "SYS"
SQL> select * from sys.transport_set_violations;
select * from sys.transport_set_violations                  *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 64-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL>
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36529445
>>You do not need to bounce the database to get it into restricted mode

Thanks for the clarification!

>>is there an option or a way to speed it up ?

What is the throughput of your disks?  Get an estimate my copying know sized files between the disks to get an idea of Megs per minute.

If you get a time of around 21 hours for the size of your export,  you are being limited by your disks.  The only way to speed it up is get faster disks or reconfigure them for better performance.

If the disks are fast enough, maybe you are CPU or memory bound during the export.

You might also look into Parallel Export.

Figure out where the slowdown is and work from there.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now