The database is down. Please specify the host credentials to access database restart and diagnostics tools

The database is down. Please specify the host credentials to access database restart and diagnostics tools.
Enterprise manager shows that database is down but in real it is not.

[oracle@grhldb01 trace]$ sqldba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 13 10:38:01 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME    OPEN_MODE
------------
TCHDB   READ WRITE

check the listener status.

[oracle@grhldb01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 26-AUG-2014 13:16:23

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                13-MAR-2014 12:53:12

Uptime                    165 days 23 hr. 23 min. 10 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /orabin/grid11gR2/grid/network/admin/listener.ora

Listener Log File         /orabin/grid11gR2/base/diag/tnslsnr/grhldb01/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.128.0.42)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

  Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "TCHDB" has 2 instance(s).

  Instance "TCHDB1", status READY, has 2 handler(s) for this service...

  Instance "TCHDB2", status READY, has 1 handler(s) for this service...

Service "TCHDB1_RAC" has 1 instance(s).

  Instance "TCHDB2", status READY, has 1 handler(s) for this service...

Service "TCHDB2_RAC" has 1 instance(s).

  Instance "TCHDB2", status READY, has 1 handler(s) for this service...

Service "TCHDBXDB" has 2 instance(s).

  Instance "TCHDB1", status READY, has 1 handler(s) for this service...

  Instance "TCHDB2", status READY, has 1 handler(s) for this service...

Service "TCH_ESP_ORANGE_02_RAC" has 2 instance(s).

  Instance "TCHDB1", status READY, has 2 handler(s) for this service...

  Instance "TCHDB2", status READY, has 1 handler(s) for this service...

Service "TCH_GBR_VODA_02_RAC" has 1 instance(s).

  Instance "TCHDB2", status READY, has 1 handler(s) for this service...

Service "TCH_GBR_VODA_RAC" has 1 instance(s).

  Instance "TCHDB2", status READY, has 1 handler(s) for this service...

The command completed successfully

it’s runing.

Solution:
select username, account_status from dba_users order by 2;
shows that
DBSNMP EXPIRED
SYSMAN EXPIRED
SYSTEM EXPIRED.

if they are locked then simple unlock with commands:

alter user DBSNMP account unlock;
alter user SYSTEM account unlock;
alter user SYSMAN account unlock;

it’s all, now enjoy with enterprise manager

To set password limit Unlimited in Oracle 11G.

When You  tried login your user(TEST) it is showing password will expired with 7 Days.


[oracle@k44-db1]$ sqlplus test/test

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:34:42 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password: 
ERROR:
ORA-28002: the password will expire within 7 days
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> 


Get to know which profile the test user is using from dba_users view,

SQL> SELECT profile FROM dba_users WHERE username='TEST';

USERNAME PROFILE
-------- ---------- 
TEST     DEFAULT

Now check the password lifetime of the default profile from dba_profiles

SQL> SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

and than set default profile limit to unlimited for below query,

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

after profile limit changed  and Alter user(TEST) password for the below statement,

SQL> alter user test identified by test;

User altered.

after that alter username  and try to connect test user,

[oracle@k44-db1]$ sqlplus test/test

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 25 11:50:15 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> 

Thank and Advance......

ORA-39029: worker 1 with process name "DW01" prematurely terminated

DataPump export is performed using COMPRESSION option. When attempting the import into target database the following errors occur:

Master table "ATLAS_TEMP"."SYS_IMPORT_TABLE_18" successfully loaded/unloaded
Starting "ATLAS_TEMP"."SYS_IMPORT_TABLE_18":  atlas_temp/******** dumpfile=pmbuvsnl_ind_atlas_09Apr2014.dmp logfile=pmbuvsnl_ind_atlas_09Apr2014_imp.log content=data_only version=11.1.0 tables=PMBUVSNL_IND_AGRI_NG.mbl_six,PMBUVSNL_IND_AGRI_NG.dn_six directory=atlas_apr14 remap_schema=PMBUVSNL_IND_AGRI_NG:atlas_temp remap_tablespace=PMBUVSNL_IND_AGRI:ATLAS_TS remap_tablespace=PMBUVSNL_IND_AGRI_IDX:ATLAS_TS table_exists_action=append 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 2 with process name "DW01" prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.
Job "ATLAS_TEMP"."SYS_IMPORT_TABLE_18" stopped due to fatal error at 15:23:01

• The alert log file confirms: ORA-07445 occured:

Errors in file /u01/app/oracle/admin/TC/bdump/tc_dw01_4583456.trc:
ORA-07445: exception encountered: core dump [kdblcbo+02c0] [SIGSEGV] ..

• It turned out, that the affected table was compressed:

SQL> select compression from dba_tables where owner='ATLAS_TEMP' and table_name='MBL_SIX';

COMPRESS
--------
ENABLED

Cause:-

Importing a compressed table with Impdp using direct path method generates ORA-7445 [kdblcbo]. Direct path is the default method that Data Pump uses for loading and unloading data.

Solution:-

Issue will be fixed in RDBMS Release 11.2.

• Alter the table to use NOCOMRESS option 

alter table atlas_temp.mbl_six nocompress;

ACCESS_METHOD=EXTERNAL_TABLE

• Import the table again it will successfully imported.

Master table "ATLAS_TEMP"."SYS_IMPORT_TABLE_19" successfully loaded/unloaded
Starting "ATLAS_TEMP"."SYS_IMPORT_TABLE_19":  atlas_temp/******** dumpfile=pmbuvsnl_ind_atlas_09Apr2014.dmp logfile=pmbuvsnl_ind_atlas_09Apr2014_imp.log content=data_only version=11.1.0 tables=PMBUVSNL_IND_AGRI_NG.mbl_six,PMBUVSNL_IND_AGRI_NG.dn_six directory=atlas_apr14 remap_schema=PMBUVSNL_IND_AGRI_NG:atlas_temp remap_tablespace=PMBUVSNL_IND_AGRI:ATLAS_TS remap_tablespace=PMBUVSNL_IND_AGRI_IDX:ATLAS_TS table_exists_action=append 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATLAS_TEMP"."MBL_SIX":"P_09APR2014"        470.1 MB  953364 rows
. . imported "ATLAS_TEMP"."DN_SIX":"P_09APR2014"         300.0 MB  908503 rows
Job "ATLAS_TEMP"."SYS_IMPORT_TABLE_19" successfully completed at 15:39:28

Enable Archivelog mode in 11G.

Short form DBA: Enable and Disable Archivelog mode in 11G.: ****Enable Archive Log Mode*** Please find the below steps required to enable archive log mode on an 11g database. Verify the database...

Enable and Disable Archivelog mode in 11G.

****Enable Archive Log Mode***

Please find the below steps required to enable archive log mode on an 11g database.

Verify the database log mode.

[oracle@ngqa ~]$ sqldba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 27 19:01:01 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     25
Current log sequence           27
SQL>
The log mode is No Archive Mode. Note that Archive destination is USE_DB_RECOVERY_FILE_DEST. You can determine the path by looking at the parameter RECOVERY_FILE_DEST.

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /orabin/ora11gR2/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 3852M
SQL>
By default, archive logs will be written to the flash recovery area. If you do not want to write archive logs to the flash recovery area you can set the parameter LOG_ARCHIVE_DEST_n to the location in which you wish to write archive logs.

SQL> alter system set log_archive_dest_1='LOCATION=/oradata/oraarch/MMINDQA/' scope = both;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oradata/oraarch/MMINDQA/
Oldest online log sequence     25
Current log sequence           27
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             511708752 bytes
Database Buffers          331350016 bytes
Redo Buffers                5132288 bytes
Database mounted.
SQL>
Lastly all that is needed it set archive log mode and open the database.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/oraarch/MMINDQA/
Oldest online log sequence     25
Next log sequence to archive   27
Current log sequence           27
SQL>
We can now see that archive log mode is enabled. Notice that Automatic archive is enabled as well.

You can switch to the log file to see that an archive is written to archive log location.

SQL> alter system switch logfile;

System altered.

SQL> host

[oracle@ngqa ~]$ ls /orabin/oraarch/arch
1_27_711369564.dbf
[oracle@ngqa ~]$ exit
exit
SQL>

***Disable Archive Log Mode***

Note: It is recommended to perform a backup of the database (after a shutdown normal or immediate) prior to changing the archive status of a database.

The following are the steps required to disable archive log mode on an Oracle 10g or 11g database.

Verify the database log mode.
[oracle@ngqa ~]$ sqldba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 27 19:01:01 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/oraarch/MMINDQA/
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28
SQL>
The Database log mode is Archive mode. Next we shut down the database and bring up back up in mount mode.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             511708752 bytes
Database Buffers          331350016 bytes
Redo Buffers                5132288 bytes
Database mounted.
SQL>
All that is left is to disable archive log mode and open the database.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oradata/oraarch/MMINDQA/
Oldest online log sequence     26
Current log sequence           28
SQL>
As you can see, ARCHIVELOG mode has been disabled.
                                                                                                                                                                   

Top 10 Largest Databases in the World:-

Through your life, you may have come across some very large collections. These collections can vary from things that you use in your day to day life to things such as stones or even bugs. You also might have met with some of the organizations having large databases like Google and Facebook who are nearing to a billion users.

1. The World Data Centre for Climate: 

With a massive 220 terabytes of data, it’s the World Data Centre for Climate (WDCC) who bags the coveted number one spot in our list that features top 10 largest databases across the globe. WDCC, operated by the Max Planck Institute for Meteorology and German Climate Computing Centre, also are the proud owners of a supercomputer worth 35 million Euros. Excluding the 220 terabyte, which are accessible across the web; the WDCC also has an additional 110 terabytes of data that are mainly used for climate simulation and 6 petabytes of extra data’s that are stored in magnetic tapes for easy access. 

2. National Energy Research Scientific computing Center: 

The National Energy Research Scientific Computing Center (NERSC) based in Oakland, California has a unique kind of database that non others can even think of. The NERSC database comprises a mammoth 2.8 petabytes of information that vary from atomic energy research, high energy physics experiments to simulations of our early universe. The NERSC which are owned and operated by the Lawrence Berkeley National Laboratory and the U.S Department of Energy, has the second largest database in the world with a whole lot of information that are handled by a minimum of 2,000 computational scientists. 

3. AT&T: 

One of the oldest telecommunication companies, the American based AT&T has one of the largest databases. The company boasts that they have the largest volume of data in one unique database. Also AT&T has one of the largest database with most number of rows (around 1.9 Trillion), second to Sprint. It’s said that AT&T has been recording the details of callers long before terabytes storing came into market. 

4. Google: 

The giants in internet searching- Google bags the number 4th spot in top 10 largest databases. Although the exact size of Google’s database is unknown, it’s said that Google accounts every single search that makes each day into its database which is around 91 million searches per day. Google stores every search and makes patterns from previous searches so that the user can be easily directed. Google also collects information of their users and stores them as entries in their database which is said to expand over 33 trillion entries. On top of that Google has simply expanded their database with Gmail and Google ads and with their acquisitions like YouTube. 

5. Sprint: 

One of the largest telecommunication providers, Sprint bags the number 5th spot with its immense and ever growing database. One of the largest mobile service providers with around 53 million subscribers, Sprint offers local and long distance land line offerings. It’s said that Sprint’s database records more than 365 million call detail per day. Sprint is also proud owner of the database with most number of insertions. According to reports the database spreads over 2.85 trillion rows. During peak hours, it’s said that this database records around 70,000 calls per second. 

6. LexisNexis: 

If you are unknown to the company LexisNexis, then you are sure to jump from your seats when you hear that the next database once belonged to Choicepoint. A billion page information book, that’s Choicepoint, which was bought by LexisNexis in 2008. This extensive database contains information on around 250 million people of American population. The information varies from addresses, phone numbers, driving records, criminal histories and even DNA data. It’s believed that LexisNexis holds nearly of 250 terabytes of personal data. 

7. Youtube: 

The owners of the largest online video library- YouTube is our number 7 candidate in our top 10 databases across the world. Reports say that about a 100 million videos are watched in YouTube which is about 60% of the overall number of videos watched online. Still the Wall Street Journal in 2006 reported that YouTube’s database features around 45 Terabytes of data. 45 terabytes won’t sound like a large number but when you consider that around 65,000 new videos are being posted daily in YouTube, just think of the database that they now are having. 

8. Amazon: 

With a massive 59 million active customers and more than 42 terabytes of data, it’s Amazon that races itself to the number 8thspot in our top 10 databases. World’s largest retail store, Amazon has huge collection of data that includes general information like phone number and address to receipts and wishlists and any sort of data that the website can extract from its users as they are logged on. It’s said that Amazon has a mass collection of 250,000 full text books which are available online. No wonder Amazon is considered as the world’s largest online community as users can interact and comment virtually on every page of this website. 

9. Central Intelligence Agency (CIA): 

An agency that had been collecting and distributing information’s on people, countries and facts- it’s the Central Intelligence Agency (CIA) who is at the number 9th spot of largest databases across the globe. Although the exact size of the database are yet to be disclosed, it’s believed that the CIA is said to have a massive database as they have been collecting information from both private and public sectors. Parts of this information are available to the public through the Electronic Reading Room and The World Fact Book. Reports say that 100 FOIA (Freedom of Information Act items) are added each month along with statistics on more than 250 countries and their entities which includes information ranging from nuclear developments to the type of beer being used during the Korean War. 

10. Library of Congress: 

Research library of the United States Congress or the de facto national library- it’s the Library of Congress that finds its ideal 10th spot in the listing of top 10 largest databases in the world. Ranging from 130 million items from little story books to newspapers-old and new, and to U.S Government proceedings, the Liberty of Congress (LC) owns a proud collection that not even the digital age can’t match up. It’s said that the text portion of LC would take up of 20 Terabytes of data. With an expansion rate of 10,000 per day, the LC spreads out in a massive 530 miles of shelf space. It’s said that the LC is the ideal place for information’s that aren’t found on the internet. 

Note: Information  taken from net..