RAM&KRISHNA BLOG

  SET   LINES   200 SET PAGES 999 COL LGWR FOR A5 COL THREAD FOR 99999 COL GROUP FOR 9999 COL STATUS FOR A8 COL ARCHIVED FOR A8 COL LOGFILE_TYPE FOR A12 COL MEMBER FOR A10 COL FILE_NAME FOR A50 COL MB FOR 9999 COL IS_RECOVERY_DEST_FILE FOR A22 SELECT DECODE ( LG . STATUS , 'CURRENT' , THREAD # || ' -->',NULL) AS LGWR, LG . THREAD # "THREAD", LG . GROUP # "GROUP", LF . TYPE LOGFILE_TYPE , LG . STATUS , LG . SEQUENCE # AS LOG_SEQUENCE, LG . ARCHIVED , LG . MEMBERS AS GROUP_MEMBER , ROUND ( LG . BYTES / 1024 / 1024 ) MB , LF . MEMBER FILE_NAME , LF . IS_RECOVERY_DEST_FILE FROM V$LOG LG , V$LOGFILE LF WHERE LG . GROUP #=LF.GROUP# ORDER BY LG . GROUP #;

    Differences Between Classic and Integrated Capture in Oracle GoldenGate Classic Capture: In Classic Capture mode, the Oracle GoldenGate Extract process captures data changes directly from Oracle redo or archive log files . In Oracle RAC environments, a separate ASM user must be created, and the necessary privileges must be granted to access the logs in ASM redo/archive. Alternatively, you can use the TRANLOGOPTIONS DBLOGREADER parameter in the Extract configuration for Classic Capture in a RAC setup. Performance : Classic Capture can be CPU-intensive and is generally slower when dealing with complex data types, such as LOBs (Large Objects). Multitenant Architecture : Not supported. RAC Support : Requires manual specification of threads based on the number of RAC nodes. TDE (Transparent Data Encryption) : More manual configuration is needed for TDE setups. Scalability : Less scalable in larger environments due to its single-thr

  How to Improve Oracle Data Pump Performance:- Use Parallelism : Set the PARALLEL parameter to at least 2 * number of CPUs . This allows multiple worker processes to perform tasks simultaneously, speeding up both export and import processes. Example: PARALLEL=4 for a system with 2 CPUs. Perform Import Using NETWORK_LINK : Use the NETWORK_LINK parameter to import data directly from the source database. This method is particularly helpful when space is constrained, as it streams the data without needing to generate dump files on the source. It can also reduce time compared to exporting and then importing, as data is streamed directly from the source to the target database. Disable Archivelog Mode (Standalone Databases) : For standalone databases, temporarily disable archive logging during the import process. Import operations can generate a lot of redo logs, slowing down the import. Disabling

prerequisite step: 1. Need to install and configure the GRID on the standalone server. 2. First check the locations of Parameter file, Control file, Data file, Temp file and Redo Log files. This is important piece of information as we move database files to ASM. show parameter pfile; select name from v$controlfile; select name from v$datafile; select name from v$tempfile; select member from v$logfile; 3.Take backup of spfile using RMAN and restore it to ASM disk group. rman target / backup as backupset spfile; restore spfile to '+DATA/spfileoradb.ora'; 4.Rename old spfile as it no longer required mv $ORACLE_HOME/dbs/spfileoradb.ora $ORACLE_HOME/dbs/spfileoradb.ora_old 5.Create new pfile which points to the spfile on ASM vi $ORACLE_HOME/dbs/initoradb.ora spfile='+DATA/spfileoradb.ora' 6.Bounce the database and check if it’s showing new location for spfile shut immediate startup nomount; show Parameter pfile; 7. As our database is already in no-mount mode, we shall connec

 -- Ensure you're logged in as the appropriate user -- 1. Define variables for schema and table names DEFINE schema_name = 'YOUR_SCHEMA_NAME'; DEFINE table_name = 'YOUR_TABLE_NAME'; -- 2. Check the status and size of the table before reorganization SELECT table_name, num_rows, blocks, empty_blocks, avg_row_len FROM dba_tables WHERE owner = '&schema_name' AND table_name = '&table_name'; -- 3. Move the table within the same tablespace to reorganize it -- No tablespace specified, it will remain in the same tablespace ALTER TABLE &schema_name..&table_name MOVE; -- 4. Rebuild indexes associated with the table -- Get all indexes associated with the table SELECT 'ALTER INDEX ' || index_name || ' REBUILD;'  FROM dba_indexes  WHERE owner = '&schema_name' AND table_name = '&table_name'; -- 5. Gather statistics for the table after reorganization EXEC DBMS_STATS.GATHER_TABLE_STATS('&schema_name'