Sunday, November 29, 2020

PRCR-1079 CRS-5017 ORA-00821 CRS-2674 CRS-2632 -> ORA-00821: Specified value of sga_target 900M is too small, needs to be at least 1384M

 [oracle@rac-p1 ~]$ . oraenv
ORACLE_SID = [racp1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac-p1 ~]$

[oracle@rac-p1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 29 15:07:01 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show parameter streams_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0
SQL>

SQL> alter system set streams_pool_size=1024M scope=spfile sid='*';
System altered.

After changing streams_pool_size

[oracle@rac-p1 ~]$ srvctl stop database -d racp
[oracle@rac-p1 ~]$ srvctl start database -d racp
PRCR-1079 : Failed to start resource ora.racp.db
CRS-5017: The resource action "ora.racp.db start" encountered the following error:
ORA-00821: Specified value of sga_target 900M is too small, needs to be at least 1384M
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/rac-p1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.racp.db' on 'rac-p1' failed
CRS-5017: The resource action "ora.racp.db start" encountered the following error:
ORA-00821: Specified value of sga_target 900M is too small, needs to be at least 1384M
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/rac-p2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.racp.db' on 'rac-p2' failed
CRS-2632: There are no more servers to try to place resource 'ora.racp.db' on that would satisfy its placement policy
[oracle@rac-p1 ~]$

[oracle@rac-p1 ~]$ . oraenv
ORACLE_SID = [racp] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac-p1 ~]$

[oracle@rac-p1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 29 15:12:11 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup mount;
ORA-00821: Specified value of sga_target 900M is too small, needs to be at least 1384M
SQL> exit
Disconnected
[oracle@rac-p1 ~]$

The instance doesn’t come up! This is easy to fix without having to restore the spfile from backup:

[oracle@rac-p1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 29 15:28:35 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup;
ORA-00821: Specified value of sga_target 900M is too small, needs to be at least 1384M
SQL>

SQL> create pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initracp1_1129.ora' from spfile='+DATA/racp/spfileracp.ora';
File created.

SQL> host vi /u01/app/oracle/product/12.1.0/db_1/dbs/initracp1_1129.ora

Now correct the value in the text file. I just changed the sga_target parameter from it here.

racp1.__sga_target=3072M
racp2.__sga_target=3072M
*.sga_target=3072M
*.streams_pool_size=256M

SQL> create spfile='+DATA/racp/spfileracp.ora' from pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initracp1_1129.ora';

File created.

SQL> startup force;
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2929552 bytes
Variable Size             822086768 bytes
Database Buffers         2382364672 bytes
Redo Buffers               13844480 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@rac-p1 ~]$

[oracle@rac-p1 ~]$ srvctl status database -d racp
Instance racp1 is running on node rac-p1
Instance racp2 is not running on node rac-p2
[oracle@rac-p1 ~]$

[oracle@rac-p1 ~]$ srvctl stop database -d racp
[oracle@rac-p1 ~]$

[oracle@rac-p1 ~]$ srvctl status database -d racp
Instance racp1 is not running on node rac-p1
Instance racp2 is not running on node rac-p2
[oracle@rac-p1 ~]$

[oracle@rac-p1 ~]$ srvctl start database -d racp
[oracle@rac-p1 ~]$

[oracle@rac-p1 ~]$ srvctl status database -d racp
Instance racp1 is running on node rac-p1
Instance racp2 is running on node rac-p2
[oracle@rac-p1 ~]$

[oracle@rac-p1 ~]$ . oraenv
ORACLE_SID = [racp1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac-p1 ~]$

[oracle@rac-p1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 29 15:43:57 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select open_mode,database_role from gv$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY
READ WRITE           PRIMARY

SQL> show parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 3G
sga_target                           big integer 3G
unified_audit_sga_queue_size         integer     1048576
SQL>

SQL> show parameter streams_p

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 256M
SQL>

SQL> create pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initracp1_1130.ora' from spfile='+DATA/racp/spfileracp.ora';
File created.
SQL>

Checking Node-2 (rac-p2)

[oracle@rac-p2 ~]$ . oraenv
ORACLE_SID = [racp2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac-p2 ~]$

[oracle@rac-p2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 29 15:19:00 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select open_mode,database_role from gv$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY
READ WRITE           PRIMARY

SQL> show parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 3G
sga_target                           big integer 3G
unified_audit_sga_queue_size         integer     1048576
SQL>

SQL> show parameter streams_p

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 256M
SQL>

SQL> create pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initracp2_1130.ora' from spfile='+DATA/racp/spfileracp.ora';
File created.

SQL>


Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)