How to manage users and redo log files in Oracle Daily maintenance _oracle

I. Managing Users and Security
various objects in DB are organized and managed in a user (scenario) manner

    Select distinct object_type from dba_objects;

Schema object, schema----> user.objects (an object under a user)

Before you create objects and Access objects, you first create the owner of the object-the user
All objects under the user

Users have related properties, some must be set explicitly, and some can use default values
Common Properties: User name, password, default tablespace (default), temporary tablespace (default), account status (can default), space quota (default), profile (default), etc.
Dba_users table

Create a user

1 Determine the distribution of the user objects on the table space, thereby determining the quota for the table space

2 Determine the user's default table space and temporary table space, maintaining the independence of user data

        Create user U1 identified by U1; ---> Default users table Space and temp tablespace
        create user U2 identified by U2 default tablespace user_u2 temporary tablespace temp_u2;-- -> Specify USER_U2 table space and TEMP_U2 table space

3 Determine user password management rules and resource idle rules, set profile
Dba_users table can be found in profile file

4 Create a user, set a password

5 giving the user the necessary roles and other direct permissions
General to set connect, resource to a new user:

Grant Connect, resource to U1;
        Grant Creata session to U1;    ---> Set permissions for a session

Delete User

    Drop user U1;

If the user scheme contains objects, drop user U1 CASCADE;
Users that are currently connected cannot be deleted

Permissions

System Permissions + object permissions
Grant grants, REVOKE recycling
SYSDBA and Sysoper to grant permissions

    SELECT * from Session_privs;    ---> Query the permissions of the current user
    select * from Session_roles;    ---> Query the role of the current user
    select * from Role_sys_privs;    ---> Query the role permissions of the current user
    select * from User_sys_privs;    ---> Query the current user's system permissions

Second, redo log files
Redo log files redo logs or online logs
Archived Log Files Archive logs
Alert Log Files Alert logs
Trace files user_dump_dest User Background_dump_dest process

Redo log Files

Inquire

    >select * from V$logfile;   

Status column: Invalid The file is inaccessible (a new logfile that does not exist or is added to the group) |stale content is not fully |deleted no longer used

    >select * from V$log;    

Status column: Unused not written to |current current group, active |active not current group, active, possibly archived or not |clearing rebuilding empty log (executing ALTER DATABASE clear logfile) becomes unused state when finished

Role

Transactions can be processed again when a database failure occurs
Records all changes made to the data, providing a recovery mechanism that can be divided into groups, at least two groups, with at least one member under each group file

Write logs in group order, G1 (F1-f2-f3-......) -G2 (f1-f2-f3-......) -G3 (f1-f2-f3-......) -......
Log switching: G1-g2-g3-...... Auto Switch manual switch (ALTER system switch logfile)

Planning

Decentralized principle: Multiple members per group, members are backed up, separated to different disks. Example:

Size principle: Switch time between groups to meet the business needs of about 20 minutes

Delete or Decrease

New Group

    ALTER DATABASE add logfile [group <x0>] (' <logfile path&name> ', ',......) size <y>, [group <x1>] ...

Add members

    ALTER DATABASE add logfile member ' <logfile path&name> ' to group <x0>, ' <logfile path&name> ' ...

Delete a group

    ALTER DATABASE drop logfile group <X>, Group ...

Active state and current state cannot be deleted
Delete a member

    ALTER DATABASE drop logfile member ' <logfile path&name> ', ' <logfile path&name> ',......;

Renaming members (log movement can be done, etc.)

    Ho CP <old> <new>
    ALTER DATABASE rename file ' <old> ' tp ' <new> ';

The current state cannot be renamed in archive mode, and can be changed in non-archive mode

"ALTER DATABASE noarchivelog|archivelog;"

Exception Handling

Log inconsistency issues at startup

Active state and current state are not available

    ALTER DATABASE clear unarchived logfile Group <X>; 

Log file missing

    ALTER DATABASE clear logfile Group <X>;

When you cannot clear in archive mode,

    Recover database using Backup controlfile;alter database open resetlog;

When the non-archive mode is not clear,

    Alter system set "_allow_resetlogs_corruption" =true scope=spfile;

Startup

    alter system reset "_allow_resetlogs_corruption" scope=spfile sid= ' * ';