Select Count(*) Script in Oracle

Today one of the requirement came, where I have to found out the the record counts of more than 500 tables. Can you believe how much time it will take to encounter select count(*) from ... for each table !! Horrible. So I found the below solution for it and  worked for me fine.

For Example under DEBSHIS schema,I want to find out all the table count details

The above will create a script of all the tables under DEBASHIS and again run that script to collect the count of table.But problem is that for 500 tables,how could you know which count is for which table because in a single function we can added a individual column which will leads to
ORA-00937: not a single-group group function. But yes for 10 or 15 tables  I can suggest you to use the below method.

spool countscript.sql

select 'select count(*) from '||table_name||';' from dba_tables where owner = 'DEBASHIS';

spool off

Then run the script from  SQLPLUS

@countscript.sql

But for >100 tables,please use the below method to get your results.

Connect to your schema or as sysdba and use the below query to get the count of the all the tables in a schema.... Cheers !!

select table_name,
       to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as count
from all_tables
where owner = 'DEBASHIS' order by table_name asc; 

For more information please refer..

http://www.dba-oracle.com/t_count_rows_all_tables_in_schema.htm 

How to Manually fill the gap of the archive redo log files in Production and Standby database

Usually, a Data Guard archive gap sequence is created as the result of a network outage and the connection between the primary and the standby site is lost. The archive gap sequence can also occur during the creation of a standby database. 

Three methods of automatic archive gap detection became available.  The following is a description of their features:
- The archiver process of the primary database polls the standby databases every 60 seconds. This is referred to as ?heartbeat.? During heartbeat, if a standby database reports an archive gap, the archiver of the primary database sends the archived redo log files required to fill the gap. Once the files have been transferred, the site is marked as up to date.

- Archived redo logs are transferred from the primary database to the standby database. On the primary database side, the LGWR or ARCH is responsible for sending the archived redo logs. On the standby site, the RFS process takes the archived redo log file and writes to the host machine. An archived redo log file is uniquely identified by its sequence number and thread number.

- When the RFS process receives an archived redo log file, it compares the sequence number with the sequence number of the previously received archived redo log file. If the sequence number of the current archived redo log file is greater than the sequence number of the last received archived redo log file plus one, a request is sent to the archiver process of the primary database that is providing a list of missing archived redo log files. The archiver of the primary database then retransmits these archived redo log files, thereby resolving the gap sequence.

- For a physical standby database, Oracle introduced a request-response system for gap resolution through the FAL background process. The FAL method of gap resolution is initiated by the Managed Recovery Process (MRP). If the MRP detects a gap in archived redo logs during the application of log files, it requests the archiver process of the primary database to resend the archived redo log files which have caused the gap sequence.

- There are two important parameters required for gap resolution through the FAL background process: fal_server and fal_client. These two parameters need to be set in the initialization parameter file of the physical standby database. fal_server is the service name of the primary database and fal_client is the service name of the standby database.

- The fal_client should be one of the remote archival destinations of the primary database pointing to this standby database. When MRP detects a gap sequence, it requests the archiver process of the primary database, identified by the fal_server parameter, and passes the sequence number of log files causing the archive gap. In addition, it passes the service name defined by the fal_client parameter to the archiver of the primary database. The archiver process of the primary database then retransmits the archived log files requested by the MRP of the standby database.


Manual Procedure...

1. start with detecting gaps in the redo logs by querying the v$archive_gap

SQL> select * from v$archive_gap;

thread#   low_sequence#   high_sequence#
-------- ----------------  ------------------
         1                     30                      34 

   the output indicates our standby database is currently missing log files from sequence 30 to
   34.

2. issue following statement on primary database to locate the archived redo log files. assuming  
    the local archive destination on primary is LOG_ARCHIVE_DEST_1

SQL> select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between 30 and 34; 

name
----------------------------------

/u02/oraarchive/DB01/arch_t1_s30.dbf  

/u02/oraarchive/DB01/arch_t1_s31.dbf  

/u02/oraarchive/DB01/arch_t1_s32.dbf  

/u02/oraarchive/DB01/arch_t1_s33.dbf  

/u02/oraarchive/DB01/arch_t1_s34.dbf 

3. stop the automatic recovery (MRP) of the data guard

SQL> alter database recover managed standby database cancel;

4. transfer manually the archived log files shown on the step 2 to standby database
5. register that archived log files on standby database

SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s30.dbf'; 

SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s31.dbf'; 

SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s32.dbf'; 

SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s33.dbf'; 

SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s34.dbf'; 

6. put the standby database into automatic recovery managed mode

 SQL> alter database recover managed standby database disconnect from session;

7. verify that the gap gets resolved on standby database

SQL> select sequence#, applied from v$archived_log order by sequence#; 

now you figure it out, that your archived log files (ARCLs) has been synchronize with your primary database. as a DBA you must take a notice at log using this SQL statement

SQL> select message from v$dataguard_status where severity like 'Warning';

also on your alert.log


For more please refer the below links...

http://asnsblues.blogspot.in/2013/12/manually-resolving-gaps-oracle-data.html 

http://www.dba-oracle.com/b_data_guard_archived_gap_sequence.htm