12 Replies Latest reply: Jul 14, 2014 10:18 PM by Nip-Oracle RSS

    Listener Configuration Standby ORA-12514 TNS:listener does not currently know of service requested...

    1854985

      Hello all, me again

      Now I have a problem with the configuration of the listener for the stnadby database.

       

      Oracle 12c RAC, On node dbb-g60-01, Instance bsrg60_01. I also attached (tail ) the init.ora for the Stanby node.

       

      I made this:

       

      linstener.ora 

      SID_LIST_LISTENER_BSRG60  =

        (SID_LIST =

          (SID_DESC =

           (GLOBAL_DBNAME = bsrg60)

            (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_bsr )

            (SID_NAME = bsrg60_1)

          )

      (SID_DESC =

           (GLOBAL_DBNAME = bsrg60)

            (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_bsr)

            (SID_NAME = bsrg60_2)

          )

       

       

      tnsnames.ora:

       

      ############################################################ 

      #                       Prod n15                           #     

      ############################################################ 

       

      bsr =

      (DESCRIPTION_LIST =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = dbb-n15-01)(PORT = 1521))

          (ADDRESS = (PROTOCOL = TCP)(HOST = dbb-n15-02)(PORT = 1521))

          (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = bsr)))

      )

       

      ############################################################ 

      #                       Standby                            #     

      ############################################################ 

       

      bsrg60 =

      (DESCRIPTION_LIST =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = dbb-g60-01)(PORT = 1521))

          (ADDRESS = (PROTOCOL = TCP)(HOST = dbb-g60-02)(PORT = 1521))

          (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = bsrg60)))

      )

       

      ############################################################ 

      #                       far_sync                                       #     

      ############################################################ 

       

      far_sync_n15 =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = dbb-n15-03)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = far_sync)

          )

        )

       

      lsnrrctl say:

       

      lsnrctl status

       

      LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 08-JUL-2014 11:41:18

       

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

       

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

      STATUS of the LISTENER

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

      Alias                     LISTENER

      Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production

      Start Date                08-JUL-2014 11:39:11

      Uptime                    0 days 0 hr. 2 min. 7 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Listener Log File         /u01/app/oracle/diag/tnslsnr/dbb-g60-01/listener/alert/log.xml

      Listening Endpoints Summary...

        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbb-g60-01)(PORT=1521)))

      Services Summary...

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

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

      Service "bsr" has 1 instance(s).

        Instance "bsrg60_1", status BLOCKED, has 1 handler(s) for this service...

      The command completed successfully

       

      But

      [oracle@dbb-g60-01 ~]$ sqlplus sys/xxxxx@bsrg60  as sysdba

       

      SQL*Plus: Release 12.1.0.1.0 Production on Di Jul 8 11:46:29 2014

       

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

       

      ERROR:

      ORA-12514: TNS: Listener kann in Connect-Deskriptor angegebenen Service aktuell

      nicht auflösen

       

      Enter user-name:

       

      What is wron ? Any idea?

      Thanks 

       

      my init.ora for this standby:

       

      bsrg601.__data_transfer_cache_size=0

      *.__data_transfer_cache_size=0

      bsrg60_1.__data_transfer_cache_size=0

      bsrg601.__db_cache_size=103347650560

      *.__db_cache_size=78G

      bsrg60_1.__db_cache_size=103347650560

      bsrg601.__java_pool_size=1879048192

      *.__java_pool_size=1792M

      bsrg60_1.__java_pool_size=1879048192

      bsrg601.__large_pool_size=1879048192

      *.__large_pool_size=1792M

      bsrg60_1.__large_pool_size=1879048192

      *.__oracle_base='/u01/app/oracle'# ORACLE_BASE set from environment

      bsrg601.__pga_aggregate_target=31675383808

      *.__pga_aggregate_target=30208M

      bsrg60_1.__pga_aggregate_target=31675383808

      bsrg601.__sga_target=115964116992

      *.__sga_target=90368M

      bsrg60_1.__sga_target=115964116992

      bsrg601.__shared_io_pool_size=536870912

      *.__shared_io_pool_size=512M

      bsrg60_1.__shared_io_pool_size=536870912

      bsrg601.__shared_pool_size=7516192768

      *.__shared_pool_size=5888M

      bsrg60_1.__shared_pool_size=7516192768

      bsrg601.__streams_pool_size=0

      *.__streams_pool_size=0

      bsrg60_1.__streams_pool_size=0

      *._adaptive_window_consolidator_enabled=TRUE

      *._aggregation_optimization_settings=0

      *._always_anti_join='CHOOSE'

      *._always_semi_join='CHOOSE'

      *._and_pruning_enabled=TRUE

      *._array_cdb_view_enabled=TRUE

      *._b_tree_bitmap_plans=TRUE

      *._bloom_filter_enabled=TRUE

      *._bloom_folding_enabled=TRUE

      *._bloom_pruning_enabled=TRUE

      *._common_data_view_enabled=TRUE

      *._complex_view_merging=TRUE

      *._compression_compatibility='12.1.0.0.0'

      *._connect_by_use_union_all='TRUE'

      *._convert_set_to_join=FALSE

      *._cost_equality_semi_join=TRUE

      *._cpu_to_io=0

      *._dimension_skip_null=TRUE

      *._eliminate_common_subexpr=TRUE

      *._enable_type_dep_selectivity=TRUE

      *._fast_full_scan_enabled=TRUE

      *._first_k_rows_dynamic_proration=TRUE

      *._gby_hash_aggregation_enabled=TRUE

      *._generalized_pruning_enabled=TRUE

      *._globalindex_pnum_filter_enabled=TRUE

      *._gs_anti_semi_join_allowed=TRUE

      *._high_priority_processes='LMS*|VKTM|LM*|LCK0|GCR*|DIAG'

      *._improved_outerjoin_card=TRUE

      *._improved_row_length_enabled=TRUE

      *._index_join_enabled=TRUE

      *._ksb_restart_policy_times='0','60','120','240'# internal update to set default

      *._left_nested_loops_random=TRUE

      *._local_communication_costing_enabled=TRUE

      *._minimal_stats_aggregation=TRUE

      *._mmv_query_rewrite_enabled=TRUE

      *._new_initial_join_orders=TRUE

      *._new_sort_cost_estimate=TRUE

      *._nlj_batching_enabled=1

      *._optim_adjust_for_part_skews=TRUE

      *._optim_enhance_nnull_detection=TRUE

      *._optim_new_default_join_sel=TRUE

      *._optim_peek_user_binds=TRUE

      *._optimizer_adaptive_cursor_sharing=TRUE

      *._optimizer_adaptive_plans=TRUE

      *._optimizer_ansi_join_lateral_enhance=TRUE

      *._optimizer_ansi_rearchitecture=TRUE

      *._optimizer_batch_table_access_by_rowid=TRUE

      *._optimizer_better_inlist_costing='ALL'

      *._optimizer_cbqt_no_size_restriction=TRUE

      *._optimizer_cluster_by_rowid=TRUE

      *._optimizer_coalesce_subqueries=TRUE

      *._optimizer_complex_pred_selectivity=TRUE

      *._optimizer_compute_index_stats=TRUE

      *._optimizer_connect_by_combine_sw=TRUE

      *._optimizer_connect_by_cost_based=TRUE

      *._optimizer_connect_by_elim_dups=TRUE

      *._optimizer_correct_sq_selectivity=TRUE

      *._optimizer_cost_based_transformation='LINEAR'

      *._optimizer_cost_hjsmj_multimatch=TRUE

      *._optimizer_cost_model='CHOOSE'

      *._optimizer_cube_join_enabled=TRUE

      *._optimizer_dim_subq_join_sel=TRUE

      *._optimizer_distinct_agg_transform=TRUE

      *._optimizer_distinct_elimination=TRUE

      *._optimizer_distinct_placement=TRUE

      *._optimizer_dsdir_usage_control=126

      *._optimizer_eliminate_filtering_join=TRUE

      *._optimizer_enable_density_improvements=TRUE

      *._optimizer_enable_extended_stats=TRUE

      *._optimizer_enable_table_lookup_by_nl=TRUE

      *._optimizer_enhanced_filter_push=TRUE

      *._optimizer_extend_jppd_view_types=TRUE

      *._optimizer_extended_cursor_sharing='UDO'

      *._optimizer_extended_cursor_sharing_rel='SIMPLE'

      *._optimizer_extended_stats_usage_control=192

      *._optimizer_false_filter_pred_pullup=TRUE

      *._optimizer_fast_access_pred_analysis=TRUE

      *._optimizer_fast_pred_transitivity=TRUE

      *._optimizer_filter_pred_pullup=TRUE

      *._optimizer_fkr_index_cost_bias=10

      *._optimizer_full_outer_join_to_outer=TRUE

      *._optimizer_gather_feedback=TRUE

      *._optimizer_gather_stats_on_load=TRUE

      *._optimizer_group_by_placement=TRUE

      *._optimizer_hybrid_fpwj_enabled=TRUE

      *._optimizer_improve_selectivity=TRUE

      *._optimizer_interleave_jppd=TRUE

      *._optimizer_join_elimination_enabled=TRUE

      *._optimizer_join_factorization=TRUE

      *._optimizer_join_order_control=3

      *._optimizer_join_sel_sanity_check=TRUE

      *._optimizer_max_permutations=2000

      *._optimizer_mode_force=TRUE

      *._optimizer_multi_level_push_pred=TRUE

      *._optimizer_multi_table_outerjoin=TRUE

      *._optimizer_native_full_outer_join='FORCE'

      *._optimizer_new_join_card_computation=TRUE

      *._optimizer_null_accepting_semijoin=TRUE

      *._optimizer_null_aware_antijoin=TRUE

      *._optimizer_or_expansion='DEPTH'

      *._optimizer_order_by_elimination_enabled=TRUE

      *._optimizer_outer_join_to_inner=TRUE

      *._optimizer_outer_to_anti_enabled=TRUE

      *._optimizer_partial_join_eval=TRUE

      *._optimizer_proc_rate_level='BASIC'

      *._optimizer_push_down_distinct=0

      *._optimizer_push_pred_cost_based=TRUE

      *._optimizer_rownum_bind_default=10

      *._optimizer_rownum_pred_based_fkr=TRUE

      *._optimizer_skip_scan_enabled=TRUE

      *._optimizer_sortmerge_join_inequality=TRUE

      *._optimizer_squ_bottomup=TRUE

      *._optimizer_star_tran_in_with_clause=TRUE

      *._optimizer_strans_adaptive_pruning=TRUE

      *._optimizer_system_stats_usage=TRUE

      *._optimizer_table_expansion=TRUE

      *._optimizer_transitivity_retain=TRUE

      *._optimizer_try_st_before_jppd=TRUE

      *._optimizer_undo_cost_change='12.1.0.1'

      *._optimizer_unnest_corr_set_subq=TRUE

      *._optimizer_unnest_disjunctive_subq=TRUE

      *._optimizer_unnest_scalar_sq=TRUE

      *._optimizer_use_cbqt_star_transformation=TRUE

      *._optimizer_use_feedback=TRUE

      *._optimizer_use_gtt_session_stats=TRUE

      *._optimizer_use_histograms=TRUE

      *._or_expand_nvl_predicate=TRUE

      *._ordered_nested_loop=TRUE

      *._parallel_broadcast_enabled=TRUE

      *._partition_cdb_view_enabled=TRUE

      *._partition_view_enabled=TRUE

      *._pga_max_size=2G

      *._pivot_implementation_method='CHOOSE'

      *._pre_rewrite_push_pred=TRUE

      *._pred_move_around=TRUE

      *._pred_push_cdb_view_enabled=TRUE

      *._push_join_predicate=TRUE

      *._push_join_union_view=TRUE

      *._push_join_union_view2=TRUE

      *._px_adaptive_dist_method='CHOOSE'

      *._px_cdb_view_enabled=TRUE

      *._px_concurrent=TRUE

      *._px_cpu_autodop_enabled=TRUE

      *._px_filter_parallelized=TRUE

      *._px_filter_skew_handling=TRUE

      *._px_groupby_pushdown='FORCE'

      *._px_join_skew_handling=TRUE

      *._px_minus_intersect=TRUE

      *._px_object_sampling_enabled=TRUE

      *._px_parallelize_expression=TRUE

      *._px_partial_rollup_pushdown='ADAPTIVE'

      *._px_partition_scan_enabled=TRUE

      *._px_pwg_enabled=TRUE

      *._px_replication_enabled=TRUE

      *._px_single_server_enabled=TRUE

      *._px_ual_serial_input=TRUE

      *._px_wif_dfo_declumping='CHOOSE'

      *._px_wif_extend_distribution_keys=TRUE

      *._query_rewrite_setopgrw_enable=TRUE

      *._remove_aggr_subquery=TRUE

      *._replace_virtual_columns=TRUE

      *._right_outer_hash_enable=TRUE

      *._rowsets_cdb_view_enabled=TRUE

      *._selfjoin_mv_duplicates=TRUE

      *._smm_max_size=1048576

      *._smm_min_size=1024

      *._smm_px_max_size=15415296

      *._sql_model_unfold_forloops='RUN_TIME'

      *._sqltune_category_parsed='DEFAULT'# parsed sqltune_category

      *._subquery_pruning_enabled=TRUE

      *._subquery_pruning_mv_enabled=FALSE

      *._table_scan_cost_plus_one=TRUE

      *._union_rewrite_for_gs='YES_GSET_MVS'

      *._unnest_subquery=TRUE

      *._use_column_stats_for_function=TRUE

      *.audit_file_dest='/u01/app/oracle/admin/bsrg60/adump'

      *.audit_trail='DB'

      *.cluster_database=TRUE

      *.compatible='12.1.0.0.0'

      *.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'# connection_brokers default value

      *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bsrg60'

      *.log_archive_dest_2='SERVICE=bsr ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=bsr'

      *.fal_server='bsr','far_sync'

      *.log_file_name_convert='+DATA/BSR','+DATA/BSRG60'

      *.db_file_name_convert='+DATA/BSR','+DATA/BSRG60'

      bsrg601.core_dump_dest='/u01/app/oracle/diag/rdbms/bsrg60/bsr1/cdump'

      bsrg602.core_dump_dest='/u01/app/oracle/diag/rdbms/bsrg60/bsr2/cdump'

      *.db_block_size=16384

      *.db_create_file_dest='+DATA'

      *.db_domain=''

      *.db_name='bsr'

      *.db_recovery_file_dest='+DATA'

      *.db_recovery_file_dest_size=50565M

      *.db_writer_processes=8

      *.diagnostic_dest='/u01/app/oracle/diag/rdbms/bsrg60/bsr1/trace'

      *.enable_pluggable_database=TRUE

      bsrg601.instance_number=1

      bsrg602.instance_number=2

      *.log_archive_format='%t_%s_%r.dbf'

      *.log_buffer=105766912# log buffer update

      *.nls_language='GERMAN'

      *.nls_territory='GERMANY'

      *.open_cursors=3000

      *.optimizer_dynamic_sampling=2

      *.optimizer_mode='ALL_ROWS'

      *.pga_aggregate_target=30108M

      *.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora

      *.processes=2000

      *.query_rewrite_enabled='TRUE'

      *.remote_login_passwordfile='EXCLUSIVE'

      *.resource_manager_plan='SCHEDULER[0x4211]:DEFAULT_MAINTENANCE_PLAN'

      *.result_cache_max_size=462688K

      *.sessions=3040

      *.sga_max_size=128849018880

      bsrg601.sga_target=115964116992

      *.sga_target=115964116992

      *.shared_servers=5

      *.skip_unusable_indexes=TRUE

      bsrg601.thread=1

      bsrg602.thread=2

      bsrg601.undo_tablespace='UNDOTBS1'

      bsrg602.undo_tablespace='UNDOTBS2'