Oracle 10g, 11g: Export and Import schema statistics

Easy way to export and import schema statistics – in case the optimizer is behaving incorrectly and you want to re-import old statistics when the optimizer behaved properly.

SCHEMA NAME – POWERCARD

Create the table to store the statistics-
EXEC DBMS_STATS.CREATE_STAT_TABLE('POWERCARD', 'STATS_TABLE');

Export schema stats – will be stored in the 'STATS_TABLE'
EXEC DBMS_STATS.export_schema_stats('POWERCARD','STATS_TABLE',NULL,'POWERCARD');

If required import these statistics back to POWERCARD schema.
EXEC DBMS_STATS.import_schema_stats('POWERCARD','STATS_TABLE',NULL,'POWERCARD');

Finally drop the table created to backup the schema stats
EXEC DBMS_STATS.drop_stat_table('POWERCARD','STATS_TABLE');

ORA-39242: Unable to export/import TABLE_DATA: … due to table attributes.

ORA-39242: Unable to export/import TABLE_DATA: … due to table attributes.

The root cause to the problem was there was some index in the unusable state.

To find the unusable index ran the following query:

select owner, index_name from dba_indexes where status=’UNUSABLE';

To resolve the issue rebuild the index by the following query:

ALTER INDEX OWNER.INDEX_NAME REBUILD ONLINE nologging parallel 8;

And after that while trying to run the import the import run without any error.

Oracle: ORA-39001: invalid argument value ORA-39071: Value for EXCLUDE is badly formed. ORA-00936: missing expression

While import/export in Unix shell:

impdp system/manager dumpfile=powercard_schema_structure_25052015.dmp directory=DUMPS logfile=imp_powercard_schema_structure_25052015_2.log table_exists_action=truncate  EXCLUDE=TABLE:"IN ('AUTHORIZATION_ALERTS', 'XML_MSG_XCHANGE_TRACE_HIST' , 'PCARD_TRACES' , 'AUTHORIZATION_ALERTS_TEMP','ATM_COMMANDS_DF','CARD_ACTIVITY','TRANSACTION_HIST_ADD_CHIP','VSS_MEMBER_SET_46_V4','TRANSACTION_HIST_ADD_FRAUD')"

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression

Solution: Use escape character  \ before special character like: '  "  (  )

impdp system/xxxx dumpfile=schema_structure_25052015.dmp directory=DUMPS logfile=imp_powercard_schema_structure_25052015_2.log table_exists_action=truncate  EXCLUDE=TABLE:\"IN \(\'AUTHORIZATION_ALERTS\', \'XML_MSG_XCHANGE_TRACE_HIST\' , \'PCARD_TRACES\' , \'AUTHORIZATION_ALERTS_TEMP\',\'ATM_COMMANDS_DF\',\'CARD_ACTIVITY\',\'TRANSACTION_HIST_ADD_CHIP\',\'VSS_MEMBER_SET_46_V4\',\'TRANSACTION_HIST_ADD_FRAUD\'\)\"

11g: Kill RAC session from SQLPLUs

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate;' from gv$session where username = 'SCOTT';

Finding all files in directory containing a text string on Linux

grep -rnw 'directory' -e "pattern"

error while loading shared libraries: libodm11.so: cannot open shared object file: No such file or directory ERROR: ORA-12547: TNS:lost contact

Enter user-name:
oracleSIT: error while loading shared libraries: libodm11.so: cannot open shared object file: No such file or directory
ERROR:
ORA-12547: TNS:lost contact

Solution:

$ cd $ORACLE_HOME/bin/ 

$ relink all

TNS-01189: The listener could not authenticate the user

Error looks like its related to listener, but it happened with us as a result of number of sessions exceeded.

Enter user-name: / as sysdba
ERROR:
ORA-00020: maximum number of processes (300) exceeded

SQL Server DR Snapshot vs. LogShipping vs. Mirroring vs. Replication vs. Failover Clustering

1) Snapshot is a static read only picture of database at a given point of time. Snapshot is implemented by copying a Page (8KB for SQL SERVER) at a time. For e.g. assume you have a table in your DB, & you want to take a snapshot of it. You specify the physical coordinates for storing snapshot & when ever original table changes the affected rows are pushed first to the the snapshot & then changes happen to the DB. (N.B. There is also something called as Snapshot Isolation Level which is different from Database Snapshot).

Usage Scenario: You have a separate DB for report generation, and want to ensure that latest data for that is available. You can periodically take snapshot of your transactional database.

2) Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs.

Usage Scenario: You can cope up with a longer down time. You have limited investments in terms of shared storage, switches, etc.

3) Mirroring which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs (actually logs are continuously merged in this scenario – no wonder it’s called Mirror :) ). Additional advantages of Mirroring include support at .NET Framework level (read no switching/routing code – requires ADO.NET 2.0 & higher) plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

Usage Scenario: You want very less down time and also a cost effective solution in terms of shared storage, switches, etc. Also you are targeting a single database which easily fits in your disks.

4) Replication is used mainly when data centers are distributed geographically. It is used to replicate data from local servers to the main server in the central data center. Important thing to note here is, there are no standby servers. The publisher & subscriber both are active.

Usage Scenario: A typical scenario involves syncing local / regional lookup servers for better performance with the main server in data center periodically, or sync with a remote site for disaster recovery.

5) Failover Clustering is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS. Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN. Advantages of a SAN storage is large efficient hot pluggable disk storage. You might see DR options like Mirroring used quite frequently with failover clustering. Here’s a good article on adding geo redundancy to a failover cluster setup.

You might want to look at the licensing options of SQL Server, various editions available and how they map to above features. You can find this information in detail here.

Hope that helps to some extent :) .

Source: Niraj Bhatt

ORA-39002: invalid operation ORA-39059: dump file set is incomplete ORA-39246: cannot locate master table within provided dump files

impdp - corrupt dump file
ORA-39246: cannot locate master table within provided dump files
ORA-39059: dump file set impdp - corrupt dump file
ORA-39246: cannot locate master table within provided dump files
ORA-39059: dump file set is incomplete

solution: Re-run expdp and copy to take valid dump files.is incomplete

solution: Re-run expdp and copy to take valid dump files.

ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim bytes disk space from limit

We need to increase the size of FRA (Flash Recovery Area) even if the space is not available on the disk:

ALTER SYSTEM SET db_recovery_file_dest_size=200G SCOPE=BOTH ;