Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

export full of a database.

Posted on 2011-09-09
19
Medium Priority
?
619 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 78

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 78

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 78

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 78

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
 
LVL 78

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 78

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 35

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

580 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