Solving error of ORA-01172: recovery of thread 1 stuck at block 224 of file 3

ORA-01172: recovery of thread 1 stuck at block 211 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed


When starting the database I got error above

SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2227080 bytes
Variable Size             310379640 bytes
Database Buffers          100663296 bytes
Redo Buffers                4276224 bytes
Database mounted.
ORA-01172: recovery of thread 1 stuck at block 211 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed

Solution - According to the error you should contact oracle support, but this is what I did

Find name of the file having problem

select name,status,enabled from v$datafile where file#=3;

/u01/app/oracle/oradata/XE/undotbs1.dbf


SQL> recover datafile 2;
Media recovery complete.

SQL> alter database open;

Database altered.

ORA-12516, TNS:listener could not find available handler with matching protocol stack

ORA-12516, TNS:listener could not find available handler with matching protocol stack

My charbench client was running fine with 100 users against an Oracle 11g R2 database created using swingbench, but when I tried to run the client with 200 users I got the following error message:

Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error: ORA-12516, TNS:listener could not find available handler with matching protocol stack

On checking to see the vaule of the process for the database I noticed that it was set to 150.

SQL> show parameter processes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 150 SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/testasm/spfiletestasm.or a SQL> alter system set PROCESSES=250 scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 3540881408 bytes Fixed Size 2211864 bytes Variable Size 1644171240 bytes Database Buffers 1879048192 bytes Redo Buffers 15450112 bytes Database mounted. Database opened. SQL> show parameter processes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 250 SQL>

The charbench client proceeded file with 200 users after the above change to the PROCESSES parameter.

Real Application Cluster vs Replication

Oracle Real Application Cluster (RAC)
Real Application Cluster (RAC) is 2 servers or more, each other work together to handle 1 data.
2 Oracle Database Instances with one external data shared storage. So 2 Database Servers is not stand-alone and very different from the Replication.

If the problem occurs in a 1 server, the other server will take over automatically without intervention from the Administrator / Oracle DBA.

RAC aimed for High Availability and High Performance.

Replication
Replication is 2 servers , the 1 is active server and other is standby server (works only receive data replication). So do not work together.

If the problem occurs in the Main Server , it must be taken over /switch to standby server manually, even also need to change ORATNS.ORA on each client PC.

Replication aimed for High Availability and not for High Performance because it only take advantage of performance of the main server.

Oracel to_date function

The Oracle to_date function is used to change a test string (or variable) into an internal date format. The to_date function is usually used in SQL when storing into the database.

Insert into mytab (date_col) values to_date(string,format);

Examples of the to_date function might include:

• to_date('10-12-06','MM-DD-YY')
• to_date('jan 2007','MON YYYY')
• to_date('2007/05/31','YYYY/MM/DD')
• to_date('12-31-2007 12:15','MM-DD-YYYY HH:MI')
• to_date('2006,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS')
• to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss')
• to_date('022002','mmyyyy')
• to_date('12319999','MMDDYYYY')
• to_date(substr( collection_started,1,12),'DD-MON-YY HH24')
• to_date('2004/10/14 21', 'yyyy/mm/dd hh24')
• TO_DATE(First_Load_Time, 'yyyy-mm-dd/hh24:mi:ss'))*24*60)

Creating a view

View is a syntax or script of query oracle syntax stored in database.
Basic syntax is :

CREATE or REPLACE VIEW view_name AS select column1,column2,... from table_name;

example :

CREATE or REPLACE VIEW v_customer AS select id_custmer,name from customer;

Modify Table Structure

Oracle basic syntax to modify table structure

Syntax 1
add a column into existing table

ALTER TABLE table_name
ADD column_name datatype;

example :
ALTER TABLE customer
ADD phone_number varchar(20);


Syntax 2
modify a data type of a field of existing table

ALTER TABLE table_name
MODIFY (column_name datatype);

example :
ALTER TABLE customer
MODIFY (name varchar(100));

create new table

Syntax 1

creating new table
----------------------------
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
...);

example :
CREATE TABLE customer (
id number,
name varchar2(50));


Syntax 2
creating table from other existing table,
the structure of new table copied from source table.
----------------------------
CREATE TABLE table_name as select * from existing_table;

example :
CREATE TABLE customer2 as select * from customer;