ORA-00257

Cannot connect to database (ORA-00257: Archiver error. Connect AS SYSDBA only until resolved. )

Error : Cannot connect to database (ORA-00257: Archiver error. Connect AS SYSDBA only until resolved. )

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon,
the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter
ARCHIVE_LOG_DEST is set up properly for archiving.alter database flashback on

There is two possible way to solution.
1. without increasing DB_RECOVERY_FILE_DEST_SIZE.
2. by increasing DB_RECOVERY_FILE_DEST_SIZE.
3. archivelog mode disable

Without increasing DB_RECOVERY_FILE_DEST_SIZE.

1. Check whether the database is in archive log mode and automatic archiving is enabled.

 SQL> archive log list;
 Database log mode              Archive Mode
 Automatic archival             Enabled
 Archive destination            USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence     29
 Next log sequence to archive   31
 Current log sequence           31

2. If archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by:

SQL> show parameter db_recovery_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size           big integer 2G
Check what the value for db_recovery_file_dest_size.

3. Find the space used in flash recovery area by using following SQL:

col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999
col round(space_used/1048576) heading “Space Used (MB)” format 99999
col name format a40
select name, round(space_limit/1048576) As space_limit,round(space_used/1048576) As space_used
from v$RECOVERY_FILE_DEST;

4. if SPACE_USED is equal to SPACE_LIMIT of db_recovery_file_dest, move the archive logs to different destination.

5. Archive all the log files

SQL> alter system archive log all;
6. Just switch the logs to verify

 SQL> alter system switch logfile;

7. DB_RECOVERY_FILE_DEST_SIZE is to delete (archive log) files from DB_RECOVERY_FILE_DEST if you are sure you have backups and the archived logs
are no longer necessary.

 $rman target /
 RMAN>delete archivelog until time 'SYSDATE-1';
 or,
 RMAN>delete archivelog all;

By increasing DB_RECOVERY_FILE_DEST_SIZE.

1. See the path of flash recovery area.

 SQL> show parameter db_recovery_file_dest;

2. Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET db_recovery_file_dest_size='50G' SCOPE=BOTH;
Sytem Altered.



[oracle@oraprod pfile]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 22 16:22:35 2018

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.5032E+10 bytes
Fixed Size                 12349192 bytes
Variable Size            2348810488 bytes
Database Buffers         1.2616E+10 bytes
Redo Buffers               54759424 bytes
Database mounted.
SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> shutdown immediate

ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.5032E+10 bytes
Fixed Size                 12349192 bytes
Variable Size            2348810488 bytes
Database Buffers         1.2616E+10 bytes
Redo Buffers               54759424 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PROD                         MOUNTED

SQL> alter pluggable database PROD open;

Pluggable database altered.

********************************************************************

archivelog mode disable

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@oraprod ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 20 16:52:35 2018

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

Connected to an idle instance.

SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.5032E+10 bytes
Fixed Size                 12349192 bytes
Variable Size            2348810488 bytes
Database Buffers         1.2616E+10 bytes
Redo Buffers               54759424 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL>
SQL>
SQL> alter database open;

Database altered.

Talend Introduction

Talend ETL TOOL

ABOUT TALEND
Talend is a next-generation leader in cloud and big data integration software that helps companies become data driven by making data more accessible, improving its quality and quickly moving it where it’s needed for real-time decision making. Talend’s open-source, native, and unified integration platform, Data Fabric, enables customers to embrace new innovations and scale to meet the evolving data demands of the business.

The only open source vendor named a leader in the Gartner Magic Quadrant for Data Integration Tools, and the Forrester Big Data Fabric Wave, Talend’s innovative solutions are relied upon by 1500+ global enterprise customers across a range of industries, including Air France, GE, and Lenovo.

Talend Open Studio for Data Integration: The Powerful ETL Tool You Can Use Today
IT groups tasked with implementing extract, transform, and load (ETL) projects have traditionally been forced to choose between two time-consuming options: develop a custom ETL tool from scratch; or try to win approval for purchasing an expensive proprietary ETL tool. With Talend Open Studio for Data Integration, busy IT departments now have a new and better option: a powerful open source ETL tool that you can download for free and start using today.

A Feature-Rich ETL Tool

Talend Open Studio for Data Integration helps you to efficiently and effectively manage all facets of data extraction, data transformation, and data loading. This leading open source ETL tool boosts developer productivity with a rich set of features including:

An Eclipse-based graphical integrated development environment that enables easy data modeling, drag-and-drop job design, and  efficient reuse of completed work across projects and modules.
More than 900 components and built-in connectors that allow you to easily link a wide array of sources and targets.
Robust ETL functionality such as string manipulations, management of slowly changing dimensions, and automatic lookup handling.
The ability to execute extract, load, and transform (ELT) processes as well as ETL processes, even within the same job.
Proven in Production Talend Open Studio for Data Integration is a proven data integration solution that's been downloaded millions of times and has hundreds of thousands of users. Organizations using Talend Open Studio for Data Integration in production environments range from small start-ups to some of the largest corporations in the world, as well as local and national government agencies.

The Benefits of Open Source

As an open source ETL tool, Talend Open Studio for Data Integration gives you the ability to access and extend source code to best suit your needs. This powerful, productivity-boosting ETL tool is surrounded by a large and active user community that shares insights and application extensions through platforms such as Talend Forum and Talend Exchange. Backed by Talend's ongoing and extensive R&D; efforts, Talend Open Studio for Data Integration is frequently updated and enhanced in ways that reflect the experiences and needs of the user community.

A Seamless Path to Enterprise Scale For enterprise-scale, projects, Talend also offers the subscription-based Talend Data Integration. Talend Data Integration extends Talend Open Studio for Data Integration with enterprise features such as collaboration and versioning tools, monitoring and management tools, and world-class technical support. Projects built on Talend Open Studio for Data Integration can seamlessly transition to Talend Data Integration.

Learn more about Talend’s data integration solutions from the many resources on this web site, or download Talend Open Studio for Data Integration today and start benefiting from the leading open source data integration tool.

Why Talend?
Because supporting increasing data volumes, users, and use-cases requires you to constantly evolve your data infrastructure, from Java and data warehouses to the Cloud and Spark and The Next Big Thing. Only Talend allows you to effortlessly adopt new technologies so you can focus more on creating business value and less on data integration.
Our mission is to give you the data agility needed to enable every person in your organization to make more informed, real-time decisions every day.



INTRODUCTION:-

What is Talend? 

Talend is the first provider of open source data integration software. Its main product is Talend Open Studio. After three years of intense research and development investment the first version of that software was released in 2006. It is an Open Source project for data integration based on Eclipse RCP that primarily supports ETL-oriented implementations and is provided for on-premises deployment as well as in a software-as-a-service (SaaS) delivery model. Talend Open Studio is mainly used for integration between operational systems, as well as for ETL (Extract, Transform, Load) for Business Intelligence and Data Warehousing, and for migration.
Talend offers a completely new vision, reflected in the way it utilizes technology, as well as in its business model.
The company shatters the traditional proprietary model by supplying open, innovative and powerful software solutions with the flexibility to meet the data integration needs of all types of organizations.

Talend Open Studio is the most open, innovative and powerful data integration solution on the market today.

Talend ETL Tool

Talend open studio for data integration is one of the most powerful data integration ETL tool available in the market. TOS lets you to easily manage all the steps involved in the ETL process, beginning from the initial ETL design till the execution of ETL data load. This tool is developed on the Eclipse graphical development environment. Talend open studio provides you the graphical environment using which you can easily map the data between the source to the destination system. All you need to do is drag and drop the required components from the palette into the work space, configure them and finally connect them together. It even provides you a metadata repository from where you can easily reuse and re-purpose your work. This definitely will help you increase your efficiency and productivity over time.With this, you can conclude that Talend open studio for DI provides an improvised data integration along with strong connectivity, easy adaptability and a smooth flow of extraction and transformation process.


Main features and benefits of that solution:

Business modeling
Graphical development
Metadata-driven design and execution
Real-time debugging
Robust execution

Provided as a packaged, out-of-the-box, ready-to-install platform, Talend Open Studio meets data integration requirements of all organizations - regardless of their size or level of data integration expertise.

There are also available Talend Open Studio extensions:

Talend Integration Suite - The first Open Source enterprise data integration solution, Talend Integration Suite supports the tough requirements of enterprise development, and scales to the highest levels of data volumes and process complexity.

Talend On Demand - The industry's first data integration Software as a Service (SaaS), Talend On Demand consolidates Talend Open Studio metadata and project information in an online, shared repository hosted by Talend.

Talend Open Profiler - The first open source data profiling tool, Talend Open Profiler, allows business users or data management staff to define a set of indicators for each data element that needs to be analyzed or monitored. It produces sophisticated reports and graphs that let users gauge at a glance the level of quality of the data, and the status of the indicators that were defined.

Talend Data Quality - The first open source data quality solution with enterprise-grade features and technical support, Talend Data Quality is a graphical data quality management environment that processes data, such as addresses, phone numbers, spellings, synonyms and abbreviations. Talend Data Quality includes both data profiling and data cleansing capabilities.

What Is ETL Process?

ETL stands for Extract, Transform and Load. It refers to a trio of processes which are required to move the raw data from its source to a data warehouse or a database. Let me explain each of these processes in detail:

Extract

Extraction of data is the most important step of ETL which involves accessing the data from all the Storage Systems. The storage systems can be the RDBMS, Excel files, XML files, flat files, ISAM (Indexed Sequential Access Method), hierarchical databases (IMS), visual information etc. Being the most vital step, it needs to be designed in such a way that it doesn’t affect the source systems negatively. Extraction process also makes sure that every item’s parameters are distinctively identified irrespective of its source system.

Transform

Transformation is the next process in the pipeline. In this step, entire data is analyzed and various functions are applied on it to transform that into the required format. Generally, processes used for the transformation of the data are conversion, filtering,  sorting, standardizing, clearing the duplicates, translating and verifying the consistency of various data sources.

Load

Loading is the final stage of the ETL process. In this step, the processed data, i.e. the extracted and transformed data, is then loaded to a target data repository which is usually the databases. While performing this step, it should be ensured that the load function is performed accurately, but by utilizing minimal resources. Also, while loading you have to maintain the referential integrity so that you don’t lose the consistency of the data. Once the data is loaded, you can pick up any
chunk of data and compare it with other chunks easily.Now that you know about the ETL process, you might be wondering how to perform all these? Well, the answer is simple using ETL Tools.


Various ETL Tools

But before I talk about ETL tools, let’s first understand what exactly is an ETL tool.

As I have already discussed, ETL are three separate processes which perform different functions. When all these processes are combined together into a single programming tool which can help in preparing the data and in the managing various databases. These tools have graphical interfaces using which results in speeding up the entire process of mapping tables and columns between the various source and target databases.

Some of the major benefits of the ETL Tools are:

It is very easy to use as it eliminates the need for writing the procedures and code.
Since the ETL Tools are GUI based they provide a visual flow of the system’s logic.
The ETL tools have built-in error-handling functionality because of which they have operational resilience.
When dealing with large and complex data, ETL tools provide a better data management by simplifying the tasks and assisting you with various functions.
ETL tools provide an advanced set of cleansing functions as compared to the traditional systems.
ETL tools have an enhanced business intelligence which directly impacts the strategic and operational decisions.
Because of the use of the ETL tools, the expenses reduces by a lot and the businesses are able to generate higher revenue.
Performance of the ETL tools is much better as the structure of its platform simplifies the construction of a high-quality data warehousing system.
There are various ETL tools available in the market, which are quite popularly used. Some of them are:


ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...