3.EXPORT WHOLE DB How does one use the import/export utilities? exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no exp scott/tiger file=emp.dmp tables=(emp,dept) Can one export a subset of a table? exp scott/tiger tables=emp query="where deptno=10" exp scott/tiger file=abc.dmp tables=abc query=\"where sex=\'f\'\" rows=yes Can one monitor how fast a table is imported? select substr(sql_text,instr(sql_text,'INTO "'),30) table_name, rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes, trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min from sys.v_$sqlarea where sql_text like 'INSERT %INTO "%' and command_type = 2 and open_versions > 0; Can one export to multiple files? exp SCOTT/TIGER FILE=D:F1.dmp,E:F2.dmp FILESIZE=10m LOG=scott.log
In case of low-performance system, it is better to add RECORDLENGTH parameter with tiny value to ensure that gzip has enough time to extract data before imp reads it: imp system/pwd@sid RECORDLENGTH=4096 file=imp_pipe log=imp_pipe.log
I got an email from users saying they are unable to connect to one of the DEVELOPMENT server. They are getting “NO LISTENER” message. So, its clear from this that listener could have been shutdown.
I logged in and checked the listener status using both “lsnrctl status” command and “ps -ef | grep tns” command. Both of the commands didn’t given any posivitive result.
So I started the listener with the below command and got error as this…
[oracle@dbserver1 admin]$ lsnrctl start LISTENER
LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 25-APR-2012 18:16:24
Copyright (c) 1991, 2005, Oracle. All rights reserved.
To check the users using which tablespace in the database;
select username,default_tablespace from dba_users ;
TO list all the users and the tablespace being used in database
set pages 999 lines 100
col username format a20
col status format a8
col tablespace format a20
col temp_ts format a20
, account_status status
, default_tablespace tablespace
, temporary_tablespace temp_ts
order by username
SQL> alter database open; alter database open * ERROR at line 1: ORA-16038: log 1 sequence# 230 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 1 thread 1: '/opt/oracle/db/home/data/imapdb/redo01.log'
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4G SCOPE=BOTH;
1. SQL> alter database backup controlfile to trace;
3. Database altered.
4. SQL> show parameter user_dump_dest
6. NAME TYPE VALUE
7. ------------------------------------ ----------- ------------------------------
8. user_dump_dest string /u01/app/oracle/admin/rev1/udump
12. SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE
13. 2 AS '/home/oracle/Desktop/create_ctl.sql';
15. Database altered.
ORA-01102: cannot mount database in EXCLUSIVE mode
Got Error when try open database :
ORACLE instance started.
Total System Global Area 267227136 bytes
Fixed Size 2212496 bytes
Variable Size 205524336 bytes
Database Buffers 54525952 bytes
Redo Buffers 4964352 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
sculkget: lock held by PID: 12359
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 12359
ORA-1102 signalled during: ALTER DATABASE MOUNT...
On OS level :
Host name where the instance is running select host_name from v$instance; You can also obtain this information by running the following from bash:
hostname or uname –n Operating system platform select platform_name from v$database –-(10g)
Similar information is returned if you run uname –s
Control files select name from v$controlfile; Location of the database control files. The init.ora parameter control_files also contains this information. Datafiles
select file_name from Dba_data_files; Location of the database datafiles Temp files select file_name from Dba_temp_files; Location of database temporary files Log files select member from v$logfile; Location of redo logs Archived logs select name from v$archived_log; Location of archived redo logs. The init.ora parameters log_archive_dest_n also contain this information. This query will not return results if your database is not in Archivelog mode. Flash recovery area select name from v$recovery_file_dest; The directory being used in an Oracle 10g installation for the flash recovery area. The init.ora parameter db_recovery_file_dest also contains this information. Other points of access on the file system indicated by parameters select * from v$parameter where value like '%/%' or value like '%/%'; The results of this query can vary significantly, depending on your installation and version of Oracle Database. Parameters that may be returned include: spfile standby_archive_dest utl_file_dir background_dump_dest user_dump_dest core_dump_dest audit_file_dest dg_broker_config_file1 dg_broker_config_file2 Programmatic access to the file system select directory_path from dba_directories; The Oracle UTL_FILE_DIR parameter and DIRECTORY database objects can be used to access files that are not a part of standard database functioning. Process Information
Session Processes select p.spid, s.username, s.program from v$process p, v$session s where p.addr=s.paddr order by 2, 3, 1; The spid can be correlated with ps –ef results to compare the information available within the database with OS information for a given process. Processes related to parallelism select slave_name, status from v$PQ_SLAVE; Many aspects of Oracle Database, such as loading, querying, object creation, recovery, and replication, can take advantage of parallelism to speed up activities that can be broken down. The parameter parallel_threads_per_cpu sets an instance’s default degree of parallelism. Memory Information
Program Global Area select * from V$PGASTAT; The parameter pga_aggregate_target is used to configure memory for all dedicated server connections. Linux utilities, including vmstat and top, can be used to monitor memory usage. System Global Area select * from v$sga;
The parameters SGA_MAX_SIZE and SGA_TARGET are used to configure dynamic memory allocation features of Oracle Database 10 g. Other parameters can be used to manually allocate memory for particular purposes. Again, various Linux utilities are available to monitor memory allocation.
RMAN> recover tablespace TEST1,TEST2 until time "to_date('07/06/12 12:15:00','DD/MM/YY HH24:MI:SS')" auxiliary destination '/aux/dest/oracle/atp/resto';
recover tablespace our_data until time "to_date('2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS')" auxiliary destination ’/opt/oracle/temp’;
Once the above statement is executed, RMAN does the following for us:
• Creates auxiliary instance (including the pfile etc..) • Mounts the auxiliary instance • Makes the candidate tablespace into OFFLINE • Restores the basic tablespace UNDO, SYTEM,SYSAUX and then the required tablespace • Applies archives (completes recovery) • Opens the database • Performs an export of the objects residing in the tablespace • Shutdown aux instance • Import the objects into the target database • Remove aux instance and cleanup That's it....you now have all the objects back in the tablespace!
Had a service name “ORCL” ,When I tried connect to that service it always gave ORA-12505, TNS:listener does not currently know of SID given in connect …
So I edited the listener.ora, added one more SID_DESC, restarted the listener. Now I can connect to the “ORCL” database. Below is the updated listener.ora