ORA-00845: MEMORY_TARGET not supported on this system

Hi All,

I Think lot of people might come across the error “ORA-00845”, in 11.2.0.3, when operating your instances with AMM (Automatic Memory Management), like me ūüėČ

Yes, I too have encountered this problem last week when I was trying to re-size my instances which are running on AMM.

I actually have my instance with 6G of¬†MEMORY_MAX_TARGET and now tried increasing it to 8G. I’v done that through a simple alter command

SQL> alter system set memory_max_target=8G scope=spfile;

System altered.

Now when I tried restarting my instance, it’s saying

ORA-00845: MEMORY_TARGET not supported on this system

I was really¬†surprised¬†on seeing this and started digging the documentation. Then I’ve found the actual reason which is,

You need to configure large enough shmfs/tmpfs on /dev/shm device to fit all memory up to MEMORY_MAX_TARGET.

You have to run the below commands as root user to do that.

# umount tmpfs

# mount -t shmfs shmfs -o size=8G /dev/shm
# df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
shmfs 10649600 0 10649600 0% /dev/shm

This has surfaced lot more questions in my mind and one of them is, why Oracle is taking the shared memory segments from a file (/dev/shm) !??

I’ve checked immediately the shared memory segments

[user@machine ~]# ipcs -m|grep oracle
0x00000000 3506181 oracle 640 4096 0
0x00000000 3538950 oracle 640 4096 0
0x91d2c260 3571719 oracle 640 4096 0

All the three of the Oracle’s shared memory segments are of 4KB in size. This really looks strange isn’t it? From where my dear Oracle if getting 8GB?

Then I tried this

[user@machine ~]# pmap `pgrep -f lgwr`

10662: ora_lgwr_CSMDB
0000000060000000 4K r-xs- /dev/shm/ora_CSMDB_3506181_0
0000000060001000 4092K rwxs- /dev/shm/ora_CSMDB_3506181_0
0000000060400000 4096K rwxs- /dev/shm/ora_CSMDB_3506181_1
0000000060800000 4096K rwxs- /dev/shm/ora_CSMDB_3506181_2
0000000060c00000 4096K rwxs- /dev/shm/ora_CSMDB_3506181_3
0000000061000000 4096K rwxs- /dev/shm/ora_CSMDB_3506181_4
0000000061400000 4096K rwxs- /dev/shm/ora_CSMDB_3506181_5
0000000061800000 4096K rwxs- /dev/shm/ora_CSMDB_3538950_0
0000000061c00000 4096K rwxs- /dev/shm/ora_CSMDB_3538950_1
0000000062000000 4096K rwxs- /dev/shm/ora_CSMDB_3538950_2
0000000062400000 4096K rwxs- /dev/shm/ora_CSMDB_3538950_3
0000000062800000 4096K rwxs- /dev/shm/ora_CSMDB_3538950_4
0000000062c00000 4096K rwxs- /dev/shm/ora_CSMDB_3538950_5
0000000063000000 4096K rwxs- /dev/shm/ora_CSMDB_3538950_6

……………………………………

……………………………………

and an ls -l /dev/shm also has given me this list as well.

SGA will be allocated in many smaller chunks so that Oracle is easily able to release some parts of SGA memory back to OS and server processes are allowed to increase their aggregate PGA size up to the amount of memory released.

But note that the PGA memory is still completely independent, allocated just by mmap’ing /dev/zero, it doesn’t really have anything to do with shared memory segments

You can see the PGA segments

00007fa3dc106000 1088K rwx– /dev/zero
00007fa3dc216000 1088K rwx– /dev/zero
00007fa3dc326000 1088K rwx– /dev/zero
00007fa3dc436000 832K rwx– /dev/zero
00007fa3dc506000 1088K rwx– /dev/zero
00007fa3dc616000 1088K rwx– /dev/zero
00007fa3dc726000 1088K rwx– /dev/zero
00007fa3dc836000 832K rwx– /dev/zero
00007fa3dc906000 1088K rwx– /dev/zero
00007fa3dca16000 960K rwx– /dev/zero
00007fa3dcb06000 1088K rwx– /dev/zero
00007fa3dcc16000 960K rwx– /dev/zero

So After going through all of this exercise, I came to know that Oracle has taken up the old POSIX-style shared memory allocation schemes where everything including shared memory if a file !!

The main point in putting so much stress on this point is to let you know the administrative implications because of this change.

  1.  ipcs -m doesn’t show the full size of these segments anymore. You need to list /dev/shm contents for that.
  2. pmap always reports that the memory is mapped even though it doesn not have physical backing storage on /dev/shm device.
  3. And most importantly the error mentioned in the title of this post

Finally after reading all this a question should be revolving in your mind. What if we use LOCK_SGA to lock pages in memory?? How oracle be able to swap free memory segments across SGA and PGA in that case???

That’s the reason Oracle won’t allow you to use MEMORY_TARGET and MEMORY_MAX_TARGET if LOCK_SGA is enabled ūüėČ

I always welcome your questions/feedback.

Regards,

CSM


Changing the passwords of Database users

Hi All,

Last week a question turned up in OTN forums about changing the passwords of Database users (Schemas).

It’s very simple, just issuing ‘ALTER USER XXX IDENTIFIED BY PASSWORD YYY’. Isn’t it? Why we have to spend so much time on a strategy to change passwords?

It’s not so simple as we think. Consider a scenario where the application stores the passwords, of course in encrypted form, and tries to connect to the Database with those. If we change the passwords without making the application aware of the changes, Your entire application will become unusable.

Leave about the application users. We’ll change their passwords in a consistent way. ¬†Can we change the passwords of users like ¬†SYSMAN, DBSNMP through the ALTER USER… command? If your answer is YES, you are wrong. Even they’re not application users, they too have some dependencies. My intention in writing this article is to address those. BTW, I’m taking some of the commands and procedures to be followed from Metalink.

Steps to change SYSMAN password [270516.1]

Steps for Changing the SYSMAN Password in 10.x Grid Control

The below steps are applicable to 10.1.0.x to 10.2.0.4 version of Grid Control:

  1. Stop the Agent monitoring the ‘OMS and Repository’ target, to avoid the SYSMAN account in the repository database from getting locked, during the password change.

    – ¬†Issue the query below to¬†Find which Grid Agent is Monitoring the “OMS and Repository” Target?

¬† ¬† ¬† ¬† ¬† ¬† ¬† SELECT target_name, target_type, host_name, emd_url¬†FROM mgmt_targetswhere target_type = ¬†‘oracle_emrep’;

–¬† Connect to the node where the above Agent is installed and stop the agent by doing,

                           cd <AGENT_HOME>/bin

                           emctl stop agent

2. Stop all the OMS instances that are associated with the repository. From each node where the OMS is running,

                            cd <OMS_HOME>/opmn/bin

                            opmnctl stopall

3. Change the SYSMAN password in the file emoms.properties:

–¬† Take a backup of the <OMS_HOME>/sysman/config/emoms.properties file.

– Edit the file emoms.properties and change the following lines

                           oracle.sysman.eml.mntr.emdRepPwd=new_password

                           oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

Note:

1. The oracle.sysman.eml.mntr.emdRepPwdSeed parameter should not be modified.
2. Do not create an additional entry for the oracle.sysman.eml.mntr.emdRepPwd / oracle.sysman.eml.mntr.emdRepPwdEncrypted, but modify the existing parameter values.
3. Do not add any extra space to the password in clear text or the value for oracle.sysman.eml.mntr.emdRepPwdEncrypted parameter.

Now we’re done with the Password changes on the application file. It’s time to change them in the Database and some other places

  1. Change the SYSMAN password in the Database hosting the repository:

– Set the $ORACLE_SID environment variable to point to the EM Repository Database.

– Logon to the Repository Database with SQL*Plus as a dba user and issue the command:

SQL> alter user sysman identified by <newpassword>;

2. Restart each of the OMS:

From each node where an OMS is running,execute:

                          cd <OMS_HOME>/opmn/bin

                          opmnctl startall

3. Change the SYSMAN password for the “OMS and Repository” target:

Restart the Agent monitoring the “OMS And Repository” target, that was stopped in Step 1:

                     cd <AGENT_HOME>/bin

                               emctl start agent

Login to the Grid Control Console and navigate to Setup ->Management Services and Repository.

From the bottom of the page in the “Overview”, click on the link “Monitoring Configuration”.
If the monitoring Agent is still down, you will get the following error message:

               Communication from the Oracle Management Service host to the Agent host failed. Refer to help for details.                 IOException in sending Request :: Connection refused 

In the Monitoring Configuration page, change the value in the field “Repository password”
Click OK

This will update the <AGENT_HOME>/sysman/emd/targets.xml of the monitoring Agent with the new SYSMAN   password.

Steps for Changing the SYSMAN Password in 10.2.0.5 and 11.1 Grid Control

When using 10.2.0.5 or 11.1 OMS, all the above steps can be performed by executing a single command:

cd <OMS_HOME>/bin

emctl config oms -change_repos_pwd -change_in_db

Example of output for OMS 10.2.0.5 running on Windows:

C:\app\oracle\oms10g>emctl config oms -change_repos_pwd -change_in_db
Oracle Enterprise Manager 10g Release 5 Grid Control
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
Enter Repository User's Current Password :
Enter Repository User's New Password :

Changing password in backend ...
Password changed in backend successfully.
Changing monitoring credentials ...
Monitoring credentials changed successfully.
Updating emoms.properties....
emoms.properties updated successfully....
Please bounce OMS.
Successfully changed repository password.
If you have multiple OMS's in your environment, please run this cmd on all of them

Note:

1. The above command
– will prompt you for the current password and the new password.
– will modify the¬†sysman¬†password in the repository database, in the emoms.properties configuration file / WLS Credential store and the monitoring credentials for the ‘OMS and Repository’ target.
– will ask you to bounce the OMS once the command is completed successfully
– for 11G, the command will modify the password for¬†sysman¬†and¬†sysman_mds¬†users in the repository database, modify the password in the credential store and change the monitoring credentials for the ‘OMS and Repository’ target.

2. The ‘-change_in_db’ parameter is optional and is used to change the SYSMAN password in the repository database.

– If the sysman password is manually modified in the repository database, this option need not be specified.
– When this parameter is specified, the command will prompt for the current sysman password.
– If the current sysman password is lost / unknown then:

a) Log in to your Repository database as SYS and run:
SQL> ALTER USER SYSMAN IDENTIFIED BY <newpassword>;

where newpassword is the new password you want for SYSMAN.

b) From a command prompt run:
cd $OMS_HOME/bin
./emctl config oms -change_repos_pwd
                     Provide newpassword when prompted.

3. If using multiple OMS, then execute the above command on one of the OMS.
On the Additional OMS, execute:

<OMS_HOME>/bin/emctl config oms -change_repos_pwd

Reminder to update the emcli, on every monitoring host, with a new password if the sysman password is changed,

as this will allow emcli to login with the new password.
Reference:
Note:1090075.1: EM CLI: How to Install and Setup emcli (Enterprise Manager Command Line Interface)?

$> emcli setup -url=http://omsmachine.domain:port/em -username=em_user

Steps to change DBSNMP password [on multiple hosts] [886984.1]

We can change the DBSNMP password on multiple target Databases through EMCLI (Enterprise Manager Command Line interface).

Please go through the link here for the installation instructions of EMCLI: https://querycsm.wordpress.com/2012/08/13/adding-targets-in-oem-12c-in-silent-mode/

1. Using input_file option with update_password EMCLI Verb

emcli update_password
-target_type=oracle_database
-target_name=database
-credential_type=DBCreds
-key_column=DBUserName:oracle
-non_key_column=”FILE1″
-input_file=”FILE1:/tmp/user_passwd”
And the contents of the user_passwd file is
DBPassword:oracle:oracle1

2. Another way of using the files option with other password management verbs like update_db_password, update_host_password, update_target_password
You can create a chng_DB_mon_passwd (Shown below) with multiple targets to change the password. May be changing the dbsnmp password on 10 db target:
And Execute like –
emcli login -user=sysman -pass=xxxxxxxx
emcli argfile chng_DB_mon_passwd

This will help in change the password at multiple targets in one emcli execution.

Chng_DB_mon_passwd File :
update_db_password -target_name=database1 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes
update_db_password -target_name=database2 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes
update_db_password -target_name=database3 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes
update_db_password -target_name=database4 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes

Have a nice day.
CSM

Not about Oracle this time but about god!!!

As a devotee of Steve Jobs, I thought of posting some unseen and rare pics of Steve which, i feel, gives my blog a ‘divine touch’.

Steve Rocks…Always…

CSM

This slideshow requires JavaScript.

Can we do Point In Time Recovery of Datafiles or Tablespaces

Hi guys,

This is an extension to the article I’ve posted last week:¬†https://querycsm.wordpress.com/2012/08/29/how-restoring-individual-datafile-effects-the-constraints-and-logical-consistency/

I tried to restore individual datafile from some backup. But I tried to replicate a scenario:  My restore operation will take out a record from a table with primary key and that record to be removed will have dependent reference data in another table.

When I tried to restore the datafile, which contains the PK table, Oracle asked for recovery followed by the restore. When I recovered the file, I got the record (The PK one) which I tried to delete earlier into the respective table. So there’s no harm for¬†the¬†referential integrity constraints happened.

My intention of disturbing the PK-FK relation is not satisfied yet…

So, I tried doing a PITR (Point In Time Recovery) of the data file like below

SQL> select to_char(sysdate,’dd:mm:yyyy – HH:mi:ss’) from dual;

TO_CHAR(SYSDATE,’DD:M


03:09:2012 – 04:26:57

SQL> select * from dept_non_cons;

ID NAME


——————————
10 maths
20 physics
50 computers

Now I’ve inserted 2 rows

SQL> insert into dept_non_cons values (70,’Chemistry’);

1 row created.

SQL> insert into dept_non_cons values (60,’Databases’);

1 row created.

SQL> commit;

SQL> select * from dept_non_cons;

ID NAME


——————————
70 Chemistry
60 Databases
10 maths
20 physics
50 computers

SQL> select to_char(sysdate,’dd:mm:yyyy – HH:mi:ss’) from dual;

TO_CHAR(SYSDATE,’DD:M


03:09:2012 – 04:27:12

Now I’m taking this datafile offline and restoring from an old backup.

That backup contains these 3 records only

10 maths
20 physics
50 computers

And now I’m recovering like

RMAN> run
2> {
3> set until time “to_date(’09 03 2012 04:26:57‘,’mm dd yyyy hh24:mi:ss’)”;
4> recover datafile 9;
5> }

So, Technically we have only 3 records till 04:26:57

but after recovery, when I did this

SQL> alter database datafile 9 online;

Database altered.

SQL> select * from dept_non_cons;

ID NAME


——————————
70 Chemistry
60 Databases
10 maths
20 physics
50 computers

How come I got Chemistry and Databases here !!! ??? Strange right??

The answer is

Oracle won’t allow PITR in case of Datafiles.

If you wish / have to do an incomplete recovery, you always have to restore ALL the datafiles, recover them until desired point in time and then open the database using resetlogs to sync all the datafiles with controlfiles and redo logs.

So there’s no question of having missing logical consistency imposed by constraints.

What about PITR in case of tablespaces? According to Oracles’ documentation http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmtspit.htm , We can do PITR of Tablespaces. So how Oracle handles this kind of problems there?

The answer for this is,

Oracle checks for this kind of¬†anomalies¬†before doing PITR of tablespaces. There’s a view called¬†SYS.TS_PITR_CHECK¬†which will record the¬†anomalies¬†for our reference.

In my case the view reported exactly what I required

SELECT *
FROM SYS.TS_PITR_CHECK
WHERE (
TS1_NAME IN (‘TST2’)
AND TS2_NAME NOT IN (‘TST2’)
);SQL> 2 3 4 5 6

own1 name1 subname1 obj1type ts1_na name2 subname2 obj2type own2 ts2_na cname reason
—— —– ——– ——– —— —– ——– ——– —— —— —– ————————-
SYS DEPT TABLE TST2 STUDE TABLE SYS RMANTS STUD_ constraint between tables

NT T FK not contained in recovery
set

SYS DEPT TABLE TST2 DEPT_ INDEX SYS RMANTS Table and Index enforcing
PK T primary key constraint
not in same tablespace

Oracle functions this way to make sure the logical consistency is maintained.

You’re welcome to ask questions/provide feedback.

have nice day.

CSM